Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips


Find Oracle library cache waits

Oracle library cache pin waits are caused by contention with the library cache, the area used to store SQL executables for re-use. The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

The libwait.sql query provides clues about whether Oracle has been waiting for library cache activities:

select
. . .
from
sys.v$system_event a,
sys.v$event_name b
where
See Code depot for complete script
b.name in ('latch free','library cache load lock',
'library cache lock','library cache pin')
group by
b.name

Output from the above script might resemble the following:


NAME WAITS
--------------------------------
latch free 16
library cache load lock 2
library cache lock 0
library cache pin 0

Seeing increasing numbers of waits for the above events could indicate an undersized shared pool.

You can dig even deeper into the library cache and uncover exactly which objects currently reside in the cache.

The libobj.sql script will show you everything you need to know on this front, but be forewarned, as this script can return large amounts of data in databases with large shared pools and many references to code and data objects:

select
owner,
name,
See Code depot for complete script
kept
from
sys.v$db_object_cache
order by
type asc;

For more on drilling down into the library cache, see my book
"Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

 

 

  
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.