A personal experience with Oracle global temporary table pit

"E Mentor Network" when using Oracle to develop a lot of times encountered some of your unexpected problems, often in the details of an unavoidable error, the following to share an unforgettable event, this time shows that the basic knowledge is really important

It was 4 o'clock in the morning and the person in charge of the customer called me and said it was strange that the download function was 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, crawl Database AWR report, completely no pressure, database server configuration is the 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, the artifact appearance, intends to use 10046 trace to locate in the end is that SQL has a problem, 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

--------------------------------------- --------------------------------
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, analysis of the problem, the first feeling is the SQL logic is not a problem, but 10046 inside no trace to the execution plan, but 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 think is what led to the implementation of the 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, through User_tables view this table is not statistical information. That is, every execution is dynamically collected, in oracle11g to perform autotrace, found level=2, I want to try to put the dynamic sampling level, say dry on 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.

Personal experience of this event summary: For this temporary table problem, I think the problem is that the sampling rate is the result of the consequences of low. 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.

Personal experience of a single Oracle global temporary table pit