Windows 2003
Oracle 9.2.0.6.0
I have a number of DML statements I want to run dynamically. I have a PLSQL proc which generates the statements and populates a tracking table with them. It then selects from the tracking table and executes each statement dynamically with a status field against each statement. a pretty simple cursor loop.
All the statements are deletes with a bitmap index hint to delete where delete_flag = Y.
delete /*+ INDEX(<bitmap index name on delete_flag>) */ table_name WHERE delete_flag = 'Y'
several hundred of these. before it runs for each statement I update the tracker table with "running" for that statement so I know which table its running against. 1 of the tables seems to be hung for a long time. hours. I would have expected the delete of this particular table to run in minutes tops. Theres maybe 1 mill rows in the table with 300k rows to be deleted.
explain plan for that statement is good, uses the index as expected with a low costing.
So I started to dig around.
Looking at the waits for this session every few seconds, it seems to be progressing through the blocks so no waits in partiuclar to worry about.
I then happen to check the SQL for that session (nothing else running on the DB while this procuedre is running)
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
And the SQL_TEXT returned is not the SQL that was submitted dynamically. the table is correct but its using the primary key to delete.
DELETE FROM hung_table_name WHERE PK_FIELD = :B1
Can anyone explain why Im seeing that delete statement and not the one submitted dynamically?