Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_STATS and stored procedures to recompile or not?
DBMS_STATS and stored procedures to recompile or not? [message #258003] Thu, 09 August 2007 15:40 Go to next message
Messages: 9
Registered: March 2007
Junior Member
While working with a fellow DBA (sybase/sqlserver dba) he explained that every time statistics are collected that he must run a script to recompile the stored procedures in his sybase database.
That made me wonder if the same were true for Oracle.
In Oracle the stored procedure doesn't have a stored execution plan, either the sql is in the cache of it isn't. If it is not in the cache and stats are gathered then the next time each sql within the proc gets parsed then the new stats are used for the Optimizer to choose the best execution plan.
If the sql is already in the cache then who cares, it is in cache and is returned at the fastest possible rate. Is my understanding of this correct or not?

Re: DBMS_STATS and stored procedures to recompile or not? [message #258042 is a reply to message #258003] Thu, 09 August 2007 23:07 Go to previous message
Michel Cadot
Messages: 63920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is correct.

Previous Topic: about image file
Next Topic: diff on numeric null and char null in unique constraint
Goto Forum:

Current Time: Tue Oct 25 10:44:25 CDT 2016

Total time taken to generate the page: 0.10708 seconds