| Query Regarding flush shared_pool [message #254844] |
Sun, 29 July 2007 09:07  |
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 #255116 is a reply to message #254852] |
Mon, 30 July 2007 12:26  |
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
