| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capturing long running SQL
Cherie
I'll take a guess at this and assume this is how the columns equate to each other
8.0.5
SQL> select compnam from v$session_longops;
COMPNAM
6 rows selected.
SQL>
8.1.6
SVRMGR> select opname from v$session_longops;
OPNAME
Table Scan Table Scan Table Scan Table Scan Table Scan5 rows selected.
Target is the full name of the object in 8.1.6, thus negating the need to select the object_name from the object_id as previously.
HTH Lee
-----Original Message-----
Sent: 18 October 2001 17:36
To: Multiple recipients of list ORACLE-L
The v$session_longops for 7.1.5 doesn't match this script.
Here is a describe of v$session_longops at 7.1.5:
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
Can you tell me if compnam is comparable to target? Or is there a comparable column? How about objid?
Thanks,
Cherie
Robertson Lee
- lerobe To: Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>
<lerobe_at_acxiom cc:
.co.uk> Subject: RE: Capturing long
running SQL
Sent by:
root_at_fatcity.c
om
10/18/01 10:30
AM
Please respond
to 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
-----Original Message-----
Sent: 18 October 2001 15:27
To: 'ORACLE-L_at_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
SID SERIAL# USERNAME START_TIM
TIME_REMAINING ELAPSED_SECONDS
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:
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
or, another of Paul's greatest hits,
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 %'
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" <ORACLE-L_at_fatcity.com>
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_at_fatcity.com
Cc: lerobe_at_acxiom.co.uk
BTW, view v$session_longops is also available on some select versions of 8.0.5.x
> -----Original Message----- > From: Deshpande, Kirti > Sent: Tuesday, October 16, 2001 10:30 AM > To: 'ORACLE-L_at_fatcity.com' > Cc: 'lerobe_at_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_at_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_at_acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Fri Oct 19 2001 - 03:26:21 CDT
--------------------------------------------------------------------
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vallee INET: dbalist_at_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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_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_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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_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_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).
 |
 |