| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Library Cache Lock
hi jonathan
i have faced a similar issue and please see whether
the below is what u are facing.
we used to analyze tables (estimate statistics) and
when snapshot refresh used to happen in the same time,
there was library cache locking in the database and no
dml will go thru.this happens until the snapshots are
done. for a few sundays(on whcih analyze was being
done)i used to think analyze was causing this issue
this because when u do check x$kgllk analzye session
used to show up in the top. but a system dump showed
otherwise.i think snapshot was doing a xclusive lock
of the master table instead of a shareable lock when
refresh was going thru(i may be wrong here) .probably
will be noticeable in a very highly concurrent oltp
with a number of snapshot sites. but i was able to
give oracle a test case in 8.1.7.4.
when materilaized view refreshes go thru it does that
library cache lock which can be disabled setting an
event on the source database(db where the mv logs
are). i think this event is 32333 but there is a
downside to this which says query rewrite on mvs will
not work. this event disables some mlog$ verification.
hope this helps
sai
--- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:
>
> 8.1.7.4
> Windows 2000 Server
> ca.200 sessions, about 30 active.
>
> About a dozen sessions go into waits
> for Library Cache Lock for the best
> part of 300 seconds. (At which they
> probably all get ORA-04021 and free
> themselves, but I couldn't tell, the problem
> happened a few minutes before I was due to
> leave the site, and the end-users were
> on the other side of the Atlantic). The
> odd session might get a library cache pin
> wait.
>
> The object they were waiting for (tracked
> via x$kglob.kglhdadr = v$session_wait.p1raw)
> was a table. No-one has done ANYTHING
> untoward to the table, such as truncate, add column,
> index, analyze, for at least 3 days. The table is
> a normal heap table with half a dozen indexes.
>
> The best bet I could come up with was a
> metalink item that mentioned conflicts relating
> to materialized views - and there is a materialized
> view log on this table.
>
> The only other relevant detail I could come up
> with is that sessions create a session, work hard
> for a few seconds, then drop the session
> (maintaining
> the connection) so the system may be exercising some
> part of the code that creates x$kgllk items rather
> more
> heavily than is normal.
>
> Any thoughts ? Any similar experiences ?
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick
> Jr
>
>
> Next public appearances:
> Jan 29th 2004 UKOUG Unix SIG - v$ and x$
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
> ____UK___June
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jan 29 2004 - 17:42:32 CST
 |
 |