耗时较长的SQL
select msn.serial_number, msi.description from mtl_system_items_tl msi , mtl_serial_numbers msn where ( UPPER(SERIAL_NUMBER) LIKE :1 AND (SERIAL_NUMBER LIKE :2 OR SERIAL_NUMBER LIKE :3 OR SERIAL_NUMBER LIKE :4 OR SERIAL_NUMBER LIKE :5)) AND ( msn.current_organization_id = msi.organization_id and msn.inventory_item_id = msi.inventory_item_id and msn.inventory_item_id = nvl( :6, msn.inventory_item_id) /* and msn.current_organization_id = :parameter.org_id */ and msi.language = userenv('LANG') ) order by serial_number call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 27.61 375.71 426621 429306 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 27.61 375.72 426621 429306 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 44 (APPS) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY (cr=429306 pr=426621 pw=0 time=375714574 us) 1 CONCATENATION (cr=429306 pr=426621 pw=0 time=375714503 us) 1 FILTER (cr=429306 pr=426621 pw=0 time=375714488 us) 1 NESTED LOOPS (cr=429306 pr=426621 pw=0 time=375714482 us) 1 TABLE ACCESS FULL MTL_SERIAL_NUMBERS (cr=429302 pr=426620 pw=0 time=375707835 us) 1 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_TL (cr=4 pr=1 pw=0 time=6639 us) 1 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_TL_U1 (cr=3 pr=0 pw=0 time=29 us)(object id 118408) 0 FILTER (cr=0 pr=0 pw=0 time=2 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MTL_SERIAL_NUMBERS (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN MTL_SERIAL_NUMBERS_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 213230) 0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_TL (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_TL_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 118408) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 SORT (ORDER BY) 1 CONCATENATION 1 FILTER 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'MTL_SERIAL_NUMBERS' (TABLE) 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_TL' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'MTL_SYSTEM_ITEMS_TL_U1' (INDEX (UNIQUE)) 0 FILTER 0 NESTED LOOPS 0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MTL_SERIAL_NUMBERS' (TABLE) 0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'MTL_SERIAL_NUMBERS_U1' (INDEX (UNIQUE)) 0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_TL' (TABLE) 0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'MTL_SYSTEM_ITEMS_TL_U1' (INDEX (UNIQUE)) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 db file sequential read 56 0.02 0.55 db file scattered read 26778 0.49 355.63 latch free 3 0.00 0.00 SQL*Net message from client 1 0.00 0.00
表数据量:
mtl_system_items_b:650647
mtl_serial_numbers:10947015
分析:
where子句中大量使用OR关联词,
SERIAL_NUMBER LIKE :2 OR SERIAL_NUMBER LIKE :3 OR SERIAL_NUMBER LIKE :4 OR SERIAL_NUMBER LIKE :5
OR关联词在CBO解析时,会变形(EXPAND)为UNION ALL的关联语句。
如:select * from t where P or Q 会展开成类似下面的语句:
select * from t where P
union all
select * from t where Q and NOT(P)
这样的变形在有的时候会引起性能问题。
解决方法:
引入/*+ no_expand */ hint到sql中,no_expand 的含义是不执行OR-expansion。
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides the cost is lower than not using it. NO_EXPAND is the opposite of USE_CONCAT. Use the no-expand when it goes faster. The optimizer might have many more permutations to consider with a OR-expansion then without -- leading to a longer parse time and perhaps a less optimal plan (if it gives up soon enough).
Doc:http://docs.oracle.com/cd/E11882_01/server.112/e10592/sql_elements006.htm#SQLRF50502
Description of the illustration no_expand_hint.gif
(See "Specifying a Query Block in a Hint")
The NO_EXPAND
hint instructs the optimizer not to consider OR
-expansion for queries having OR
conditions or IN
-lists in the WHERE
clause. Usually, the optimizer considers using OR
expansion and uses this method if it decides that the cost is lower than not using it. For example:
SELECT /*+ NO_EXPAND */ * FROM employees e, departments d WHERE e.manager_id = 108 OR d.department_id = 110;
修复后:
select /*+ no_expand*/ msn.serial_number, msi.description from mtl_system_items_tlmsi , mtl_serial_numbers msn where (UPPER(SERIAL_NUMBER) LIKE :1 AND (SERIAL_NUMBER LIKE :2 OR SERIAL_NUMBERLIKE :3 OR SERIAL_NUMBER LIKE :4 OR SERIAL_NUMBER LIKE :5)) AND( msn.current_organization_id = msi.organization_id andmsn.inventory_item_id =msi.inventory_item_id and msn.inventory_item_id = nvl( :6, msn.inventory_item_id)/* and msn.current_organization_id = :parameter.org_id*/ and msi.language = userenv('LANG') ) order by serial_number call count cpu elapsed disk query current rows ------------- -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 4 0 0 Fetch 1 0.00 0.08 4 9 0 1 ------------- -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.08 4 13 0 1 Misses in librarycache during parse: 1 Misses in librarycache during execute: 1 Optimizer mode:ALL_ROWS Parsing user id:44 (APPS) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 0 SORT (ORDER BY) 0 NESTED LOOPS 0 TABLE ACCESS MODE: ANALYZED (BY INDEXROWID) OF 'MTL_SERIAL_NUMBERS'(TABLE) 0 INDEX MODE: ANALYZED (RANGE SCAN)OF 'MTL_SERIAL_NUMBERS_N12'(INDEX) 0 TABLE ACCESS MODE: ANALYZED (BY INDEXROWID) OF 'MTL_SYSTEM_ITEMS_TL'(TABLE) 0 INDEX MODE: ANALYZED (UNIQUE SCAN)OF 'MTL_SYSTEM_ITEMS_TL_U1'(INDEX (UNIQUE)) Elapsed timesinclude waiting on following events: Eventwaitedon Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Netmessage toclient 1 0.00 0.00 db filesequentialread 4 0.05 0.08 SQL*Netmessage fromclient 1 0.01 0.01 ********************************************************************************