Analyze table takes very long time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Analyze table takes very long time

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    141

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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  3. #3
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    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 10:45 AM.
    I remember when this place was cool.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  10. #10
    Join Date
    Dec 2002
    Posts
    9
    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
  •  



Click Here to Expand Forum to Full Width