Deferred statistics publish-延遲統計資訊釋出功能介紹
Deferred statistics publish,延遲統計資訊釋出,將新生成的統計資訊存放到一塊臨時的區域,供充分測試以驗證統計資訊對執行計劃確有促進作用的情況下,再發布到資料字典供全域性session使用,規避了因收集方法不當等原因引起統計資訊不準確從而導致optimizer選擇次優plan的問題
---建立測試表
drop table dsptest1;
create table dsptest1 tablespace ts_acct_dat_01 as select * from dba_objects where object_type in ('TABLE','PROGRAM');
select object_type,count(1) from dsptest1 group by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
PROGRAM 19
TABLE 63650
create index ind_objtype on dsptest1(object_type) tablespace ts_acct_ind_01;
---在沒有統計資訊的情況下、以禁用dynamic sampling的方式執行查詢,在資料分佈具有明顯傾斜度的情況下,迫使optimizer走索引,如果不禁用dynamic sampling optimizer會選擇FTS
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 758 | 153K| 79 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DSPTEST1 | 758 | 153K| 79 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJTYPE | 303 | | 75 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
14 rows selected.
---檢視dsptest1表的統計資訊偏好值
select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
TRUE
---設定publish偏好值為FALSE
exec dbms_stats.set_table_prefs('ad','dsptest1','publish','false');
select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
FALSE
---執行統計(帶histogram資訊)
exec dbms_stats.gather_Table_stats('ad','dsptest1',cascade=>TRUE,method_opt=>'for all columns size skewonly');
---檢視資料字典裡無相關統計資訊
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1
select owner,index_name,blevel,clustering_factor from dba_ind_statistics where index_name='IND_OBJID';
no rows selected
---檢視pending區域裡的統計資訊
select owner,table_name,num_rows,last_analyzed from dba_tab_pending_stats where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1 63669 20150104 07:43:42
select owner,index_name,blevel,clustering_factor from dba_ind_pending_stats where index_name='IND_OBJTYPE';
OWNER INDEX_NAME BLEVEL CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- -----------------
AD IND_OBJTYPE 913
---OPTIMIZER_USE_PENDING_STATISTICS=FALSE時重新執行查詢,用的還是range scan
SQL> show parameter optimizer_use_pending_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 758 | 153K| 79 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DSPTEST1 | 758 | 153K| 79 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJTYPE | 303 | | 75 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
---OPTIMIZER_USE_PENDING_STATISTICS=TRUE時重新執行查詢,用上了FTS,說明optimizer使用了pending statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
SQL> show parameter optimizer_use_pending_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean TRUE
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63652 | 5967K| 164 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| DSPTEST1 | 63652 | 5967K| 164 (2)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
13 rows selected.
---publish pending stats
exec dbms_stats.publish_pending_stats(ownname=>'ad',tabname=>'dsptest1');
---檢視資料字典裡的統計資訊
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1 63669 20150104 07:43:42
select owner,index_name,blevel,clustering_factor,last_analyzed from dba_ind_statistics where index_name='IND_OBJTYPE';
OWNER INDEX_NAME BLEVEL CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ------------------------------ ---------- ----------------- -----------------
AD IND_OBJTYPE 1 913 20150104 07:43:45
---OPTIMIZER_USE_PENDING_STATISTICS=FALSE再次執行查詢,optimizer準確的選擇了FTS,這次是從data dictionary裡獲取的statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63652 | 5967K| 164 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| DSPTEST1 | 63652 | 5967K| 164 (2)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
13 rows selected.
---建立測試表
drop table dsptest1;
create table dsptest1 tablespace ts_acct_dat_01 as select * from dba_objects where object_type in ('TABLE','PROGRAM');
select object_type,count(1) from dsptest1 group by object_type;
OBJECT_TYPE COUNT(1)
------------------- ----------
PROGRAM 19
TABLE 63650
create index ind_objtype on dsptest1(object_type) tablespace ts_acct_ind_01;
---在沒有統計資訊的情況下、以禁用dynamic sampling的方式執行查詢,在資料分佈具有明顯傾斜度的情況下,迫使optimizer走索引,如果不禁用dynamic sampling optimizer會選擇FTS
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 758 | 153K| 79 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DSPTEST1 | 758 | 153K| 79 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJTYPE | 303 | | 75 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
14 rows selected.
---檢視dsptest1表的統計資訊偏好值
select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
TRUE
---設定publish偏好值為FALSE
exec dbms_stats.set_table_prefs('ad','dsptest1','publish','false');
select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
FALSE
---執行統計(帶histogram資訊)
exec dbms_stats.gather_Table_stats('ad','dsptest1',cascade=>TRUE,method_opt=>'for all columns size skewonly');
---檢視資料字典裡無相關統計資訊
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1
select owner,index_name,blevel,clustering_factor from dba_ind_statistics where index_name='IND_OBJID';
no rows selected
---檢視pending區域裡的統計資訊
select owner,table_name,num_rows,last_analyzed from dba_tab_pending_stats where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1 63669 20150104 07:43:42
select owner,index_name,blevel,clustering_factor from dba_ind_pending_stats where index_name='IND_OBJTYPE';
OWNER INDEX_NAME BLEVEL CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- -----------------
AD IND_OBJTYPE 913
---OPTIMIZER_USE_PENDING_STATISTICS=FALSE時重新執行查詢,用的還是range scan
SQL> show parameter optimizer_use_pending_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 758 | 153K| 79 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DSPTEST1 | 758 | 153K| 79 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJTYPE | 303 | | 75 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
---OPTIMIZER_USE_PENDING_STATISTICS=TRUE時重新執行查詢,用上了FTS,說明optimizer使用了pending statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
SQL> show parameter optimizer_use_pending_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean TRUE
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63652 | 5967K| 164 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| DSPTEST1 | 63652 | 5967K| 164 (2)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
13 rows selected.
---publish pending stats
exec dbms_stats.publish_pending_stats(ownname=>'ad',tabname=>'dsptest1');
---檢視資料字典裡的統計資訊
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD DSPTEST1 63669 20150104 07:43:42
select owner,index_name,blevel,clustering_factor,last_analyzed from dba_ind_statistics where index_name='IND_OBJTYPE';
OWNER INDEX_NAME BLEVEL CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ------------------------------ ---------- ----------------- -----------------
AD IND_OBJTYPE 1 913 20150104 07:43:45
---OPTIMIZER_USE_PENDING_STATISTICS=FALSE再次執行查詢,optimizer準確的選擇了FTS,這次是從data dictionary裡獲取的statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63652 | 5967K| 164 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| DSPTEST1 | 63652 | 5967K| 164 (2)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
13 rows selected.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1411707/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 延遲從庫介紹MySql
- Oracle 統計資訊介紹Oracle
- DBAPI 資料介面釋出平臺功能介紹API
- ABP Framework 5.2 RC 釋出及新增功能介紹Framework
- RabbitMQ延遲訊息的延遲極限是多少?MQ
- BitTorrent 客戶端 Deluge 2.0 釋出:新功能介紹客戶端
- 延遲塊清理介紹(select也會產生redo的原因)
- 關於資料庫 statistics_level的介紹資料庫
- Oracle資料庫審計功能介紹Oracle資料庫
- WSTMall V1.4.1 功能介紹及開源釋出、下載
- [Redis]延遲訊息佇列Redis佇列
- [20210301]延遲顯示輸出.txt
- MQTT 釋出/訂閱模式介紹MQQT模式
- 喜訊!延遲退休來了🙃
- NLPIR大資料平臺的文字資訊提取功能介紹大資料
- Redis(設計與實現):---釋出與訂閱介紹Redis
- 功能介紹
- 延遲退休最新訊息!如何做好應對計劃?
- MySQL InnoDB儲存引擎更新Cardinality統計資訊的策略介紹MySql儲存引擎
- 啟動延時縮短 50%-80%,函式計算釋出映象加速功能函式
- 打造低延遲互動音訊: Oboe音訊
- 短視訊平臺搭建,ios端延遲的執行方式,新增各種延遲iOS
- Bootstrap Blazor 元件介紹 Table (三)列資料格式功能介紹bootBlazor元件
- PHP手術麻醉資訊系統原始碼,手術前管理模組功能介紹PHP原始碼
- 晶片行業管理系統功能介紹晶片行業
- 多功能 Linux 系統監控工具Glances功能介紹Linux
- AirPods介紹|AirPods Pro 的空間音訊功能介紹和開啟教程AI音訊
- Redis資料操作長延遲分析Redis
- OneAuth 3月釋出: Cloud UD 特性介紹Cloud
- RocketMQ系列(五)廣播與延遲訊息MQ
- 延遲訊息的五種實現方案
- 延遲繫結
- smartctl 屬性資訊介紹
- 谷歌釋出白皮書 詳細介紹幾大服務中如何打擊虛假資訊谷歌
- FreeSql aop功能介紹SQL
- 短影片電商系統,編寫延遲訊息實現程式碼
- SAP 電商雲 Spartacus UI 支援延遲載入的新功能的入口設計UI
- SciTech-Statistics-英語授課:Business Statistics商務統計
- [20210529]延遲開啟資料庫.txt資料庫