10g and analyze

[bookouri]

In our old 8i databases we used to analyze the tables fairly regularly. How is it recommended to handle analyze in 10g? I've found a few references but was just wondering how people are actually doing it compared to the references Im finding.

any comments would be appreciated

 

[LKBrwnDBA]

1) Use dbms_stats package
2) Activate 'monitoring' on all tables/indexes.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bookouri]

Do you mean activate addm on all tables/indexes?

 

[LKBrwnDBA]

NO, do this:

ALTER {TABLE|INDEX} {Table_Name|Index_Name} MONITORING;

-- OR --

DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING (
ownname VARCHAR2 DEFAULT NULL,
monitoring BOOLEAN DEFAULT TRUE);



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bookouri]

Thanks, I've never read anything related to that dbms_stats monitoring option.. Ill have to research that..

thanks again

 

[bookouri]

Am I correct in thinking that you no longer want to use analyze in 10g? Should I only be using the dbms_stats package now?

 

[LKBrwnDBA]

I have read some articles where both are compared and sometimes one is faster than the other.

The one thing that I know works faster/better is that when you set the MONITORING option, you can use the 'GATHER_AUTO' option of the DBMS_STATS.GATHER_SCHEMA_STATS procedure which gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics.


My 2¢




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb