From oracle-l-bounce@freelists.org Fri Sep 10 17:08:51 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8AM8p321253 for ; Fri, 10 Sep 2004 17:08:51 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8AM8pI21248 for ; Fri, 10 Sep 2004 17:08:51 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 349FC72C8AC; Fri, 10 Sep 2004 17:14:48 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18373-71; Fri, 10 Sep 2004 17:14:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9271472C729; Fri, 10 Sep 2004 17:14:47 -0500 (EST) Message-ID: <433A07749711884D8032B6A0AB115262C2BD72@conmsx07.corp.acxiom.net> From: Wolfson Larry - lwolfs To: oracle-l@freelists.org Subject: V$SESSION_LONGOPS Date: Fri, 10 Sep 2004 17:13:07 -0500 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 9398 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: lawrence.wolfson@acxiom.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/