1 2 Previous Next 26 Replies Latest reply: Jan 29, 2013 1:12 PM by 695836 RSS

    Delete statement very slow

    938302
      Hi Team,

      I have a DB 9.2.0.7 ON Unix,

      e have a performance issue the delete statements on the database are running very slow they are taking about 5 hours.

      delete from FACT_VENTAS_DIA where ID_FECHA between to_date('01/01/2008','DD/MM/YYYY') AND TO_DATE('15/01/2008','DD/MM/YYYY');

      And the delete doesn't end.

      Regards,

      Guido
        • 1. Re: Delete statement very slow
          ji li
          Couple suggestions you may have already done (or not).

          Look in the alert log for any errors that might give clues.
          Make sure there are no mount points full on your operating system (e.g., where archives go, etc.).

          Then, you can look at your os performance and make sure you are not I/O bound or somethng else.

          Lots of things to look for. Once you start doing your initial research, then you can query v$transaction, v$session_longops, v$sysstat, etc...
          • 2. Re: Delete statement very slow
            Rob_J
            How much data are you expecting to delete?
            What does it say in the v$session_event and v$session_wait views for the session which is deleting the data?
            Are there high disk queues?
            Are there lots of indexes which have to be maintained while the delete is happening?

            In short, we need more information to help you. Slow compared to what? Is it somehting you normally do and it's taking longer, for example? It might be slow but it might be the quickest that your system is able to process the data so it might be optimal but you are asking it to be faster than is possible. There are so many possible answers to this question at the moment it's hard to help.
            • 3. Re: Delete statement very slow
              AdamMartin
              1. Is ID_FECHA indexed?
              2. Is there another table with a foreign key that points to ID_FECHA? If so, is that column in the other table indexed?

              Please post the table definition as well as its indexes.
              • 4. Re: Delete statement very slow
                Osama_Mustafa
                Some useful Tips
                http://stackoverflow.com/questions/5792425/strategy-to-improve-oracle-delete-performance
                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164
                • 5. Re: Delete statement very slow
                  938302
                  HI,

                  1. Is ID_FECHA indexed?

                  Yes, the ID_FECHA was indexed
                  2. Is there another table with a foreign key that points to ID_FECHA? If so, is that column in the other table indexed?

                  Yes.. and was indexed

                  But the problem persist,
                  • 6. Re: Delete statement very slow
                    AdamMartin
                    You missed the most important part:

                    Please post the table definition as well as its indexes.
                    • 7. Re: Delete statement very slow
                      sb92075
                      HOW To Make TUNING request
                      SQL and PL/SQL FAQ
                      • 8. Re: Delete statement very slow
                        938302
                        HI,

                        SQL> SET LINE 200
                        SQL> select INDEX_NAME, COLUMN_NAME from dba_ind_columns where TABLE_NAME = 'FACT_VENTAS_DIA' AND COLUMN_NAME = 'ID_FECHA';

                        INDEX_NAME
                        ------------------------------
                        COLUMN_NAME
                        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        I2_FACT_VENTAS_DIA
                        ID_FECHA

                        FACT_VENTAS_DIA_C1
                        ID_FECHA

                        I4_FACT_VENTAS_DIA
                        ID_FECHA


                        SQL>


                        SQL> desc FACT_VENTAS_DIA;
                        Name Null? Type
                        ----------------------------------------- -------- ----------------------------
                        ID_FECHA DATE
                        ID_CLIENTE_INASE NUMBER(20)
                        ID_NIVEL_CLIENTE VARCHAR2(3)
                        ID_CLAVE_NO_VENTA VARCHAR2(4)
                        ID_RUTA NUMBER(10)
                        ID_VENDEDOR NUMBER(10)
                        ID_TIPO_RUTA NUMBER(10)
                        ID_SUCURSAL NUMBER(5)
                        PESOS_DULCE NUMBER(16,2)
                        CANTIDAD_DULCE NUMBER(16,2)
                        PESOS_SALADO NUMBER(16,2)
                        CANTIDAD_SALADO NUMBER(16,2)
                        PESOS_DEVOLUCIONES NUMBER(16,2)
                        CANTIDAD_DEVOLUCIONES NUMBER(16,2)
                        VISITA_EFECTIVO NUMBER(1)
                        IND_VPROG NUMBER
                        ID_SEMANA_ANO NUMBER(10)



                        Regards,

                        Guido
                        • 9. Re: Delete statement very slow
                          Jan-Marten Spit
                          where is the explain plan?

                          what is the size of the index on the date column?
                          how many rows in the table?
                          what is the size of the table?
                          • 10. Re: Delete statement very slow
                            AdamMartin
                            Thank you. But DDL (create table... / create index...) would have been a lot more useful. You can use DBMS_METADATA.GET_DDL to obtain the DDL of already-built database objects.

                            So is ID_FECHA part of a unique key on this table? What are all the child tables of FACT_VENTAS_DIA? It is odd to have a table with the term "fact" in it be a parent table to other child tables. But you indicated that there are indeed other tables with foreign keys that point back to this table. That's a red flag.
                            • 11. Re: Delete statement very slow
                              938302
                              what is the size of the index on the date column?

                              ACT_VENTAS_DIA' AND COLUMN_NAME = 'ID_FECHA');

                              NUM_ROWS SAMPLE_SIZE
                              ---------- -----------
                              47541636 47541636
                              47541636 47541636
                              47541636 47541636

                              SQL>

                              how many rows in the table?
                              139000000

                              But for my where has 20000 registers

                              what is the size of the table?

                              3244.75 Mb


                              Regards,

                              Guido
                              • 12. Re: Delete statement very slow
                                938302
                                Update


                                EXPLAIN PLAN for delete from FACT_VENTAS_DIA where ID_FECHA between to_date ('01/03/2008','DD/MM/YYYY') AND TO_DATE('15/03/2008','DD/MM/YYYY');


                                SQL> SELECT *
                                FROM TABLE(DBMS_XPLAN.DISPLAY); 2

                                -------------------------------------------------------------------------------- ----------------------
                                | Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
                                -------------------------------------------------------------------------------- ----------------------
                                | 0 | DELETE STATEMENT | | 420K| 12M| 9986 | | | |
                                | 1 | DELETE | FACT_VENTAS_DIA | | | | | | |
                                | 2 | TABLE ACCESS FULL | FACT_VENTAS_DIA | 420K| 12M| 9986 | 18,00 | P->S | QC (RAND) |
                                -------------------------------------------------------------------------------- ----------------------

                                Note: cpu costing is off, PLAN_TABLE' is old version

                                10 rows selected.


                                Regards,

                                Guido
                                • 13. Re: Delete statement very slow
                                  Richard Harrison .
                                  Hi,
                                  Have any of these indexes got the column in the where clause in position '1' in the index?

                                  Regards,
                                  Harry
                                  • 14. Re: Delete statement very slow
                                    jgarry
                                    You need to put
                                     tags before and after your output so we can read it easier.  You also need to show your predicate in the plan.  See http://www.orafaq.com/node/1798                                                                                                                                                                                                                                                                                                                                                    
                                    1 2 Previous Next