Hi, Bruce,
I happen to be loading a lot of data using our stored procedure right now.
select sql_text from v$sql where users_executing > 0 shows:
INSERT /*+ APPEND PARALLEL(CLAIM) */ INTO CLAIM ( CLAIM_ID, [snipped]
BEGIN sp_insert_claim; END;
SELECT /*+ Q7898000 NO_EXPAND ROWID(A1) */ A1."LOSS_DA" C0,[many other columns]
,A1."CLAIM_ID" C8 FROM "MCILR"."CLAIM" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
A1[snipped]
The first INSERT line is definitely part of our sp_insert_claim. The third
SELECT is heavily rewritten by the PL/SQL engine. But I can tell it's part of
the procedure simply by looking at the FROM clause, plus I'm the only one on
this database at this moment. (Tomorrow they need the data to be loaded)
Rows in dba_lock_internal shows more than just blocked sessions. For instance,
many "Cursor Definition Lock"s in null mode are there to cause parsed cursors
to be thrown away if referenced objects are altered in any way. Oracle calls
these breakable parse locks. They're not really locks; just a "trigger"
mechanism for dependent cursors to be invalidated on certain conditions.
BTW, according to Anjo Kolk's Wait Event paper, in addition to TM locks, IV
(library cache invalidation) and DL (direct loader) locks also use ID1 for
object number.
Yong Huang
- "Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au> wrote:
> I don't think v$sql will give you the same information - it would show the
> top level actual package being executed, but not those which are called by
> that package - these do show up in Steve's code.
>
> Regarding the dba_lock_internal code - the only problem appears to be in the
> name used for the column - any rows returned are actually blocked - is that
> correct?
>
> Bruce Reardon
>
>
> -----Original Message-----
> Sent: Friday, 31 October 2003 12:34 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi, Bruce,
>
> Steve Adams' code is based on x$kglpn (librarycache pin), which is correct.
> But
> the code based on dba_lock_internal blindly assumes id1 is the object name.
> There's a similar common misinterpretation; many DBAs assume v$lock.id1 is
> the
> object ID, which is only true for type = 'TM' (or maybe several other types).
>
> This info *is* in dba_lock_internal, but the script below just needs a type
> predicate in the WHERE clause.
>
> A simpler solution may be just look at v$sql where users_executing > 0 for
> your
> package or procedure (shown in sql_text column). Although v$open_cursor could
> also be used, I don't think a row showing in there always indicates a library
> cache pin (executing) on the object.
>
> Yong Huang
>
> --- "Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au> wrote:
> > David,
> > You could use Steve Adam's script Executing_packages.sql at
> > http://www.ixora.com.au/scripts/misc.htm to see what packages are
> executing.
> >
> > More generally, use dba_lock_internal to look at what is being blocked:
> >
> > based on Oracle-L script by Diego Cutrone
> [mailto:diegocutrone_at_yahoo.com.ar]
> > (Friday, 29 August 2003 7:54 AM)
> >
> > COLUMN lock_id2 FORMAT A30
> >
> > select to_char(SESSION_ID,'999') sid ,
> > substr(LOCK_TYPE,1,30) Type,
> > substr(lock_id1,1,45) Object_Name,
> > substr(mode_held,1,4) HELD,
> > substr(mode_requested,1,4) REQ,
> > lock_id2 lock_addr
> > FROM dba_lock_internal
> > WHERE
> > mode_requested <> 'None'
> > and mode_requested <> mode_held
> > ;
> >
> > and use inverse of this with a given object_name to find who has the
> internal
> > locks.
> >
> > HTH,
> > Bruce Reardon
> >
> > -----Original Message-----
> > Sent: Friday, 31 October 2003 10:59 AM
> >
> > I need to figure out a way to see if a procedure is running before
> attempting
> > a compile
> > and I can't figure out what tables to look in. Here's a test I set up
> >
> > create or replace procedure sleep(i_val number)
> > is
> >
> > begin
> > dbms_lock.sleep(i_val);
> > end;
> > /
> >
> > exec sleep(60);
> >
> >
> > I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to
> > spot
> > the sleep stored procedure or it's session. Of course I could look in
> > v$session and
> > see it in this example but in a stored procedure that has more to it you
> will
> > only see
> > the current step it is at in the procedure and not the procedure itself.
> >
> > I'm trying to be able to identify sessions that hold the lock/latch on a
> > stored procedure
> > so I can kill them when sometimes the session is disconnected and just
> hangs.
> >
> > Thx, Dave
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: yong321_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 30 2003 - 21:19:25 CST
