[20160118]提示index_join.txt

lfree發表於2016-01-18
[20160118]提示index_join.txt

--生產系統最佳化,遇到1例使用index_join提示的異常情況,透過例子來說明:

1.環境:

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table tx as select rownum id , mod(rownum,100) idx , mod(rownum,100) idy,'a' c1,'b' c2 ,lpad('x',100,'x') pad from dual connect by level<=2e5;
Table created.

SCOTT@book> create index i_tx_idx_c1 on tx(idx,c1);
Index created.

SCOTT@book> create index i_tx_idx_c2 on tx(idx,c2);
Index created.

SCOTT@book> create index i_tx_idy_c2 on tx(idy,c2);
Index created.

--分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1'

2.測試:
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> select count(1) from tx where idx=42 and (c1='a' or c2='b');
  COUNT(1)
----------
      1000

Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |       |   456 (100)|          |      1 |00:00:00.03 |     460 |       |       |          |
|   1 |  SORT AGGREGATE         |                  |      1 |      1 |     7 |            |          |      1 |00:00:00.03 |     460 |       |       |          |
|*  2 |   VIEW                  | index$_join$_001 |      1 |   2000 | 14000 |   456   (1)| 00:00:06 |   2000 |00:00:00.03 |     460 |       |       |          |
|*  3 |    HASH JOIN            |                  |      1 |        |       |            |          |   2000 |00:00:00.03 |     460 |  1557K|  1557K| 1602K (0)|
|*  4 |     INDEX RANGE SCAN    | I_TX_IDX_C1      |      1 |   2000 | 14000 |     6   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
|*  5 |     INDEX FAST FULL SCAN| I_TX_IDX_C2      |      1 |   2000 | 14000 |   561   (1)| 00:00:07 |   2000 |00:00:00.03 |     454 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2AEE34FF / TX@SEL$1
   3 - SEL$2AEE34FF
   4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
   5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("C1"='a' OR "C2"='b'))
   3 - access(ROWID=ROWID)
   4 - access("IDX"=42)
   5 - filter("IDX"=42)

--在沒有看執行計劃以前,我一直認為不會使用index_join,使用它的連線條件是rowid=rowid,而且不會回表查詢。而我的查詢是存在or
--條件,什麼可能會使用index_join呢?
--仔細看上面的執行計劃可以發現id=4 access("IDX"=42),沒有包括"C1"='a'的條件。才明白透過兩個索引掃描IDX=42的條件,在同
--rowid=rowid連線,再過濾("C1"='a' OR "C2"='b')。
--但是如果再仔細看執行計劃可以發現,id=5選擇 INDEX FAST FULL SCAN,而使用filter("IDX"=42),這樣造成邏輯讀達到454.實際上這
--裡應該選擇access("IDX"=42),不知道為什麼oracle選擇錯誤的執行路徑。

--我改寫如下:select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
--執行計劃依舊。

3.我有改寫如下:把後面的idx換成idy,我的測試例子這兩個欄位是相等的。
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');

Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |      1 |        |       |    10 (100)|          |      1 |00:00:00.01 |      12 |
|   1 |  SORT AGGREGATE                  |             |      1 |      1 |    10 |            |          |      1 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION COUNT        |             |      1 |   3980 | 39800 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP OR                     |             |      1 |        |       |            |          |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN            | I_TX_IDX_C1 |      1 |        |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|             |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN            | I_TX_IDY_C2 |      1 |        |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TX@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDY" "TX"."C2")))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("IDX"=42 AND "C1"='a')
   7 - access("IDY"=42 AND "C2"='b')

--這個就比較複合我原來自己的想象,很奇怪使用idx就不行,我根據上面的提示,修改查詢語句如下:

--修改提示後面的部分,IDY替換成IDX,查詢條件修改回來(idx=42 and c2='b'):
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TX"@"SEL$1" OR(1 1 ("TX"."IDX" "TX"."C1") 2 ("TX"."IDX" "TX"."C2")))
END_OUTLINE_DATA
*/ count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');

--執行計劃有回到了前面的使用index_join的情況:

Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |       |   456 (100)|          |      1 |00:00:00.03 |     460 |       |       |          |
|   1 |  SORT AGGREGATE         |                  |      1 |      1 |     7 |            |          |      1 |00:00:00.03 |     460 |       |       |          |
|*  2 |   VIEW                  | index$_join$_001 |      1 |   2000 | 14000 |   456   (1)| 00:00:06 |   2000 |00:00:00.03 |     460 |       |       |          |
|*  3 |    HASH JOIN            |                  |      1 |        |       |            |          |   2000 |00:00:00.03 |     460 |  1557K|  1557K| 1598K (0)|
|*  4 |     INDEX RANGE SCAN    | I_TX_IDX_C1      |      1 |   2000 | 14000 |     6   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
|*  5 |     INDEX FAST FULL SCAN| I_TX_IDX_C2      |      1 |   2000 | 14000 |   561   (1)| 00:00:07 |   2000 |00:00:00.03 |     454 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

