Here is what we use which also incorporates using our own definition
of STALE with monitoring on. It includes indexes as well as
partitioned and non-partitioned tables as well as non-monitored tables.
HTH
select 'analyze table ' || '${SCHEMA}.' ||
a.table_name ||
' compute statistics;' from
dba_tables a, all_tab_modifications
b where a.owner =
upper('${SCHEMA}')
and b.table_owner =
a.owner
and a.table_name =
b.table_name
and a.partitioned =
'NO' and
(a.num_rows is
null
or
((b.inserts+b.deletes+b.updates)*100)
/ decode(a.num_rows,0,1,a.num_rows) >
${PERCENTAGE}
or b.truncated = 'YES')
union select 'analyze table ' ||
'${SCHEMA}.' ||
a.table_name || ' partition ('
||
b.partition_name || ') compute
statistics;' from
dba_tab_partitions a, all_tab_modifications
b where a.table_owner =
upper('${SCHEMA}') and
b.table_owner = a.table_owner
and a.table_name =
b.table_name and
a.partition_name =
b.partition_name and (a.num_rows
is
null
or
((b.inserts+b.deletes+b.updates)*100)
/ decode(a.num_rows,0,1,a.num_rows) >
${PERCENTAGE}
or b.truncated = 'YES')union select 'analyze index ' ||
'${SCHEMA}.' || index_name
||
' compute statistics;' from
dba_indexes where
table_owner=upper('${SCHEMA}' )
and index_name not in (select distinct
index_name
from
dba_part_indexes
where
owner=upper('${SCHEMA}')
and locality = 'LOCAL') and
table_name
in
(select distinct a.table_name from
dba_tab_partitions a, all_tab_modifications
b where a.table_owner =
upper('${SCHEMA}') and
b.table_owner = a.table_owner
and a.table_name =
b.table_name and
a.partition_name =
b.partition_name and (a.num_rows
is
null
or
((b.inserts+b.deletes+b.updates)*100)
/ decode(a.num_rows,0,1,a.num_rows) >
${PERCENTAGE}
or b.truncated = 'YES'))union select 'analyze
table ' || '${SCHEMA}.'
||
table_name || ' compute
statistics;' from
dba_tables where owner =
upper('${SCHEMA}') and
monitoring = 'NO';
If you have any questions, please feel free to call me or drop me a
note.
Stephen AndertScottsdale, Arizona
>>> rgramolini_at_tax.state.vt.us 01/03/02 06:00AM
>>>Good morning everyone,Can anyone give me the command to
analyze a partitioned tabled? To analyzethis table unpartitioned I
use: analyze table schema.table_name computestatistics;I am
partitioning it into 6 partitions and want to compute statistics oneach
partition.Thanks in advance,Ruth-- Please see the
official ORACLE-L FAQ: <A
href="
http://www.orafaq.com/">
http://www.orafaq.com-- Author: Ruth
Gramolini INET: rgramolini_at_tax.state.vt.usFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Thu Jan 03 2002 - 09:29:32 CST
