[20190524]使用use_concat or_expand提示優化.txt

lfree發表於2019-05-24

[20190524]使用use_concat or_expand提示優化.txt

--//上午看了連結https://connor-mcdonald.com/2019/05/22/being-generous-to-the-optimizer,突然想起我們生產系統類似語句。
--//現在想想覺得開發的想象力太豐富,寫這些語句是否考慮長期執行導致的結果。對方例子相對簡單,我優化的例子簡直就是變態。
--//連結:[20150814]使用use_concat提示.txt => http://blog.itpub.net/267265/viewspace-1771727/
--//實際上看了馬上想到使用use_concat or_expand提示優化sql語句.同時看了連結
--//https://jonathanlewis.wordpress.com/2019/05/22/danger-hints/,一起測試看看。

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table address ( street int, suburb int, post_code int,  data char(100));
insert into address select mod(rownum,1e4), mod(rownum,10), mod(rownum,1e2), rownum from dual connect by level  <= 1e5;
commit;
 
exec dbms_stats.gather_table_stats('','ADDRESS')

create index i_address_stress on address ( street );
create index i_address_suburb on address ( suburb );
create index i_address_post_code on address ( post_code );

2.測試:

variable val number = 6
variable choice number = 1
alter session set statistics_level = all;

SCOTT@test01p> select data from   address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val );
DATA
-----
6
10006
20006
30006
40006
50006
60006
70006
80006
90006
10 rows selected.


Plan hash value: 3645838471
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |       |   445 (100)|          |     10 |00:00:00.02 |    1636 |
|*  1 |  TABLE ACCESS FULL| ADDRESS |      1 |    100 | 10800 |   445   (1)| 00:00:01 |     10 |00:00:00.02 |    1636 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / ADDRESS@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   2 - :2 (NUMBER): 6
   4 - :2 (NUMBER, Primary=2)
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(((:CHOICE=2 AND "SUBURB"=:VAL) OR ("STREET"=:VAL AND :CHOICE=1)))

--//選擇全表掃描.加入提示:/*+ or_expand(@sel$1) */

select /*+ or_expand(@sel$1) */ data from   address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val );

SCOTT@test01p> @ dpc '' outline
Plan hash value: 1427591975
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |      1 |        |       |   456 (100)|          |     10 |00:00:00.06 |      13 |      4 |
|   1 |  VIEW                                  | VW_ORE_B7380F92  |      1 |  10010 |   997K|   456   (1)| 00:00:01 |     10 |00:00:00.06 |      13 |      4 |
|   2 |   UNION-ALL                            |                  |      1 |        |       |            |          |     10 |00:00:00.06 |      13 |      4 |
|*  3 |    FILTER                              |                  |      1 |        |       |            |          |     10 |00:00:00.06 |      13 |      4 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS          |      1 |     10 |  1050 |    11   (0)| 00:00:01 |     10 |00:00:00.06 |      13 |      4 |
|*  5 |      INDEX RANGE SCAN                  | I_ADDRESS_STRESS |      1 |     10 |       |     1   (0)| 00:00:01 |     10 |00:00:00.06 |       3 |      4 |
|*  6 |    FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |
|*  7 |     TABLE ACCESS FULL                  | ADDRESS          |      0 |  10000 |  1054K|   445   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$9162BF3C   / VW_ORE_B7380F92@SEL$B7380F92
   2 - SET$9162BF3C
   3 - SET$9162BF3C_1
   4 - SET$9162BF3C_1 / ADDRESS@SEL$1
   5 - SET$9162BF3C_1 / ADDRESS@SEL$1
   6 - SET$9162BF3C_2
   7 - SET$9162BF3C_2 / ADDRESS@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SET$9162BF3C_2")
      OUTLINE_LEAF(@"SET$9162BF3C_1")
      OUTLINE_LEAF(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SEL$B7380F92")
      OUTLINE(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92")
      INDEX_RS_ASC(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1")
      FULL(@"SET$9162BF3C_2" "ADDRESS"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   2 - :2 (NUMBER): 6
   4 - :2 (NUMBER, Primary=2)
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:CHOICE=1)
   5 - access("STREET"=:VAL)
   6 - filter(:CHOICE=2)
   7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL))))
--//注意看下劃線.
--//但是使用下劃線提示,改動程式碼的情況下如何呢?

SELECT /*+ or_expand(@sel$1 (1) (2) ) */ data
  FROM address
 WHERE ( :choice = 1 AND street = :val )
    OR ( :choice = 2 AND suburb = :val )
    OR ( :choice = 3 AND post_code = :val);

SCOTT@test01p> @ dpc '' outline
...
Plan hash value: 1427591975
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |      1 |        |       |   456 (100)|          |     10 |00:00:00.01 |      13 |
|   1 |  VIEW                                  | VW_ORE_B7380F92  |      1 |  10010 |   997K|   456   (1)| 00:00:01 |     10 |00:00:00.01 |      13 |
|   2 |   UNION-ALL                            |                  |      1 |        |       |            |          |     10 |00:00:00.01 |      13 |
|*  3 |    FILTER                              |                  |      1 |        |       |            |          |     10 |00:00:00.01 |      13 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS          |      1 |     10 |  1050 |    11   (0)| 00:00:01 |     10 |00:00:00.01 |      13 |
|*  5 |      INDEX RANGE SCAN                  | I_ADDRESS_STRESS |      1 |     10 |       |     1   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|*  6 |    FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  7 |     TABLE ACCESS FULL                  | ADDRESS          |      0 |  10000 |  1054K|   445   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$9162BF3C   / VW_ORE_B7380F92@SEL$B7380F92
   2 - SET$9162BF3C
   3 - SET$9162BF3C_1
   4 - SET$9162BF3C_1 / ADDRESS@SEL$1
   5 - SET$9162BF3C_1 / ADDRESS@SEL$1
   6 - SET$9162BF3C_2
   7 - SET$9162BF3C_2 / ADDRESS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SET$9162BF3C_2")
      OUTLINE_LEAF(@"SET$9162BF3C_1")
      OUTLINE_LEAF(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SEL$B7380F92")
      OUTLINE(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92")
      INDEX_RS_ASC(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1")
      FULL(@"SET$9162BF3C_2" "ADDRESS"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   2 - :2 (NUMBER): 6
   4 - :2 (NUMBER, Primary=2)

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

   3 - filter(:CHOICE=1)
   5 - access("STREET"=:VAL)
   6 - filter(:CHOICE=2)
   7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL))))

--//正像連結講的那樣如果增加1個或條件,導致執行計劃變得不合理.實際上跟嚴重的是查詢發生了錯誤.
--//如果仔細看Predicate Information 就很容易發現沒有:CHOICE=1的filter.如果查詢:

SCOTT@test01p> variable choice number = 3
SELECT /*+ or_expand(@sel$1 (1) (2) ) */ data
  FROM address
 WHERE ( :choice = 1 AND street = :val )
    OR ( :choice = 2 AND suburb = :val )
    OR ( :choice = 3 AND post_code = :val);

no rows selected.

--//取消提示:
SELECT data  FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val);
...
1000 rows selected.

--//兩者的結果集不一樣.明顯這個是一個bug.

修改如下:

SELECT /*+ or_expand(@sel$1 (1) (2) (3) ) */ data
  FROM address
 WHERE ( :choice = 1 AND street = :val )
    OR ( :choice = 2 AND suburb = :val )
    OR ( :choice = 3 AND post_code = :val);
...

--//注使用提示/*+ or_expand(@sel$1 ) */結果是正確的.

