[20140301]直方圖與優化.txt

[20140301]直方圖與優化.txt

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t(id number,name varchar2(10),status varchar2(1));
Table created.

insert into t select rownum,'testtest12','Y' from dual connect by level<=9999;
insert into t values(10000,'TTTTTTTT34','N');
commit ;

create index i_t_status on t(status);

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

select * from t where status='N';

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |      1 |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--直方圖給出了準確的估計。

--假設白天的業務更新了資料，不存在status='N'的記錄，晚上做了分析後呢？

SCOTT@test01p> update t set status='Y' where id=10000;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |      1 |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--執行計劃選擇了索引。修改一些資料模型。加入5000條status＝'A'的記錄。

insert into t select 10000+rownum ,'AAAA123456','A' from dual connect by level<=5000;
commit ;

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |    14 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |   2500 |    14   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |   2500 |     6   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--執行計劃選擇索引，但是E_rows=2500，明顯估計偏大。再增加一些資料呢?

insert into t select 15000+rownum ,'BAAA123456','A' from dual connect by level<=5000;
commit ;

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    19 (100)|
|*  1 |  TABLE ACCESS FULL| T    |   5000 |    19   (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"='N')

--實際上這次選擇了全表掃描。實際上我們一條'N'的資料都沒有。
--再次執行相同語句，執行計劃沒有變化。

SYS@test> @hide feedback
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%feedback%')
NAME                             DESCRIPTION                                DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
-------------------------------- ------------------------------------------ -------------- -------------- -------------
_optimizer_feedback_control      controls the optimizer feedback framework  TRUE
_optimizer_gather_feedback       optimizer gather feedback                  TRUE           TRUE           TRUE
_optimizer_performance_feedback  controls the performance feedback          TRUE           OFF            OFF
_optimizer_use_feedback          optimizer use feedback                     TRUE           TRUE           TRUE

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,method_OPT=>'FOR ALL COLUMNS SIZE 1') ;
PL/SQL procedure successfully completed.

--刪除直方圖。

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    19 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  10000 |    19   (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"='N')

--可以發現估計值更多。10000，佔50%。多次執行執行計劃不變。

--修改為繫結變數呢？

SCOTT@test01p> variable a varchar2(1);
SCOTT@test01p> exec :a := 'N';
PL/SQL procedure successfully completed.

SCOTT@test01p> select * from t where status=:a;
no rows selected

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2m42nhwqx52s9, child number 0
-------------------------------------
select * from t where status=:a
Plan hash value: 1601196873
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    19 (100)|      0 |00:00:00.01 |      68 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |    19   (0)|      0 |00:00:00.01 |      68 |
-------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (VARCHAR2(30), CSID=852): 'N'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"=:A)

--依舊這樣。注意 A-Rows＝0. 按照道理，應該會使用12c的statistics feedback。但是依舊沒有出現。
--真不知道選擇statistics feedback的依據是什麼？

如何解決這個問題呢？

1.使用SPM或者sql profile。
2.或者偽造直方圖資訊，只要統計資訊存在status='N'的少量的記錄。執行計劃會選擇使用索引。
3.刪除統計，使用動態取樣，不過感覺如果表很大，可能存在一些問題。
4.修改統計分析表的時間。
5.總之，即使像12c，這樣的問題oracle的cbo依舊存在一些問題。

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