4.使用use_concat提示看看:

select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where idx=42 and (c1='a' or c2='b');

select
/*+ use_concat index(tx i_tx_idx_c1) index(tx i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');

Plan hash value: 802604350
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |       |  4011 (100)|          |      1 |00:00:00.02 |    4012 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |     7 |            |          |      1 |00:00:00.02 |    4012 |
|   2 |   CONCATENATION               |             |      1 |        |       |            |          |   2000 |00:00:00.01 |    4012 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TX          |      1 |   2000 | 14000 |  2005   (0)| 00:00:25 |   2000 |00:00:00.01 |    2006 |
|*  4 |     INDEX RANGE SCAN          | I_TX_IDX_C2 |      1 |   2000 |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| TX          |      1 |      1 |     7 |  2005   (0)| 00:00:25 |      0 |00:00:00.01 |    2006 |
|*  6 |     INDEX RANGE SCAN          | I_TX_IDX_C1 |      1 |   2000 |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1_1 / TX@SEL$1
   4 - SEL$1_1 / TX@SEL$1
   5 - SEL$1_2 / TX@SEL$1_2
   6 - SEL$1_2 / TX@SEL$1_2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2"))
      INDEX_RS_ASC(@"SEL$1_2" "TX"@"SEL$1_2" ("TX"."IDX" "TX"."C1"))
      END_OUTLINE_DATA
  */

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

   4 - access("IDX"=42 AND "C2"='b')
   5 - filter(LNNVL("C2"='b'))
   6 - access("IDX"=42 AND "C1"='a')

--這樣存在一個毛病,要回表,我的表的索引聚集因子很大,回表邏輯讀很更高,為什麼會回表,id=3為什麼不好確定,實際上我認為不
--需要僅僅需要查詢rowid就ok了。
--但是id = 5 存在一個filter(LNNVL("C2"='b'))條件必須回表才能知道結果。

5.使用提示INDEX_COMBINE,and_equal看看:

select
/*+ index_combine(t1  i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');

select
/*+ AND_EQUAL(t1  i_tx_idx_c1 i_tx_idx_c2) */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');

--執行計劃都是使用index_join。

6.存在2個疑問:
1.使用index_join為什麼選擇INDEX FAST FULL SCAN,明明僅僅access就ok了,oracle確選擇了過濾。
2.select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b'); 這樣的查詢邏輯讀很小,為什麼後面一個換成idx=42,
  走BITMAP_TREE的提示走BITMAP OR就不行。
3.感覺oracle 的最佳化器即使發展到現在,越來越複雜,但是依舊問題多多。
4.改寫如下:

select count(1) from (
select rowid from tx where idx=42 and c1='a'
union
select rowid from tx where idx=42 and c2='b');

Plan hash value: 924432443
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |       |    12 (100)|          |      1 |00:00:00.01 |      12 |       |       |          |
|   1 |  SORT AGGREGATE      |             |      1 |      1 |       |            |          |      1 |00:00:00.01 |      12 |       |       |          |
|   2 |   VIEW               |             |      1 |   4000 |       |    12  (17)| 00:00:01 |   2000 |00:00:00.01 |      12 |       |       |          |
|   3 |    SORT UNIQUE       |             |      1 |   4000 | 68000 |    12  (17)| 00:00:01 |   2000 |00:00:00.01 |      12 |   115K|   115K|  102K (0)|
|   4 |     UNION-ALL        |             |      1 |        |       |            |          |   4000 |00:00:00.01 |      12 |       |       |          |
|*  5 |      INDEX RANGE SCAN| I_TX_IDX_C1 |      1 |   2000 | 34000 |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
|*  6 |      INDEX RANGE SCAN| I_TX_IDX_C2 |      1 |   2000 | 34000 |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SET$1 / from$_subquery$_001@SEL$1
   3 - SET$1
   5 - SEL$2 / TX@SEL$2
   6 - SEL$3 / TX@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("IDX"=42 AND "C1"='a')
   6 - access("IDX"=42 AND "C2"='b')

--這樣才能獲得比較好的查詢計劃。

