about the author:
Ground is strong, once a ITPUB MS version of the master, current Oracle Database Management Master. I have been working in a country
Enterprise, serving the tobacco industry, developed a large ERP system based on Oracle database, a national information industry department
Key projects. At the same time, it is responsible for Oracle Database Management and Optimization, and provides Oracle database management for many tobacco companies.
Optimization and technical support. At present, we must serve a telecom value-added business system provider enterprise in Beijing, responsible for database business. tube
More than 30 provincial database platforms in the country. Practical experience is rich, longer than database diagnosis, optimization and SQL adjustment. hope
Hope to learn from the level of Oracle technology.
Mail:
Eygle@itpub.net
The following is the whole process of SQL optimization analysis, once sent related posts on ITPUB, now sorted
Under, add a detailed description, I hope some help to everyone.
Environmental description:
Database Version: Oracle8.1.7.4
Platform: HP-UX11i
The following is a piece of code obtained from the programmer, the developer complained that this code is slow, I execute this code
Get an execution plan, the analysis is as follows:
This is the execution plan and statistics of the code:
SQL> SELECT "sp_trans". "TRANS_NO", 2 "sp_trans_sub". "Item_code", 3 "sp_item", 4 "sp_item". "Chart_id", 5 "sp_item". "Specification", 6 "sp_trans_sub "COUNTRY", 7 "sp_trans_sub". "Qty", 8 "sp_Trans_sub". "Price", 9 "sp_trans". "Vendor_code", 10 "sp_trans". "Pay_Mode", 11 NVL ("sp_TRANS_SUB". " Pay_Qty ", 0), 12 0 as pay_this13 from" sp_item ", 14" sp_trans ", 15" sp_trans_sub "." TRANS_NO "=" sp_trans "." TRANS ") and17 (" sp_item "." Item_code "=" sp_trans_sub "." Item_code ") and18 (" sp_trans "." Vendor_code "= '20011021023') 19/8 rows selected.Execution: 00: 00: 00.51execution PLAN -------------------------- -------------------------------------------------- 0 SELECT STATEMENT Optimizer = CHOOSE1 0 NESTED LOOPS2 1 NESTED LOOPS3 2 TABLE ACCESS (FULL) OF 'SP_TRANS'4 2 TABLE ACCESS (BY INDEX ROWID) OF' SP_TRANS_SUB'5 4 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) 6 1 Table Access (by index rowid) of 'sp_item'7 6 index (unique scan) of' pk_sp_item '(unique) statistics ----------------------- ------------ ------------------------- 0 Recursive Calls4 DB Block Gets323 Consistent Gets0 Physical Reads0 Redo Size1809 Bytes Sent Via SQL * Net To Client425 Bytes Received Via SQL * Net from Client2 SQL * NET ROUNDTRIPS TO / FROM Client0 Sorts (Memory) 0 Sorts (Disk) 8 Rows Processed This section is used to follow the supplier in 2003 that started by the process (very small amount), query time Approximately 0.51 seconds.
There were no analysis of these tables before, and the database selection is the RBO optimizer.
In order to speed up the execution of the code, Analyze Related Tables: SQL> Analyze Table SP_TRANS_SUB Compute Statistics; Table Analyzed.elapsed: 00: 00: 30.64SQL> SELECT "sp_trans". "TRANS_NO", 2 "sp_trans_sub". "Item_code", 3 " SP_Item "." Item_name ", 4" sp_item "." Sp_Id ", 5" sp_item ", 6" sp_trans_sub ", 7" sp_trans_sub "." Qty ", 8" sp_trans_sub "." Price ", 9" sp_trans "." Vendor_code ", 10" sp_trans "." Pay_mode ", 11 nVL (" sp_trans_sub "." Pay_qty ", 0), 12 0 as pay_this13 from" sp_item ", 14" sp_trans_sub ", 15" SP_TRANS "16 where (" sp_trans_sub "." TRANS_NO "=" sp_Trans "." TRANS_NO ") and17 (" sp_item "." Item_code "=" sp_trans_sub "." Item_code ") and18 (" SP_TRANS "." Vendor_code "= '20011021023') 19/8 rows selected.ELAPSED: 00: 00: 06.49EXECUTION Plan -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- 0 Select Statement Optimizer = Choose (COST = 18577 Card = 126726520bytes = 30034185240) 1 0 Merge Join (COST = 18577 card = 126726520 bytes = 30034185240) 2 1 sort (join) (cost = 14722 card = 310300 BYtes = 20790100) 3 2 hash join (COST = 358 card = 310300 BYT ES = 20790100) 4 3 Table Access (Full) of 'sp_trans' (COST = 43 Card = 229 bytes = 8473) 5 3 Table Access (Full) Of 'sp_TRANS_SUB' (COST = 158 Card = 135502 BYtes = 4065060) 6 1 Sort (join) (cost = 3855 card = 40840 bytes = 6942800) 7 6 Table Access (full) of 'sp_item' (COST = 77 card = 40840 bytes =
6942800) Statistics ------------------------------------------------- ----------- 150 recursive calls89 db block gets1837 consistent gets755 physical reads60 redo size1732 bytes sent via SQL * Net to client425 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client4 sorts (memory ) 1 sorts (disk) 8 rows processedSQL> SQL> analyze table sp_trans compute statistics; Table analyzed.Elapsed: 00: 00: 13.00SQL> SQL> SELECT "SP_TRANS" "TRANS_NO", 2 "SP_TRANS_SUB" "ITEM_CODE",.. 3 "sp_item". "Item_name", 4 "sp_item". "Chart_id", 5 "sp_item". "Specification", 6 "sp_trans_sub", 7 "sp_trans_sub". "Qty", 8 "sp_trans_sub". "Price", 9 "sp_trans". "Vendor_code", 10 "sp_trans". "Pay_mode", 11 nVL ("sp_trans_sub". "Pay_qty", 0), 12 0 as pay_this13 from "sp_item", 14 "sp_trans_sub", 15 "sp_Trans". "TRANS_NO" = "sp_trans". "TRANS_NO") and17 ("sp_item". "Item_code" = "sp_trans_sub". "Item_code") and18 (("sp_trans". "Vendor_Code "= '20011021023')) 19/8 rows selected.ELAPSED: 00: 00: 01.62EXE CUTION Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 1453 Card = 447198 BYtes = 101066748) 1 0 NESTED LOOPS (COST = 1453 Card = 447198 BYtes = 101066748) 2 1 Hash Join (COST = 358 Card = 1095 bytes = 61320) 3 2 Table Access (Full) of 'sp_trans' (COST = 43 Card = 273 bytes = 7098) 4 2 Table Access (Full) of 'sp_trans_sub' (COST =
158 card = 135502 BYtes = 4065060) 5 1 Table Access (by index rot = 40840 bytes = 6942800) 6 5 index (unique scan) of 'pk_sp_item' statistics --- -------------------------------------------------- ----- 0 recursive calls8 db block gets1344 consistent gets0 physical reads0 redo size1824 bytes sent via SQL * Net to client425 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client3 sorts (memory) 0 sorts (disk) 8 rows processedSQL> SQL> analyze table sp_item compute statistics2 / Table analyzed.Elapsed: 00: 00: 11.67SQL> SELECT "SP_TRANS" "TRANS_NO", 2 "SP_TRANS_SUB" "ITEM_CODE", 3 "sP_ITEM" "ITEM_NAME"... 4 "sp_item". "Chart_id", 5 "sp_item". "Specification", 6 "sp_trans_sub". "Country", 7 "sp_trans_sub". "Qty", 8 "sp_trans_sub". "Price", 9 "sp_trans" "Vendor_code", 10 "sp_trans". "Pay_mode", 11 nVL ("sp_trans_sub". "Pay_qty", 0), 12 0 as pay_this13 from "sp_item", 14 "sp_trans_sub", 15 "sp_trans" 16 where (" SP_TRANS_SUB "." TRANS_NO "=" sp_trans "." TRANS_NO ") and17 (" sp_item " "Tem_code" = "sp_trans_sub". "Item_code") and18 ("SP_TRANS". "Vendor_code" = '20011021023') 19/8 rows selected.Execution: 00: 00: 01.43execution PLAN ------------ -------------------------------------------------- --0 Select Statement Optimizer = Choose (COST = 687 Card = 1362 BYtes = 128028) 1 0 Hash Join (COST = 687 Card = 1362 bytes = 128028) 2 1 Hash Join (COST = 358 Card =
1362 BYtes = 76272) 3 2 Table Access (Full) of 'sp_trans' (COST = 43 Card = 273 BYtes = 7098) 4 2 Table Access (Full) of 'sp_TRANS_SUB' (COST = 158 Card = 135502 BYtes = 4065060) 5 1 Table Access (Full) of 'sp_item' (COST = 77 Card = 29547 bytes = 1122786) statistics ------------------------------------------------------------------------------------------------------------------------------------ ------------------------------ 0 Recursive Calls12 DB Block Gets1820 Consistent Gets0 Physical Reads0 Redo Size1732 Bytes Sent Via SQL * Net To Client425 Bytes Received Via SQL * NET ROM Client2 SQL * Net RoundTrips To / from Client3 Sorts (Memory) 0 Sorts (Disk) 8 Rows Processed After all tables have been analyzed, we found that the last oracle gave me three full mete scans. Moreover, from DBBLOCK GETS, CONSISTENT GETS all "improve".
Then I built an index on the vendor_code column, trying to speed up the execution of the code by indexing (in practical application)
In, by adding an appropriate index to speed up SQL execution is a common method, especially in RBO mode, but adding
Clarister may change many relevant SQL execution plans, whether the exact performance is improved, and the corresponding test is required.
Certificate, Oracle9i provides index monitors, you can pass Alter Index .. monitoring usage statement
To collect information information of the index to confirm that the index is used normally):
SQL> CREATE INDEX IDX_VENDOR ON SP_TRANS (vendor_code); index has been created. Time: 00: 00: 02.03SQL> SELECT "sp_trans". "TRANS_NO", 2 "sp_Trans_Sub". "Item_code", 3 "sp_item". "Item_name", 4 "sp_item". "Chart_id", 5 "sp_item "" Specification ", 6" sp_trans_sub "." Country ", 7" sp_trans_sub "." Qty ", 8" sp_trans_sub "." Price ", 9" sp_trans "." Vendor_code ", 10" sp_trans "." Pay_mode "." PAY_MODE " 11 NVL ("sp_trans_sub". "Pay_qty", 0), 12 0 as pay_this13 from "sp_item", 14 "sp_trans_sub", 15 "sp_trans_sub". "TRANS_NO" = "sp_trans". "TRANS_NO ") and17 (" sp_item "." Item_code "=" sp_trans_sub "." Item_code ") and18 (" SP_TRANS "." Vendor_code "= '20011021023') 19 / Selected 8 lines.
Time: 00: 00: 01.42EXECUTION Plan --------------------------------------- ------------------- 0 Select Statement Optimizer = choose (cost = 646 card = 1362 bytes = 128028) 1 0 Hash Join (COST = 646 card = 1362 bytes = 128028 2 1 hash join (COST = 317 card = 1362 bytes = 76272) 3 2 Table access (by index rot = 273 bytes = 7098) 4 3 index (Range scan) of 'idx_vendor '(COST = 1 Card = 273) 5 2 Table Access (Full) of' Sp_Trans_Sub '(COST = 158 Card = 135502 BYtes = 4065060) 6 1 Table Access (Full) Of' sp_Item '(COST = 77 card = 29547 bytes = 1122786) statistics ----------------------------------------- ----------------- 0 recursive calls8 db block gets1546 consistent gets1 physical reads0 redo size1732 bytes sent via SQL * Net to client425 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client3 sorts (Memory) 0 Sorts (Disk) 8 Rows ProcessedSQL> Now the database uses this index, the execution plan is a little better, but the effect is still unsatisfactory. Then I deleted the index created, add Rule Tips, which we got the initial implementation plan.
SQL> SELECT / * Rule * / "sp_trans". "TRANS_NO", 2 "Item_code", 3 "sp_item". "Item_name", 4 "sp_item". "Chart_id", 5 "sp_item". "." Specification, 6 "sp_trans_sub". "Country", 7 "sp_Trans_sub". "Qty", 8 "sp_Trans_Sub". "Price", 9 "sp_trans". "Vendor_code", 10 "sp_trans". "Pay_mode", 11 NVL ("Sp_trans_sub". "Pay_qty", 0), 12 0 as pay_this13 from "sp_item", 14 "sp_Trans_Sub", 15 "sp_trans_sub". "TRANS_NO" = "sp_trans". "TRANS_NO") and17 ("Sp_item". "Item_code" = "sp_trans_sub". "Item_code") and18 ("sp_trans". "Vendor_code" = '20011021023') 19 / Selected 8 lines. Time: 00: 00: 00.7Execution Plan --------------------------------------- ------------------- 0 Select Statement Optimizer = Hint: rule1 0 Nested Loops2 1 NESTED LOOPS3 2 Table Access (Full) of 'Sp_Trans'4 2 Table Access (by Index ROWID) OF 'SP_TRANS_SUB'5 4 INDEX (RANGE SCAN) OF' PK_SP_TRANS_SUB '(UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF' SP_ITEM'7 6 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE) Statistics-- -------------------------------------------------- ------ 0 recursive calls4 db block gets323 consistent gets0 physical reads0 redo size1809 bytes sent via SQL * Net to client426 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client0 sorts (memory) 0 sorts (disk 8 Rows ProcessedSQL> Then we create this index again, and the time drawn is greatly shortened (add the necessary index to the database query for performance issues, is one of the important means of DBA solving problems):
SQL> CREATE INDEX IDX_VENDOR ON SP_TRANS (vendor_code); index has been created. Time: 00: 00: 02.43SQL> SELECT / * Rule * / "sp_TRANS". "TRANS", 2 "sp_trans_sub". "Item_code", 3 "sp_item". "Item_name", 4 "sp_item". " Chart_id ", 5" sp_item "." Specification ", 6" sp_trans_sub "." Country ", 7" sp_trans_sub "." Qty ", 8" sp_trans_sub "." Price ", 9" sp_trans "." Vendor_code ", 10" SP_TRANS "." PAY_MODE ", 11 NVL (" sp_trans_sub "." Pay_qty ", 0), 12 0 as pay_this13 from" sp_item ", 14" sp_trans_sub ", 15" sp_trans_sub "." TRANS_NO "= "Sp_Trans". "TRANS_NO") and17 ("sp_item". "Item_code" = "sp_trans_sub". "Item_code") and18 ("sp_trans". "Vendor_code" = '20011021023') 19 / Selected 8 lines.
Time: 00: 00: 00.31EXECUTION Plan --------------------------------------- ------------------- 0 Select Statement Optimizer = Hint: rule1 0 Nested Loops2 1 Nested Loops3 2 Table Access (By Index Rowid) of 'Sp_Trans'4 3 Index (Range Scan) Of 'IDX_VENDOR' (Non-Unique) 5 2 Table Access (By Index RowID) of 'Sp_Trans_Sub'6 5 Index (Range Scan) Of' PK_SP_TRANS_SUB '7 1 Table Access (By Index RowID) of' Sp_Item '8 7 index (unique scan) of' pk_sp_item '(unique) statistics ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ 0 Recursive Calls0 DB Block Gets49 Consistent Gets1 Physical Reads0 Redo Size1809 Bytes Sent Via SQL * Net To Client426 Bytes Received Via SQL * NET From client2 SQL * Net RoundTrips To / from Client0 Sorts (Memory) 0 Sorts (Disk) 8 Rows ProcessedSQL> However, this is not the fastest, this is RBO we modify the initial_index_cost_adj this initialization parameter, then view the SQL executive
Draw.
SQL> ALTER session set Optimizer_index_cost_adj = 302 / session has changed. Time: 00: 00:00.20sql> SELECT "sp_TRANS". "TRANS_NO", 2 "sp_Trans_Sub". "Item_code", 3 "sp_item", "Item_Name", 4 "sp_item". "Chart_id", 5 "sp_item "" Specification ", 6" sp_trans_sub "." Country ", 7" sp_trans_sub "." Qty ", 8" sp_trans_sub "." Price ", 9" sp_trans "." Vendor_code ", 10" sp_trans "." Pay_mode "." PAY_MODE " 11 NVL ("sp_trans_sub". "Pay_qty", 0), 12 0 as pay_this13 from "sp_trans", 14 "sp_item", 15 "sp_trans_sub" 16 where ("sp_trans_sub". "TRANS_NO" = "sp_trans". "TRANS_NO ") and17 (" sp_item "." Item_code "=" sp_trans_sub "." Item_code ") and18 (" SP_TRANS "." Vendor_code "= '20011021023') 19 / Selected 8 lines.
Time: 00: 00: 00.11Execution Plan --------------------------------------- ------------------- 0 Select Statement Optimizer = Choose (COST = 658 card = 1095 bytes = 102930) 1 0 NESTED LOOPS (COST = 658 Card = 1095 BYtes = 102930 2 1 nested loops (cost = 329 card = 1095 bytes = 61320) 3 2 Table access (by index rowid) of 'sp_trans' (COST = 1 card = 273 bytes = 7098) 4 3 INDEX (RANGE SCAN) of' IDX_VENDOR '(Cost = 1 card = 273) 5 2 Table access (by index rot = 135502bytes = 4065060) 6 5 index (Range scan) of' pk_sp_trans_sub '(unique) (COST = 3 card = 135502) 7 1 Table Access (by index rot = 29547 bytes = 1122786) 8 7 Index (unique scan) of 'pk_sp_item' statistics - -------------------------------------------------- ------ 0 recursive calls0 db block gets49 consistent gets0 physical reads0 redo size1809 bytes sent via SQL * Net to client426 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client0 sorts (memory) 0 sorts (disk 8 Rows ProcessedSQL > Let's take a look at the huge impact of the following two parameters for CBO: Optimizer_index_caching
This initialization parameter represents a percentage, with a value between 0 and 99.
The default is 0, and when the CBO uses the index to access the data, the ratio of the data in memory is 0%, which is 0%.
It means that access data will be required to generate physical read and expensive at cost by indexing access. If you use default settings, Oracle
When evaluating costs, many times will choose a full table scan.
Optimizer_index_cost_adj
This initialization parameter represents a percentage, with a value between 1 and 10,000.
This parameter represents a table of index scanning and full mete scanning costs. The default value 100 indicates that the index scanning cost is equal to
Table scan.
These parameters have a significant impact on the implementation of CBO, and the default value is usually adjusted for the database.
Generally speaking, I can set to about 90 for optimizer_index_caching.
For most OLTP systems, Optimizer_Index_cost_adj can be set between 10 and 50. For numbers
According to the warehouse and DSS system, it may not be simple to set optimizer_index_cost_adj to 50, usually I
We need repeated adjustments to achieve a reasonable value.
SQL> DROP INDEX IDX_VENDOR; index has been discarded. Time: 00:00:00.61sql> / Has selected 8 lines. Time: 00: 00: 00.11Execution Plan --------------------------------------- ------------------- 0 Select Statement Optimizer = choose (cost = 700 card = 1095 bytes = 102930) 1 0 NESTES = 700 card = 1095 BYtes = 102930 2 1 nested loops (cost = 371 card = 1095 bytes = 61320) 3 2 Table access (full) of 'sp_trans' (COST = 43 card = 273 bytes = 7098) 4 2 Table access (by index rowid) of' sp_trans_sub (COST = 2 Card = 135502bytes = 4065060) 5 4 Index (Range Scan) of 'PK_SP_TRANS_SUB' (COST = 3 CARD = 135502) 6 1 Table Access (by index rowid) of 'sp_item' (COST = 1 Card = 29547 bytes = 1122786) 7 6 INDEX (unique scan) of 'pk_sp_item' (unique) statistics ---------------------------- ------------------------------ 0 Recursive Calls4 DB Block Gets323 Consistent Gets0 Physical Reads0 Redo Size1809 Bytes Sent Via Sql * Net To Client426 Bytes Received VIA SQL * NET ROM Client2 SQL * NET ROTTRIPS TO / FROM Client0 Sorts (Memory) 0 Sorts (Disk) 8 Rows ProcessedSQL> Related Documents: The following documents are quite good reading materials, interested, you can read carefully:
About Optimizer_Index_cost_adj and the like affecting the parameters and settings of CBO:
http://www.evdbt.com/searchintelligencecbo.doc
For the calculation of cost, please refer to the following article:
http://www.centrexcc.com/a look under The HOOD OF CBO -2 The 1
0053% 20event.pdf
http://www.centrexcc.com/a look under The HOOD OF CBO -2 The 1
0053% 20event.ppt
For CBO, please refer to the following documentation:
Http://metalink.oracle.com/metalink/plsql/ml2_documents.showdocument?p_database
_id = NOT & P_ID = 35934.1
http://www.itpub.net/showthread.php?threadid=88905
For the setting of the implementation plan, please refer to:
