I have unexpected 'hung' situations in a session while trying to do some DML. The queries and selections are fine. I'm trying to cleanse a table of certain 'unnecessary' records. The table is MATERIALS with about 3.9 million records, and the records with ACTIVE = 'N' account for about 1.8 million. I tried cleaning in chunks as follows:
Check the timings...
INSERT /*+ APPEND */ INTO M_NON_ACTIVE_DUMP SELECT * FROM MATERIALS WHERE ACTIVE = 'N' AND MATNR BETWEEN '103' AND '105';
22,145 Records inserted in 1.1 seconds commit;
DELETE FROM MATERIALS WHERE ACTIVE = 'N' AND MATNR BETWEEN '103' AND '105';
22,145 rows deleted in 4.1 seconds
commit; and so on... by changing the ranges of MATNR so that, I roughly get about 20,000 records to INSERT into the dump table and DELETE from materials table. But after four such INSERTS and DELETES (the inserts taking on an average one second and the delete three seconds), the delete simply hung. The insert was done in 1.1 second. But the delete took more than two hours before I did a Ctrl C.
The only way to determine what's happening here is to observe the session, and you can do that in one of two ways. First, you can query the v$session_wait table to determine if the session is spending most of its time executing or waiting. Second, you can trace the session, using dbms_support.start_trace. If dbms_support is not installed on your system, log in as SYS and run ORACLE_HOME/rdbms/admin/dbmssupp.sql. Don't forget to grant execute on it to the username running the session of interest. Once the session reaches the point at which it seems to be "hung up", you can format the trace with tkprof and examine it to see what's happening.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.