每日踩坑之oracle 11g 统计信息收集

一.在统计信息收集踩过的坑

收集统计信息需要注意什么呢，应该跟其他运维操作一样的注意点：
1.操作之前查看原来的信息。
2.做好备份。
3.搞清楚执行的命令具体的含义，造成的后果。（这一步可能需要多测试和犯错）
4.回退措施

之前踩过的坑是在手动收集某个表统计信息之前没有查看直方图的信息，默认没有收集直方图，导致出现了性能问题，最后使用备份的统计信息进行了回退。

二.数据库自动收集统计信息任务

首先来了解一下数据库自动收集统计信息的任务详情。

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> 

可以看到，任务是默认开启的

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

自动统计信息收集是通过调用dbms_stats.gather_database_stats_job_proc来实现的。

3.查看什么时间去执行

--查看具体什么时间做统计信息收集
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点执行，周六、周日是在6点执行。

三.手动收集统计信息

以对单个表收集统计信息为例：

1.常用脚本选项解释

DBMS_STATS.GATHER_TABLE_STATS
如下给SCOTT.DEPT表收集统计信息：

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

表对应的schema

1.2.tabname

表名

1.3.ESTIMATE_PERCENT

采样率
对于小于1GB的表，建议设置100%采样。
对于1GB-5GB的表，建议50%采样。
对于大于5GB的表，建议30%采样。
对于特别大的表，建议分区，然后对每个分区进行统计信息收集。

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 --表示对出现在where条件中列自动判断是否收集直方图
for all columns size repeat --表示当前有哪些列收集了直方图，现在就对哪些列收集直方图
for columns column1 size skeonly --表示单独对column1列收集直方图，对于其余列，如果之前收集过，现在也收集直方图。

在实际工作中，当系统趋于稳定之后，使用repeat方式收集直方图。

1.5.no_invalidate

表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS.AUTO_INVALIDATE,表示让oracle自己决定是否立即失效。
如果值设置为False，则为立即失效，相关sql会根据最新的统计信息进行硬解析。

1.6.cascade

表示收集表的统计信息的时候，是否级联收集索引的统计信息，默认是数据库自己判断，如果设置为TRUE，则级联收集索引的统计信息。

1.7.force

是否强制收集统计信息，默认为FALSE，如果不在维护窗口，不建议设置为TRUE。

1.8.degree

并行度

2.规范步骤

2.1.首先查看对应表的统计信息

--首先查看上次收集时间
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.如果有必要（收集策略有改动等），备份之前的统计信息

-创建统计信息历史保留表
exec  dbms_stats.create_stat_table(ownname=> 'SCOTT' ,stattab=> 'stat_table' ); 
--导出整个scheme的统计信息
exec  dbms_stats.export_schema_stats(ownname=>'SCOTT' ,stattab  => 'stat_table' ); 

2.3.选择合适的收集策略

–如果系统运行稳定，则可以选择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;
/

如果是个新系统，则可以用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.回退

--导入表的历史统计信息
exec  dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'TEST',stattab=>'stat_table'); 

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
exec  dbms_stats.import_schema_stats(ownname=>'SCOTT' ,stattab=>'stat_table' );