SCOTT@test01p> @ dpc '' outline    
Plan hash value: 3525475520
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |      1 |        |       |   900 (100)|          |   1000 |00:00:00.02 |    1640 |
|   1 |  VIEW                                  | VW_ORE_B7380F92  |      1 |  11009 |  1096K|   900   (1)| 00:00:01 |   1000 |00:00:00.02 |    1640 |
|   2 |   UNION-ALL                            |                  |      1 |        |       |            |          |   1000 |00:00:00.02 |    1640 |
|*  3 |    FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS          |      0 |     10 |  1050 |    11   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  5 |      INDEX RANGE SCAN                  | I_ADDRESS_STRESS |      0 |     10 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  6 |    FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  7 |     TABLE ACCESS FULL                  | ADDRESS          |      0 |  10000 |  1054K|   445   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  8 |    FILTER                              |                  |      1 |        |       |            |          |   1000 |00:00:00.02 |    1640 |
|*  9 |     TABLE ACCESS FULL                  | ADDRESS          |      1 |    999 |   108K|   445   (1)| 00:00:01 |   1000 |00:00:00.02 |    1640 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$49E1C21B   / VW_ORE_B7380F92@SEL$B7380F92
   2 - SET$49E1C21B
   3 - SET$49E1C21B_1
   4 - SET$49E1C21B_1 / ADDRESS@SEL$1
   5 - SET$49E1C21B_1 / ADDRESS@SEL$1
   6 - SET$49E1C21B_2
   7 - SET$49E1C21B_2 / ADDRESS@SEL$1
   8 - SET$49E1C21B_3
   9 - SET$49E1C21B_3 / ADDRESS@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SET$49E1C21B_3")
      OUTLINE_LEAF(@"SET$49E1C21B_2")
      OUTLINE_LEAF(@"SET$49E1C21B_1")
      OUTLINE_LEAF(@"SET$49E1C21B")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE_LEAF(@"SEL$B7380F92")
      OUTLINE(@"SET$49E1C21B")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92")
      INDEX_RS_ASC(@"SET$49E1C21B_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$49E1C21B_1" "ADDRESS"@"SEL$1")
      FULL(@"SET$49E1C21B_2" "ADDRESS"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "ADDRESS"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   2 - :2 (NUMBER): 6
   4 - :2 (NUMBER, Primary=2)
   6 - :2 (NUMBER, Primary=2)
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:CHOICE=1)
   5 - access("STREET"=:VAL)
   6 - filter(:CHOICE=2)
   7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL))))
   8 - filter(:CHOICE=3)
   9 - filter(("POST_CODE"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL)) AND (LNNVL(:CHOICE=2) OR LNNVL("SUBURB"=:VAL))))

3.測試使用use_concate看看:
set linesize 100
SELECT /*+ use_concat */ data
  FROM address
 WHERE ( :choice = 1 AND street = :val )
    OR ( :choice = 2 AND suburb = :val )
    OR ( :choice = 3 AND post_code = :val);

SCOTT@test01p> @ dpc '' outline
...
Plan hash value: 2048882018
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |      1 |        |       |   900 (100)|          |   1000 |00:00:00.01 |    1640 |
|   1 |  CONCATENATION                        |                  |      1 |        |       |            |          |   1000 |00:00:00.01 |    1640 |
|*  2 |   FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS          |      0 |     10 |  1110 |    11   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  4 |     INDEX RANGE SCAN                  | I_ADDRESS_STRESS |      0 |     10 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  5 |   FILTER                              |                  |      1 |        |       |            |          |   1000 |00:00:00.01 |    1640 |
|*  6 |    TABLE ACCESS FULL                  | ADDRESS          |      1 |   1000 |   108K|   445   (1)| 00:00:01 |   1000 |00:00:00.01 |    1640 |
|*  7 |   FILTER                              |                  |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  8 |    TABLE ACCESS FULL                  | ADDRESS          |      0 |   9999 |  1083K|   445   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1_1 / ADDRESS@SEL$1
   4 - SEL$1_1 / ADDRESS@SEL$1
   6 - SEL$1_2 / ADDRESS@SEL$1_2
   8 - SEL$1_3 / ADDRESS@SEL$1_3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((5 2) (6 3) (7 4) (8 5) (9 6) (10 7) (2 8) (4 9) (3 10)))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      
--//始終不明白裡面那一串表示什麼?

      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE_LEAF(@"SEL$1_3")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_1" "ADDRESS"@"SEL$1")
      FULL(@"SEL$1_2" "ADDRESS"@"SEL$1_2")
      FULL(@"SEL$1_3" "ADDRESS"@"SEL$1_3")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   2 - :2 (NUMBER): 6
   4 - :2 (NUMBER, Primary=2)
   6 - :2 (NUMBER, Primary=2)

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

   2 - filter(:CHOICE=1)
   4 - access("STREET"=:VAL)
   5 - filter(:CHOICE=3)
   6 - filter(("POST_CODE"=:VAL AND (LNNVL("STREET"=:VAL) OR LNNVL(:CHOICE=1))))
   7 - filter(:CHOICE=2)
   8 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=3) OR LNNVL("POST_CODE"=:VAL)) AND (LNNVL("STREET"=:VAL) OR LNNVL(:CHOICE=1))))

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

相關文章