Skip to Main Content
  • Questions

  • Tracking Progress Of A Delete Statement

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, elie.

Asked: July 06, 2001 - 10:47 am UTC

Last updated: October 09, 2013 - 5:51 pm UTC

Version: oracle 8.1.7.0

Viewed 10K+ times! This question is

You Asked

Sometimes I run a DELETE statement that takes a long time to complete. I have often wondered how many rows have already been deleted so that I can decide whether or not to abort the statement. Is there a way to display how many rows have been deleted while the DELETE is occurring?

Thank you.

Elie Grunhaus
egrunhaus@concentrix.com


and Tom said...

Sure, I cannot tell you the rows but for any long running query we can get lots of good info.

I loaded a table with 1.5 million rows and then issued "delete from t". Gave it a bit of time to run and then queried (the SID of the delete was 17, found that in v$session) :

ops$tkyte@ORA817.US.ORACLE.COM> exec print_table( 'select * from v$session_longops where sid = 17 and time_remaining > 0' )


SID : 17
SERIAL# : 84
OPNAME : Table Scan
TARGET : OPS$TKYTE.T
TARGET_DESC :
SOFAR : 6415
TOTALWORK : 23601
UNITS : Blocks
START_TIME : 06-jul-2001 11:13:33
LAST_UPDATE_TIME : 06-jul-2001 11:17:56
TIME_REMAINING : 705
ELAPSED_SECONDS : 263
CONTEXT : 0
MESSAGE : Table Scan: OPS$TKYTE.T: 6415 out of 23601 Blocks done
USERNAME : OPS$TKYTE
SQL_ADDRESS : 83255028
SQL_HASH_VALUE : 4031062735
QCSID : 0


And then later:

ops$tkyte@ORA817.US.ORACLE.COM> exec print_table( 'select * from v$session_longops where sid = 17 and time_remaining > 0' )
SID : 17
SERIAL# : 84
OPNAME : Table Scan
TARGET : OPS$TKYTE.T
TARGET_DESC :
SOFAR : 9450
TOTALWORK : 23601
UNITS : Blocks
START_TIME : 06-jul-2001 11:13:33
LAST_UPDATE_TIME : 06-jul-2001 11:20:11
TIME_REMAINING : 596
ELAPSED_SECONDS : 398
CONTEXT : 0
MESSAGE : Table Scan: OPS$TKYTE.T: 9450 out of 23601 Blocks done
USERNAME : OPS$TKYTE
SQL_ADDRESS : 83255028
SQL_HASH_VALUE : 4031062735
QCSID : 0
-----------------


It shows how long the statement is running and even tries to guess as to how long it'll take. You can see in this case the number of blocks processed so far...


see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958 <code>
for print_table if you like that output...



Rating

  (4 ratings)

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

Comments

Tracking Progress Of A Delete Statement

Elie Grunhaus, July 06, 2001 - 1:00 pm UTC

Thanks very much for the QUICK response! I like the expertise available to me through your column. It makes my job much easier.

What about a parallel query

A reader, October 06, 2013 - 1:45 am UTC 

I get the following - sql running - 
SQL> /
--------------------
ETL_IDRA(611,39389) ospid = 14024812 program = pmdtm@nzapap70 (TNS V1-V3)
Saturday  06:28  Saturday  18:40
-- Delete incremental data from CPED datalayer table  DELETE FROM
IDR_T.CVRG_BASED_PRSN_ELGB_DUES A WHERE EXISTS (SELECT 1 FROM
ETL_IDRA.CPED_WKLY_DELTA B WHERE A.MRN_PLS_NB = B.MRN_PLS_NB AND
A.SBSCRB_MRN_PLS_NB = B.SBSCRB_MRN_PLS_NB AND A.PRCHSR_ID
--------------------
ETL_IDRA(657,46208) ospid = 29687994 program = oracle@nzapdb169 (P000)
Saturday  16:32  Saturday  18:40
-- Delete incremental data from CPED datalayer table  DELETE FROM
IDR_T.CVRG_BASED_PRSN_ELGB_DUES A WHERE EXISTS (SELECT 1 FROM
ETL_IDRA.CPED_WKLY_DELTA B WHERE A.MRN_PLS_NB = B.MRN_PLS_NB AND
A.SBSCRB_MRN_PLS_NB = B.SBSCRB_MRN_PLS_NB AND A.PRCHSR_ID
--------------------


SQL> exec print_table( 'select * from v$session_longops where sid=657' )
SID                           : 657
SERIAL#                       : 46208
OPNAME                        : Hash Join
TARGET                        :
TARGET_DESC                   :
SOFAR                         : 588
TOTALWORK                     : 0
UNITS                         : Blocks
START_TIME                    : 05-oct-2013 16:53:32
LAST_UPDATE_TIME              : 05-oct-2013 18:39:23
TIMESTAMP                     :
TIME_REMAINING                :
ELAPSED_SECONDS               : 6351
CONTEXT                       : 0
MESSAGE                       : Hash Join:  : 588 out of 0 Blocks done
USERNAME                      : ETL_IDRA
SQL_ADDRESS                   : 0700000893206FA0
SQL_HASH_VALUE                : 1111389484
SQL_ID                        : 9bqxn3d13wx9c
QCSID                         : 611
-----------------

PL/SQL procedure successfully completed.

SQL> 

What do you make of that?

The query above continued to run for 3 hours

A reader, October 08, 2013 - 1:02 pm UTC

and never showed up in v$session_longops.
Tom Kyte
October 09, 2013 - 5:51 pm UTC

that means it was using an operation like a nested loops join.

long operations are things that take more than about 3-5 seconds. A full scan of a table - that is a big operation.

reading an index, getting a row, read index, get a row, read index, get a row and so on - that is a plan that might take a really long time but doesn't do any single thing for a long time. reading the index to get a rowid - fast, read the table by rowid, fast. But if you do something fast millions of times - it is going to take a long time. But we just see millions of really fast operations - not a single long operation but millions of teeny tiny fast operations.


so, check out the plans being used.


you can use the real time sql monitor in 11g to monitor long running sql regardless of the plan.



if you have a long running query that is using indexes - it is probably the wrong plan.

Thanks

A reader, October 10, 2013 - 7:13 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.