Home » RDBMS Server » Server Administration » dbms_stats - dbms_utility -analyze command : Differences?
dbms_stats - dbms_utility -analyze command : Differences? [message #60639] Mon, 23 February 2004 05:13 Go to next message
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 #60640 is a reply to message #60639] Mon, 23 February 2004 05:57 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
dbms_stats is available in 8i also and is recommended over dbms_utility. Its detailed in the 8i documentation.

Yes,Analyze is available in both 8i and 9i.

HTH
Thiru
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #60644 is a reply to message #60640] Mon, 23 February 2004 18:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #60648 is a reply to message #60647] Mon, 23 February 2004 23:59 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Yeah u r right, DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA_name') will analyze the whole schema and it will work fine.

Moreover i also use the same for my reporting servers.

Daljit Singh.
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #60650 is a reply to message #60648] Tue, 24 February 2004 01:08 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you so much Daljit!

Best regards,

Patrick Tahiri.
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #60655 is a reply to message #60647] Tue, 24 February 2004 04:08 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
I believe you got your answers from Daljit. DBMS_STATS has got lot more functionality than dbms_utility or Analyze.

-Thiru
Re: dbms_stats - dbms_utility -analyze command : Differences? [message #192813 is a reply to message #60655] Wed, 13 September 2006 14:01 Go to previous message
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
Previous Topic: UPPERCASE with accent
Next Topic: how to setup ORA_DBA group in Windows XP for 'oracle' OS UserID
Goto Forum:
  


Current Time: Fri Dec 02 20:30:41 CST 2016

Total time taken to generate the page: 0.13334 seconds