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
------------------------------
Table Scan Blocks Processed
Sort Progression
Sort Progression
Table Scan Blocks Processed
Table Scan Blocks Processed
Sort Progression
6 rows selected.
SQL>
8.1.6
SVRMGR> select opname from v$session_longops;
OPNAME
----------------------------------------------------------------
Table Scan
Table Scan
Table Scan
Table Scan
Table Scan
5 rows selected.
SVRMGR>
Hmmmmm, objid is a funny one as it relates to the actual object_id in
dba_objects which you would then need to select object_name to get the
proper name of it. However in objid I get a lot of 0s, not sure what they
are.
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 <[EMAIL PROTECTED]>
<lerobe@acxiom cc:
.co.uk> Subject: RE: Capturing long
running SQL
Sent by:
[EMAIL PROTECTED]
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
= 1003
-----Original Message-----
Sent: 18 October 2001 15:27
To: '[EMAIL PROTECTED]'
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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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: '[EMAIL PROTECTED]'
> Cc: '[EMAIL PROTECTED]'
> 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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
