1 2 Previous Next 26 Replies Latest reply on Jan 29, 2013 7:12 PM by 695836 Go to original post
      • 15. Re: Delete statement very slow

        yes has position 1.

        • 16. Re: Delete statement very slow
          Hemant K Chitale
          Oracle is executing a serial delete. You could use parallelism in the DELETE with an ALTER SESSION ENABLE PARALLEL DML and then using a Parallel Hint in the DELETE statement.

          The optimizer expects to delete 420K rows. That would be a large number of blocks. The Clustering Factor for the available index(es) would lead Oracle to determine whether it should use an Index or do a Full Table Scan relative to the number of blocks for a full table scan.

          How many rows does the delete statement actually have to delete ?

          Hemant K Chitale
          • 17. Re: Delete statement very slow
            DB Dude

            how to know oracle is executing a serial delete? just lack of parallel hint?

            • 18. Re: Delete statement very slow
              Hemant K Chitale
              Under "PQ Distrib" in the Explain Plan we see "P->S QC (RAND) "

              Hemant K Chitale
              • 19. Re: Delete statement very slow
                Jan-Marten Spit
                you have a table with 139.000.000 rows, 3.2GiB high water mark, from which you want to delete 20.000.

                your delete is doing a full table scan.

                i asked for the size of the index, but you gave the number of iindexed rows, which is 47.541.636 rows...are some (quite a few) ID_FETCHA values NULL, or are the statistics stale?

                the where clause specifies a DATE column which is indexed. this date has probably an increasing nature - is there a histogram on ID_FETCHA? If so, you may consider to delete it (after testing) - histograms and constantly increasing values are generally a bad idea - the CBO will revert to density analysis when it sees the rabge specified in the where clause is outside the histogram range - which may cause a problem of you have a few odd (far in the past, of far into the future) date values. your explain plan estimates a delete of 420.000 rows, not 20.000.

                suppose it is 20.000 rows, and suppose you need to access 20.000 different blocks for it, and suppose they are not in cache - 20.000 * 12ms = 240 seconds worst case - as a rough estimate, as we did not cater for updating the indexes.

                3200MiB / 240s = 13.3MiB/s, which is something the hardware should be able to do :D

                5 hours is insane - unless,
                - a LOT of indexes on the table
                - row chaining / migration
                - config problem
                - hardware problem

                note that the cost is estimated as 9986, which means estimated time (assuming default SREADTIM) 9986*12ms = 119 seconds.

                so the CBO cost is not in touch with reality for this particular delete statement. note that maybe your fault, by providing wrong statistics to it :)

                also, the FTS is not a really bad decision...unless a lot of the table and index blocks are in cache.

                all in all - not enough info :)

                things i would check:

                what is your top wait event for the deleting session? are you sure you are not bound by DBWR or LGWR?
                clustering factor of the index?
                size of the index segment?
                how many indexes?
                very stale statistics?
                histogram on the FETCHA column?
                row chaining or row migration?
                values of SREADTIM and MREADTIM from sys.aux_stats$?
                and so on :)

                good luck!
                • 20. Re: Delete statement very slow
                  what is your top wait event for the deleting session?

                  5     32     db file sequential read     156882     0     168195     1     583     1681945670
                  4     32     log file switch completion     3     0     14     5     8     136686
                  7     32     PX Deq: Parse Reply     4     0     4     1     4     44250
                  2     32     process startup     2     0     3     2     2     34005
                  3     32     buffer busy waits     3     0     2     1     1     22672
                  1     32     latch free     2     0     1     0     1     6060
                  6     32     PX Deq: Join ACK     1     0     0     0     0     4905
                  are you sure you are not bound by DBWR or LGWR?
                  I don't know. How Can Identify this?

                  clustering factor of the index?
                  size of the index segment?      OWNER     SEGMENT_NAME     SEGMENT_TYPE     MB
                  1     DATAW1     FACT_VENTAS_DIA_C1     INDEX     2262
                  2     DATAW1     I1_FACT_VENTAS_DIA     INDEX     1281
                  3     DATAW1     I2_FACT_VENTAS_DIA     INDEX     1795
                  4     DATAW1     I3_FACT_VENTAS_DIA     INDEX     1415
                  5     DATAW1     I4_FACT_VENTAS_DIA     INDEX     1768
                  6     DATAW1     I5_FACT_VENTAS_DIA     INDEX     1415
                  7     DATAW1     I6_FACT_VENTAS_DIA     INDEX     1542
                  8     DATAW1     I7_FACT_VENTAS_DIA     INDEX     1093

                  how many indexes?
                  very stale statistics?
                  I executed the statistics for the table.
                  histogram on the FETCHA column?
                  row chaining or row migration?
                  row chaining
                  values of SREADTIM and MREADTIM from sys.aux_stats$?
                  SQL> select * from sys.aux_stats$;

                  no rows selected



                  • 21. Re: Delete statement very slow
                    Jan-Marten Spit
                    so a delete via full table scan is taking a long time - and you see db file sequential reads.

                    for each deleted row the indexes need to be updated, so it may be pulling in index blocks with sequential reads, so it's not abnormal to see them for such a delete...but there are no scattered reads to be seen..which is kind of odd for a full table scan, unless there are a lot of chained or migrated rows in the table.

                    if you are the one responsible for this problem, i would start reading about the Oracle internals that play a role here. And you may want to attempt to reorganize the table and it's indexes. how often has this delete been running against this table? :D
                    • 22. Re: Delete statement very slow
                      This table has 150 million of register, and this the first time you do a delete
                      • 23. Re: Delete statement very slow
                        You can try this workaround:
                        1)Create a table as :
                        create table fact_ventas_dia_temp as select * from fact_ventas_dia where 1=2;
                        2) Insert data that you want to keep
                        insert /*+ APPEND */into fact_ventas_dia_temp select * from fact_ventas_dia where id_fecha not between  to_date('01/01/2008','DD/MM/YYYY') AND TO_DATE('15/01/2008','DD/MM/YYYY');
                        3) Truncate the original table
                        truncate table fact_ventas_dia;
                        4) Insert the data from temp table to the original table:
                        insert /*+ APPEND */into fact_ventas_dia select * from fact_ventas_dia; 
                        • 24. Re: Delete statement very slow
                          For deleting 20K rows out of 150M? That's silly. Get the dang index working.
                          • 25. Re: Delete statement very slow
                            - Run a trace. You have nothing to lose, and everything to gain by doing so. It could illuminate you with valuable information.

                            - Try the delete with an index hint, and try the delete in parallel, as suggested. These may not be solutions to the root cause, but there may be something learned from trying it.

                            - Discover all the tables that reference the table you are deleting from. This potential issue keeps getting ignored in this thread. It might not be a factor at all, but I feel it has not yet been ruled out completely.

                            It looks like something else is going on besides just the full table scan / non-use of the index. Because even with a full table scan, it still should not take 5 hours to read 150M records and delete 20,000 of them. Either there is some expensive recursive sql going on, or else there is some other systemic problem with the db setup.
                            • 26. Re: Delete statement very slow
                              Hmm,another option...

                              Make all the indexes unusable except on id_fetcha. Use a index hint. Later rebuild the unusable indexes.
                              1 2 Previous Next