OraFAQ Forum: Server Administration » Tuning Oracle Analyze

Home » RDBMS Server » Server Administration » Tuning Oracle Analyze

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning Oracle Analyze [message #264587]

Mon, 03 September 2007 20:37

stevefox777
Messages: 26
Registered: September 2006

Junior Member

Oracle version : 9.2.0.7.0
OS : HP-UX B.11.11 U 9000/800
# of CPUs : 12

Hi,
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.

(1)

EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS ('SCHEMA1');
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA ('SCHEMA1','compute');

control file parameter settings:
parallel_max_servers=5
parallel_min_servers=0

(2)

EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS ('SCHEMA1');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA1',100,DEGREE=>12,CASCADE=>true);

control file parameter settings:
parallel_max_servers=12
parallel_min_servers=0

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

Any help will be greatly appreciated.

Cheers
Steve

Report message to a moderator

Re: Tuning Oracle Analyze [message #264590 is a reply to message #264587]

Mon, 03 September 2007 20:59

BlackSwan
Messages: 26766
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?".

Report message to a moderator

Re: Tuning Oracle Analyze [message #266400 is a reply to message #264587]

Mon, 10 September 2007 14:25

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.

Report message to a moderator

Previous Topic:	Dropping tablespace did not free disk space - HELP!
Next Topic:	Compiling INVALID Objects

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 07:26:47 CDT 2024