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

          Edited by: Rakesh jayappa on 18 Sep, 2010 4:52 AM
          • 17. Re: Fragmentation effect
            askraks
            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
              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
                Philippe Florent
                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
                  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
                    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
                      Philippe Florent
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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