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
tobyc
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?

Thanks
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: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is correct.

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


Current Time: Fri Dec 02 18:49:06 CST 2016

Total time taken to generate the page: 0.32870 seconds