一.在統(tǒng)計信息收集踩過的坑
收集統(tǒng)計信息需要注意什么呢，應(yīng)該跟其他運維操作一樣的注意點：
1.操作之前查看原來的信息。
2.做好備份。
3.搞清楚執(zhí)行的命令具體的含義，造成的后果。（這一步可能需要多測試和犯錯）
4.回退措施
之前踩過的坑是在手動收集某個表統(tǒng)計信息之前沒有查看直方圖的信息，默認沒有收集直方圖，導(dǎo)致出現(xiàn)了性能問題，最后使用備份的統(tǒng)計信息進行了回退。
二.數(shù)據(jù)庫自動收集統(tǒng)計信息任務(wù)
首先來了解一下數(shù)據(jù)庫自動收集統(tǒng)計信息的任務(wù)詳情。
1.是否默認開啟
SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
SQL>
可以看到，任務(wù)是默認開啟的
2.具體操作是什么
SQL> col client_name for a40
SQL> col task_name for a20
SQL> col operation_name for a40
SQL> SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task;
CLIENT_NAME TASK_NAME OPERATION_NAME STATUS
---------------------------------------- -------------------- ---------------------------------------- ------------------------
sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED
auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
---
SQL> col program_action for a80
SQL> col program_type for a20
SQL> SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';
PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION
------------------------------ -------------------- --------------------------------------------------------------------------------
GATHER_STATS_PROG STORED_PROCEDURE dbms_stats.gather_database_stats_job_proc
自動統(tǒng)計信息收集是通過調(diào)用dbms_stats.gather_database_stats_job_proc來實現(xiàn)的。
3.查看什么時間去執(zhí)行
--查看具體什么時間做統(tǒng)計信息收集
SQL> SELECT a.WINDOW_NAME,a.REPEAT_INTERVAL,a.duration FROM dba_scheduler_windows a WHERE ENABLED = 'TRUE';
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected
可以看到，默認是在周一到周五的晚上10點執(zhí)行，周六、周日是在6點執(zhí)行。
三.手動收集統(tǒng)計信息
以對單個表收集統(tǒng)計信息為例：
1.常用腳本選項解釋
DBMS_STATS.GATHER_TABLE_STATS
如下給SCOTT.DEPT表收集統(tǒng)計信息：
begin
DBMS_STATS.GATHER_TABLE_STATS( ownname=>'SCOTT',
tabname=>'EMP',
ESTIMATE_PERCENT=>10,
method_opt=>'for all columns size 1',
no_invalidate => FALSE,
cascade=>true,
force=>true,
degree=>8);
end;
/
1.1.ownername
表對應(yīng)的schema
1.2.tabname
表名
1.3.ESTIMATE_PERCENT
采樣率
對于小于1GB的表，建議設(shè)置100%采樣。
對于1GB-5GB的表，建議50%采樣。
對于大于5GB的表，建議30%采樣。
對于特別大的表，建議分區(qū)，然后對每個分區(qū)進行統(tǒng)計信息收集。
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott',tabname=>'emp',partname=>'p1',method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>8) ;
1.4.method_opt
用于控制收集直方圖策略
for all columns size 1 --表示所有列都不收集直方圖
for all columns size skewonly --表示對表中所有列收集自動判斷是否收集直方圖
for all columns size auto --表示對出現(xiàn)在where條件中列自動判斷是否收集直方圖
for all columns size repeat --表示當前有哪些列收集了直方圖，現(xiàn)在就對哪些列收集直方圖
for columns column1 size skeonly --表示單獨對column1列收集直方圖，對于其余列，如果之前收集過，現(xiàn)在也收集直方圖。
在實際工作中，當系統(tǒng)趨于穩(wěn)定之后，使用repeat方式收集直方圖。
1.5.no_invalidate
表示共享池中涉及到該表的游標是否立即失效，默認值為DBMS_STATS.AUTO_INVALIDATE,表示讓oracle自己決定是否立即失效。
如果值設(shè)置為False，則為立即失效，相關(guān)sql會根據(jù)最新的統(tǒng)計信息進行硬解析。
1.6.cascade
表示收集表的統(tǒng)計信息的時候，是否級聯(lián)收集索引的統(tǒng)計信息，默認是數(shù)據(jù)庫自己判斷，如果設(shè)置為TRUE，則級聯(lián)收集索引的統(tǒng)計信息。
1.7.force
是否強制收集統(tǒng)計信息，默認為FALSE，如果不在維護窗口，不建議設(shè)置為TRUE。
1.8.degree
并行度
2.規(guī)范步驟
2.1.首先查看對應(yīng)表的統(tǒng)計信息
--首先查看上次收集時間
select stale_stats,last_analyzed from dba_tab_statistics where owner = 'SCOTT' and table_name = 'EMP';
--查看直方圖收集情況
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct/b.num_rows*100,2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner='SCOTT'
and a.table_name='EMP';
2.2.如果有必要（收集策略有改動等），備份之前的統(tǒng)計信息
-創(chuàng)建統(tǒng)計信息歷史保留表
exec dbms_stats.create_stat_table(ownname=> 'SCOTT' ,stattab=> 'stat_table' );
--導(dǎo)出整個scheme的統(tǒng)計信息
exec dbms_stats.export_schema_stats(ownname=>'SCOTT' ,stattab => 'stat_table' );
2.3.選擇合適的收集策略
–如果系統(tǒng)運行穩(wěn)定，則可以選擇repeat
begin
DBMS_STATS.GATHER_TABLE_STATS( ownname=>'SCOTT',
tabname=>'EMP', ESTIMATE_PERCENT=>10,
method_opt=>'for all columns size repeat',
no_invalidate => FALSE,
cascade=>true,
degree=>8);
end;
/
如果是個新系統(tǒng)，則可以用auto
begin
DBMS_STATS.GATHER_TABLE_STATS( ownname=>'SCOTT',
tabname=>'EMP',
ESTIMATE_PERCENT=>10,
method_opt=>'for all columns size auto',
no_invalidate => FALSE,
cascade=>true,
degree=>8);
end;
/
2.4.回退
--導(dǎo)入表的歷史統(tǒng)計信息
exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'TEST',stattab=>'stat_table');
--如果進行分析后，大部分表的執(zhí)行計劃都走錯，需要導(dǎo)回整個scheme的統(tǒng)計信息
exec dbms_stats.import_schema_stats(ownname=>'SCOTT' ,stattab=>'stat_table' );
