Oracle 'or exists/in'結合使用引起的filter執行計劃 的優化

maohaiqing0304發表於2015-03-17
OracleFilter優化

標題： Oracle 'or exists/in'結合使用引起的filter執行計劃 的最佳化 

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]


註釋： 
    客戶反映生產環境很卡的問題，根據常用SQL檢視發現瓶頸出現在如下sql 每1小時執行上千次，每次執行73s.
    所以這對這個sql看下怎麼最佳化.

環境：
AIX
7.1
 >> CPU 64 (16C)
 >> Mem 64
Oracle 11.2.0.3.0
 >>
Mem 40G


表結構/資料情況

TABLE_NAME
INDEX_NAME
COLUMN_NAME
BLEVEL
LEAF_BLOCKS
DISTINCT_KEYS
CLUSTERING_FACTOR
NUM_ROWS
STATUS
CS_CHANGE_ACCE PK_CHANGE_ORDER_ID ORDER_ID 1 392 91178 65010 91178 VALID
CS_ORDER_ITEM
IDX_COI_ORDER_ID
ORDER_ID
2
35082
638272
9077480
15016701
VALID

SQL> set autot on
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select count(1)            ----&gt篇幅原因，選擇count(1)復現
  2    from cs_order_item t
  3   where 1 = 1
  4     AND (T.ORDER_ID = 1528168 OR
  5         T.ORDER_ID IN
  6         (SELECT CA.ORDER_ID
  7             FROM CS_CHANGE_ACCE CA
  8            WHERE CA.TAG IN (SELECT t.TAG
  9                               FROM CS_CHANGE_ACCE t
10                              WHERE t.ORDER_ID = 1537775)))
11     and t.oi_source in (1, 2)
12   order by t.begin_date;

  COUNT(1)
----------
        12

已用時間:  00: 01: 13.28

執行計劃
----------------------------------------------------------
Plan hash value: 1964544126

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU )| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |    17 |   108 K  (2)| 00:21 :44 |
|   1 |  SORT AGGREGATE                 |                    |     1 |    17 |            |          |
|*  2 |   FILTER                       |                    |     |       |            |          |
|*  3 |    TABLE ACCESS FULL           | CS_ORDER_ITEM      |    14M|   234M|   108 K  (2)| 00:21 :44 |
|   4 |    NESTED LOOPS                |                    |     1 |    22 |     3    (0)| 00:00 :01 |
|   5 |     TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE     |     1 |    11 |     2    (0)| 00:00 :01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_CHANGE_ORDER_ID |     1 |       |     1    (0)| 00:00 :01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE     |     1 |    11 |     1    (0)| 00:00 :01 |
|*  8 |      INDEX UNIQUE SCAN         | PK_CHANGE_ORDER_ID |     1 |       |     0    (0)| 00:00 :01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."ORDER_ID"=1528168 OR  EXISTS (SELECT 0 FROM "CS_CHANGE_ACCE"
              "CA","CS_CHANGE_ACCE" "T" WHERE "T"."ORDER_ID"=1537775 AND "CA"."TAG"="T"."TAG" AND
              "CA"."ORDER_ID"=:B1))
   3 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
   6 - access("CA"."ORDER_ID"=:B1)
   7 - filter("CA"."TAG"="T"."TAG")
   8 - access("T"."ORDER_ID"=1537775)


統計資訊
----------------------------------------------------------
          1  recursive calls
          1  db block gets
   19981341  consistent gets
     392349  physical reads
          0  redo size
        217  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


Filter/子查詢非巢狀 知識點：
Subquery Unnesting(子查詢非巢狀)： 
如果SQL語句中的where條件後面有子查詢，子查詢前面有in,not in,exists,not exists,
CBO很可能會對該子查詢進行等價改寫，改寫的過程其實就叫做子查詢擴充套件。Oracle始終認為SQL語句進行改寫之後，
CBO能更好的最佳化，當然了，並不是所有的子查詢都會被改寫，子查詢中有些限制條件會阻止CBO進行改寫(因為改寫之後不等價)。

說白了就是 將半連線與反連線的子查詢語句轉化成普通的連線,如果沒有子查詢非巢狀化,執行的基本的方法就是基於FILTER操作.
這是一個類似於nested loop的一種方法,和nest loop不同之處是要維護一個hash table 且filter的效能實際上跟列值distinct數有關，
oracle執行時實際上做了很大最佳化，最壞情況下才會出現對外表每一行 執行一次filter操作，如果distinct值比較少，那執行效率還是非常高的，甚至有可能比nl更高.

