1 2 Previous Next 17 Replies Latest reply on Jul 17, 2014 2:27 PM by observer_83

    DELETE operation is taking very long time

    observer_83

      Hi Experts,

       

      I have one requirement from user where in approx 50k rows to be deleted from table, but delete operation is taking hell lot of time and still unfinished after 7+ hours.However same statement with select is giving o/p in 1 sec,

       

      Details as below

       

      DB Oracle 11gr2

       

      Statement

       

      delete

      from T1 SCONV WHERE SCONV.JDOID in

      (SELECT STG.JDOID FROM T2 STG

      WHERE

      TABLE_NAME='T1' AND IS_PARENT='Y')

      AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

      CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')

       

      SCONV.JDOID is NOT NULL column where oracle created unique index is already using in plan.

      UIM_ROLLBK_IDX  is composite index on table  T2(TABLE_NAME,IS_PARENT_Y,JDOID)            

       

       

      Explain plan

       

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

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

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

      |   0 | DELETE STATEMENT              |                             |    15 |   885 |  1179   (1)| 00:00:15 |

      |   1 |  DELETE                       |          T1                 |       |       |            |          |

      |   2 |   NESTED LOOPS                |                             |       |       |            |          |

      |   3 |    NESTED LOOPS               |                             |    15 |   885 |  1179   (1)| 00:00:15 |

      |   4 |     SORT UNIQUE               |                             |  1745 | 55840 |   302   (1)| 00:00:04 |

      |*  5 |      INDEX RANGE SCAN         | UIM_ROLLBK_IDX              |  1745 | 55840 |   302   (1)| 00:00:04 |

      |*  6 |     INDEX UNIQUE SCAN         | SYS_C0011467                |     1 |       |     1   (0)| 00:00:01 |

      |*  7 |    TABLE ACCESS BY INDEX ROWID  | T1     |     1 |    27 |     2   (0)| 00:00:01 |

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

       

      This is quite surprising to me. Fresh stats is available on tables and indexes are also rebuild to support deletion. One more observation is there  when i am monitoring deletion its waiting for "db file scattered read" in place of sequential read.

       

      Space is available in UNDO and Temp Tablespace and no significant error in alert log

       

      Can anyone please explain how to speed up deletion process and what all things to check & improve this.

       

      Rgds

      Gaurav

        • 1. Re: DELETE operation is taking very long time
          observer_83

          Can someone please respond his/her experience on the same??

          • 2. Re: DELETE operation is taking very long time
            Martin Preiss

            how many rows do you get for the query:

            SELECT STG.JDOID

              FROM T2 STG

            WHERE TABLE_NAME='T1'

               AND IS_PARENT='Y'

            ?

            • 3. Re: DELETE operation is taking very long time
              Billy~Verreynne

              What is the DELETE operation's wait states and events? Perhaps it is waiting for another session to commit/rollback in  order for it to lock the relevant rows to delete.

               

              Finding the rows are typically less work, then deleting the rows. As the delete needs additional I/O to enforce constraints, remove rows from indexes, fire triggers, and so on.

               

              So comparing a SELECT against a DELETE for the same basic data set (same predicates), is not a fair comparison ito work.

              • 4. Re: DELETE operation is taking very long time
                Martin Preiss

                and a second question: did you create the execution plan with explain plan or did you gather it from the memory using dbms_xplan.display_cursor?

                • 5. Re: DELETE operation is taking very long time
                  observer_83

                  Thanks Martin for the response

                   

                  SELECT STG.JDOID

                    FROM T2 STG

                  WHERE TABLE_NAME='T1'

                     AND IS_PARENT='Y'


                  it resolves 50K records as highlighted earlier, also i am aware delete is always heavy than select but how much? if i am able to select same data set with in 1 sec and not able to delete it even 7-8 hours and indefinite time (not known). wait event it was displaying "db file scattered read" which is also not relevant to delete i guess..?


                  Can i have some more areas to be checked, i checked table has no unindexed foreign keys.Need help in troubleshooting.

                   

                  explain plan was captured from memory dbms_xplan.display_cursor.

                   

                  Thanks ..

                  • 6. Re: DELETE operation is taking very long time
                    Sven W.

                    Are there any FK constraints on another table pointing to the delete table?

                    If there are, are those FKs indexed? For each row the database will do a select on such detail tables to check if child records exists.

                    • 7. Re: DELETE operation is taking very long time
                      observer_83

                      There is only Primary constraint enabled and no FK on this table.

                       

                      Are there any FK constraints on another table pointing to the delete table?


                      Can you please share me how to find it?


                      Thanks ...

                      • 8. Re: DELETE operation is taking very long time
                        Sven W.

                        This will show you the PK of your table

                        select pk.constraint_name
                        from user_constraints pk
                        where pk.table_name = 'YOURTABLE'
                        and pk.constraint_type in ('P','U')
                        ;
                        
                        

                         

                        This will show you all the foreign keys pointing to the PK of your table

                         

                        select pk.constraint_name as PK
                             , fk.constraint_name as FK
                             , fk.table_name as tk_table
                        from user_constraints pk
                        join user_constraints fk on fk.r_constraint_name = pk.constraint_name
                        where pk.table_name = 'YOURTABLE'
                        and pk.constraint_type in ('P','U')
                        and fk.constraint_type = 'R';
                        
                        

                         

                        And this adds a crude index check. (It only looks if one of the columns in the constraint on the detail table is indexed).

                        If you find tables/FKs where the "isindexed" column returns "No" then those might be a performance issue.

                         

                        select pk.constraint_name as PK
                             , fk.constraint_name as FK
                             , fk.table_name as tk_table
                             , case when exists (select null
                                                 from user_cons_columns cc
                                                 join user_ind_columns ic on cc.column_name = ic.column_name and ic.table_name = cc.table_name
                                                 where cc.constraint_name = fk.constraint_name
                                                 )
                                    then 'Yes'
                                    else 'No'
                                end as isindexed
                        from user_constraints pk
                        join user_constraints fk on fk.r_constraint_name = pk.constraint_name
                        where pk.table_name = 'YOURTABLE'
                        and pk.constraint_type in ('P','U')
                        and fk.constraint_type = 'R';
                        
                        
                        • 9. Re: DELETE operation is taking very long time
                          Dan Jankowski

                          Scattered reads do suggest a full table scan, as opposed to access by index rowid. From what's been said so far, I would suggest that unindexed foreign keys are to blame. Also worth checking whether those foreign keys have an ON DELETE CASCADE option set, as this will further burden the delete with having to delete child records.

                          • 10. Re: DELETE operation is taking very long time
                            Martin Preiss

                            the optimizer expects that only 15 rows have to be deleted and creates its plan based on the estimate. Furthermore the optimizer expects this operation to be very fast. So there are two possibilities:

                            • the operation is blocked by something else: this should be visible in v$lock and v$session.
                            • the operation is very slow: this could be the result of a suboptimal choice in plan creation. If the optimizer expects 15 iterations a nested loop is a good idea. For 50k iterations this could no longer be true.

                            Following the second possible option: is data for (table_name, is_parent) evenly distributed or is there a skew effect? Does the addition of JDOID change the picture?

                             

                            You could also try to create a plan with rowsource statistics and stop the execution after some time. The result should tell you how many loops had been completed till the operation was killed.

                            • 11. Re: DELETE operation is taking very long time
                              it resolves 50K records as highlighted earlier, also i am aware delete is always heavy than select but how much? if i am able to select same data set with in 1 sec and not able to delete it even 7-8 hours and indefinite time (not known).

                              Just because the first set of rows is returned in '1 sec' doesn't mean the entire select query only took 1 sec. Most tools only return the first 'page' of results.

                               

                              Post results that validate the number of rows to be deleted and that show the plan using DBMS_XPLAN.DISPLAY_CURSOR.

                               

                              This was your query:

                              delete

                              from T1 SCONV WHERE SCONV.JDOID in

                              (SELECT STG.JDOID FROM T2 STG

                              WHERE

                              TABLE_NAME='T1' AND IS_PARENT='Y')

                              AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

                              CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')

                              So show the count that query produces by replacing 'DELETE' with 'SELECT COUNT(*)':

                              SELECT COUNT(*)

                              from T1 SCONV WHERE SCONV.JDOID in

                              (SELECT STG.JDOID FROM T2 STG

                              WHERE

                              TABLE_NAME='T1' AND IS_PARENT='Y')

                              AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

                              CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')

                              Also post the ENTIRE session showing an 'EXPLAIN PLAN FOR' that delete query above and the results.

                              • 12. Re: DELETE operation is taking very long time
                                Sven W.

                                 

                                Statement

                                 

                                delete

                                from T1 SCONV WHERE SCONV.JDOID in

                                (SELECT STG.JDOID FROM T2 STG

                                WHERE

                                TABLE_NAME='T1' AND IS_PARENT='Y')

                                AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

                                CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')

                                 

                                SCONV.JDOID is NOT NULL column where oracle created unique index is already using in plan.

                                UIM_ROLLBK_IDX  is composite index on table  T2(TABLE_NAME,IS_PARENT_Y,JDOID)           

                                 

                                 

                                Explain plan

                                 

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

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

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

                                |   0 | DELETE STATEMENT              |                             |    15 |   885 |  1179   (1)| 00:00:15 |

                                |   1 |  DELETE                       |          T1                 |       |       |            |          |

                                |   2 |   NESTED LOOPS                |                             |       |       |            |          |

                                |   3 |    NESTED LOOPS               |                             |    15 |   885 |  1179   (1)| 00:00:15 |

                                |   4 |     SORT UNIQUE               |                             |  1745 | 55840 |   302   (1)| 00:00:04 |

                                |*  5 |      INDEX RANGE SCAN         | UIM_ROLLBK_IDX              |  1745 | 55840 |   302   (1)| 00:00:04 |

                                |*  6 |     INDEX UNIQUE SCAN         | SYS_C0011467                |     1 |       |     1   (0)| 00:00:01 |

                                |*  7 |    TABLE ACCESS BY INDEX ROWID  | T1     |     1 |    27 |     2   (0)| 00:00:01 |

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

                                 

                                 

                                Just noticed two things.

                                1) You never mentioned how big table T1 is. Do you delete 50k rows from a 51k rows table or from a 3 petaRows table?

                                2) It looks like the subquery on T2 is executed once for each row in T1 (nested loop on lines 2 and 3)

                                 

                                This looks strange. Are the table, column and index statistics correct for table t1?

                                The estimated number of rows (15) from the plan doesn't match at all with yout 50k to delete info.

                                • 13. Re: DELETE operation is taking very long time
                                  observer_83

                                  Hi Dan ,

                                   

                                  There is no unindexed foreign keys , this i checked very first.

                                   

                                  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

                                  Hi Martin,

                                   

                                  1. there is no lock or blocking session pointing to this .

                                  2.looking at the plan i also got confused why it is only counting 15 rows in place of 50K. i flushed the shared pool before fresh deletion again. but still same.

                                  3 data is not not evenly distributed as PREVIOUSCONFIGURATION column alone has 30 million of null's

                                   

                                  You could also try to create a plan with rowsource statistics and stop the execution after some time. The result should tell you how many loops had been completed till the operation was killed.


                                  Can you tell me how to check this, may be with commands to check would be worth?


                                  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

                                  Hi rp0428


                                  select * from table(dbms_xplan.display_cursor('09d51z465h9xa'))

                                  /

                                   

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

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

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

                                  |   0 | DELETE STATEMENT              |                             |    15 |   885 |  1179   (1)| 00:00:15 |

                                  |   1 |  DELETE                                |                T1            |       |       |            |          |

                                  |   2 |   NESTED LOOPS                |                             |       |       |            |          |

                                  |   3 |    NESTED LOOPS               |                             |    15 |   885 |  1179   (1)| 00:00:15 |

                                  |   4 |     SORT UNIQUE               |                             |  1745 | 55840 |   302   (1)| 00:00:04 |

                                  |*  5 |      INDEX RANGE SCAN         | UIM_ROLLBK_IDX              |  1745 | 55840 |   302   (1)| 00:00:04 |

                                  |*  6 |     INDEX UNIQUE SCAN         | SYS_C0011467                |     1 |       |     1   (0)| 00:00:01 |

                                  |*  7 |    TABLE ACCESS BY INDEX ROWID| T1  |     1 |    27 |     2   (0)| 00:00:01 |

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

                                   

                                  SELECT COUNT(*)

                                  from T1 SCONV WHERE SCONV.JDOID in

                                  (SELECT STG.JDOID FROM T2 STG

                                  WHERE

                                  TABLE_NAME='T1' AND IS_PARENT='Y')

                                  AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

                                  CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS')

                                   

                                  SQL>COUNT(*)

                                            49981

                                  Elapsed: 00:00:00:01

                                  06:42:35 SQL>

                                   

                                  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

                                  Hi Sven,

                                   

                                  1. out T1 table has 3.2 million rows and T2 table has around 1.4 million rows.

                                  2. Yes statistics are up2date and indexes are also rebuild. Yes only 15 rows displaying in plan is quite questionable, Can you please share more on it what else should i check, flushing of shared poll is already been done.

                                   

                                  Thanks

                                  • 14. Re: DELETE operation is taking very long time
                                    Martin Preiss

                                    the optimizer is a clever piece of software - but still it does not always produce the correct cardinalities. Uneven distribution on the indexed T2 columns could be a reason for wrong calculations - and sometimes histograms can solve this kind of problem. But I would try to get some additional data for analysis and create the mentioned plan with rowsource statistics. All you have to do is to add the hint gather_plan_statistics:

                                    delete /*+ gather_plan_statistics */

                                    from T1 SCONV WHERE SCONV.JDOID in

                                    (SELECT STG.JDOID FROM T2 STG

                                    WHERE

                                    TABLE_NAME='T1' AND IS_PARENT='Y')

                                    AND SCONV.PREVIOUSCONFIGURATION IS NULL AND

                                    CREATEDDATE = to_date('20140711', 'YYYYMMDD HH24:MI:SS');

                                     

                                    select *

                                      from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

                                    If you have to stop the execution before completion (and I guess: you have to) you would have to use sql_id and child_number instead of the NULL values - and of course the statistics will only show the number of iterations and resource usage up to this point, but maybe this will already shed some light on the issue. Some information on the meaning of these incomplete statistics can be found in: Re: Should I wait until the finish of the execution time of the query for execution plan?

                                    1 person found this helpful
                                    1 2 Previous Next