Quote:
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