Re: Stalling stored procs?

From: Kirt Thomas <kremovethisspamthingthomas_at_gfsiinc.com>
Date: Sun, 15 Oct 2000 22:04:01 -0500
Message-ID: <9frkussr7abgv25qga7rtf5g7sfac9qge2@4ax.com>

PL/SQL lock timer? What happens when you look at the running sql, and stats?

You can get the sql_address from...

select vp.pid vpid,
       vs.sid vsid,
       vs.process client_ospid,
       vp.spid server_ospid,
       vs.sql_address saddr,
       vs.sql_hash_value shv,
       vs.type
  from v$session vs,
       v$process vp

  where vs.paddr = vp.addr
    and vs.sid = YOUR SID

And then..

select sql_text from v$sqltext_with_newlines   where address = saddr -- from above sql   order by piece;

select sql_text from v$open_cursor where sid=vsid; --also from first sql..

If you're a good shell scriptor you can do it all automagically :) If you'd just like to have the script without the fun, drop me an email <G>.

Here's another handy query....

select to_char(vs.logon_time,'ddMon_hh24:mi') time,

       trunc(to_number((sysdate-vs.logon_time)*24*60),2) runtime,
       to_char(vs.sid)||','||to_char(vs.serial#) "SID",
       vs.program "Program",
       vs.status "Status",
       vp.spid,
       vs.process, 
       vs.machine,
       decode(vsw.event,'db file scattered read','__db file scattered
read',vsw.event) event,
       vsw.seq#,
       vsw.p1,
       vsw.p2,
       vsw.p3,
       value "LRs",

decode(vs.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',vs.command) cmd

  from v$session vs,
       v$process vp,
       v$sesstat vss ,
       v$session_wait vsw
  where vs.paddr=vp.addr

    and vs.sid = vss.sid
    and vs.sid = vsw.sid
    and vss.statistic# = 9 -- Logical Reads     and vs.type != 'BACKGROUND'
  order by vs.program

I usually pipe this thru a grep to git rid of pipe, ACT and MACHINE - to get a clearer look at the batch jobs running. You can get your sid from here for the first query.

Also - can'y you turn on tracing?

On Mon, 16 Oct 2000 02:12:15 GMT, Greg Weston <gwestonREMOVE_at_CAPShome.com> wrote:

>AIX 4.x on an RS6k, Oracle 8.1.6, lots of free disk space and RAM,
>nothing else going on on the system. Under normal circumstances the
>system is primarily OLTP but has a batch cycle that lasts an hour or
>two per day. We can't interrupt the OLTP during the batch processing on
>a regular basis, and one particular phase of batch which calls a stored
>proc a few hundred times in a row was causing a problem periodically
>with live transactions taking took long. I was able to get actual OLTP
>activity suspended and turn on a progress journal built into the stored
>procs during that sub-job one night and what I found was that we were
>apparently stalled for more than 10 of the 27 minutes the job ran (6
>stalls, 1.5-2.5 minutes each). Roughly 1/2 hour is the normal run
>length for the job, so I don't think the addition of the progress info
>played any role. I'm looking for information from anyone on
>circumstances under which they have observed stalling or something that
>might be interpreted that way.
Received on Sun Oct 15 2000 - 22:04:01 CDT