Thanks for the question, Ravi.

Asked: January 02, 2003 - 10:12 pm UTC

Last updated: September 08, 2006 - 4:00 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Recently I came across a FAQ which gives a way to track the progress of a long running delete. Is the below anwser correct. If it is why can't oracle provide the tracking in a v$ kind of view.

Question: What query would you run to track the progress of an Oracle DELETE statement ??
Correct Answer: select TRUNC(sum(USED_UREC)/&&num_of_indexes)
from v$transaction
where addr in
(select taddr
from v$session
where audsid in
(select audsid
from v$session
where sid=&&the_sid
)
)

You have to divide the answer by the number of indexes on the table, since the query results include the deleted rows from all the indexes. Using AUDSID IN (&&the_sid) includes the results from all parallel slaves associated with the main delete SID.
:

and Tom said...

We do -- it is called v$session_longops.

In fact, using dbms_application_info, you yourself could populate this dynamic performance view during the execution of say a long running procedure so you could monitor IT'S progress as well. My book "Expert one on one Oracle" goes into that in some detail.

Search for that (v$session_longops) here to read more about it.

Rating

(2 ratings)

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

Comments

v$session_longops

Ravi Reddy, January 03, 2003 - 1:33 pm UTC

a. Is the sql given before true to track the delete statement.

Also v$session_longops some times do not show the delete progress.It shows long running analyze statements but not delete's. The particular delete ran for 50mts but there is no entry in v$session_longops.

January 03, 2003 - 2:32 pm UTC

that'll show you a measure of how much rollback that is using -- don't know how to translate that directly into a "progress monitor".  The v$session_longops view does.


BIG_TABLE(12,819) ospid = 4388 command = 7 program = sqlplus@aria-dev (TNS V1-V3) dedicated server=4389
Friday    14:31  Friday    14:31 last et = 5
delete from big_table

ops$tkyte@ORA817DEV> @printtbl8 'select * From v$session_longops where sid = 12 and serial# = 819 and time_remaining > -0';
SID                           : 12
SERIAL#                       : 819
OPNAME                        : Table Scan
TARGET                        : BIG_TABLE.BIG_TABLE
TARGET_DESC                   :
SOFAR                         : 110
TOTALWORK                     : 22822
UNITS                         : Blocks
START_TIME                    : 03-jan-2003 14:31:26
LAST_UPDATE_TIME              : 03-jan-2003 14:32:01
TIME_REMAINING                : 7227
ELAPSED_SECONDS               : 35
CONTEXT                       : 0
MESSAGE                       : Table Scan:  BIG_TABLE.BIG_TABLE: 110 out of 22822 Blocks done
USERNAME                      : BIG_TABLE
SQL_ADDRESS                   : 86879A08
SQL_HASH_VALUE                : 734451454
QCSID                         : 0
-----------------

PL/SQL procedure successfully completed.


it works for me....

Why is my delete struck at 99%

Subash, September 07, 2006 - 2:29 pm UTC

Tom,
I have tracked the delete I am trying to do with an IN Clause. It zooms upto 99% and shows 727 out of 728 blocks done (in less than 2 mins) and then it just sits there showing time remaining as 1 sec for ever. I had to kill the session thrice after waiting for about half hour each time so I dont antagonize other users on the database. Can you give me some additional pointers to look at

Regards
Subash

September 08, 2006 - 4:00 pm UTC

you give no example to work with. no real information.

guess I'd see what it was waiting - v$session_wait

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

v$session_longops

Why is my delete struck at 99%

More to Explore

PL/SQL demos

PL/SQL docs

Tracking the progress of a long running delete

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

v$session_longops

Why is my delete struck at 99%

More to Explore

PL/SQL demos

PL/SQL docs