定期分析數(shù)據(jù)庫(kù)對(duì)象的腳本
2024-07-21 02:35:50
供稿：網(wǎng)友
Oracle9以后假如你想用基于成本的優(yōu)化器，需要定期(每周)對(duì)數(shù)據(jù)庫(kù)里的表和索引做analyze分析。
數(shù)據(jù)庫(kù)參數(shù)文件initorasid.ora里默認(rèn)的優(yōu)化器 optimizer_mode = choose
你要改成 optimizer_mode = first_rows (OLTP系統(tǒng))
optimizer_mode = all_rows (DSS 系統(tǒng))
下面是一個(gè)可以在UNIX環(huán)境自動(dòng)生成分析表和索引的腳本analyze.sh
(sys用戶的密碼passWord要根據(jù)情況修改。)
---------------------------------------------------------------------------------------
su - oracle -c "sqlplus sys/password"<
set pages 9999
set heading off
set echo off
set feedback off
spool /oracle_backup/bin/analyze.sql;
select
'analyze table 'owner'.'table_name' estimate statistics sample 5000 rows;'
from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT');
select
'analyze index 'owner'.'index_name' compute statistics;'
from dba_indexes
where owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;
set echo on
set feedback on
spool /oracle_backup/log/analyze.log;
@/oracle_backup/bin/analyze.sql
spool off;
exit;
---------------------------------------------------------------------------------------
假如你經(jīng)常變動(dòng)的表和索引只屬于某個(gè)特定的用戶(假如是test)可以把上面的
owner not in ('SYS','SYSTEM','PERFSTAT') 改成
owner in ('TEST')
來(lái)進(jìn)行定期的分析。
注重事項(xiàng)：假如你使用的是默認(rèn)的優(yōu)化器(choose)，一定不要定期使用上面那個(gè)analyze.sh腳本。
因?yàn)檫@時(shí)優(yōu)化器可能更傾向于全表掃描。
假如統(tǒng)計(jì)分析資料不全，SQL運(yùn)行時(shí)會(huì)對(duì)缺少統(tǒng)計(jì)資料的表進(jìn)行數(shù)據(jù)采集。會(huì)大大降低SQL的執(zhí)行速度。
我們要用下面這個(gè)del_analyze.sh腳本定期刪除可能產(chǎn)生的分析結(jié)果, 保證優(yōu)化器按規(guī)則(rule)執(zhí)行。
---------------------------------------------------------------------------------------
su - oracle -c "sqlplus sys/password"<
set pagesize 9999;
set linesize 120;
set heading off;
set echo off;
set feedback off;
spool /oracle_backup/bin/del_analyze.sql;
select
'analyze table 'owner'.'table_name' delete statistics;'
from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT');
select
'analyze index 'owner'.'index_name' delete statistics;'
from dba_indexes
where owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;
set echo on;
set feedback on;
spool /oracle_backup/log/del_analyze.log;
@/oracle_backup/bin/del_analyze.sql
spool off;
exit;
