5 Replies Latest reply on Apr 26, 2012 8:45 AM by Hemant K Chitale

    Delete hang

    864988
      Dear all,

      Oracle database version; 11.2.0.1, under redhat linux.

      Ware having problem executing a purge procedure. The procedure works fine in test environment according to the developers but hangs forever in production. I advised to run the procedure with 10046 trace enabled. But the trace file was little big so they just sent the tkprof output. See below the tkprof output . Did you see any problem?

      Purge Procedure:
      CREATE OR REPLACE PROCEDURE VZ_PURGE_ACCOUNTS AS 
      
        CURSOR C_DELETED_ACCS IS
            SELECT ID
            FROM ACCOUNT
            WHERE DELETED= 1;
      
        CP_LOGIN_ID VARCHAR2(20);
        CP_ID INTEGER;
      
      BEGIN
      
         FOR DEL_ACC_REC IN C_DELETED_ACCS
        LOOP   
          DBMS_OUTPUT.PUT_LINE('Deleting ACC  with ID:'||DEL_ACC_REC.ID );
          BEGIN
            SELECT ID,LOGIN_ID INTO CP_ID, CP_LOGIN_ID  FROM CONTROL_POINT  WHERE ACCOUNT = DEL_ACC_REC.ID;
          
            DELETE ACTIVE_CLIENT      WHERE CONTROL_POINT = CP_ID;
            DELETE ACTIVE_CLIENT      WHERE JOINED_CONTROL_POINT  = CP_ID;
            DELETE GROUPS             WHERE ID = CP_ID;  
            DELETE LOGIN              WHERE ID = CP_ID;
            DELETE EQUIPMENT_INSTANCE WHERE CONTROL_POINT = CP_ID;      
            DELETE CONTROL_POINT      WHERE ID = CP_ID;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('NO CPs found for ACC:'|| DEL_ACC_REC.ID );
          END;
          
          DELETE EQUIPMENT_INSTANCE WHERE ACCOUNT = DEL_ACC_REC.ID;
          DELETE ACCOUNT            WHERE ID = DEL_ACC_REC.ID;    
          DELETE MEDIA              WHERE ACCOUNT =  DEL_ACC_REC.ID; 
          DBMS_OUTPUT.PUT_LINE('Deleted ACC  with ID:'||DEL_ACC_REC.ID );    
        END LOOP;
        
        COMMIT;    
        
      END VZ_PURGE_ACCOUNTS;
        • 1. Re: Delete hang
          864988
          and the TKPROF output:
          TKPROF: Release 11.2.0.1.0 - Development on Tue Apr 24 15:20:42 2012
          
          Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
          
          Trace file: vchprd11_ora_14369_purge_trace.trc
          Sort options: default
          
          ********************************************************************************
          count    = number of times OCI procedure was executed
          cpu      = cpu time in seconds executing 
          elapsed  = elapsed time in seconds executing
          disk     = number of physical reads of buffers from disk
          query    = number of buffers gotten for consistent read
          current  = number of buffers gotten in current mode (usually for update)
          rows     = number of rows processed by the fetch or execute call
          ********************************************************************************
          
          SQL ID: 2dkszq8ftbfw0
          Plan Hash: 2039192013
          SELECT ID 
          FROM
           ACCOUNT WHERE DELETED= 1
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        1      0.00       0.01          0          0          0           0
          Execute      1      0.00       0.00          0          0          0           0
          Fetch        1      0.00       0.00          0         15          0         100
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total        3      0.00       0.01          0         15          0         100
          
          Misses in library cache during parse: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
              100  TABLE ACCESS FULL ACCOUNT (cr=15 pr=0 pw=0 time=396 us cost=103 size=30840 card=3855)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            Disk file operations I/O                        1        0.00          0.00
          ********************************************************************************
          
          SQL ID: 9wrfvc4a0chpj
          Plan Hash: 3084788774
          SELECT ID,LOGIN_ID 
          FROM
           CONTROL_POINT WHERE ACCOUNT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      0.00       0.00          0          0          0           0
          Fetch       17      0.00       0.01          1         51          0          17
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       37      0.00       0.02          1         51          0          17
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                1  TABLE ACCESS BY INDEX ROWID CONTROL_POINT (cr=3 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
                1   INDEX RANGE SCAN IDX_FK_CONTROL_POINT_ACCOUNT (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 123350)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                   8        0.00          0.00
            library cache lock                              2        0.00          0.00
            library cache pin                               2        0.00          0.00
            KJC: Wait for msg sends to complete             1        0.00          0.00
            db file sequential read                         1        0.01          0.01
          ********************************************************************************
          
          SQL ID: 6n6jmtjjacwh3
          Plan Hash: 1274359068
          DELETE ACTIVE_CLIENT 
          WHERE
           CONTROL_POINT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      0.00       0.03          2         34         31           6
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       20      0.00       0.03          2         34         31           6
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  ACTIVE_CLIENT (cr=2 pr=0 pw=0 time=0 us)
                0   INDEX RANGE SCAN IDX_FK_ACTIVE_CLIENT_CP (cr=2 pr=0 pw=0 time=0 us cost=1 size=28 card=1)(object id 123357)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            SQL*Net message to client                       1        0.00          0.00
            SQL*Net message from client                     1       12.46         12.46
            ges message buffer allocation                  27        0.00          0.00
            library cache lock                              2        0.00          0.00
            library cache pin                               2        0.00          0.00
            KJC: Wait for msg sends to complete             2        0.00          0.00
            gc cr block 2-way                               1        0.00          0.00
            gc current grant busy                          13        0.00          0.00
            enq: TM - contention                            5        0.00          0.00
            db file sequential read                         2        0.01          0.02
          ********************************************************************************
          
          SQL ID: 2bc0w9hyxmu0p
          Plan Hash: 4188883886
          DELETE ACTIVE_CLIENT 
          WHERE
           JOINED_CONTROL_POINT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      0.00       0.00          0         34          0           0
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       20      0.00       0.00          0         34          0           0
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  ACTIVE_CLIENT (cr=2 pr=0 pw=0 time=0 us)
                0   INDEX RANGE SCAN IDX_FK_ACTIVE_CLIENT_JOINED_CP (cr=2 pr=0 pw=0 time=0 us cost=1 size=28 card=1)(object id 123359)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                   7        0.00          0.00
            gc cr block 2-way                               4        0.00          0.00
            enq: TM - contention                            3        0.00          0.00
          ********************************************************************************
          
          SQL ID: bjf6bf08gxjz4
          Plan Hash: 2924446840
          DELETE GROUPS 
          WHERE
           ID = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      0.01       0.39         20         34         44           8
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       20      0.01       0.39         20         34         44           8
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  GROUPS (cr=2 pr=1 pw=0 time=0 us)
                1   INDEX UNIQUE SCAN SYS_C0013595 (cr=2 pr=0 pw=0 time=0 us cost=1 size=22 card=1)(object id 77299)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                  39        0.00          0.00
            library cache lock                              3        0.00          0.00
            library cache pin                               3        0.00          0.00
            KJC: Wait for msg sends to complete             2        0.00          0.00
            row cache lock                                  1        0.00          0.00
            gc current grant busy                           8        0.00          0.00
            db file sequential read                        20        0.07          0.37
            gc cr grant 2-way                               9        0.00          0.00
            gc current grant 2-way                         10        0.00          0.00
          ********************************************************************************
          
          SQL ID: cp9rsmpp3drsv
          Plan Hash: 1113954837
          DELETE LOGIN 
          WHERE
           ID = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      0.02       0.21         18         34        104          10
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       20      0.02       0.21         18         34        104          10
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  LOGIN (cr=250 pr=2 pw=0 time=0 us)
                1   INDEX UNIQUE SCAN SYS_C0013598 (cr=2 pr=0 pw=0 time=0 us cost=1 size=17 card=1)(object id 77301)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                  35        0.00          0.00
            enq: TM - contention                            3        0.00          0.00
            gc current grant 2-way                         10        0.00          0.00
            db file sequential read                        18        0.02          0.18
            gc cr grant 2-way                               6        0.00          0.00
            gc current grant busy                           7        0.00          0.00
            gc current block 2-way                          5        0.00          0.00
            library cache lock                              1        0.00          0.00
            library cache pin                               1        0.00          0.00
          ********************************************************************************
          
          SQL ID: 4aumq07d45u44
          Plan Hash: 1807375833
          DELETE EQUIPMENT_INSTANCE 
          WHERE
           CONTROL_POINT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     17      1.01       6.07        391         35      86850         133
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       20      1.01       6.07        391         35      86850         133
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  EQUIPMENT_INSTANCE (cr=822719 pr=2321 pw=0 time=0 us)
               36   INDEX RANGE SCAN IDX_FK_EQI_CONTROL_POINT (cr=3 pr=0 pw=0 time=140 us cost=1 size=267 card=3)(object id 123363)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                8832        0.00          0.01
            library cache lock                            408        0.00          0.09
            library cache pin                             408        0.00          0.07
            enq: TM - contention                           23        0.00          0.00
            gc current grant busy                          79        0.00          0.02
            gc current block 2-way                       6898        0.00          2.63
            gc current grant 2-way                        190        0.00          0.04
            db file sequential read                       391        0.06          4.77
            KJC: Wait for msg sends to complete            73        0.00          0.00
            gc current block busy                           5        0.03          0.06
            gc current block congested                      2        0.00          0.00
            latch: gc element                               1        0.00          0.00
            log file switch completion                      1        0.11          0.11
            log file switch (checkpoint incomplete)         2        0.74          0.93
            latch: cache buffers chains                     1        0.00          0.00
          ********************************************************************************
          
          SQL ID: 1300jc9wpbwds
          Plan Hash: 1745547790
          DELETE CONTROL_POINT 
          WHERE
           ID = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        2      0.00       0.00          0          0          0           0
          Execute     16      0.12       1.99        293         32        881          16
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       18      0.13       1.99        293         32        881          16
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  CONTROL_POINT (cr=198 pr=11777 pw=0 time=0 us)
                1   INDEX UNIQUE SCAN SYS_C0013556 (cr=2 pr=0 pw=0 time=0 us cost=1 size=55 card=1)(object id 77279)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                 317        0.00          0.00
            library cache lock                             39        0.00          0.00
            library cache pin                              39        0.00          0.00
            KJC: Wait for msg sends to complete             7        0.00          0.00
            enq: TM - contention                            2        0.00          0.00
            gc current grant busy                          41        0.00          0.01
            db file sequential read                       293        0.04          1.83
            gc current block 2-way                         22        0.00          0.00
            gc current grant 2-way                         96        0.00          0.01
          ********************************************************************************
          
          SQL ID: bv7vfv318vs75
          Plan Hash: 1350967167
          DELETE EQUIPMENT_INSTANCE 
          WHERE
           ACCOUNT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        3      0.00       0.00          0          0          0           0
          Execute     16      0.02       0.02          0         33         96           0
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       19      0.02       0.02          0         33         96           0
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  EQUIPMENT_INSTANCE (cr=3 pr=0 pw=0 time=0 us)
                0   INDEX RANGE SCAN IDX_FK_EQI_ACCOUNT (cr=3 pr=0 pw=0 time=0 us cost=1 size=267 card=3)(object id 123353)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                  33        0.00          0.00
            library cache lock                              7        0.00          0.00
            library cache pin                               7        0.00          0.00
            KJC: Wait for msg sends to complete             1        0.00          0.00
            enq: TM - contention                            4        0.00          0.00
            gc cr block 2-way                               1        0.00          0.00
          ********************************************************************************
          
          SQL ID: 5z1bsm2htgj9c
          Plan Hash: 3092315974
          DELETE ACCOUNT 
          WHERE
           ID = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        2      0.00       0.00          0          0          0           0
          Execute     16      0.15       0.85         64         32        963          16
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       18      0.15       0.85         64         32        963          16
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  ACCOUNT (cr=4188 pr=1556 pw=0 time=0 us)
                1   INDEX UNIQUE SCAN SYS_C0013504 (cr=2 pr=0 pw=0 time=0 us cost=1 size=69 card=1)(object id 77257)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                 445        0.00          0.00
            library cache lock                             83        0.00          0.01
            library cache pin                              83        0.00          0.01
            KJC: Wait for msg sends to complete            21        0.00          0.00
            row cache lock                                  1        0.00          0.00
            enq: TM - contention                           21        0.00          0.00
            gc current grant busy                          47        0.00          0.01
            db file sequential read                        64        0.04          0.64
            gc current grant 2-way                         39        0.00          0.00
            gc current block 2-way                          6        0.00          0.00
          ********************************************************************************
          
          SQL ID: 5vvbs7mjtk10w
          Plan Hash: 3272940213
          DELETE MEDIA 
          WHERE
           ACCOUNT = :B1 
          
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        2      0.00       0.00          0          0          0           0
          Execute     16      0.01       0.01          0        110          0           0
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total       18      0.01       0.01          0        110          0           0
          
          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: ALL_ROWS
          Parsing user id: 98     (recursive depth: 1)
          
          Rows     Row Source Operation
          -------  ---------------------------------------------------
                0  DELETE  MEDIA (cr=11 pr=0 pw=0 time=0 us)
                0   INDEX RANGE SCAN IDX_ACCCOUNT_BASE_NAME (cr=11 pr=0 pw=0 time=0 us cost=6 size=60690 card=510)(object id 77594)
          
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation                  17        0.00          0.00
            gc cr block 2-way                              17        0.00          0.00
          
          
          
          ********************************************************************************
          
          OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        1      0.00       0.00          0          0          0           0
          Execute      1      1.41       4.94          0          0      40869           0
          Fetch        0      0.00       0.00          0          0          0           0
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total        2      1.41       4.95          0          0      40869           0
          
          Misses in library cache during parse: 1
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            SQL*Net message to client                       1        0.00          0.00
            SQL*Net message from client                     1       12.46         12.46
            ges message buffer allocation               35245        0.00          0.04
            library cache lock                              4        0.00          0.00
            library cache pin                               5        0.00          0.00
            gc current grant busy                         144        0.00          0.03
            db file sequential read                     83268        0.43        711.22
            gc current grant 2-way                       2941        0.00          0.64
            gc current block 2-way                      32079        0.01         12.32
            gc current block congested                     27        0.00          0.01
            gc current grant congested                      2        0.00          0.00
            log file switch completion                      9        0.14          0.66
            log file switch (checkpoint incomplete)         7        5.66         24.78
            gc current block busy                          33        0.11          0.62
            latch: cache buffers chains                    29        0.00          0.00
            latch: gc element                               3        0.00          0.00
            gc buffer busy release                          3        0.03          0.04
            gc buffer busy acquire                          3        0.10          0.20
            read by other session                           1        0.01          0.01
          
          
          OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
          
          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse      330      0.07       0.10          0          0         62           0
          Execute  19939    132.23    1612.14     182523    3098316    3181058      352357
          Fetch      588      0.01       0.06          4        776          0         973
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total    20857    132.32    1612.32     182527    3099092    3181120      353330
          
          Misses in library cache during parse: 61
          Misses in library cache during execute: 60
          
          Elapsed times include waiting on following events:
            Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
            ges message buffer allocation              184180        0.00          0.27
            library cache lock                            586        0.00          0.12
            library cache pin                             586        0.00          0.10
            Disk file operations I/O                        2        0.00          0.00
            row cache lock                                 30        0.00          0.00
            KJC: Wait for msg sends to complete           118        0.00          0.00
            gc cr block 2-way                           13568        0.00          5.23
            gc current grant busy                       58790        0.01         14.87
            db file sequential read                    180359        0.67       1408.85
            enq: TM - contention                           90        0.00          0.02
            gc current grant 2-way                      45187        0.01         10.67
            gc current block 2-way                      40996        0.00         15.54
            gc cr grant 2-way                             167        0.00          0.03
            gc cr multi block request                   35022        0.00         10.94
            db file scattered read                        421        0.41          1.73
            enq: TT - contention                           73        0.00          0.01
            gc current block congested                     35        0.00          0.01
            gc current grant congested                     75        0.00          0.01
            log file switch completion                     18        0.17          1.72
            log file switch (checkpoint incomplete)        24        4.54         27.68
            gc current block busy                          11        0.03          0.14
            latch: cache buffers chains                    40        0.00          0.00
            gc cr block busy                                7        0.02          0.06
            gc cr block congested                          11        0.00          0.00
            latch: gc element                              17        0.00          0.00
            gc remaster                                     1        0.03          0.03
            gcs drm freeze in enter server mode             7        0.07          0.35
            latch: object queue header operation            3        0.00          0.00
            latch: gcs resource hash                        3        0.00          0.00
            db file parallel read                          15        0.01          0.03
            latch: cache buffers lru chain                  2        0.00          0.00
            gc buffer busy release                          2        0.00          0.00
          
            168  user  SQL statements in session.
          19823  internal SQL statements in session.
          19991  SQL statements in session.
          ********************************************************************************
          Trace file: vchprd11_ora_14369_purge_trace.trc
          Trace file compatibility: 11.1.0.7
          Sort options: default
          
                 1  session in tracefile.
               168  user  SQL statements in trace file.
             19823  internal SQL statements in trace file.
             19991  SQL statements in trace file.
                62  unique SQL statements in trace file.
            960355  lines in trace file.
              1617  elapsed seconds in trace file.
          • 2. Re: Delete hang
            Hemant K Chitale
            The statements for this section of the report :
            VERALL TOTALS FOR ALL RECURSIVE STATEMENTS
             
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse      330      0.07       0.10          0          0         62           0
            Execute  19939    132.23    1612.14     182523    3098316    3181058      352357
            Fetch      588      0.01       0.06          4        776          0         973
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total    20857    132.32    1612.32     182527    3099092    3181120      353330
            are not present in the output.

            Most of the elapsed time is in these waits: (180,359 waits for a total time of 1,408seconds)
              db file sequential read                    180359        0.67       1408.85
            Unfortunately, the SQL that incurs these waits does NOT appear in the output.


            Hemant K Chitale
            1 person found this helpful
            • 3. Re: Delete hang
              Hemant K Chitale
              In addition to my previous post .... my guess is that there is an ON DELETE Trigger on the target table and that trigger is where these 1600+seconds are being spent.


              Hemant K Chitale
              1 person found this helpful
              • 4. Re: Delete hang
                864988
                Thank you. I will check with developers if there is any trigger.
                How can I identify the statements causing the waits?
                • 5. Re: Delete hang
                  Hemant K Chitale
                  Every statement has a section that shows "Elapsed times include waiting on following events:" .


                  Hemant K Chitale