Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capturing long running SQL

RE: Capturing long running SQL

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Thu, 18 Oct 2001 08:32:15 -0700
Message-ID: <F001.003AED9D.20011018084048@fatcity.com>

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

-----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

  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:

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,

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" <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 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: 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: 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).
Received on Thu Oct 18 2001 - 10:32:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US