微課sql優(yōu)化(2)-為什么需要收集統(tǒng)計(jì)信息
1、為什么需要收集統(tǒng)計(jì)信息
Cost-based optimizer (CBO)，Based on object statistics，優(yōu)化器統(tǒng)計(jì)信息描述數(shù)據(jù)庫(kù)中的對(duì)象的詳細(xì)信息，
查詢優(yōu)化器使用這些統(tǒng)計(jì)信息為每個(gè)SQL語(yǔ)句選擇最佳執(zhí)行計(jì)劃。
數(shù)據(jù)庫(kù)將優(yōu)化器統(tǒng)計(jì)信息存儲(chǔ)在數(shù)據(jù)字典中。您可以使用數(shù)據(jù)字典視圖訪問這些統(tǒng)計(jì)信息。
由于數(shù)據(jù)庫(kù)中的對(duì)象可能會(huì)不斷更改，因此必須定期更新統(tǒng)計(jì)信息，以便準(zhǔn)確描述這些對(duì)象。Oracle數(shù)據(jù)庫(kù)自動(dòng)維護(hù)優(yōu)化器統(tǒng)計(jì)信息。
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軟件包手動(dòng)維護(hù)優(yōu)化器統(tǒng)計(jì)信息。
2、哪些表需要收集統(tǒng)計(jì)信息
自動(dòng)統(tǒng)計(jì)任務(wù)能滿足大部分?jǐn)?shù)據(jù)庫(kù)的業(yè)務(wù)需求，但存在特殊情況需要手工收集統(tǒng)計(jì)信息，
1、業(yè)務(wù)表數(shù)據(jù)被delete、truncate、impdp/imp大批量數(shù)據(jù)變理時(shí)。
2、數(shù)據(jù)變更量超過10%時(shí)。
查詢哪些表需要收集統(tǒng)計(jì)信息
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 hh34:mi') last_analyzed
from dba_tab_statistics
where owner = 'HT'
and (stale_stats = 'YES' or last_analyzed is null);
3、練習(xí)題1、測(cè)試統(tǒng)計(jì)信息閾值： 變更量超過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);
SQL> DELETE FROM ht.TEST WHERE ROWNUM<=(select count(1) from ht.TEST)*0.1;
8632 rows deleted.
SQL> commit;
Commit complete.
exec dbms_stats.flush_database_monitoring_info; --刷新變更信息
set line 200
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 hh34: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 hh34:mi') last_analyzed
from dba_tab_statistics
where owner = 'HT' AND TABLE_NAME='TEST';
4、小結(jié)
哪些情況需要從新收集統(tǒng)計(jì)信息,
1、業(yè)務(wù)表數(shù)據(jù)被delete、truncate、impdp/imp大批量數(shù)據(jù)變更時(shí)。
2、數(shù)據(jù)變更量超過10%時(shí),統(tǒng)計(jì)信息狀態(tài)標(biāo)記為過期
。
