is a global temporary table pit by Oracle.

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. Run the same SQL, fast time is slow, fast time is 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 scene of the 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. It's a little messy at the moment, the first time I have such a 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 SQL has a problem, trace several times, only once is slow. There are also episodes, the scene is not very likely to use sqlplus, the interaction of a lot of time. Cobwebs from a multitude of SQL , and eventually to SQL, the comparison 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. Analyze the problem, the first feeling is that the SQL logic is not a problem, but 10046 inside no trace to run the plan, just read the logical reading, the slow one should be produced 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 operation of the plan is not allowed, suddenly think of rel_oid_classify is the global temporary table, high-speed thinking of a possible,rel_oid_classify statistical information is not allowed to lead, through the User_ Tables view this table is not statistical information. That's the dynamic collection of every run, running Autotrace in Oracle 11g, and discovering level=2, I'd like to try the level of dynamic sampling and 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;

Send to the developer and change the relevant function. After the increment, the problem was solved after many tests. 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 sample rate is low after the consequences. For the global temporary table to pay attention to two points, one is to lock the temporary table to collect statistics, because you collect statistics is certainly wrong, and second, it is best to use dynamic use. The basis of learning knowledge is very important.

is a global temporary table pit by Oracle.