Re: Sessionns blocked by library cache lock

On Nov 28, 4:50 pm, "Ranko.Mo..._at_gmail.com" <Ranko.Mo..._at_gmail.com> wrote:
> Hi List,
> This is hanganalyze dump file:
>
> Found 15 objects waiting for
> <cnode/sid/sess_srno/proc_ptr/ospid/wait_event>
> <0/190/6488/0xdb6bde98/12701/library cache lock>
> Open chains found:
> Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> <0/79/27/0xdb6dc6a0/15367/No Wait>
> -- <0/190/6488/0xdb6bde98/12701/library cache lock>
> -- <0/185/318/0xdb6dad78/23551/library cache lock>
> -- <0/12/144/0xdb6d2fb0/1666/library cache pin>
> Other chains found:
> Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> <0/16/66/0xdb6dc198/23559/library cache lock>
> Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> <0/17/100/0xdb6c5c60/23962/library cache pin>
> Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> <0/32/12/0xdb6dcba8/15369/No Wait>
> Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
> <0/39/1075/0xdb6e0d10/22710/library cache pin>
>
> How to prevent/detect this ?
>
> Regards, Ranko

Library cache locks are usually being caused by excessive parsing. If you don't use bind variables you will get excessive parsing. You should probably try to reduce the number of parses and/or use bind variables.

-- 
Sybrand Bakker
Senior Oracle DBA