Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Arun.

Asked: December 14, 2004 - 11:41 am UTC

Last updated: February 10, 2011 - 4:10 pm UTC

Version: 9.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Always a pleasure coming on this site. Tom, the other day, I was using Oracle Enterprise Manager. SQL*Plus and vi are generally the tools I use, but I came across something that was interesting: If I were to go to OEM and track a session, some of them had operations labeled as "long operations". When I would click on the long operation, it would give me a status/percentage complete bar. This bar tells me how many blocks it needs to process for a given operation such as an index scan, and how many blocks it's completed so far. So, I started to think about how I would write a similar report in SQL*Plus, and was wondering if you have any advice? I like these kinds of problems because it prompts me to revisit the Oracle docs.

Thanks for reading my question. Hope you're doing well.

Arun




and Tom said...

just query v$session_longops where time_remaining>0

all oem is doing it charting the information found in there....



Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Arun Mathur, December 14, 2004 - 10:46 pm UTC


Out of curiosity

Arun Mathur, December 22, 2004 - 2:33 pm UTC

I don't mean to virtually lock you and Jonathan Lews in the same room, but do you know where I can get more information as to how Oracle calculates the values stored in v$session_longops?

Thanks again, Tom. Have a great holiday.

Arun


Tom Kyte
December 22, 2004 - 2:43 pm UTC

Here is a cut and paste on this from "Expert One on One Oracle"

<quote>
Changes made to this view are immediately visible to other sessions - without the need to commit your transaction.  For any process that updates this view, you will be able to monitor their progress from another session by querying the V$SESSION_LONGOPS view. You too have the ability to populate rows in this view - typically 1 row but you may use others if you like.

The API to set the values in this view is defined as:

PROCEDURE SET_SESSION_LONGOPS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT 

Where:

o RINDEX: tells the server which row to modify in the V$SESSION_LONGOPS view. If you set this value to dbms_application_info.set_session_longops_nohint, a new row will be allocated in this view for you and the index of that row will be returned in RINDEX. Subsequent calls to SET_SESSION_LONGOPS with the same value for rindex will update that already existing row.

o SLNO: is an internal value. You should initially pass a null number in and ignore its value otherwise. You should pass the same value in with each call.

o OP_NAME: the name of the long running process.  It is limited to 64 bytes in size and should be set to some string that will be easily identified and provides some meaning to you.

o TARGET: Typically used to hold the object id that is the target of the long running operation (eg: the object id of the table being loaded). You may supply any number you wish here or leave it null.

o CONTEXT: A user defined number. This number would have meaning only to you - it is simply any number you wish to store.

o SOFAR: This is defined as any number you want to store but - if you make this number be some percentage or indicator of the amount of work done, the database will attempt to estimate your time to completion for you. For example, if you have 25 things to do and they all take more or less the same amount of time - you could set SOFAR to the number of things done so far and then set the next parameter TOTALWORK. The server will figure out how long it took you to get to where you are and estimate how long it will take you to complete.

o TOTALWORK: This is defined as any number you want to store but - the same caveat for SOFAR applies here. If SOFAR as a percentage of TOTALWORK represents your progress, the server will compute the time remaining to complete your task.

o TARGET_DESC: This is used to describe the contents of the TARGET input from above.  If the TARGET actually contained an object id, this might contain the object name for that object id.

o UNITS: A descriptive term that categorizes what SOFAR and TOTALWORK are measured in - units might be 'files' or 'iterations' or 'calls' for example.

These are the values you can set - when you look at the V$SESSION_LONGOPS view, you'll see it has many more columns than these however:

ops$tkyte@ORA8I.WORLD> 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

The SID and SERIAL# columns are used to join back to V$SESSION to pick up the session information. The columns marked with ** are the ones you have control over and set. The START_TIME column marks the time this record was created (typically your first call to DBMS_APPLICATION_INFO.set_session_longops). The LAST_UPDATE_TIME column represents the time of your last call to set_session_longops. The TIME_REMAINING is an estimate in seconds of the time to completion, it is equal to round(elapsed_seconds*((totalwork/sofar)-1)). The ELAPSED_SECONDS column is the time in seconds since the start of the long running operation and the last update time. The MESSAGE column is a derived column - it concatenates together pieces of the OPNAME, TARGET_DESC, TARGET, SOFAR, TOTALWORK, and UNITS column to make a readable description of the work in process. The USERNAME is the name of the user this process is executing under. The SQL_ADDRESS and SQL_HASH_VALUE may be used to look into V$SQLAREA to see what SQL statement this process was last executing. The QCSID is used with parallel query - it would be the session of the parallel coordinator.
</quote>


