RE: V$SESSION_LONGOPS
- From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
- To: 'Jacques Kilchoer' <Jacques.Kilchoer@xxxxxxxxx>, Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 10 Sep 2004 18:01:53 -0500
Got the following from Metalink. So I don't see something because
"** Only considered a 'Longop' if the table exceeds 10,000 blocks formatted
blocks."
OK maybe. Thought they were more than that but I could be wrong.
Throwing out 6 second rule.
Thanks, Jacques,
Larry
From: Claudia <mailto:claudia.o_callaghan@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
O'Callaghan 05-Mar-03 04:32
Subject: Re : Queries running longer than 15 minutes
You can't rely on V$SESSION_LONGOPS as not all long running operations get
logged there. You can try the following
select a.username,a.sid,a.status,a.last_call_et,b.sql_text
from v$session a, v$sqlarea b, v$process c
where a.sql_address=b.address and a.sql_hash_value=b.hash_value
and a.paddr=c.addr
and a.username is not null
and a.last_call_et>(5*60)
and a.status='ACTIVE';
where last_call_et is the number of seconds since the call began. (Note: it
is not updated continuously but is close enough)
Note:68438.1
Subject: Oracle8i: User-definable V$SESSION_LONGOPS Entries
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 10-FEB-1999
Last Revision Date: 05-MAY-2004
Oracle8i User definable V$SESSION_LONGOPS Entries
=================================================
Oracle8i V$SESSION_LONGOPS entries can be maintained by users.
In Oracle 8.0, the view V$Session_longops was introduced to allow
Server processes to indicate some idea of how far they had progressed
(specifically RMAN).
In Oracle8i, this list was expanded to include:
Archiving *
Rman Backup and Restore
Parallel Query
Recovery ( Crash and Media )
Full Table scans **
Sorting
Analyze using DBMS_STATS Not seen yet
Hash Cluster Creation " " "
Hash Joins Phase 2 " " "
* Archiving is documented as a longop, but may not be coded.
** Only considered a 'Longop' if the table exceeds 10,000 blocks
formatted blocks.
In addition, an API has been produced to allow users to define their
own Longops and monitor them. This script demonstrates how to run a job
and maintain the longops info, and has a second script to run to
monitor progress. Cut the scripts at the line of stars and execute
in two seperate sessions.
***********************************************************************
REM Create sequence for this demonstration first:
create sequence longops_demo_seq;
REM
REM This script demonstrates a sample use of the interface to
REM v$session_longops to allow the application developer to
REM insert and manipulate their own rows in this memory array
REM The only prerequisites for this script are a big_emp table
REM to select from, and a sequence called longops_demo_seq.
REM The pl/sql block only opens a cursor and fetches from it
REM until %NOTFOUND. If anything else is needed, place your own
REM specific processing below 'User operation comes in here'
REM
REM
REM Andrew Holland ( aholland.uk )February 1999
REM
DECLARE
l_ename VARCHAR2(30);
l_rindex NUMBER; -- Index to the slot in v$session_longops
l_sltno NUMBER; -- some sort of internal status field
l_seq NUMBER; -- Unique ID for the 'Run'
l_count NUMBER; -- of employees
l_loopcnt NUMBER :=0; -- of passes through the loop
l_err NUMBER; -- For error handling
--
-- Main cursor we are going to step through
--
CURSOR c_emp IS SELECT ename FROM scott.big_emp;
--
-- cursor to get the total number of rows for the
-- target to compute the percentage complete
--
CURSOR c_emp_count IS SELECT COUNT(*) FROM scott.big_emp;
--
BEGIN
--
SELECT longops_demo_seq.nextval INTO l_seq FROM dual;
--
-- Get the count of rows in the cursor
--
OPEN c_emp_count;
FETCH c_emp_count INTO l_count; -- get the count of
rows
CLOSE c_emp_count;
--
OPEN c_emp;
FETCH c_emp into l_ename; -- prefetch first row
--
-- Initialize The row in v$session_longops
--
l_rindex:=dbms_application_info.set_session_longops_nohint;
--
--
-- Initialize the row in v$session_longops for our operation
--
dbms_application_info.set_session_longops(
rindex=>l_rindex, -- This is the row in the array
slno=>l_sltno, -- This is internal ????
op_name=>'Batch Job: '||l_seq, -- This is the name of the
operation
target=>null, -- Could be the obid we are working
on
context=>null, -- Client defined context
sofar=>l_loopcnt, -- The number of times round the
loop
totalwork=>l_count, -- The target number of times round
target_desc=>'Employees', -- Name of what we are processing
units=>'People'); -- Units to measure target_desc in
WHILE c_emp%FOUND LOOP
--
l_loopcnt:=l_loopcnt+1; -- We've done a row at the start
-- because we prefetched.
dbms_application_info.set_session_longops(
rindex=>l_rindex, -- The same row we just created.
slno=>l_sltno,
sofar=>l_loopcnt,
totalwork=>l_count );
FETCH c_emp INTO l_ename; -- Fetch the next row.
--
-- You may want to.......... dbms_lock.sleep(1);
-- Or something to slow the loop down a bit
--
-- User operation comes in here if necessary.
--
END LOOP;
CLOSE c_emp;
EXCEPTION
-- Don't really care it's a demo.
WHEN OTHERS THEN
l_err := sqlcode;
RAISE_APPLICATION_ERROR( -20001,'Error : '||
TO_CHAR(l_err)||' in Longops Demo');
END;
/
*************************************************************************
REM
REM
REM Is you want to see the above script in Action, then run the following
REM SQL*Plus script many times while you job is running.
REM You may want to get the serial# for 'Your' Job first
REM
REM COLUMN percent FORMAT 999.99
REM COLUMN message FORMAT A50 WORD
REM SELECT to_char(start_time,'hh24:mi:ss') stime,
REM message,( sofar/totalwork)* 100 percent
REM FROM v$session_longops
REM /
-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer@xxxxxxxxx]
Sent: Friday, September 10, 2004 5:27 PM
To: lawrence.wolfson@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: V$SESSION_LONGOPS
Are you sure that the operation you were monitoring would have activity that
would cause it to show in v$session_longops?
Metalink note 68438.1 describes the kind of things that would show up in
that view in an Oracle 8.1 database. There might be an updated Metalink note
somewhere that shows the behaviour in Oracle 9.2
**********************************************************************
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. Thank You.
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
