V$SESSION_LONGOPS

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Fri, 10 Sep 2004 17:13:07 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BD72@conmsx07.corp.acxiom.net>

Yesterday a client complained about some long running sql he had. He said it had been running well for quite some time. When we investigated we found he had no idea how it had been running and only assumed it had been running well. It just started to time out on his app server and that was reason for his call.

Not sure who worked on this originally as the plans in the test systems are different. One worked a lot better and the other about the same. We fixed them all with a new index.

When he called I used TOAD to check on long running code (Trial Version 8.0.0.47) and nothing showed up.

Then I ran the script below and the only things that showed up were some batch work between 1:30 and 3:30 AM.
But then he sent us the code and we ran it to find it took 3-4 minutes. Just wondering where I'm missing the boat here. That is, why didn't this code or TOAD's pick up his query?????? I tried to find doc but didn't see it in the Master Index.

I don't know that any of my clients are using dbms_application_info (Chapter 3 Supplied Packages). I'm confused as when that is needed and not. It seems you only need it to name processes.

>From ASKTOM I got :
"data in v$session_longops hangs out until those slots are needed for reuse. You
should approach v$session_longops with specific sid,serial#'s -- you do not query the table by itself. You can join this to v$session or whatnot. You should ignore rows in there that are not for current sessions. You cannot clear them. "

and

"to find "long running stuff in current sessions", use last_call_et in v$session along with "status = 'ACTIVE'" last_call_et (with timed statistics=true) will tell you how long they've been doing whatever they last submitted. You can search this site for showsql.sql to see how to marry up v$session with v$sql to see what SQL they are executing and last_call_et will tell you how long they've been doing it."

Thanks
Larry

I read something about V$SESSION_LONGOPS and wrote this script. I usually run it every hour on busy systems. I can't remember where I picked up the info, but I went out on Google and found a couple of similar scripts. Sometimes I get duplicate info but haven't followed up on that. In general it does well showing heavy activity.

COL MINUTES        FORMAT 999,990.99
COL HOURS          FORMAT 999,990.99
COL MESSAGE        FORMAT A22
COL OPNAME         FORMAT A20        HEA "O P E R A T I O N"
COL PCT_COMPLETE   FORMAT A04        HEA "PCT |DONE" TRUNC JUST RIGHT
COL SERIAL#        FORMAT 99999
COL SID            FORMAT 999
COL STARTED        FORMAT A20        HEA "S T A R T  T I M E"
COL TARGET         FORMAT A16

COL TARGET_DESC FORMAT A16
COL TIME_REMAINING FORMAT 99,999,999

COL UNITS          FORMAT A10
COL USERNAME       FORMAT A08

SET ECHO OFF
SET ECHO ON
SPOOL LONG_RUNNING.$DB
SELECT SID
,SERIAL#
,USERNAME
,OPNAME
,TO_CHAR(START_TIME,'DD-MON-YYYY HH24 MI SS') STARTED
,SOFAR/TOTALWORK*100||'%' PCT_COMPLETE
,ELAPSED_SECONDS/60 MINUTES

--    ,TOTALWORK
--    ,UNITS
--    ,TIME_REMAINING

   FROM V$SESSION_LONGOPS
   WHERE USERNAME != 'PATROL'
    AND USERNAME != 'SYS'
   ORDER BY ELAPSED_SECONDS DESC
;

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Received on Fri Sep 10 2004 - 17:08:51 CDT