So, these values are set by you -- the others are calculated as described.

In the event ORACLE is the one setting the values -- it depends on the process.  a full scan knows how many blocks it has to scan, how many it has scanned and so on -- so that is its units and so on. 

Thanks again Tom

Arun Mathur, December 22, 2004 - 3:27 pm UTC

Do come for another Atlanta OUG soon.

Multiple Long Operations in a Query

Warren, January 18, 2005 - 7:29 pm UTC

Tom,

Thanks for your answers. As always, most useful!

Let's say that I have a select query that has multiple long operations that need to be performed to get a resultset. Perhaps a couple of Hash Joins and a Sort Output. Each of these long operations is performed one at a time and each is write one at a time to the v$session_longops view. Once the time_remaining for the first is 0, then the second long_operation becomes visible in v$session_longops with a elapsed_seconds and time_remaining.

Is there somewhere that I would be able to see all the "queued" up long_operations so that I could build a status_bar for the complete query? For example:

long_op 1: Hash Join - total time 30 seconds (currently at 15)

long_op 2: Hash Join - total time 1:30 seconds (currently at 0)

long op 2: Sort Output - total time 30 seconds (currently at 0).

It would be nice to be able to sum up the elapsed_seconds and compare against the sum of time_remaining for a status bar of percentage complete.

I know that there is more to a query than just the long_operations, but they tend to be the major time components.

Thanks,

Warren

Tom Kyte
January 19, 2005 - 10:15 am UTC

no, there is not, not as far as I am aware. I have'nt seen it anyway.

Query Progress

Yogesh, January 31, 2005 - 12:57 pm UTC

I want to track the query progress. I used following query

SELECT SID, COMPNAM, SOFAR, TOTALWORK, START_TIME, CURRENT_TIME, ELAPSED_SECONDS FROM v$session_longops WHERE sid = 41

following is the output

SID COMPNAM SOFAR TOTALWORK START_TIME CURRENT_TIME ELAPSED_SECONDS
41 Sort Progression 1 1 31/01/2005 16:23:47 31/01/2005 16:23:47 0
41 Table Scan Blocks Processed 0 1077 31/01/2005 16:24:05 31/01/2005 16:24:05 0
41 SQL Execution Progression 6966 6966 31/01/2005 16:24:05 31/01/2005 17:50:12 5167

In second row, figures are statis since last couple of hours. Is there any way to check if this query doing some work internally?




Tom Kyte
January 31, 2005 - 1:15 pm UTC

only look at rows "where time_remaining > 0"

Any other way?

Kariya, July 22, 2005 - 8:48 am UTC

I am trying to check the status of long running query in oracle 7.3 where I can not see v$session_longops view. Is there any other way of checking long running query?


long operation in 10g r2

abz, October 19, 2006 - 3:27 am UTC

I used to see gui of long operation in 9i O.E.M.

Can you please tell me how to see that in 10g R2.

I know we can query the v$session view, but I want to see
it as gui, specially the blue bar running in the long operation tab.


any body can answer the above

abz, October 27, 2006 - 12:39 am UTC

Can any body please answer the above question.

please answer

abz, October 28, 2006 - 11:05 am UTC

Tom, can you please reply for the above message.

Tom Kyte
October 28, 2006 - 11:21 am UTC

have you tried otn.oracle.com -> discussion forums or metalink tars.

Not a big user of EM myself.

A reader, December 28, 2006 - 3:02 pm UTC

Hi,

In most of the examples, it is shown that to monitor the progess in v$SESSION_LONGOPS using dbms_application_info.set_session_longops is always put in a loop.
For eg.
declare
v_mod_name varchar2(40);
v_act_name varchar2(20);
b_rindex binary_integer;
b_slno binary_integer;
n_sofar number:=0;
n_totwork number:=100;
begin
b_rindex := dbms_application_info.set_session_longops_nohint;
for x in 1..n_totwork loop
<< do some activities like insert,update, merge etc>>

