By the Oracle Global temp Table Pit

Today 4 o'clock in the morning, the person in charge of the scene of the customer called me, said very strange, the download function is fast and slow. This download is very complex, and I've been optimizing it before, turning on the computer in half-dreaming, and watching the field colleagues work in PL/SQL developer remotely. Execute the same SQL, fast and slow, about 0.6s, slow time more than 1 minutes.

This SQL has called a function that dynamically generates nearly 200 query statements, and there are bound variables in SQL. is the field test environment, just deployed, thought should not be caused by database load.

1. Fetch database AWR report , no pressure at all, database server configuration is leverage. This is a bit of a mess at the moment, the first time you encounter this problem. The scene 9 o'clock to demonstrate with the customer, at this time already almost 5 o'clock.

2. artifact appearance , intended to use 10046 trace to locate the problem is that SQL, trace several times, only once is slow. There are also episodes, the scene is not very likely to use Sqlplus, interactive a lot of time. Cobwebs from a multitude of SQL , and finally to SQL, the contrast is:

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. Analyzing the problem, the first feeling is that there is a problem with the SQL logic, but 10046 there is no trace to the execution plan, but read the logical reading, the slow one should have produced a Cartesian product. After a simple check, SQL logic does not have a problem, people's first feeling is not necessarily reliable.

4. I was wondering what led to the implementation plan is not allowed, suddenly think of Rel_oid_classify is a global temporary table, quickly think of a possible,rel_oid_classify statistical information is not allowed to lead, through User_ Tables view this table is not statistical information. That is, every execution is dynamically collected, executed in Oracle 11g Autotrace, found level=2, I would like to try to put the dynamic sampling level, say dry.

SELECT/*+ dynamic_sampling (T) */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;

To the developer, modify the relevant function. After multiple tests, the problem is resolved. It was almost 7 o'clock, and it was already bright, and I felt a little sleepy, but could not sleep again.

Summary: For this temporary table problem, I think the problem is that the sampling rate is low after the consequences of the result. For global temporal tables, be aware of two points, one is to lock the temporary table to collect statistics, because you collect statistics is definitely wrong, and second, it is best to use dynamic adoption. The basis of learning knowledge is very important.

By the Oracle Global temp Table Pit