Home » RDBMS Server » Server Administration » Tuning Oracle Analyze
Tuning Oracle Analyze [message #264587] Mon, 03 September 2007 20:37 Go to next message
Messages: 26
Registered: September 2006
Junior Member
Oracle version :
OS : HP-UX B.11.11 U 9000/800
# of CPUs : 12

I am looking for a method to make Oracle Anaylze run with less time as possible.
Currently I am testing two methods below to do Oracle Analyze but I don't see a significant difference in speed.



control file parameter settings:



control file parameter settings:

If you know any other methods of making Oracle Analyze run faster please let me know.

Any help will be greatly appreciated.

Re: Tuning Oracle Analyze [message #264590 is a reply to message #264587] Mon, 03 September 2007 20:59 Go to previous messageGo to next message
Messages: 25537
Registered: January 2009
Location: SoCal
Senior Member
One of the basics of Engineering is as follows:
You can have it good, fast, or cheap; pick any TWO.

Regardless of the front end interface, in order to collect an accurate set of statistics a fixed number of objects have to be "100%" read.
Each & every I/O operation takes a finite amount of time.
Unless or until you can revoke laws of physics, collecting statistics will take a measurable duration.

Your question is about the same as me asking you, "How can I teach my per pig how to fly?".

Re: Tuning Oracle Analyze [message #266400 is a reply to message #264587] Mon, 10 September 2007 14:25 Go to previous message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
I see that you are doing a "compute". Try doing an "estimate".

Here is how I see it

you do a compute (which is a 100% and can take a long time as you are reading ALL the data) = 100% accurate data

you do an estimate 10% (small and fast and you are reading 10% of the data) which may be 95% accurate estimate. Note that the 10% is the amount of records read, not the % of accuracy. Say you have a table with 1 mill rows and do a 10% analyze. then you are reading 100,000 rows. The stats are pertty good as reading 10% of the data gives you an estimate of how the whole table is. Saves you on time also.

You can also say estimate 10000 rows where you read ONLY 10,000 rows.

I usually do a 10% estimate. Works good enough for me.
Sanjay B.
Previous Topic: Dropping tablespace did not free disk space - HELP!
Next Topic: Compiling INVALID Objects
Goto Forum:

Current Time: Wed Jul 26 01:53:57 CDT 2017

Total time taken to generate the page: 1.53038 seconds