Home » RDBMS Server » Server Administration » Query Regarding flush shared_pool
Query Regarding flush shared_pool [message #254844] Sun, 29 July 2007 09:07 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,
I ma using Oracle 9iR2

Can anybody suggest if the statement
'alter system flush shared_pool;' will cause invalidation of all existing query plans?

Also is it recommended to execute this statement after gathering database statistics (we are taking stale statistics)?

Thanks and Regards,
OraSaket
Re: Query Regarding flush shared_pool [message #254852 is a reply to message #254844] Sun, 29 July 2007 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if the statement 'alter system flush shared_pool;' will cause invalidation of all existing query plans?

Yes.

Quote:
is it recommended to execute this statement after gathering database statistics (we are taking stale statistics)?

No.

Never use it.
The only times I had to use it is when there was a bug (application or Oracle one).

Regards
Michel


Re: Query Regarding flush shared_pool [message #255116 is a reply to message #254852] Mon, 30 July 2007 12:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It's pretty much equivalent to resetting the shared pool to the state it was when you started the database. It can be useful in a quiet dev database to see what DML a client runs against the database (flush shared pool; run client application accessing the database; query shared pool to see SQL executed by that user). Using sql trace is the more usual approach - but this can be easier. The technique has been used in the past to clean up the shared pool resulting from apps running SQL using litteral values rather than bind variables (resulting in a cache full of statements only run once).

Previous Topic: Archive log files on remote disk
Next Topic: Loading dbf files into database
Goto Forum:
  


Current Time: Fri Dec 09 13:26:27 CST 2016

Total time taken to generate the page: 0.20100 seconds