dbms_stats - dbms_utility -analyze command : Differences? [message #60639] |
Mon, 23 February 2004 05:13 |
Patrick Tahiri
Messages: 119 Registered: January 2004
|
Senior Member |
|
|
Hi,
I'm using Oracle 8i.
I would like to know the differences between dbms_stats and dbms_utility?
Is dbms_stat available only for Oracle 9i? And then I can only use dbms_utility (ex: dbms_utility.analyze_schema) with my Oracle 8i server (and for Oracle 9i: dbms_sats.gather_schema_stats ??)?
And Analyze is available for both: Oracle 8i and Oracle 9i?!
I will write a script (a crontab job: dbms_utility/dbms_stats) that will be executed on a weekly basis and that will analyze all the objects in all my schemas! So I'll keep up to date my statistics and histograms on my tables and indexes to help the CBO make the good decisions (avoid bad choices of the CBO)!
Thank you for your help!
Regards,
Patrick Tahiri.
|
|
|
|
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #60644 is a reply to message #60640] |
Mon, 23 February 2004 18:30 |
Daljit Singh
Messages: 290 Registered: October 2003 Location: Texas
|
Senior Member |
|
|
Hi,
DBMS_STATS is always preferrd over DBMS_UTILITY because it provides more control like in dbms_stats u can select the statistics in parallel u can backup the previous statistics and then collect the new one and again use the previously backed up statistics if needed. Moreover u can export/import the statistics among different databases.
For more info refer oracle documentation.
Daljit Singh
|
|
|
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #60647 is a reply to message #60640] |
Mon, 23 February 2004 22:07 |
Patrick Tahiri
Messages: 119 Registered: January 2004
|
Senior Member |
|
|
Hi,
Ok!
Is: DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA_name') equivalent to dbms_utility.analyze_schema('SCHEMA_NAME','COMPUTE') ?
Does DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA_name') analyze, compute and gather the statistic for all my objects (table and corresponding indexes) in the target schema? Gather is well default!
My objective is to run once a week (sunday night) DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA_name') for all my schemas on my 3 Oracle servers! What do you think about it: is it recommanded??
Many thanks!
Regards,
Patrick Tahiri.
|
|
|
|
|
|
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #192813 is a reply to message #60655] |
Wed, 13 September 2006 14:01 |
bbathini
Messages: 1 Registered: August 2006
|
Junior Member |
|
|
i have the same requirement ,
i need to calculate the statistics of particular schema partially i,e i need some of the tables in that particular schema to be calculated and discarding some of the tables
can anyone help me ...
patric can you give me the script which you have used for this requiremnt , you can change the names of confidential parameters
please help me.
Thanks
Ski
|
|
|