dbms_application_info.set_session_longops(....);
end loop;

end;

Can the progess be monitored without this loop.
My concern is I have a merge, followed by a update, both affecting tables with 20 million rows and increasing and this loop may introduce more delay.

Regards,
Pras



Tom Kyte
December 29, 2006 - 9:20 am UTC

if the update uses a full scan, you can monitor the progress via the full scan operation, we'll update v$session_longops for you.

how oracle processes a plan

abz, April 13, 2007 - 12:51 pm UTC

Can you please describe how oracle processes an execution plan, it shows step nos. in the plan, because of which
we thought that is going step by step, but when we monitor
the query progress that doesnt seem to be true.

For example, the document says
"when there are more than two tables in the FROM clause,
oracle joins two of them first, then join this result to third table and then join this result to forth table and so on"

I tried to monitor a plan like this
Neste Loops
A
Nested Loops
b
nested loops
c ful scan
d index scan

I observed the wait events,
first there was a wait event on object C then on
D then on B and then on A, then again on C, D, B, A.
I was just refreshing the dbconsole again and again
to see the wait events.

The above seqence of wait events proves that it reads
something from C then D then B then A, then again
on C, .., this seems that is joining all the four tables
togather as a single one step.

I have seen the logic of Nested loops in this site, but all
examples are showing only ONE nested loops join logic.
Can you please show a psudo code ( for loops) of the above
plan with multiple nested loops?

Tom Kyte
April 13, 2007 - 7:12 pm UTC

give me a real plan, your plan does not make sense to me.

but nested loops are in general "a nested loop, like a for loop"

scott%ORA10GR2> select /*+ ordered index( dept, pk_dept) use_nl(emp dept) */ * from emp, dept where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   798 |    17
|   1 |  NESTED LOOPS                |         |    14 |   798 |    17
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   518 |     3
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


that'll be processed like this:


for x in ( select * from emp )
loop
    for y in (select * from dept where deptno = X.DEPTNO )
    loop
       output record
    end loop
end loop


steps in the plan are NOT done atomically - they are executed much like a program, we'll switch from step to step to step all over the place.

abz, April 16, 2007 - 2:02 am UTC

Then don't you think that the word "STEP" is a little bit misleading here.

What I understand now is that it will
do some work on step 2 then jump to step3 then to step
1 and then back to step 2 then to step 4, and so on
right?


Here is a real plan. Can you please write the
psudo code for this plan? note that there are two
nested loops here. I just want to understand clearly
how to dry run the explain plan in our brains.

Thank you.

Execution Plan
----------------------------------------------------------
Plan hash value: 3390050878

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83M| 36G| 3067K (1)| 10:13:31 |
| 1 | TABLE ACCESS BY INDEX ROWID | CI_BILL | 14 | 1694 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 83M| 36G| 3067K (1)| 10:13:31 |
| 3 | NESTED LOOPS | | 6064K| 1989M| 638K (1)| 02:07:47 |
| 4 | TABLE ACCESS FULL | CI_SA | 6064K| 1220M| 31807 (3)| 00:06:22 |
| 5 | TABLE ACCESS BY INDEX ROWID| CI_ACCT | 1 | 133 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | XM148P0 | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | XT033S1 | 15 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."ACCT_ID"="B"."ACCT_ID")
7 - access("A"."ACCT_ID"="C"."ACCT_ID")
Tom Kyte
April 16, 2007 - 1:20 pm UTC

no, step is accurate.

there are loops, just like in a program - because a sql statement is compiled into ..... a program to be executed.

(you see that little CODE button - please use it, else proportional fonts and plans just don't mix, no formatting on the plan)

the nested loops are just "for loops"


abz, April 17, 2007 - 12:34 pm UTC

1)
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref1224

Refer to 13.4.2.1
....
The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.
......

Then later on the same document 13.4.2.2
...
Step 3 reads all rows of the employees table.
...

But we can see that the rightmost indented step is
step 5 and NOT 3.

Also, the way steps are described, it builds a picture
in ones mind that steps are being done ATOMICALLY.


