Re: Library Cache Miss

On 06/12/2016 09:42 AM, sumit Tyagi wrote:
> *4 . Do Oracle have any persistent area where the information of the
> SQLs are stored which were present in shared pool before instance
> restart . *
> *
> *

The answer is no. Oracle is not DB2, permanently compiled SQL does not exist in Oracle, the way it exists in DB2. Oracle does have baselines which are essentially hints that force selected plans, but those hints cannot be considered "persistent area where the information of the SQLs are stored".
In addition to that, I don't see any problem with what happens at the restart time. Databases are not often restarted. I know of a large US bank with a database on 4 large AIX boxes which was last time restarted in 2014 and hasn't been restarted ever since. Simply put, Oracle databases should not be restarted frequently. Oracle is the best in business when it comes to supporting continuous operation. Also, there are issues with compiled SQL, even with DB2. When the table structure changes or statistics changes significantly, manual "rebind" must be issued. Oracle will adjust automatically, without the DBA doing anything. And yes, it will require a hard parse and a cache miss. Oracle does have permanently compiled PL/SQL and you can prevent misses by pinning the procedures into the SGA using DBMS_SHARED_POOL.KEEP. That is usually done form a trigger, fired when starting the instance.

At the end of all things, is there a problem? Do you have a performance problem with a particular application or did you invent a problem by setting goals for the Oracle statistic indicators? Regards

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l