RE: library cache lock on import
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Juan Carlos Reyes Pacheco" <juancarlosreyesp@xxxxxxxxx>
- Date: Fri, 27 Jan 2006 11:48:57 -0500
Argh, that's what happens when cutting and pasting and cleaning things
up hastily!
libpin.sql should be fine.
liblock.sql, the line that reads:
and vsw.event = 'library cache pin'
SHOULD BE:
and vsw.event = 'library cache lock'
If no sessions are waiting on a library cache pin or library cache lock,
respectively, then nothing will be output.
Sorry for the extended confusion. With the fix above, I believe both
scripts to be (finally) correct.
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
-----Original Message-----
From: Juan Carlos Reyes Pacheco [mailto:juancarlosreyesp@xxxxxxxxx]
Sent: Friday, January 27, 2006 9:07 AM
To: Bobak, Mark
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: library cache lock on import
Hi Mark, I'm not sure if the last two are right, the first you post
showed the a package being blocked meanwhile the other two don't returns
anything.
On 1/25/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:
> Whoops, I got my liblock.sql script (which shows who is waiting on
> what when 'library cache lock' waits occur), mixed up with my
> libpin.sql (which shows whos waiting on what when 'library cache pin'
waits occur).
>
> Thanks to joseph Amalraj for catching my error!
>
> Sorry for the confusion. To be clear, and for anyone whos interested,
> here are *both* scripts, tested in 8iR3 and 9iR2:
>
> libpin.sql:
>
> select /*+ ordered */
> decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
> 'CLUSTER',
> 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
> 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
> 11, 'PACKAGE BODY', 12, 'TRIGGER',
> 13, 'TYPE', 14, 'TYPE BODY',
> 19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
> 21, 'LOB',
> 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
> 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
> RESOURCE',
> 32, 'INDEXTYPE', 33, 'OPERATOR',
> 34, 'TABLE SUBPARTITION', 35, 'INDEX
> SUBPARTITION',
> 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
> 42, 'MATERIALIZED VIEW',
> 43, 'DIMENSION',
> 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE
> PLAN',
> 48, 'CONSUMER GROUP',
> 51, 'SUBSCRIPTION', 52, 'LOCATION',
> 55, 'XML SCHEMA', 56, 'JAVA DATA',
> 57, 'SECURITY PROFILE', 59, 'RULE',
> 62, 'EVALUATION CONTEXT',
> 'UNDEFINED') object_type,
> lob.KGLNAOBJ object_name,
> pn.KGLPNMOD lock_mode_held,
> pn.KGLPNREQ lock_mode_requested,
> ses.sid,
> ses.serial#,
> ses.username
> FROM
> v$session_wait vsw,
> x$kglob lob,
> x$kglpn pn,
> v$session ses
> WHERE
> pn.KGLPNUSE = ses.saddr and
> pn.KGLPNHDL = lob.KGLHDADR
> and lob.kglhdadr = vsw.p1raw
> and vsw.event = 'library cache pin'
> order by lock_mode_held desc
> /
>
> and liblock.sql:
>
> select /*+ ordered */
> decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
> 'CLUSTER',
> 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
> 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
> 11, 'PACKAGE BODY', 12, 'TRIGGER',
> 13, 'TYPE', 14, 'TYPE BODY',
> 19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
> 21, 'LOB',
> 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
> 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
> RESOURCE',
> 32, 'INDEXTYPE', 33, 'OPERATOR',
> 34, 'TABLE SUBPARTITION', 35, 'INDEX
> SUBPARTITION',
> 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
> 42, 'MATERIALIZED VIEW',
> 43, 'DIMENSION',
> 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE
> PLAN',
> 48, 'CONSUMER GROUP',
> 51, 'SUBSCRIPTION', 52, 'LOCATION',
> 55, 'XML SCHEMA', 56, 'JAVA DATA',
> 57, 'SECURITY PROFILE', 59, 'RULE',
> 62, 'EVALUATION CONTEXT',
> 'UNDEFINED') object_type,
> lob.KGLNAOBJ object_name,
> lk.KGLLKMOD lock_mode_held,
> lk.KGLLKREQ lock_mode_requested,
> ses.sid,
> ses.serial#,
> ses.username
> FROM
> v$session_wait vsw,
> x$kglob lob,
> x$kgllk lk,
> v$session ses
> WHERE
> lk.KGLLKUSE = ses.saddr and
> lk.KGLLKHDL = lob.KGLHDADR
> and lob.kglhdadr = vsw.p1raw
> and vsw.event = 'library cache pin'
> order by lock_mode_held desc
> /
>
>
>
>
> --
> Mark J. Bobak
> Senior Oracle Architect
> ProQuest Information & Learning
>
> "There are 10 types of people in the world: Those who understand
> binary, and those who don't."
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
> Sent: Wednesday, January 25, 2006 3:01 PM
> To: juancarlosreyesp@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: RE: library cache lock on import
>
> Juan,
>
> Here's a script I call liblock.sql that I use when I see library cache
> lock waits, to see who is blocking who. Hope it helps....
>
> Here ya go:
> select /*+ ordered use_nl(lob pn ses) */
> decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE',
> 3, 'CLUSTER',
> 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
> 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
> 11, 'PACKAGE BODY', 12, 'TRIGGER',
> 13, 'TYPE', 14, 'TYPE BODY',
> 19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
> 21, 'LOB',
> 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
> 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
> RESOURCE',
> 32, 'INDEXTYPE', 33, 'OPERATOR',
> 34, 'TABLE SUBPARTITION', 35, 'INDEX
> SUBPARTITION',
> 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
> 42, 'MATERIALIZED VIEW',
> 43, 'DIMENSION',
> 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE
> PLAN',
> 48, 'CONSUMER GROUP',
> 51, 'SUBSCRIPTION', 52, 'LOCATION',
> 55, 'XML SCHEMA', 56, 'JAVA DATA',
> 57, 'SECURITY PROFILE', 59, 'RULE',
> 62, 'EVALUATION CONTEXT',
> 'UNDEFINED') object_type,
> lob.kglnaobj object_name,
> pn.kglpnmod lock_mode_held,
> pn.kglpnreq lock_mode_requested,
> ses.sid,
> ses.serial#,
> ses.username
> from v$session_wait vsw,
> x$kglob lob,
> x$kglpn pn,
> v$session ses
> where vsw.event = 'library cache lock'
> and vsw.p1raw = lob.kglhdadr
> and lob.kglhdadr = pn.kglpnhdl
> and pn.kglpnmod != 0
> and pn.kglpnuse = ses.saddr
> /
>
>
> -Mark
>
> --
> Mark J. Bobak
> Senior Oracle Architect
> ProQuest Information & Learning
>
> "There are 10 types of people in the world: Those who understand
> binary, and those who don't."
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes
> Pacheco
> Sent: Wednesday, January 25, 2006 2:21 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: library cache lock on import
>
> Hi please if you can,
> I'm getting an library cache lock on an import, it seems to be related
> to the compilation of some procedures using database links, directed
> to the same database (this is because in others systems the same links
> points to other database, in this case not)
>
> Thep roblem is I can-t figure out what means this exactly for example
> chyecking v$session row_wait_obj#, I found the table
> SCHEDULER$_SRCQ_INFO, that don't makes too much sense to me
>
> or the namespace I can't find on V$DB_OBJECT_CACHE
>
> Thanks
> SELECT * FROM V$SESSION_WAIT
> where not wait_class='Idle'
> order by 1,2
>
> SID SEQ#
> EVENT
> P1TEXT P1
> P1RAW P2TEXT
> P2
> P2RAW P3TEXT
> P3 P3RAW
> WAIT_CLASS_ID WAIT_CLASS#
> WAIT_CLASS
> WAIT_TIME SECONDS_IN_WAIT
> STATE
> -------------------------------------- -------------------
> 91 457
> library cache lock
> handle address
> 858746552 332F6EB8 lock
> address
> 872673408
>
> 3403F080 100*mode+namespace
>
> 201
000000C9
>
> 3875070507 4
>
> Concurrency
> 0 1220
>
> WAITING
>
>
> --
> Oracle Certified Profesional 9i 10g
> Orace Certified Professional Developer 6i
>
> 8 years of experience in Oracle 7,8i,9i,10g and developer 6i
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
--
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i
8 years of experience in Oracle 7,8i,9i,10g and developer 6i
BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD
Other related posts:
