This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Sep 25, 2010 12:14 AM by Jonathan Lewis RSS

Fragmentation effect

614736 Newbie
Currently Being Moderated
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
    justin cave-1981725 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Sorry had problem to post this message
  • 7. Re: Fragmentation effect
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks

    Is there any idea how long may coalesce take?

    Do you think such incrimination is normal?
  • 12. Re: Fragmentation effect
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

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