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: 64284
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: Mon Jan 23 02:51:06 CST 2017

Total time taken to generate the page: 0.20110 seconds