| 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: V$SESSION_LONGOPS
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_at_au.transport.bombardier.com>
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 " " "
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
--
--
CURSOR c_emp IS SELECT ename FROM scott.big_emp;
--
--
CURSOR c_emp_count IS SELECT COUNT(*) FROM scott.big_emp;
-- BEGIN -- SELECT longops_demo_seq.nextval INTO l_seq FROM dual;Received on Fri Sep 10 2004 - 17:58:17 CDT
--
-- 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_at_quest.com] Sent: Friday, September 10, 2004 5:27 PM To: lawrence.wolfson_at_acxiom.com; oracle-l_at_freelists.org 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_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/
 |
 |