Oracle-L: RE: Anyone use V$SESSION

From: Orr, Steve <sorr_at_rightnow.com>
Date: Thu, 07 Mar 2002 13:11:51 -0800
Message-ID: <F001.004229C3.20020307131151@fatcity.com>

Well here's what's curious...
Notice the changing SID-serial# with the same sql address and hash. Notice how elapsed seconds gyrates. Elapsed seconds goes from zero to 447,507,719 yet there were only a few seconds between the queries. How can a

session with 447,507,719 seconds of elapsed time suddenly appear??? Why are there sessions going back to November when the computer hasn't even been up that long? None of the sessions in V$SESSION_LONGOPS are in V$SESSION. The sql
address and hash is not extant in V$SQL, V$SQLAREA, etc. Is this view supposed to behave this way?

-----Original Message-----

No it is not broken, unless someone explicitly updates underlying information it is not visible .... I monitor this when I am creating or rebuilding indexes to importing ... at that time Oracle will populate relevant fields, and that is really useful. Raj

Rajendra Jamadagni MIS, ESPN Inc.

-----Original Message-----
Sent: Thursday, March 07, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L

Is my V$SESSION_LONGOPS view broken?

Check out the curious results below. Notice the changing SID-serial# and how elapsed seconds gyrates. None of the below sessions are in V$SESSION. The sql address and hash is not extant in V$SQL, V$SQLAREA, etc. (Note, we are using PQO with timed statistics.)

col SID-ser# format a10
col secs format 999,999,999 heading "Elapsed|Seconds" col addhash format a15 heading "SQL Address|Hash"

select substr(lo.sid||'-'||lo.serial#,1,10) "SID-Ser#",

        lo.last_update_time "Last Update",
        lo.elapsed_seconds secs,
        lo.sql_address||'-'||lo.sql_hash_value addhash

from v$session_longops lo
where lo.username not like 'SYS%'
/

                                    Elapsed SQL Address
SID-Ser#   Last Update              Seconds Hash
---------- ------------------- ------------ ---------------
30-6021    11/28/2001 23:20:21           77 095B5B94-0
30-6021    11/28/2001 23:21:35           74 095B5B94-0
30-6021    11/28/2001 23:22:21           46 095B5B94-0
30-6021    11/28/2001 23:23:24           62 095B5B94-0
30-6021    11/28/2001 23:24:26           62 095B5B94-0
24-3435    11/28/2001 23:59:30            9 59D39B8C-363345
30-7110    11/29/2001 00:40:21            9 59D39B8C-363345

73-2593 03/07/2002 11:39:54 447,507,594 56B9FA30-792775

SQL>/

                                    Elapsed SQL Address
SID-Ser#   Last Update              Seconds Hash
---------- ------------------- ------------ ---------------
30-6021    11/28/2001 23:20:21           77 095B5B94-0
30-6021    11/28/2001 23:21:35           74 095B5B94-0
30-6021    11/28/2001 23:22:21           46 095B5B94-0
30-6021    11/28/2001 23:23:24           62 095B5B94-0
30-6021    11/28/2001 23:24:26           62 095B5B94-0
24-3435    11/28/2001 23:59:30            9 59D39B8C-363345
30-7110    11/29/2001 00:40:21            9 59D39B8C-363345
51-61397   03/07/2002 11:40:52            0 56B9FA30-792775

SQL>/
                                    Elapsed SQL Address
SID-Ser#   Last Update              Seconds Hash
---------- ------------------- ------------ ---------------
30-6021    11/28/2001 23:20:21           77 095B5B94-0
30-6021    11/28/2001 23:21:35           74 095B5B94-0
30-6021    11/28/2001 23:22:21           46 095B5B94-0
30-6021    11/28/2001 23:23:24           62 095B5B94-0
30-6021    11/28/2001 23:24:26           62 095B5B94-0
24-3435    11/28/2001 23:59:30            9 59D39B8C-363345
30-7110    11/29/2001 00:40:21            9 59D39B8C-363345

63-34890 03/07/2002 11:41:59 447,507,719 56B9FA30-792775

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Orr, Steve
INET: sorr_at_rightnow.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: Orr, Steve
INET: sorr_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: Anyone use V$SESSION_LONGOPS ???