From lerobe@acxiom.co.uk Fri, 19 Oct 2001 00:06:36 -0700 From: Robertson Lee - lerobe Date: Fri, 19 Oct 2001 00:06:36 -0700 Subject: RE: Capturing long running SQL Message-ID: MIME-Version: 1.0 Content-Type: text/plain Mark, I'm on 8.0.5 and it shows the following (bit different), and yes, its on the Alpha. SQL> desc v$session_longops; Name Null? Type ------------------------------- -------- ---- SID NUMBER SERIAL# NUMBER UPDATE_COUNT NUMBER COMPNAM VARCHAR2(30) OPID VARCHAR2(64) OBJID NUMBER CONTEXT NUMBER STEPID NUMBER MSG VARCHAR2(512) STEPSOFAR NUMBER STEPTOTAL NUMBER SOFAR NUMBER TOTALWORK NUMBER APPLICATION_DATA_1 NUMBER APPLICATION_DATA_2 NUMBER APPLICATION_DATA_3 NUMBER START_TIME DATE CURRENT_TIME DATE ELAPSED_SECONDS NUMBER Cheers Lee -----Original Message----- Sent: 18 October 2001 17:41 To: Multiple recipients of list ORACLE-L LMAO - nice rhyme.. This SQL doesn't work for me? I take it you are trying this against your Alpha server, so what does your output of a desc on v$session_longops show? Mine is as follows on an 8.1.7 instance running on Win2K: SQL> desc v$session_longops Name Null? Type ----------------------------------------- -------- ------------------ SID NUMBER SERIAL# NUMBER OPNAME VARCHAR2(64) TARGET VARCHAR2(64) TARGET_DESC VARCHAR2(32) SOFAR NUMBER TOTALWORK NUMBER UNITS VARCHAR2(32) START_TIME DATE LAST_UPDATE_TIME DATE TIME_REMAINING NUMBER ELAPSED_SECONDS NUMBER CONTEXT NUMBER MESSAGE VARCHAR2(512) USERNAME VARCHAR2(30) SQL_ADDRESS RAW(4) SQL_HASH_VALUE NUMBER QCSID NUMBER Does this view differ in different versions? Reading more about it now.. Cheers Mark -----Original Message----- Lee - lerobe Sent: Thursday, October 18, 2001 16:30 To: Multiple recipients of list ORACLE-L erm... whoops, you find the strangest things in your clipboard !!!!! Correct pasting is as follows. select a.compnam, a.objid, a.sofar consumed , to_char(a.start_time,'YY/MM/DD HH24:MI') START_TIME , round(a.elapsed_seconds/60,1) ELAPSED_MIN, a.msg , b.username , c.sql_text from v$session_longops a, v$session b, v$sqlarea c where a.sid = b.sid and a.serial# = b.serial# and b.sql_address = c.address and b.sql_hash_value = c.hash_value and b.username not in ('SYS','SYSTEM'); gave me Table Scan Blocks Processed 164181 44 01/10/18 14:07 0 Table Scan Blocks Processed : Object id = 164181: So far = 44 : Total = 44 GNEWSO_UK SELECT rowid, "ACXIOM"."ACXIOM_SOURCE".* FROM ACXIOM."ACXIOM_SOURCE" Where ACXIOM_SOURCE_TYPE_CODE = 1003 -----Original Message----- Sent: 18 October 2001 15:27 To: 'ORACLE-L@fatcity.com' Sorry, I passed it onto Kirti but didn't pass on to the list. I received this from a guy called Paul, sorry Paul I don't know your surname but its down to him. Initial tests show that it works but I need to test on a good long running query. They've got cars big as bars. They've got rivers of gold. But the wind goes right through you, It's no place for the old. When you first took my hand On a cold Christmas Eve You promised me Broadway was waiting for me. -----Original Message----- Sent: 18 October 2001 15:55 To: Multiple recipients of list ORACLE-L Lee, Did you come up with a workable solution for this? Preliminary testing of the v$session_longops view gave me some weird data returned in the in the start_time column: SQL> edit Wrote file afiedt.buf 1 select l.sid, 2 l.serial#, 3 s.username, 4 l.start_time, 5 l.time_remaining, 6 l.elapsed_seconds 7 from v$session_longops l, 8 v$session s 9* where l.sid = s.sid SQL> / SID SERIAL# USERNAME START_TIM TIME_REMAINING ELAPSED_SECONDS ---------- ---------- ------------------------------ --------- ------------- - --------------- 12 49527 SYS 17-OCT-01 0 9 13 62085 SYS 0 435240985 13 62085 SYS 16-OCT-01 0 12 14 2710 SYS 16-OCT-01 0 19 17 408 SYS 15-MAR-92 0 302578324 17 408 SYS 0 435241415 6 rows selected. Now, there are no users on this system, and if I were to put this in to practice (I'm thinking of doing a UDC for this) I would put in a where clause discounting the SYS user, and maybe add in a join with v$sqltext to get the offending statements.. Do you get a proper data return for users other than SYS? I haven't got a database to check with users on against ATM.. Looks like I've got the longest running database in the world :P Cheers Mark -----Original Message----- Lee - lerobe Sent: Wednesday, October 17, 2001 09:25 To: Multiple recipients of list ORACLE-L Thanks to everyone for their responses. Certainly given me something to work on. Lee -----Original Message----- Sent: 16 October 2001 18:45 To: Multiple recipients of list ORACLE-L You could fool around with: -- currently running sql.sql select sql_text, sid, s.serial#, last_call_et, hash_value, sa.buffer_gets, sa.executions, s.module, p.spid from v$session s, v$sqlarea sa, v$process p where s.sql_address = sa.address and s.sql_hash_value = sa.hash_value and s.status = 'ACTIVE' and s.paddr = p.addr and s.audsid != 0 and sql_text not like 'select sql_text%' and sql_text not like 'begin%tmsrv%' order by 4 desc; or, another of Paul's greatest hits, -- waiting sql.sql select event, sql_text, s.sid, serial#, s.last_call_et; from v$session s,v$session_wait w, v$sqlarea sa where s.sql_address = sa.address and s.sql_hash_value = sa.hash_value and w.sid = s.sid and w.event != 'Null event' and w.event != 'rdbms ipc message' and w.event != 'pipe get' and w.event != 'virtual circuit status' and w.event not like '%timer%' and w.event not like 'SQL*Net message %' and s.audsid != 0 order by last_call_et Note: last_call_et is estimated only, but generally reliable, and is expressed in seconds. The output of these look great in Benthic's Golden, but YMMV in sqlplus et.al. HTH, Paul ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" Sent: Tuesday, October 16, 2001 12:45 PM Aha, this view we have. Time to start digging about. Thanks again Kirti Regards Lee -----Original Message----- Sent: 16 October 2001 16:35 To: ORACLE-L@fatcity.com Cc: lerobe@acxiom.co.uk BTW, view v$session_longops is also available on some select versions of 8.0.5.x - Kirti > -----Original Message----- > From: Deshpande, Kirti > Sent: Tuesday, October 16, 2001 10:30 AM > To: 'ORACLE-L@fatcity.com' > Cc: 'lerobe@acxiom.co.uk' > Subject: RE: Capturing long running SQL > > If running 8i, you may want to check the view v$session_longops. > May be there is some useful info. > > Regards, > > - Kirti Deshpande > Verizon Information Services > http://www.superpages.com > > -----Original Message----- > From: Robertson Lee - lerobe [SMTP:lerobe@acxiom.co.uk] > Sent: Tuesday, October 16, 2001 10:16 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Capturing long running SQL > > Erm.... thanks for the reply BUT, I know how to use dbms_job etc. > what I > dont know is what SQL to use to get sql that has been running for > longer > than an hour. > > Thanks for the reply anyway. > > Lee > > > 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe@acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vallee INET: dbalist@pythian.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe@acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark@cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe@acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark@cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe@acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).