2) 13.4.1.4
A join order is the order in which different join items, such as tables, are accessed and joined together. For example, in a join order of table1, table2, and table3, table table1 is accessed first. Next, table2 is accessed, and its data is joined to table1 data to produce a join of table1 and table2. Finally, table3 is accessed, and its data is joined to the result of the join between table1 and table2.

But as you said it executes like a program, and
it jumps from step to step all over, then what 13.4.1.4
means.

Thanks
Tom Kyte
April 18, 2007 - 10:49 am UTC

sigh...

don't know what to say - nested loops are for loops. the steps feed into each other, they are NOT done atomically.

User opinion of querying v$session_longops

Arun Mathur, April 17, 2007 - 2:30 pm UTC

Tom,

I've been querying the v$session_longops view while building a materialized view. One of the guys in the group saw a consistent "/" and "<enter>", then said:

"Wow, you can play pinball with Oracle too?"

;)

Regards,
Arun

How to get executed selects during given period

mrora, April 24, 2007 - 2:49 pm UTC

Dear Tom.
Is there any possibility to get start_time, end_time of select staements or executed sql's during given period. Let say during 20:00 and 22:00 I need list as below:

Start_time End_time SQL
1. 20:10:09 20:10:10 select * from t1;
2. ....

Thanks in advance.

Tom Kyte
April 24, 2007 - 3:03 pm UTC

no, not really

not every execution is individually logged anywhere. v$sql has optimally one row - and we just increment counters.

Insetion Status

A reader, May 26, 2007 - 8:31 am UTC

Can we get time remaining to complete a insert statement when we are inserting from table (table from same schema and remote db).
For example we have this statement
Insert into test(a,b,c)(select seq.nextval,b,c from test_1 t1,test_2@remo_db td where t1.a=td.a) And it is taking much time how we check how much of data inserted and how much remaining.

When we have some clob columns in a remote db table it takes too much time to insert record although most of records have null value. How we can make this insertion efficient.

Tom Kyte
May 26, 2007 - 12:17 pm UTC

no one knows how much data is left to insert until after it is inserted.

A reader, May 28, 2007 - 1:37 am UTC

It means that there is noway to get the status of Insertion query even we are selecting data from one table and inserting into anothere? Can we check the stauts of particular session to get any sort of information?

When we have some clob columns in a remote db table it takes too much time to insert record although most of records have null value. How we can make this insertion efficient?

Tom Kyte
May 30, 2007 - 9:29 am UTC

you have a wealth of information in the various v$ views

however, until you hit the end of a result set, no one really knows WHERE the end is.

Getting information for a particular session

Arun Mathur, May 30, 2007 - 9:56 am UTC

>> Can we check the stauts of particular session to get any sort of information?

How about run a trace on the active process via dbms_system.set_ev?

Regards,
Arun

Tom Kyte
May 30, 2007 - 3:45 pm UTC

and what would that give you? any information as to when the query is going to finish? no - it'll just give you basically information you can get right from v$ tables (what we waiting on, how long have we been running, how long have our long running operations been going and so on)

Agreed

A reader, May 30, 2007 - 4:04 pm UTC


Long Running Sessions

Mohan Raju, December 05, 2010 - 8:12 pm UTC

Hi Tom,

I have been querying v$session_longops when one report is running on database but session details are not capturing in v$session_longops.

Session details.

   SID       SEQ# EVENT                          STATE                       P1         P2         P3 SECONDS_IN_WAIT
------ ---------- ------------------------------ ------------------- ---------- ---------- ---------- ---------------
  5652         90 SQL*Net message from client    WAITED KNOWN TIME   1952673792          1          0            1027

SQL> select target, sofar, totalwork, (sofar/totalwork)*100 pct_done from v$session_longops where SID=5652 and serial#=50105;

no rows selected

Many times i observed some sessions are not reporting in v$session_longops  could you please help in this regard how to proceed.


Thanks,
Mohan


Tom Kyte
December 07, 2010 - 9:36 am UTC

well, unless they are doing something we consider to be "long" (like a full tablescan of a big table, an rman backup, a big sort) - *we* don't put anything in there.

The application developer can use dbms_application_info.set_session_longops to put something useful in there if they are running a long running stored procedure.

Parallel full table scan - longops block size?

Kim Berg Hansen, February 09, 2011 - 3:13 am UTC

Hi, Tom

