1 2 Previous Next 29 Replies Latest reply: Sep 25, 2010 2:14 AM by Jonathan Lewis RSS

    Fragmentation effect

    614736
      10g R2
      Table has about 0.5 billion records at this time
      There is a process deleting records (5-6 millions per day)

      At the beginning of process there is a select getting min date (indexed column) to start from.
      I see this select taking more time - plus about 1 min each day (surprise).
      I tried shrink but canceled execution in a couple of hours. Based on previous discussions looks like it never completed on large tables.

      What other people really do in such cases besides full table reorganization?

      Thanks.
        • 1. Re: Fragmentation effect
          user6415560
          There is a process deleting rows. Is there also a process that is inserting rows? If so, how many rows are being inserted each day?

          You state that the date column is indexed, but then you seem to imply that the SELECT statement is doing a table scan. That doesn't make sense. If your date column is indexed,
          SELECT MIN(date_column)
            FROM your_table
          should just need to do an index scan and should happen almost instantly.

          If you are dealing with a table that has hundreds of millions of rows, are you using partitioning?

          Justin
          • 2. Re: Fragmentation effect
            618702
            Dear Bolev,

            You should consider buying the "Partitioning" feature of the Oracle database.

            Please read more about the partitioning;

            http://www.oracle.com/us/products/database/options/partitioning/index.htm

            Regards.

            Ogan
            • 3. Re: Fragmentation effect
              614736
              Before this process was first started it was instantly, now it is 11 min and getting longer

              No partitioning of cause..
              • 4. Re: Fragmentation effect
                JustinCave
                Is there also a process that is inserting rows? If so, how many rows are being inserted each day?

                You state that the date column is indexed, but then you seem to imply that the SELECT statement is doing a table scan. That doesn't make sense. Is the query roughtly of the form I posted earlier? If so, is the query plan really doing a table scan?

                Can you post the query and its query plan?

                Justin
                • 5. Re: Fragmentation effect
                  614736
                  Before this process was first started it was instantly, now it is 11 min and getting longer

                  No partitioning of cause..
                  • 6. Re: Fragmentation effect
                    614736
                    Sorry had problem to post this message
                    • 7. Re: Fragmentation effect
                      JustinCave
                      I understand that your query is taking longer than you want it to. We're happy to try to help, but we're going to need more information. If you can answer the questions that people are asking, that would help us understand your problem well enough to assist.

                      1) Is there also a process that is inserting rows? If so, how many rows are being inserted each day?

                      2) You state that the date column is indexed, but then you seem to imply that the SELECT statement is doing a table scan. That doesn't make sense. Is the query roughtly of the form I posted earlier? If so, is the query plan really doing a table scan?

                      3) Can you post the query and its query plan?

                      Justin
                      • 8. Re: Fragmentation effect
                        614736
                        Hi Justin
                        1. About 0.5 million insrts per day
                        2. Correction - I am saying takes now same time as full scan, not saying it is doing full scan. Query just SELECT MIN (ADATE) FROM ATABLE

                        I am planning partitioning but not now. This question sounds mostly: "What's going on?" :)
                        • 9. Re: Fragmentation effect
                          614736
                          Plan
                          SELECT STATEMENT ALL_ROWS Cost: 1,120,291 Bytes: 8 Cardinality: 1 Time: 13,444
                               2 SORT AGGREGATE Bytes: 8 Cardinality: 1
                                    1 INDEX FULL SCAN (MIN/MAX) INDEX test_table_idx Bytes: 3,384,205,336 Cardinality: 423,025,667
                          • 10. Re: Fragmentation effect
                            Jonathan Lewis
                            Bolev wrote:
                            10g R2
                            Table has about 0.5 billion records at this time
                            There is a process deleting records (5-6 millions per day)

                            At the beginning of process there is a select getting min date (indexed column) to start from.
                            I see this select taking more time - plus about 1 min each day (surprise).
                            This looks as if your query for min(adate) is having to walk through increasing numbers of empty blocks each day. I'm going to guess that you then delete data for a given date range above that date - leaving even more empty blocks at the low end of the index. If this is the case, a "coalesce" might be more appropriate than a shrink. This may take some time on the first run, but it operates as a large number of small transactions so won't have to unwind itself if the process crashes.

                            Since you are inserting 0.5 M rows per day and deleting 5-6M rows per day for a net loss of 4.5 - 5.5 M rows per day, at some stage it will probably make sense to rebuild most of the indexes, and at some stage it will probably make sense to "move" the table (and rebulid the indexes again).

                            Regards
                            Jonathan Lewis
                            • 11. Re: Fragmentation effect
                              614736
                              Thanks

                              Is there any idea how long may coalesce take?

                              Do you think such incrimination is normal?
                              • 12. Re: Fragmentation effect
                                JustinCave
                                The behavior you're seeing is expected if the process is similar to what Jonathan outlined. I wouldn't necessarily call it "normal" simply because it's not normal to have systems with hundreds of millions of rows that don't use partitioning.

                                If you are doing something along the lines of what Jonathan outlined where you are deleting all the data with the oldest dates every day and inserting data with today's date, do you really need to hit the base table to determine the min date? Would it be possible to store that data in a separate table that is maintained by your insert and delete processes?

                                Justin
                                • 13. Re: Fragmentation effect
                                  Hemant K Chitale
                                  INDEX FULL SCAN (MIN/MAX) INDEX test_table_idx
                                  Wouldn't the effort for the Index Full Scan to find MIN alone only depend on the BLevel of the Index ?

                                  (If the query was attempting to find both MIN and MAX, it would be be much greater : http://hemantoracledba.blogspot.com/2009/02/minmax-queries-execution-plans-and-cost_01.html )


                                  Hemant K Chitale
                                  • 14. Re: Fragmentation effect
                                    Hemant K Chitale
                                    INDEX FULL SCAN (MIN/MAX) INDEX test_table_idx
                                    Wouldn't the effort for the Index Full Scan to find MIN alone only depend on the BLevel of the Index ?

                                    (If the query was attempting to find both MIN and MAX, it would be be much greater : http://hemantoracledba.blogspot.com/2009/02/minmax-queries-execution-plans-and-cost_01.html )


                                    Hemant K Chitale
                                    1 2 Previous Next