Oracle global temporary table pitfalls

Oracle global temporary table pitfalls

The person in charge at the customer's site called me at a.m. and said it was strange that the downloading feature was slow. This download feature is very complex and I have been optimizing it for a long time. When I was awake, I turned on my computer and remotely watched my colleagues operate in PL/SQL developer. If you run the same SQL statement, the time is slow, the time is about 0.6 s, and the time is longer than 1 minute.

This SQL statement calls a function to dynamically generate nearly 200 query statements. All SQL statements contain variable binding. It is a test environment on site. It was just deployed and thought it should not be caused by database load.

1. Capture the AWR Report of the database. There is no pressure at all, and the configuration of the database server is excellent. This is a bit messy at the moment. This was the first time I encountered this problem. The customer will be presented at nine o'clock, and it will be almost five o'clock.

2. It is intended to use 10046 trace to locate the problem with the SQL statement, which is slow only once. There were also episodes during this period, and sqlplus was not very useful at the scene, which made interaction a lot of time. From the numerous SQL statements, I finally located the SQL statement. The comparison is as follows:

Select distinct d. ID, D. TABLE_NAME, DCT. COLUMN_NAME, GG. DATA_TYPE,
GG. TECHPARAM_NAME, DCT. SORT_NO FROM GG_CLASSIFY_TECHPARAM DCT, GG_TECHPARAM
GG, GG_CLASSIFY D, REL_OID_CLASSIFY t where dct. TECHPARAM_ID = GG. ID AND
D. ID = DCT. CLASSIFY_ID and t. CLASSIFY_ID = D. ID
Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 61.00 61.04 0 25968917 0 156
-----------------------------------------------------------------------
Total 3 61.00 61.04 0 25968917 0 156

Call count cpu elapsed disk query current rows
-----------------------------------------------------------------------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.80 0.81 0 32461 0 156
-----------------------------------------------------------------------
Total 3 0.80 0.81 0 32461 0 156

3. analyze the problem. The first thought is whether there is a problem with the SQL logic. Unfortunately, the execution plan is not traced in 10046. However, when reading the logic, the slow one should produce a Cartesian product. After a simple check, there is no problem with the SQL logic, and people's first feeling is not necessarily reliable.

4. I was wondering what led to inaccurate execution plans. Suddenly I remembered that REL_OID_CLASSIFY is a global temporary table. It was possible that the statistical information of REL_OID_CLASSIFY was inaccurate, you can use user_tables to view the table without statistics. That is to say, every execution involves dynamic collection. Execute autotrace in Oracle 11g and find level = 2. I 'd like to try the dynamic sampling level, just do it.

SELECT/* + dynamic_sampling (T 10) */distinct d. ID, D. TABLE_NAME, DCT. COLUMN_NAME, GG. DATA_TYPE,
GG. TECHPARAM_NAME, DCT. SORT_NO FROM GG_CLASSIFY_TECHPARAM DCT, GG_TECHPARAM
GG, GG_CLASSIFY D, REL_OID_CLASSIFY t where dct. TECHPARAM_ID = GG. ID AND
D. ID = DCT. CLASSIFY_ID and t. CLASSIFY_ID = D. ID;

Send it to developers and modify related functions. After multiple tests, the problem is solved. Now it's almost 7 o'clock, and it's already bright. I'm a little sleepy, but I can't go to bed again.

Conclusion: I think the problem with this temporary table is the consequence of a low sampling rate. Pay attention to two points for global temporary tables. First, you must lock the function of collecting statistics for temporary tables, because the statistics you collect are definitely incorrect; the second is to use it dynamically. It is important to learn knowledge.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian