OraFAQ Forum: SQL & PL/SQL » Is Analyze table is mandatory?

Home » SQL & PL/SQL » SQL & PL/SQL » Is Analyze table is mandatory?

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

Is Analyze table is mandatory? [message #22911]

Wed, 06 November 2002 01:29

Ramki
Messages: 26
Registered: September 2002

Junior Member

Pls expline in detail why Analyze table is needed ?
and is it mandatroy ?
regards
-Ramki

Report message to a moderator

Re: Is Analyze table is mandatory? [message #22914 is a reply to message #22911]

Wed, 06 November 2002 03:10

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

1. I recomend Analyzing PRODUCTION databases only.
2. There 2 ways, that oracle can execute an sql query
   (OPTIMIZATION).
   first ---> oracle decides the best way ( choose )
   second ---> oracle decides based on certain rules ( rule based)
3. to oracle Make use of CHOOSE optimizer
   there should be some statistical data available.
   so that it can calculate the best and easiest PLAN
   to execute the query.
4. These statistics would be available only if you ANALYZE the schema.
5. sometimes, this process itself will be an overhead.
   so the DBA should take responsibility to move the
   statistical data OUT OF SYSTEM TABLESPACE frequently.

Report message to a moderator

Re: Is Analyze table is mandatory? [message #22917 is a reply to message #22914]

Wed, 06 November 2002 05:10

Ramki
Messages: 26
Registered: September 2002

Junior Member

5. sometimes, this process itself will be an overhead.
so the DBA should take responsibility to move the
statistical data OUT OF SYSTEM TABLESPACE frequently.

what is overhead ? Is it mean when table data grow, periviously analyzed statistical data is will be wrong for current query execution.
So DBA has to do periodicaly re-analyze so that,
oracle will use CHOOES based OPTIMIZATION correctly.
pls expine me.
regards
-Ramki

Report message to a moderator

Re: Is Analyze table is mandatory? [message #22955 is a reply to message #22914]

Thu, 07 November 2002 08:34

F.Tollenaar
Messages: 2
Registered: November 2002

Junior Member

>1. I recomend Analyzing PRODUCTION databases only.

So, you never tune your statements until you enter PRODUCTION ???

IMHO You should either analyze dev&test with representative data, or copy statistics from PROD

Report message to a moderator

Previous Topic:	count database table
Next Topic:	TABLE JOIN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 10:59:55 CDT 2024