网站安全检测在线,抖音小程序商城,照片视频制作软件,设计师网站兼职众所周知,统计信息直接影响到Oracle优化器最后的执行计划,所以定期收集统计信息成为DBA一项常规的工作,但是,对于一些大表,比如数据量超过几千万条,表分析后却有可能会导致应用系统一些SQL执行计划变差,比如出现大量的全表扫,严重影响数据库性能.如果出现这种情况,一种方法是对…众所周知,统计信息直接影响到Oracle优化器最后的执行计划,所以定期收集统计信息成为DBA一项常规的工作,但是,对于一些大表,比如数据量超过几千万条,表分析后却有可能会导致应用系统一些SQL执行计划变差,比如出现大量的全表扫,严重影响数据库性能.如果出现这种情况,一种方法是对找一张该查询涉及到的小表,执行grant select on table_name to public操作,然后再看执行计划是否变好,有时可能这个动作要尝试多次.如果实在不行只有导回原先的统计信息。这就需要分析之前对统计信息作过备份。下面阐述备份的操作 conn perfstat/perfstat 创建统计信息的备份表 exec dbms_stats.CREATE_STAT_TABLE(OWNNAMEPERFSTAT,STATTABBK_STAT_200809,TBLSPACETOOLS); 表的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) || ,statownPERFSTAT,TABNAME || || upper(table_name) || ,STATTABBK_STAT_200809,STATIDBK_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(OWNNAMEMOCSACCT,statownPERFSTAT,TABNA METPAYORDER,STATTABBK_STAT_200809,STATIDBK_STAT_20080918); exec dbms_stats.EXPORT_TABLE_STATS(OWNNAMEMOCSACCT,statownPERFSTAT,TABNA MET_ACCTWATER,STATTABBK_STAT_200809,STATIDBK_STAT_20080918); exec dbms_stats.EXPORT_TABLE_STATS(OWNNAMEMOCSACCT,statownPERFSTAT,TABNA MET_BALANCELOG,STATTABBK_STAT_200809,STATIDBK_STAT_20080918); exec dbms_stats.EXPORT_TABLE_STATS(OWNNAMEMOCSACCT,statownPERFSTAT,TABNA MET_DEDUCTIONINFO,STATTABBK_STAT_200809,STATIDBK_STAT_20080918); exec dbms_stats.EXPORT_TABLE_STATS(OWNNAMEMOCSACCT,statownPERFSTAT,TABNA MET_BOSSUSERSTATUSNOTIFY,STATTABBK_STAT_200809,STATIDBK_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_nameb.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)||,cascadeTRUE); 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_m500 union all select exec dbms_stats.gather_table_stats(OWNNAME||||upper(owner)||,tabname|||| upper(table_name)||,cascadeTRUE,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)||,cascadeTRUE,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)||,cascadeTRUE,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)||,cascadeTRUE,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_m20480 执行后我得到下列结果 exec dbms_stats.gather_table_stats(OWNNAMEMOCSCARD,tabnameT_CARDCODE,cascadeTRUE); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameSERVICEINFO,cascadeTRUE); exec dbms_stats.gather_table_stats(OWNNAMEMOCSCARD,tabnameSERVICEINFO,cascadeTRUE); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameTPAYAPPINFO,cascadeTRUE); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameT_USERACCT,cascadeTRUE,estimate_percent 10); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameT_ACCTINFO,cascadeTRUE,estimate_percent 10); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameT_ACCTBOOK,cascadeTRUE,estimate_percent 10); exec dbms_stats.gather_table_stats(OWNNAMEMOCSCARD,tabnameT_CARDWATER,cascadeTRUE,estimate_percent 10); exec dbms_stats.gather_table_stats(OWNNAMEMOCSCARD,tabnameT_CARDINFO,cascadeTRUE,estimate_percent 10); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameT_ACCTINFO_HIS,cascadeTRUE,estimate_percent 3); exec dbms_stats.gather_table_stats(OWNNAMEMOCSACCT,tabnameT_TEMP_ACCTWATER,cascadeTRUE,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) || ,statownPERFSTAT,TABNAME || || upper(table_name) || ,STATTABBK_STAT_200809,STATIDBK_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
