Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL and SQL optimization (SQL*Plus: Release on Solaris 10)
PLSQL and SQL optimization [message #360542] Fri, 21 November 2008 05:37 Go to next message
Messages: 8
Registered: August 2008
Junior Member

When do sql statements in a package get optimized?

If a session is already running an inefficient sql statement repeatedly in a plsql package loop and you add an index which WILL (tested and proved) significantly improve execution of that statement if used will the session take advantage of it? Or is the sql optimized when the package is first called and not again until the package completes? This is in Oracle

I got the impression that the sql is optimized when the package is called but I've also read that the package checks for interrupts - does it also re-optimize the sql then? Are there any tricks to force it to re-optimize? (Flush shared pool?)

Re: PLSQL and SQL optimization [message #360547 is a reply to message #360542] Fri, 21 November 2008 05:46 Go to previous message
Michel Cadot
Messages: 63910
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If create a new index, the execution plans are invalidated and the parsing will occur the next time the query will be executed, from PL/SQL or not.

Previous Topic: assign table name to a variable
Next Topic: update procedure using cursor / insert rank based on year (merged 4 different posts)
Goto Forum:

Current Time: Mon Oct 24 04:49:13 CDT 2016

Total time taken to generate the page: 0.09425 seconds