Re: Library lock cache

jn8852 wrote:
> Hi,
>
> I'm currently performance testing a J2EE application which persists
> data in an Oracle 10g database. The application is a messaging
> application receiving around 100 messages per second.
>
> My problem is that after a certain level of load is reached, the
> following query that saves these messages hangs for long periods of
> time:
>
> insert into Messages(col1, col2, col3, col4, col5, col6, col7, col8,
> col9, col10, col11, col12, col13) values (:1, :2, :3, :4, :5, :6, :7,
> :8, :9, :10, :11, :12, :13).
>
> Looking at the web OEM i see a large amount of query time (up to 20
> minutes in some cases!) being allocated against a Library Cache Lock.
> Once this query has got to this stage I can reproduce the same
> behaviour under smaller loads.
>
> Searching google I notice various articles saying that bind variables
> solve this (which i'm using), but can't really find anyother solutions!
>
> I suspect changing the SGA may have some improvement, but i'm not an
> Oracle expert so not sure exactly what to set it too! The box i'm
> runing on is a dual core Windows 2003 Server box with 1GB of ram.
>
> What other things could be causing this?

See metalink Note:169139.1 Note:22908.1 Note:115656.1 Note:262946.1 Note:122793.1 Note:287059.1 . If you don't have support, get it.

Could be your code is accessing some other code that someone is performing some ddl on or where compilation is necessary, profligate privilege granting, not using session_cached_cursors, timed_statistics bug, could be shared pool or java pool too small, could be SGA too large, could be j2ee doesn't scale well...

show parameter pool
show parameter cache
show sga

select * from dba_hist_librarycache;
select * from dba_hist_java_pool_advice;
select * from dba_kgllock;
select * from dba_blockers;

It also makes a difference what version/patch level of everything you are using, with things like Bug 2997330 floating about.

jg

-- 
@home.com is bogus.
http://www.mtn.org/quack/welcome.htm