[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【效能優化】直方圖優化直方圖
- 【效能優化】執行計劃與直方圖優化直方圖
- 【效能優化】Oracle直方圖解析優化Oracle直方圖圖解
- [20170615]直方圖-高度直方圖(11g).txt直方圖
- 直方圖均衡化直方圖
- 直方圖均衡化原理與實現直方圖
- 直方圖直方圖
- [20210205]警惕toad下優化直方圖相關sql語句.txt優化直方圖SQL
- [20180122]列統計與直方圖.txt直方圖
- OpenCV之影象直方圖均衡化OpenCV直方圖
- 收集直方圖及檢視直方圖資訊直方圖
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- OpenCV計算機視覺學習(9)——影像直方圖 & 直方圖均衡化OpenCV計算機視覺直方圖
- [20120905]刪除直方圖.txt直方圖
- opencv——影像直方圖與反向投影OpenCV直方圖
- 直方圖(histograms)直方圖Histogram
- 7.3 直方圖直方圖
- oracle 直方圖Oracle直方圖
- 灰度直方圖均衡化及其實現直方圖
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- col_usage$與直方圖的收集直方圖
- cursor_sharing=similar 與 直方圖MILA直方圖
- 直方圖Histograms與CRUSOR_SHARING直方圖Histogram
- 04:垂直直方圖直方圖
- oracle直方圖使用Oracle直方圖
- 直方圖學習直方圖
- Oracle直方圖解析Oracle直方圖圖解
- [zt] Histograms - 直方圖Histogram直方圖
- dba_histograms等高直方圖和等頻直方圖的理解Histogram直方圖
- [20161208]11g直方圖與char資料型別.txt直方圖資料型別
- 灰度影像直方圖均衡化公式及實現直方圖公式
- 直方圖中最大矩形直方圖
- Oracle直方圖詳解Oracle直方圖
- Cursor_sharing=SIMILAR取值與直方圖(上)MILA直方圖
- Cursor_sharing=SIMILAR取值與直方圖(下)MILA直方圖
- [20190630]如何確定直方圖型別.txt直方圖型別
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- 影像演算法之直方圖均衡化(灰度影像)演算法直方圖