I've used gv$session_longops to monitor the progress of a full table scan in parallel. It is very nice that I can follow how the child processes "chunk" the table scan into several "rowid range scans".

The table has about 6.5 million blocks (8K blocksize). The 3 child processes were each doing "chunks" where longops specified TOTALWORK about 70.000-140.000 and UNITS were "blocks". When the scan completed, 258 "chunks" had been processed. But the sum of TOTALWORK for all 258 entries in gv$session_longops ended up at 26 million?

Which is more likely:
Did Oracle scan the table 4 times?
Or is the "blocks" in gv$sesssion_longops.totalwork shown in 2K blocksize disregarding database blocksize?

I'm a bit curious because after the scan had been running for a while, I summed the TOTALWORK value of the completed chunks and estimated how much more work left - and then the scan used 4 times as long as I estimated :-) Not a problem in itself, it is mostly a matter of knowing what TOTALWORK shows me so I can make better estimates of "how much time left of the scan..."

Thanks for your thoughts :-)

Regards
Kim Berg Hansen

Tom Kyte
February 10, 2011 - 4:10 pm UTC

.. Not a problem in itself, it is mostly a matter of knowing what TOTALWORK shows me so I can make better estimates of "how much time left of the scan..." ...

why not just use the TIME_REMAINING estimate - we already did the math for you. We show you the so far and the estimated time remaining?


totalwork is the number placed in there by the application, for example:

ops$tkyte%ORA11GR2> declare
  2      l_nohint number default dbms_application_info.set_session_longops_nohint;
  3      l_rindex number default l_nohint;
  4      l_slno   number;
  5  begin
  6      for i in 1 .. 10
  7      loop
  8          dbms_lock.sleep(1);
  9          dbms_application_info.set_session_longops
 10          ( rindex =>  l_rindex,
 11            slno   =>  l_slno,
 12            op_name => 'my long running operation',
 13            target  =>  1234,
 14            target_desc => '"msg description"',
 15            context     => 0,
 16            sofar       => i,
 17            totalwork   => 10,
 18            units       => 'loops'
 19          );
 20      end loop;
 21  end;
 22  /


I set totalwork to 10 meaning I had 10 iterations I was going to make. So, totalwork in general should be constant - unless it figures out partway through that it over/underestimated the work to be done.

TIME_REMAINING not useful for parallel full table scan?

Kim Berg Hansen, February 11, 2011 - 1:34 am UTC

Sorry, Tom - I must not have been all clear :-)

Yes, I am aware that Oracle did the math for me in the TIME_REMAINING column of the view. And the TIME_REMAINING can be calculated independent of what the "unit of work" is, since TOTALWORK and SOFAR is the same unit. So the unit could be "lightyears" or "kilometers" - no matter for the TIME_REMAINING column.

My scenario was parallel full table scan with three child processes each doing "chunks" of rowid range scans. The TIME_REMAINING can tell me how long time is approximately left of EACH of the chunks of rows - but it cannot tell me how many "chunks" are left to scan...

What I tried to estimate was this:

I started the scan at 12 noon. After my table scan had been running for half an hour, I selected gv$sesssion_longops for the sids of the three processes. There were 17 entries in gv$session_longops after half an hour. The sum of TOTALWORK of these 17 entries was about 1.6 million and the units said "blocks". The table was 6.5 million blocks, and therefore I estimated that the parallel full table scan must be about 25% done, and the scan would therefore finish at 2 pm having scanned 6.5 million blocks in about 68 "chunks".

But in reality the scan did not finish until 8 pm having rowid range scanned 258 "chunks" and the sum of TOTALWORK of those 258 entries in gv$session_longops was 26 million.

Thus I concluded that the estimate I did at 12:30 pm was terribly wrong :-) And since the factor of 4 immediately stared me in the eye, I took a wild guess that the programmer who wrote the parallel table scan code perhaps was hardcoding blocksize of 2K in his calls to dbms_application_info.set_session_longops? It was the more comfortable guess for me, since I did not wish to believe that Oracle actually scanned each of the 6.5 million blocks 4 times :-)

But is there another way of guesstimating how much work is left for a parallel full table scan using rowid range scans? If I had another way then I would not have to worry about blocksize in longops :-)

Thanks for your time, as always.