-
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?
-
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.
-
Thanks Mr Hanky,
Do we have to flush the shared pool after Analyze or gather statistictics.?
-
You certainly don't have to. Flushing will clear out the plans for the SYS objects too - so probably not a good idea.
-
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"
-
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?
-
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.
-
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?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|