众所周知,统计信息直接影响到Oracle优化器最后的执行计划,所以定期收集统计信息成为DBA一项常规的工作,但是,对于一些大表,比如数据量超过几千万条,表分析后却有可能会导致应用系统一些SQL执行计划变差,比如出现大量的全表扫,严重影响数据库性能.如果出现这种情况,一种方法是对找一张该查询涉及到的小表,执行grant select on table_name to public操作,然后再看执行计划是否变好,有时可能这个动作要尝试多次.如果实在不行，只有导回原先的统计信息。这就需要分析之前对统计信息作过备份。下面阐述备份的操作：

 

conn perfstat/perfstat

创建统计信息的备份表：

exec dbms_stats.CREATE_STAT_TABLE(OWNNAME=>'PERFSTAT',STATTAB=>'BK_STAT_200809',TBLSPACE=>'TOOLS');

表的owner为perfstat，这张存放统计信息的表名为：bk_stat_200809,表空间为perfstat的默认表空间tools。

为这张表创建一个同义词，方便其它用户访问：

create public synonym BK_STAT_200809 for BK_STAT_200809;

赋予perfstat权限，否则无法备份统计信息：

conn /as sysdba

grant analyze any to perfstat;

生成备份需要分析的表的统计信息的SQL，条件因需求不同而不同，我这里是锁定某几个用户下面08年1月前分析过的表。

conn perfstat/perfstat

set line 250

set pages 10000

set timing on

set time on

col script_for_export_stat for a250

spool script_for_export_stat.sql

select 'exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>' || '''' ||

       upper(owner) || ''',statown=>''PERFSTAT'',TABNAME=>' || '''' || upper(table_name) ||

       ''',STATTAB=>''BK_STAT_200809'',STATID=>''BK_STAT_20080918'');' as script_for_export_stat

  from dba_tables

 where last_analyzed is not null

   and owner in ('MOCSCARD','MOCSACCT')

   and last_analyzed < to_date('20080101', 'yyyymmdd')

   order by owner;

spool off

这样，生成如下面的SQL：

exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'MOCSACCT',statown=>'PERFSTAT',TABNA

ME=>'TPAYORDER',STATTAB=>'BK_STAT_200809',STATID=>'BK_STAT_20080918');

 

exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'MOCSACCT',statown=>'PERFSTAT',TABNA

ME=>'T_ACCTWATER',STATTAB=>'BK_STAT_200809',STATID=>'BK_STAT_20080918');

 

exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'MOCSACCT',statown=>'PERFSTAT',TABNA

ME=>'T_BALANCELOG',STATTAB=>'BK_STAT_200809',STATID=>'BK_STAT_20080918');

 

exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'MOCSACCT',statown=>'PERFSTAT',TABNA

ME=>'T_DEDUCTIONINFO',STATTAB=>'BK_STAT_200809',STATID=>'BK_STAT_20080918');

 

exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>'MOCSACCT',statown=>'PERFSTAT',TABNA

ME=>'T_BOSSUSERSTATUSNOTIFY',STATTAB=>'BK_STAT_200809',STATID=>'BK_STAT_20080918

');

另外，可以查看一下需要分析的表的大小：

select a.owner,a.segment_name,a.segment_type,a.tablespace_name,round(a.bytes/1024/1024/1024,2) tablesize,b.last_analyzed

from dba_segments a,dba_tables b

where a.segment_name=b.table_name

and b.owner in ('MOCSACCT','MOCSCARD')

and b.last_analyzed < to_date('20080101', 'yyyymmdd');

 

分析表的方法可用analyze语句进行分析，也可用dbms_stats包进行分析。Oracle 9i开始就推荐使用dbms_stats包进行分析，且analyze语句对于分区表的支持不是很好：用analyze语句分析后，查询dba_tables.last_analyed信息会不准确，但是dba_part_tables. last_analyed信息是准确的，存在这个bug。因此，在分析时，推荐使用dbms_stats包来进行分析。―――小荷语

根据不表的不同大小，执行不同的采样值

小于500M的表，estimate_percent按100%分析，大于500M小于1G的表，按50%分析；大于1G小于5G的表，按10%分析；大于5G小于10G的表，按3%分析；大于10G的表小于20G的表按1%分析；大于20G的表不建议轻易分析，当然，这也与每个项目及系统的特点有关。

 

可以用以下的语句直接生成分析语句：

set line 250

col script_for_gather_stat for a250

spool script_for_gather_stat.sql

select  'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE);' as script_for_gather_stat

from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m<=500

 union all

 select  'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=> 50);' as script_for_gather_stat

from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m between 500 and 1024

  union all

 select  'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=> 10);' as script_for_gather_stat

from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m between 1024 and 5120

  union all

 select  'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=> 3);' as script_for_gather_stat

from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m between 5120 and 10240

   union all

 select  'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=> 1);' as script_for_gather_stat

from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m between 10240 and 20480

    union all

select  '--NOT ANALYZE TABLE LAGER THAN 20G:'||table_name from (

select owner,segment_name table_name, sum(bytes) / 1024 / 1024 size_m

  from dba_segments

 where owner in ('MOCSCARD','MOCSACCT')

   and segment_name in

       (select table_name

          from dba_tables

         where last_analyzed is not null

           and owner in ('MOCSCARD','MOCSACCT')

           and table_name not in

               ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

           and last_analyzed < to_date('20080101', 'yyyymmdd'))

           group by segment_name,owner

 order by size_m) a

 where a.size_m>20480

执行后我得到下列结果：

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSCARD',tabname=>'T_CARDCODE',cascade=>TRUE);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'SERVICEINFO',cascade=>TRUE);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSCARD',tabname=>'SERVICEINFO',cascade=>TRUE);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'TPAYAPPINFO',cascade=>TRUE);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'T_USERACCT',cascade=>TRUE,estimate_percent=> 10);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'T_ACCTINFO',cascade=>TRUE,estimate_percent=> 10);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'T_ACCTBOOK',cascade=>TRUE,estimate_percent=> 10);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSCARD',tabname=>'T_CARDWATER',cascade=>TRUE,estimate_percent=> 10);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSCARD',tabname=>'T_CARDINFO',cascade=>TRUE,estimate_percent=> 10);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'T_ACCTINFO_HIS',cascade=>TRUE,estimate_percent=> 3);

exec dbms_stats.gather_table_stats(OWNNAME=>'MOCSACCT',tabname=>'T_TEMP_ACCTWATER',cascade=>TRUE,estimate_percent=> 3);

 

现在，就可以执行以上的分析语句进行分析了，建议在系统空闲的时候进行分析用crontab定时任务完成。

 

一旦发生意外，则可以导回原来的统计信息！

col script_for_import_stat for a250

spool script_for_import_stat.sql

select 'exec dbms_stats.IMPORT_TABLE_STATS(OWNNAME=>' || '''' ||

       upper(owner) || ''',statown=>''PERFSTAT'',TABNAME=>' || '''' || upper(table_name) ||

       ''',STATTAB=>''BK_STAT_200809'',STATID=>''BK_STAT_20080918'');' as script_for_import_stat

  from dba_tables

 where last_analyzed is not null

   and owner in ('MOCSCARD','MOCSACCT')

   and table_name not in ('SUBSCRIPTION_HISTORY', 'SUBSCRIBER_HISTORY')

   and last_analyzed < to_date('20080101', 'yyyymmdd');

 

spool off