This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Sep 25, 2010 12:14 AM by Jonathan Lewis Go to original post RSS
  • 15. 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
  • 16. Re: Fragmentation effect
    askraks Pro
    Currently Being Moderated
    a

    Edited by: Rakesh jayappa on 18 Sep, 2010 4:52 AM
  • 17. Re: Fragmentation effect
    askraks Pro
    Currently Being Moderated
    Hi,

    please execute dba_tab_modification, check how many DML happening on that table

    select insert,delete,update from dba_tab_modification where table_name='&enter_table_name';

    If the added of three column is 10% of the whole rows in the table then please collect the stats and coalesce the index, it will help

    Oracle suggest to collect the stats on the table if 10% of data got modified.

    Hope this helps you

    Kind Regards,
    Rakesh Jayappa
  • 18. Re: Fragmentation effect
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    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 ?
    Hemant,

    For min() oracle will go to the first (logical) leaf blokck in the index - but if the index hasn't had a chance to reuse empty blocks, there may be a large number of empty leaf blocks between the first block and the first block which actually has data.

    Regards
    Jonathan Lewis
  • 19. Re: Fragmentation effect
    792858 Pro
    Currently Being Moderated
    Hi Jonathan,
    For min() oracle will go to the first (logical) leaf blokck in the index - but if the index hasn't had a chance to reuse empty blocks, there may be a large number of empty leaf blocks between the first block and the first block which actually has data.
    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)..
    I suppose the first part explains the need of a coalesce. But why do you recommend a rebuild of the indexes in that case ? It's rare you recommend that. Can you explain further why "daily deletions - daily insertions = 4.5M to 5M" makes you think it will probably be necessary at some stage ?
    Best regards
    Phil
  • 20. Re: Fragmentation effect
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Philippe Florent wrote:
    Hi Jonathan,
    For min() oracle will go to the first (logical) leaf blokck in the index - but if the index hasn't had a chance to reuse empty blocks, there may be a large number of empty leaf blocks between the first block and the first block which actually has data.
    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)..
    I suppose the first part explains the need of a coalesce. But why do you recommend a rebuild of the indexes in that case ? It's rare you recommend that. Can you explain further why "daily deletions - daily insertions = 4.5M to 5M" makes you think it will probably be necessary at some stage ?
    Best regards
    Phil
    Phil,

    First, note that I actually said (my emphasis): +it will probably make sense to rebuild most of the indexes+

    If you check back on the figures, the OP has 500M rows in the table, and the net effect of hist work is to delete about 5M rows (one percent) per day, and he has been following this pattern for several days.

    Presumably he's planning to stop in less than 100 days, but he does seem to be doing a large-scale delete (albeit spread over time) and large-scale deletes always raise the question of the best way to handle related indexes (as Richard Foote, and others) have often pointed out.

    As we've seen, the pattern produced by one of his indexes as it empties out has introduced a slowdown to his processing. Different patterns in other indexes may mean that he doesn't see a slowdown due to other indexes, but it is possible that in some indexes the patterns will lead to a speed-up if he rebuilds those indexes a couple of times before he gets down to his final "steady state" - especially if he optimises his deletes (see http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/ - especially comments 12, 13, 15)

    Regards
    Jonathan Lewis
  • 21. Re: Fragmentation effect
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    but if the index hasn't had a chance to reuse empty blocks .....
    Yes, I agree. It is likely that the index has been the victim of an "insidious" pattern of DELETEs !

    Hemant K Chitale
  • 22. Re: Fragmentation effect
    792858 Pro
    Currently Being Moderated
    Hi Jonathan,
    I see. After all it's a special case that needs a special approach (and anyway the best thing is always to test !)
    In some cases I delete only once a year, a short downtime is not forbidden and I use this approach : http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
    In other cases the tables are partitioned and it really eases the maintenances tasks.
    I rarely try to delete directly many records in a big table but if I have the case I will think about that.
    Thanks for the explanation !
    Best regards
    Phil
  • 23. Re: Fragmentation effect
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Bolev wrote:

    Is there any idea how long may coalesce take?
    You said that the process is taking an extra minute each day - so we could guess that the worst case for coalescing the latest part of the index that you've emptied is one minute. But the coalesce then has to scan the rest of the index - possibly doing nothing to modify later leaf blocks. You could check the possible time for the task by forcing a count with full scan (not fast full scan) of the index and adding one minute. This may take longer than the 11 minutes you are seeing at present, of course, but it's a task that you might be able to take off the critical path.

    Regards
    Jonathan Lewis
  • 24. Re: Fragmentation effect
    Tony Sleight Newbie
    Currently Being Moderated
    I agree with the previous posts regarding the use of indexes, I just have one small input to make.

    Are you collecting new statistics after each insert and delete exercise? Particularly as you are searching on min(date), it may not be helpful to the execution plan if the statistics for the indexed column are out of sync with the real distribution of data.
  • 25. Re: Fragmentation effect
    565683 Newbie
    Currently Being Moderated
    Hi Tony,

    But collecting the stats again would it really mean a lot to this query ? I believe the plan is still not going to change.

    Thanks,
    Balaji
  • 26. Re: Fragmentation effect
    user503699 Expert
    Currently Being Moderated
    TonySUK wrote:
    I agree with the previous posts regarding the use of indexes, I just have one small input to make.

    Are you collecting new statistics after each insert and delete exercise? Particularly as you are searching on min(date), it may not be helpful to the execution plan if the statistics for the indexed column are out of sync with the real distribution of data.
    While the suggestion about collecting statistics is valid, it is not the cause of the problem here as OP has mentioned that the SELECT is able to use INDEX FULL SCAN.
    Following is a demo of what Jonathan has described so far. While the demo does not include execution time details, the "consistent gets" statistics (for the query) should explain the slow-down in query execution.
    Scenario 1: When DELETE and INSERT is done using same session
    SQL> select * from v$version ;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> create table t1 nologging as select trunc(sysdate)+l1 t1dt, lpad('x', 100, 'x') t1desc from (select level l1 from dual connect by level <= 100), (select level l2 from dual connect by level <= 10000) ;
    
    Table created.
    
    SQL> exec dbms_stats.gather_table_stats(user, 'T1') ;
    
    PL/SQL procedure successfully completed.
    
    SQL> create index t1_idx on t1(t1dt, t1desc) nologging ;
    
    Index created.
    
    SQL> analyze index t1_idx validate structure ;
    
    Index analyzed.
    
    SQL> select name, height, blocks, del_lf_rows, blks_gets_per_access from index_stats ;
    
    NAME                               HEIGHT     BLOCKS DEL_LF_ROWS BLKS_GETS_PER_ACCESS
    ------------------------------ ---------- ---------- ----------- --------------------
    T1_IDX                                  4      24295           0               5004.5
    
    SQL> set autotrace on statistics
    SQL> select min(t1dt) from t1 ;
    
    MIN(T1DT)
    ---------
    21-SEP-10
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              4  consistent gets
              3  physical reads
              0  redo size
            226  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> /
    
    MIN(T1DT)
    ---------
    21-SEP-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            226  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace off
    SQL> delete from t1 where t1dt between to_date('21-SEP-2010', 'DD-MON-YYYY') and to_date('21-OCT-2010', 'DD-MON-YYYY') ;
    
    310000 rows deleted.
    
    SQL> commit ;
    
    Commit complete.
    
    SQL> analyze index t1_idx validate structure ;
    
    Index analyzed.
    
    SQL> select name, height, blocks, del_lf_rows, blks_gets_per_access from index_stats ;
    
    NAME                               HEIGHT     BLOCKS DEL_LF_ROWS BLKS_GETS_PER_ACCESS
    ------------------------------ ---------- ---------- ----------- --------------------
    T1_IDX                                  4      24295       22361           4951.45139
    
    SQL> insert into t1 select to_date('29-DEC-2010', 'DD-MON-YYYY')+l, lpad('y', 100, 'y') from (select level l from dual connect by level <= 100), (select level l2 from dual connect by level <= 1000) ;
    
    100000 rows created.
    
    SQL> commit ;
    
    Commit complete.
    
    SQL> analyze index t1_idx validate structure ;
    
    Index analyzed.
    
    SQL> select name, height, blocks, del_lf_rows, blks_gets_per_access from index_stats ;
    
    NAME                               HEIGHT     BLOCKS DEL_LF_ROWS BLKS_GETS_PER_ACCESS
    ------------------------------ ---------- ---------- ----------- --------------------
    T1_IDX                                  4      24295           0           2328.02941
    
    SQL> set autotrace on statistics
    SQL> select min(t1dt) from t1 ;
    
    MIN(T1DT)
    ---------
    22-OCT-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           3218  consistent gets
           3212  physical reads
              0  redo size
            227  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> /
    
    MIN(T1DT)
    ---------
    22-OCT-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           3218  consistent gets
              0  physical reads
              0  redo size
            227  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace off
    Scenario 2: When DELETE and INSERT is done using different sessions (probably a scenario more similar to OP's
    Session 1
    SQL> create table t1 nologging as select trunc(sysdate)+l1 t1dt, lpad('x', 100, 'x') t1desc from (select level l1 from dual connect by level <= 100), (select level l2 from dual connect by level <= 100
    00) ;
    
    Table created.
    
    SQL> exec dbms_stats.gather_table_stats(user, 'T1') ;
    
    PL/SQL procedure successfully completed.
    
    SQL> create index t1_idx on t1(t1dt, t1desc) nologging ;
    
    Index created.
    
    SQL> analyze index t1_idx validate structure ;
    
    Index analyzed.
    
    SQL> select name, height, blocks, del_lf_rows, blks_gets_per_access from index_stats ;
    
    NAME                               HEIGHT     BLOCKS DEL_LF_ROWS BLKS_GETS_PER_ACCESS
    ------------------------------ ---------- ---------- ----------- --------------------
    T1_IDX                                  4      24295           0               5004.5
    
    SQL> set autotrace on statistics
    SQL> select min(t1dt) from t1 ;
    
    MIN(T1DT)
    ---------
    21-SEP-10
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            228  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> /
    
    MIN(T1DT)
    ---------
    21-SEP-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            228  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace off
    SQL> delete from t1 where t1dt between to_date('21-SEP-2010', 'DD-MON-YYYY') and to_date('21-OCT-2010', 'DD-MON-YYYY') ;
    
    310000 rows deleted.
    
    SQL> commit ;
    
    Commit complete.
    Session 2:
    SQL> insert into t1 select to_date('29-DEC-2010', 'DD-MON-YYYY')+l, lpad('y', 100, 'y') from (select level l from dual connect by level <= 100), (select level l2 from dual connect by level <= 1000) ;
    
    100000 rows created.
    
    SQL> commit ;
    
    Commit complete.
    
    SQL> analyze index t1_idx validate structure ;
    
    Index analyzed.
    
    SQL> select name, height, blocks, del_lf_rows, blks_gets_per_access from index_stats ;
    
    NAME                               HEIGHT     BLOCKS DEL_LF_ROWS BLKS_GETS_PER_ACCESS
    ------------------------------ ---------- ---------- ----------- --------------------
    T1_IDX                                  4      24295        4484           2341.21765
    
    SQL> set autotrace on statistics
    SQL> select min(t1dt) from t1 ;
    
    MIN(T1DT)
    ---------
    22-OCT-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           5258  consistent gets
           3429  physical reads
              0  redo size
            225  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> /
    
    MIN(T1DT)
    ---------
    22-OCT-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           5258  consistent gets
              0  physical reads
              0  redo size
            225  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace off
    SQL> alter index t1_idx coalesce ;
    
    Index altered.
    
    SQL> set autotrace on statistics
    SQL> select min(t1dt) from t1 ;
    
    MIN(T1DT)
    ---------
    22-OCT-10
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            225  bytes sent via SQL*Net to client
            277  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Hope this helps.
  • 27. Re: Fragmentation effect
    614736 Newbie
    Currently Being Moderated
    Thanks. everybody for good discussion

    I will try to update it as soon as I get delete/insert balance on this table.
  • 28. Re: Fragmentation effect
    614736 Newbie
    Currently Being Moderated
    Hi Jonathan -
    Coalesce seems to be a best decision in this case. it takes several minutes.
    My first test was to coalesce on this index and then rebuild it which reduced full index scan time from 14 min to 0.05 sec (numbers are approximate)
    Then I increased number of cleaned rec to 10 ml per day.
    Today I executed just coalesce and reduced full index scan time from current 0.5 to 0.05 sec.

    It is probably a good idea to include coalesce on some indexes in automatic cleanup script,


    Thanks.
  • 29. Re: Fragmentation effect
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Bolev wrote:
    Hi Jonathan -
    Coalesce seems to be a best decision in this case. it takes several minutes.
    Thanks for the feedback.

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

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