| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Perfomance decreased while performing a stored procedure
A copy of this was sent to "Nigel Rudgyard" <nigel_at_rudgyard.demon.co.uk.nospam> (if that email address didn't require changing) On Sat, 21 Feb 1998 13:22:52 -0000, you wrote:
>Something else which you should be aware of is that once one the query
>execution plan of for procedure has been determined it will not be
>recalculated, therefore if the data upon which it operates varies
>significantly then the procedure should be flushed from the shared pool.
>
>For example, when the procedure is initially executed perhaps a full table
>scan represents the best way of solving the query contained within the
>procedure, but over time, as rows are added, this becomes less and less
>efficient.
>
thats not right. execution plans for a query are depdendent on the base tables -- any changes to the base tables that would affect execution plans (adding an index, gathering statistics) will invalidate those execution plans and flush them from the shared pool (the execution plans)
you don't need to flush a procedure from the shared pool to get the plans for its queries to change. the dependency mechanism will automatically cause execution plans to change.
also, flushing a stored procedure from the shared pool does not imply that the execution plans will be rebuilt for the queries (so getting the query out of the shared pool will not recomplie the query plans for the sql in the stored procedure). the shared pool maintains execution plans separately from the pcode for pl/sql. If a procedure issues "select * from EMP", 2 objects will be in the shared pool -- the procedure itself and the execution plan for "select * from EMP". If the procedure is flushed, the execution plan will probably stay in the shared pool and the next time the procedure is reloaded, the execution plan will just be reused.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Feb 22 1998 - 00:00:00 CST
 |
 |