你好,游客 登录 注册 搜索
背景:
阅读新闻

NO_EXPAND Hint性能优化一例 - PTIAN ORACLE EBS REPOSITORY

[日期:2013-04-19] 来源:  作者: [字体: ]

耗时较长的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。

Tom Kyte:

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

NO_EXPAND Hint

Description of no_expand_hint.gif follows
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

********************************************************************************

 






收藏 推荐 打印 | 录入:admin | 阅读:
相关新闻