微課sql最佳化(2)-為什麼需要收集統計資訊

orastar發表於2020-03-03

1、為什麼需要收集統計資訊

Cost-based optimizer (CBO)，Based on object statistics，最佳化器統計資訊描述資料庫中的物件的詳細資訊，查詢最佳化器使用這些統計資訊為每個SQL語句選擇最佳執行計劃。

資料庫將最佳化器統計資訊儲存在資料字典中。您可以使用資料字典檢視訪問這些統計資訊。

由於資料庫中的物件可能會不斷更改，因此必須定期更新統計資訊，以便準確描述這些物件。Oracle資料庫自動維護最佳化器統計資訊。

11g： Scheduled Maintenance Window Times ， In 11g daily maintenance windows are provided. by default these are defined as :

Weeknights: Starts at 10 p.m. and ends at 2 a.m.

Weekends: Starts at 6 a.m. is 20 hours long.

10g： With Oracle Database 10g the default maintenance window is configured to cover the following periods:

10 pm to 6 am every weekday

All weekend (Friday 10 pm to Monday 6 am)

可以使用該DBMS_STATS軟體包手動維護最佳化器統計資訊。

2、哪些表需要收集統計資訊

自動統計任務能滿足大部分資料庫的業務需求，但存在特殊情況需要手工收集統計資訊，

1、業務表資料被delete、truncate、impdp/imp大批次資料變理時。

2、資料變更量超過10%時。

查詢哪些表需要收集統計資訊

col table_name for a30

col object_type for a30

select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed

from dba_tab_statistics

where owner = 'HT'

and (stale_stats = 'YES' or last_analyzed is null);

3、練習題1、測試統計資訊閾值：變更量超過10%

建立測試表：

create table ht.test as select * from dba_objects;

收集統計資訊：

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'TEST',method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);

刪除10%資料：

SQL> DELETE FROM ht.TEST WHERE ROWNUM<=(select count(1) from ht.TEST)*0.1;

8632 rows deleted.

Commit complete.

exec dbms_stats.flush_database_monitoring_info; --重新整理變更資訊

檢視資料變更資訊

col table_owner for a10

col table_name for a30

col pname for a20

col spname for a20

select table_owner,table_name,partition_name pname,subpartition_name spname,inserts,updates,deletes from dba_tab_modifications

where table_owner='HT';

TABLE_OWNE TABLE_NAME PNAME SPNAME INSERTS UPDATES DELETES

------ --- --- --- ---- ---------------- ----------- -------------- -- -- ---------- -- ---------- -- -- -- ----------

HT TEST 0 0 8632

檢視統計資訊是否過期

col table_name for a30

col object_type for a30

COL last_analyzed for a20

select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed

from dba_tab_statistics

where owner = 'HT' AND TABLE_NAME='TEST';

OWNER TABLE_NAME OBJECT_TYPE STALE_STA LAST_ANALYZED

---------- ---------------------- ------------------------------ --------- --------------------

HT TEST TABLE NO 2017-08-18 21:26

再刪除一行資料：

DELETE FROM ht.TEST WHERE ROWNUM=1;

exec dbms_stats.flush_database_monitoring_info; --重新整理變更資訊

再次檢視統計資訊是否過期

col table_name for a30

col object_type for a30

COL last_analyzed for a20

select owner, table_name, object_type, stale_stats, to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') last_analyzed

from dba_tab_statistics

where owner = 'HT' AND TABLE_NAME='TEST';

4、小結

哪些情況需要從新收集統計資訊,

1、業務表資料被delete、truncate、impdp/imp大批次資料變更時。

2、資料變更量超過10%時,統計資訊狀態標記為過期。

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

相關文章

微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊
2020-03-03
SQL
微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢
2020-03-03
SQL
微課sql最佳化(7)、統計資訊收集(5)-關於直方圖
2020-03-03
SQL直方圖
微課sql最佳化(6)、統計資訊收集(4)-關於動態取樣
2020-03-03
SQL
微課sql最佳化(5)、統計資訊收集(3)-關於預設取樣率
2020-03-03
SQL
收集資料庫統計資訊需要收集直方圖資訊.
2016-05-18
資料庫直方圖
Oracle 12c資料庫最佳化器統計資訊收集的最佳實踐（三）｜何時不需要收集統計資訊
2017-10-26
Oracle資料庫
滲透測試需要需要學什麼？資訊收集工具之Nmap
2022-03-09
Fixed Objects Statistics統計資訊收集 - 2
2016-11-23
Object
微課sql最佳化(9)、如何獲取執行計劃
2020-03-04
SQL
微課sql最佳化(11) 、如何檢視執行計劃
2020-03-04
SQL
網路安全中資訊收集是什麼？資訊收集分為哪幾類?
2023-03-08
微課sql最佳化(13)、表的連線方法(2)-基礎概念
2020-03-04
SQL
微課sql最佳化(4)、幫助“表姐”最佳化報表
2020-03-03
SQL
微課sql最佳化(1)、基礎概念介紹
2020-03-03
SQL
Oracle收集統計資訊
2018-06-27
Oracle
Oracle 統計資訊收集
2017-07-18
Oracle
收集oracle統計資訊
2014-04-02
Oracle
ORACLE 收集統計資訊
2014-07-24
Oracle
微課sql最佳化(17)、不改程式碼,最佳化SQL(1)-最佳化方法總結
2020-03-04
SQL
資訊化為什麼需要專案管理?(轉)
2007-08-10
專案管理
收集統計資訊的SQL指令碼（sosi.sql）--崔華大師
2017-06-02
SQL指令碼
【統計資訊】Oracle常用的收集統計資訊方式
2020-11-30
Oracle
收集全庫統計資訊
2020-09-16
mysql如收集統計資訊
2016-07-05
MySql
MySQL 5.5 統計資訊收集
2017-12-15
MySql
微課sql最佳化(10)、關於資料訪問方法
2020-03-04
SQL
邊緣計算是什麼以及為什麼需要它
2019-01-09
【TUNE_ORACLE】定製化收集統計資訊SQL參考
2021-08-02
OracleSQL
Oracle 12c資料庫優化器統計資訊收集的最佳實踐（三）｜何時不需要收集統計資訊
2017-09-01
Oracle資料庫優化
MySQL系統如何收集統計資訊
2017-09-14
MySql
企業為什麼需要CRM系統
2020-05-28
天行健課堂 | 為什麼FMEA需要檔案化？
2023-04-26
Oracle統計資訊自動收集
2017-12-13
Oracle
oracle收集統計資訊job停止
2012-12-04
Oracle
微課錄製的幾種方式：錄製微課用什麼軟體好？
2020-07-20
為什麼需要Docker？
2019-01-21
Docker
微課sql最佳化(12)、表的連線方法(1)-幫助網友最佳化報表SQL
2020-03-04
SQL