Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

10g and analyze

Status
Not open for further replies.

bookouri

IS-IT--Management
Joined
Feb 23, 2000
Messages
1,464
Location
US
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
 

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
 
Do you mean activate addm on all tables/indexes?
 

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);

[noevil]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Thanks, I've never read anything related to that dbms_stats monitoring option.. Ill have to research that..

thanks again
 
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?
 

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.
[noevil]

My 2¢




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top