oracle 表分析影响吗,Oracle 统计信息（备份/导入）表分析-[注意要刷缓存]

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