Oracle SQL Optimization Summary

Two consecutive companies as a foreign staff to the client side work, lack of sense of belonging at the same time, the database technology love is my only consolation, after all, this is their favorite thing, but also can do.

Because of the needs of the client project, I started to contact Oracle, most of which worked on workflow optimization and business data troubleshooting. To do this job better, I have consulted Oracle talent, oracle.10g performance analysis and optimization ideas, database design and optimization based on massive data, and case study of SQL optimized video. Basically I am working in contact with Oracle SQL optimization, based on long time to do SQL optimization work, now do a summary of Oracle's SQL optimization.

It is known that the oracle10g execution plan uses the Cbo:cost-based optimization-based optimizer. Of course, the Oracle 10G running SQL Execution plan is definitely the system to think of the best, many times there is no way to see the optimization point, no matter how the change is not better than the original performance. What do we do? Every time I still look at the implementation of the plan to study half a day, many times this is not needed, or there is a faster way to find the problem.

Of course, everyone's habits and optimization ideas will be different, the processing of optimization problems will be different. Based on my recent optimization of a program of four modification points, the program has the original 7 hours or so shortened to about 1 hours of execution. Summarize my steps and optimization ideas.

1, direct observation table data volume and table structure attributes, do not rigidly consider the optimal execution plan (big data table using full Table Hash association)

Abbreviated Original sql:

SELECT count (1)
From Z_mid_r3_bom_org1 A
WHERE trunc (A.DATUV) <=trunc (sysdate) and trunc (a.datub) >=trunc (sysdate)
and exists (select 1 from Rpt_fp_shortage_order C
where InStr (A.NAME,C.MTM) >0
and a.werks= C.siteid)
and exists (select 1 from Mst_bomcomponentsalt b
where INSTR (b.bomid,a.name) >0
and A.idnrk=b.alternateitem
and A.werks=b.siteid);

The actual result set for this SQL is probably in the 20多万条 record, and the result set is inserted into another table. The amount of data in the Z_MID_R3_BOM_ORG1 table and Mst_bomcomponentsalt table is recorded in alligators, and the amount of data in the Rpt_fp_shortage_order table is about 7,000.

Execution Plan:

SELECT STATEMENT all_rowscost:2,595 bytes:64 cardinality:1 5 FILTER 1 TABLE ACCESS Full TABLE prdabppcdb. Z_mid_r3_bom_org1 cost:1,198 bytes:55,616 cardinality:869 2 table ACCESS full table prdabppcdb. Rpt_fp_shortage_order cost:3 bytes:17 cardinality:1 4 table ACCESS by INDEX ROWID TABLE prdabppcdb. Mst_bomcomponentsalt cost:91 bytes:48 cardinality:1 3 index FAST full SCAN index PRDABPPCDB. s9525213703_2688 Cost:3 cardinality:195 by the implementation plan, prdabppcdb. The Mst_bomcomponentsalt table uses a fast index scan, which appears to be optimal. The Available Table Property index is already in use. But with nearly 200,000 records, the observation log found that this step was inserted in a fast 2-hour period. With the full Table hash Association, the modified Insert table takes approximately 3 minutes to complete.

Modified sql:

SELECT count (1)  
from Z_mid_r3_bom_org1 A
WHERE trunc (A.DATUV) <=trunc (sysdate)  and trunc (A. datub) >=trunc (sysdate)
and exists (select 1 from Rpt_fp_shortage_order c 
where InStr (A.NAME,C.MTM) > 0
and a.werks= C.siteid)
and exists (select/*+full (b) */ 1 from Mst_bomcomponentsalt B
where INSTR ( B.bomid,a.name) >0
and A.idnrk=b.alternateitem
and A.werks=b.siteid);

Modified Execution Plan: Planselect STATEMENT  all_rowscost:8,748  bytes:111  cardinality:1                      6 SORT AGGREGATE  bytes:111  cardinality:1     &NBS P                5 FILTER                &NB Sp     3 hash JOIN semi  cost:8,676  bytes:111  cardinality:1     &NBSP;&NBSP ;               1 table ACCESS full table prdabppcdb. Z_mid_r3_bom_org1 cost:2,493  bytes:107,280  cardinality:1,788            &NBSP ;     2 table ACCESS full table prdabppcdb. Mst_bomcomponentsalt cost:6,172  bytes:52,636,539  cardinality:1,032,089          & nbsp   4 table ACCESS full table prdabppcdb. Rpt_fp_shortage_order cost:72  bytes:4,389  cardinality:133  

2, split SQL, segmented execution to observe the entire SQL statement block, targeted optimization

The optimization here is to establish a function index on the table z_dim_product (about 200,000 of the data volume), Lpad (product,18, ' 0 ')

Abbreviated SQL:

SELECT MAX (Hier11_code)
From Z_dim_product
WHERE substr (product,length (product) -9,10) = B.item (table b is another associated table)

Modified sql:

SELECT MAX (Hier11_code)
From Z_dim_product
WHERE Lpad (product,18, ' 0 ') = Lpad (b.item,18, ' 0 ')

3, large table optimization to fully utilize the partition and partition index

4, from the business perspective to adjust the logic, rewrite SQL, this method is often the most effective in all optimization.

..........

The next 3\4 method does not upload SQL, there is time to continue to record the personal optimization summary

Oracle SQL Optimization Summary