-
analyze compute stats
Hi Friends,
I forgot the syntax on how to analyze and compute stats on all tables/indexes
in one's schema. Can u help pls ....thanks
-
-
there is no syntax to forget..
you can use dbms_utility package..
Eg.,
EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);
-
Originally Posted by jennifer2007
I forgot the syntax on how to analyze and compute stats on all tables/indexes
You forgot to mention you also forgot the syntax on how to google or metalink it
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by ams-jamali
there is no syntax to forget..
you can use dbms_utility package..
Eg.,
EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);
ugh no way should you use dbms_utility, not at all
-
Originally Posted by ams-jamali
EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
Ugh, dbms_utility.
I feel more secure in my job today.
Jeff Hunter
-
Dave..,
can you please explain(if it is not secure) why i should not use dbms.utility package to find entire schema stats.
(by god's graze till now i have used only on testDB but not in prod)
Last edited by ams-jamali; 05-31-2007 at 07:21 AM.
Reason: increase details
-
DBMS_UTILITY.ANALYZE_SCHEMA got desupported in Ora9i
The only supported way to generate stats in Ora9i and upper is DBMS_STATS
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
only now i came to know it is desupported.
Thanks PavB
-
To add more points for clarity:
DBMS_STATS was available from 8i onwards apart from ANALYZE/DBMS_UTILITY. Oracle recommended to use DBMS_STATS as this collects statistics globally and more accurate and also the ability to parallelise statistics collection. These are not possible in ANALYZE and it collects statistics at the lowest level.
Optimizer uses global statistics to gather access path.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|