Oracle Database Discussions

I have a delete statement running from more than 24 hrs now and the session info says its waiting on user I/O. There are no blocking sessions and its doing a full table scan of a table having around 500000 records. I dont understand what exactly its waiting on and how to check that and why it taking more than 24 hrs to FTS of 1 table? Here are some of the statistics:

SQL> select blocking_session, event, wait_class, wait_time, seconds_in_wait, state from v$session where sid=1026;

BLOCKING_SESSION EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT
---------------- ------------------------- ---------- ---------- ---------------
STATE
-------------------
db file scattered read User I/O 0 0
WAITING


SQL> select * from table(dbms_xplan.display_cursor('1g5k0k3qpy8j2'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1g5k0k3qpy8j2, child number 0
-------------------------------------
DELETE FROM RX_TX WHERE ID IN (SELECT ID FROM TEMP_PURGE WHERE TABLE_NAME = 'rx_
tx')


Plan hash value: 3126475949

--------------------------------------------------------------------------------
------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)
| Time |

--------------------------------------------------------------------------------
------------

| 0 | DELETE STATEMENT | | | | | 17239 (100)
| |

| 1 | DELETE | RX_TX | | | |
| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 2 | HASH JOIN RIGHT SEMI| | 513K| 123M| 14M| 17239 (2)
| 00:03:27 |

|* 3 | TABLE ACCESS FULL | TEMP_PURGE | 557K| 8717K| | 2789 (2)
| 00:00:34 |

| 4 | TABLE ACCESS FULL | RX_TX | 578K| 130M| | 6918 (2)
| 00:01:24 |

--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------


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

2 - access("ID"="ID")
3 - filter("TABLE_NAME"='rx_tx')


22 rows selected.

SQL> select b.name, a.value from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.value > 0 2 3
4 and b.name like '%wait%'
5 and a.sid=1026;

NAME VALUE
---------------------------------------------------------------- ----------
concurrency wait time 1615
application wait time 388
user I/O wait time 13403000
enqueue waits 1
shared hash latch upgrades - no wait 7924935
redo log space wait time 2852

6 rows selected.


Any help would be appreciable.

This deletes more that 60% of the records from this table so indexed should be out of question here, i think.

Daljit Singh

Message was edited by:
Daljit