-
Analyze table takes very long time
Hi all !
Every month we execute the analyze table statement on our application schema tables (v 7.3.4 on RS6000).
It takes more and more time ... about 5-6 hours today.
Is it normal ?
Does anyone has ever encountered this problem ?
Thanks a lot in adavance for your replies ...
-
How big is your db?
It will take longer and longer as data is added and new indexes created.
(Top marks for doing this on a regular basis! )
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Also Did you analyze you latest index created? if you don't that might be your problem.
check if somebody created an index recently :
select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS
from dba_objects
where created=(select max(created) from dba_objects) ;
Giani
-
If you are using the "compute statistics" option the indexes on the table will be analyzed implicitly.
MH
I remember when this place was cool.
-
MH, that inspires me . . .
htanga, you could:
a) ANALYZE ... ESTIMATE STATISTICS ... to save time
and/or
b) split the job into 4 bits and run one each week-end.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally posted by DaPi
MH, that inspires me . . .
htanga, you could:
a) ANALYZE ... ESTIMATE STATISTICS ... to save time
and/or
b) split the job into 4 bits and run one each week-end.
Question...
If this is a production box would it be best to test any changes first before implementation? I am just concerned because if he is usually running "compute" and suddenly changes to estimate there could be problems since the optimizer will have different stats to use. Maybe testing this on one or two tables would be best first.
Splitting it into different jobs is a great idea, maybe run them in parallel, that would be more efficient and would yield the same results that you currently have.
MH
Ps. This is the first time I have "inspired" someone. Usually it is words like "repulsed" especially when Jeff is around.
Jeff where have you been?
Last edited by Mr.Hanky; 12-20-2002 at 11:45 AM.
I remember when this place was cool.
-
MH - if you look at the the history of art you will see inspiration coming from the most unlikely (and repulsive) sources!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Re: Analyze table takes very long time
Originally posted by htanga
Hi all !
Every month we execute the analyze table statement on our application schema tables (v 7.3.4 on RS6000).
It takes more and more time ... about 5-6 hours today.
Is it normal ?
Does anyone has ever encountered this problem ?
Thanks a lot in adavance for your replies ...
Use DBMS_STATS, set table MONITORING and analyze only the STALE tables.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Re: Re: Analyze table takes very long time
Originally posted by julian
Use DBMS_STATS, set table MONITORING and analyze only the STALE tables.
DBMS_STATS? Table MONITORING? In Oracle 7.3 ???
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
hmmm...,if I remember correctly, there used to be a locking issue when selecting from and analyzing the same object!
what type of analyze? I suspect in your application that you are using RBO, so what are you analyzing for? estimate stats and gathering stats in general are for CBO!
if you are validating structure, then that is a (no no) if the business users are accessing the table!! since this will lock the object and all its dependencies (if cascade) is used for the duration of the analyze.
Have you noticed anyone complaining about performance when whatever analyze you are doing is performed?
but I like the dead locking issue better, however, if memory serves. this was fixed in 7.3.4 and you are on 7.3.4. So I am back to you locking the table. Any error messages, deadlock errors?
________________________________________________________________
ORA-1578
A nipple is the only intuitive interface, everything else needs a manual
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|