**** hint 
UNNEST – 子查詢展開,不讓子查詢巢狀在裡面,也就是這裡說的子查詢非巢狀化.
NO_UNNEST – 子查詢巢狀化.

***  檢視生產環境子查詢非巢狀_unnest_subquery value 
SQL> select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault
  2    from sys.x$ksppi x, sys.x$ksppcv y
  3   where x.inst_id = userenv('Instance')
  4     and y.inst_id = userenv('Instance')
  5     and x.indx = y.indx
  6     and x.ksppinm like '_unnest%'
  7   order by translate(x.ksppinm, ' _', ' ');

NAME                           VALUE                                                ISDEFAULT
-----------------------------------------------------------------------------------------------------
_unnest_subquery         TRUE                                                     TRUE

SQL>
 

分析 ： cs_order_item.order_id選擇性較高的索引，CBO 沒做成子查詢擴充套件，
      導致cs_order_item 14M的驅動表和CS_CHANGE_ACCE 小表做迴圈 
      (驅動表(CS_ORDER_ITEM)行數等於(CS_CHANGE_ACCE)被驅動表掃描次數(14M) )
      所以我們選擇用union all形式手動子查詢擴充套件(**註釋用hint無作用) 

SQL> SELECT COUNT(1)
  2    FROM (select t.*
  3            from cs_order_item t
  4           where 1 = 1
  5             AND (T.ORDER_ID = 1528168)
  6             and t.oi_source in (1, 2)
  7          union all
  8          select t.*
  9            from cs_order_item t
10           where 1 = 1
11             AND t.oi_source in (1, 2)
12             and T.ORDER_ID IN
13                 (SELECT CA.ORDER_ID
14                    FROM CS_CHANGE_ACCE CA
15                   WHERE CA.TAG IN (SELECT t.TAG
16                                      FROM CS_CHANGE_ACCE t
17                                     WHERE t.ORDER_ID = 1537775)));

  COUNT(1)
----------
        12

已用時間:  00: 00: 00.02

執行計劃
----------------------------------------------------------
Plan hash value: 3789129724
--------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU )| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |     1 |       |   158   (2 )| 00: 00:02 |
|   1 |  SORT AGGREGATE                    |                    |     1 |       |            |          |
|   2 |   VIEW                            |                    |    69 |       |   158   (2 )| 00: 00:02 |
|   3 |    UNION-ALL                       |                    |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID    | CS_ORDER_ITEM      |    23 |   207 |    18    (0)| 00:00 :01 |
|*  5 |      INDEX RANGE SCAN             | INDEX_ORDER_ID     |    23 |       |     3    (0)| 00:00 :01 |
|   6 |     NESTED LOOPS                  |                    |       |       |            |          |
|   7 |      NESTED LOOPS                 |                    |    46 |  1426 |   140    (2)| 00:00 :02 |
|   8 |       NESTED LOOPS                |                    |     2 |    44 |   106    (2)| 00:00 :02 |
|   9 |        TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE     |     1 |    11 |     2    (0)| 00:00 :01 |
|* 10 |         INDEX UNIQUE SCAN         | PK_CHANGE_ORDER_ID |     1 |       |     1    (0)| 00:00 :01 |
|* 11 |        TABLE ACCESS FULL          | CS_CHANGE_ACCE     |     2 |    22 |   104    (2)| 00:00 :02 |
|* 12 |       INDEX RANGE SCAN            | INDEX_ORDER_ID     |    23 |       |     2    (0)| 00:00 :01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID   | CS_ORDER_ITEM      |    23 |   207 |    17    (0)| 00:00 :01 |
--------------------------------------------------------------------------------------------------------

Predicate Information ( identified by operation id):
---------------------------------------------------

   4 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
   5 - access("T"."ORDER_ID"=1528168)
  10 - access("T"."ORDER_ID"=1537775)
  11 - filter("CA"."TAG"="T"."TAG")
  12 - access("T"."ORDER_ID"="CA"."ORDER_ID")
  13 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)


統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        217  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


    總結：
       看來CBO很多時候遇到一些寫法也做不到我們想的那樣，所以需要我們手動做子查詢擴充套件；
       最近發現好多sql的過濾條件 ( a.id=1 or exists/in ..子查詢) 都選擇了filter 執行計劃，都選擇了union all 做的解決。

 

  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...

此條目發表在   SQL、SQL最佳化篇  分類目錄。將固定連線加入收藏夾。



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1462937/，如需轉載，請註明出處，否則將追究法律責任。

相關文章