DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Analyze....

  1. #1
    Join Date
    Jun 2001
    Location
    California
    Posts
    124

    Analyze....

    Does Analyze Statistics (Compute/Estimate) or dbms_stats causes SQL statements in Shared Pool to be Invalidated ? Invalidated means, Oracle has to do again Hard Parse.
    If my application has one schema and I use dbms_stat's package or (dbms_utility or analyze table ..) to analyze schema, would it cause all sql statements in shared pool for this schema to be invalidated.
    The other question is: If Analyzing does causes SQL statements in Shared SQL area to invalidate, then should I flush shared pool before or after analyzing it.
    Does, it make sense to flush shared pool or not ? Doesn't find any supportive theory.Any thoughts would be appreciated?

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    http://download-west.oracle.com/docs...tats.htm#26713


    When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currently parsed SQL statements that access the object.

    The next time such a statement executes, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics the next time Oracle parses them.

    When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type, if you analyze the column or domain index.


    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    Thanks Mr Hanky,

    Do we have to flush the shared pool after Analyze or gather statistictics.?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You certainly don't have to. Flushing will clear out the plans for the SYS objects too - so probably not a good idea.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by bhallar
    Do we have to flush the shared pool after Analyze or gather statistictics.?
    I dont understand why do u get such crazy ideas?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    One implication that has just occured to me:

    Will the invalid plans that arise from running ANALYZE cause us to over-estimate the fraction of the shared pool that is usefully occupied?

    for example:
    Code:
    select invalidations, count(*) from v$sql
    group by invalidations 
    
    INVALIDATIONS   COUNT(*)
    ------------- ----------
                0       1738
                1       1768
                2          4
               93          1
    As a very rough guess, is 50% of my shared pool free for re-use?

    Can someone point me to a better v$ view for investigating this?

  7. #7
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    I dont understand why do u get such crazy ideas?
    sigh*, in short a perl script (select sysdate from dual) which is used by VCS to check the database status was taking more than 60 sec when ANALYZE was running. Seems to be problem with shared pool memory fragmentataion causing latches on libraray cache and thus select sysdate taking more than expected. The problem disappear if I do "alter system FLUSH shared_pool" just before analyzing the schema.

    One more thing I would like to share ,

    Oracle also flushes a shared SQL area from the shared pool
    When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by bhallar
    sigh*, in short a perl script (select sysdate from dual) which is used by VCS to check the database status was taking more than 60 sec when ANALYZE was running. Seems to be problem with shared pool memory fragmentataion causing latches on libraray cache and thus select sysdate taking more than expected. The problem disappear if I do "alter system FLUSH shared_pool" just before analyzing the schema.

    One more thing I would like to share ,

    Oracle also flushes a shared SQL area from the shared pool
    When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool.
    strange, never happened to me running select sysdate takes ove 60 seconds when I am analyzing tables

    Oracle invalidates the plans and flush the SQL out of shared pool, why would you want to do it as well?

  9. #9
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    Oracle invalidates the plans and flush the SQL out of shared pool, why would you want to do it as well?
    I don't want to do this, actually I came accross this after posting this thread.

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by bhallar
    Oracle also flushes a shared SQL area from the shared pool
    When the ANALYZE statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool.
    Answers my question. Thanks.

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