This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 29, 2013 11:12 AM by 695836 RSS

Delete statement very slow

938302 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    You missed the most important part:

    Please post the table definition as well as its indexes.
  • 7. Re: Delete statement very slow
    sb92075 Guru
    Currently Being Moderated
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 8. Re: Delete statement very slow
    938302 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points