5.還可以做如下測試:
select count(rowid) from tx where idx=42 and (c1='a' or c2='b');
select rowid from tx where idx=42 and (c1='a' or c2='b');

--你可以發現oracle會使用全表掃描。但是如果後面的換成idy,寫成如下:

select count(rowid) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');
select count(1) from tx where (idx=42 and c1='a') or (idy=42 and c2='b');

Plan hash value: 2850621532
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |      1 |        |       |    10 (100)|          |      1 |00:00:00.01 |      12 |
|   1 |  SORT AGGREGATE                  |             |      1 |      1 |    10 |            |          |      1 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION COUNT        |             |      1 |   3980 | 39800 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP OR                     |             |      1 |        |       |            |          |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP CONVERSION FROM ROWIDS|             |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN            | I_TX_IDX_C1 |      1 |        |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|             |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN            | I_TX_IDY_C2 |      1 |        |       |     5   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------------------------------------------------------------

6.是否是聚集因子太大的問題呢?

--exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C1',CLSTFCT => 3394, force=>true);
exec DBMS_STATS.set_index_stats(ownname=>user,indname=>'I_TX_IDX_C2',CLSTFCT => 3394, force=>true);

SelecT
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2AEE34FF")
      OUTLINE_LEAF(@"SEL$1")
      INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C1") ("TX"."IDX" "TX"."C2"))
      END_OUTLINE_DATA
  */
count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');

Plan hash value: 1635136096
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |       |   456 (100)|          |      1 |00:00:00.03 |     460 |       |       |          |
|   1 |  SORT AGGREGATE         |                  |      1 |      1 |     7 |            |          |      1 |00:00:00.03 |     460 |       |       |          |
|*  2 |   VIEW                  | index$_join$_001 |      1 |   2000 | 14000 |   456   (1)| 00:00:06 |   2000 |00:00:00.03 |     460 |       |       |          |
|*  3 |    HASH JOIN            |                  |      1 |        |       |            |          |   2000 |00:00:00.03 |     460 |  1557K|  1557K| 1600K (0)|
|*  4 |     INDEX RANGE SCAN    | I_TX_IDX_C1      |      1 |   2000 | 14000 |     6   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
|*  5 |     INDEX FAST FULL SCAN| I_TX_IDX_C2      |      1 |   2000 | 14000 |   561   (1)| 00:00:07 |   2000 |00:00:00.03 |     454 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
--問題依舊,感覺oracle最佳化器依舊問題多多。

7.補充如果要倒過來連線,因為2個索引統計基本相似,可以改名:
SCOTT@book> alter index i_tx_idx_c1 rename to i_tx_idx_z1;
Index altered.

SCOTT@book> select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b');
  COUNT(1)
----------
      2000

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  167tfyfdcrxyn, child number 0
-------------------------------------
select count(1) from tx where (idx=42 and c1='a') or (idx=42 and c2='b')

Plan hash value: 3304306490

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |       |   456 (100)|          |      1 |00:00:00.04 |     460 |       |       |          |
|   1 |  SORT AGGREGATE         |                  |      1 |      1 |     7 |            |          |      1 |00:00:00.04 |     460 |       |       |          |
|*  2 |   VIEW                  | index$_join$_001 |      1 |   2000 | 14000 |   456   (1)| 00:00:06 |   2000 |00:00:00.04 |     460 |       |       |          |
|*  3 |    HASH JOIN            |                  |      1 |        |       |            |          |   2000 |00:00:00.04 |     460 |  1557K|  1557K| 1616K (0)|
|*  4 |     INDEX RANGE SCAN    | I_TX_IDX_C2      |      1 |   2000 | 14000 |     6   (0)| 00:00:01 |   2000 |00:00:00.01 |       6 |       |       |          |
|*  5 |     INDEX FAST FULL SCAN| I_TX_IDX_Z1      |      1 |   2000 | 14000 |   561   (1)| 00:00:07 |   2000 |00:00:00.03 |     454 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2AEE34FF / TX@SEL$1
   3 - SEL$2AEE34FF
   4 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
   5 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2AEE34FF")
      OUTLINE_LEAF(@"SEL$1")
      INDEX_JOIN(@"SEL$1" "TX"@"SEL$1" ("TX"."IDX" "TX"."C2") ("TX"."IDX" "TX"."C1"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("C1"='a' OR "C2"='b'))
   3 - access(ROWID=ROWID)
   4 - access("IDX"=42)
   5 - filter("IDX"=42)

8.10.2.0.4上測試問題依舊。

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

相關文章