oracle 9i表分析,Oracle9i, 10g 优化分析统计(dbms_stats)脚本

---------------------------------------------------------

Oracle9i (未开启table monitoring时)

---------------------------------------------------------

索引全分析

表分两种，大表10%，小表100%

#start

$ORACLE_HOME/bin/sqlplus /nolog

prompt "analyze start,please wait......"

set serveroutput on size 100000

declare

v_per   number(3);

v_start number := dbms_utility.get_time;

v_time  number;

begin

for rec in (select segment_name,segment_type,ceil(sum(bytes)/1024/1024) segment_size

from user_segments

where segment_type like 'TABLE%' or segment_type like 'INDEX%'

group by segment_name,segment_type)

loop

--start analyze

if rec.segment_type = 'INDEX' then

dbms_stats.gather_index_stats(ownname => 'USER',

indname => rec.segment_name,

degree  => 8

);

elsif rec.segment_type = 'INDEX PARTITION' then

dbms_stats.gather_index_stats(ownname => 'USER',

indname => rec.segment_name,

degree  => 8

);

elsif rec.segment_type = 'TABLE' then

case when rec.segment_size < 100 then

v_per := 100;

else

v_per := 10;

end case;

dbms_stats.gather_table_stats(ownname          => 'USER',

tabname          => rec.segment_name,

estimate_percent => v_per,

method_opt       => 'FOR ALL COLUMNS SIZE 1',       -- SIZE 1 不收集直方图信息

degree           => 8);

elsif rec.segment_type = 'TABLE PARTITION' then

v_per := 10;

dbms_stats.gather_table_stats(ownname          => 'USER',

tabname          => rec.segment_name,

estimate_percent => v_per,

method_opt       => 'FOR ALL COLUMNS SIZE 1',

degree           => 8);

end if;

v_time  := ceil((dbms_utility.get_time - v_start)/100);

dbms_output.put_line(rpad(rec.segment_name||'('||rec.segment_size||'M)',40,'.')||'elaped time '||v_time||'s');

v_start := dbms_utility.get_time;

end loop;

end;

/

prompt "analyze end"

exit

EOF

<

--------------------------------------------------------------

Oracle9i,10g (开启table monitoring时)

--------------------------------------------------------------

都采用auto方式。

SQL> being

dbms_stats.gather_schema_stats(

ownname => 'USER',

estimate_percent => dbms_stats.auto_sample_size,    -- Oracle根据表的大小和列值分布在5％到20％间取值

method_opt => 'for all columns size auto',

cascade=>TRUE,

degree => 8 );

end;

/