Q:Index fragmentation ?? interesting challenge ?

From: Amihay Gonen <Amihay.Gonen_at_ecitele.com>
Date: Thu, 1 Mar 2012 02:39:32 +0200
Message-ID: <5BB479729855824B9A8B9724F455F70E015D5F0554E4_at_ILPTMAIL02.ecitele.com>



Hi I've two identical systems (as much as possible ), Solaris 10 P9 ,Oracle EE 10.2.0.4 . The most heavy table is a reugarl table (not IOT/Partition) amount of write/delete from this table are the same , but I've found out that on one system there are more writing to indexes (although the writing/updating/deleting to the table is the same). In the bad system I've 60K write per 20K operation in the good system.

I did analyze structure on those indexes (found the better performing index has higher blevel) , but I didn't reach to conclusion .

I attaching of the data I've got so far , I would like to have your input/ideas suggesting to progress from here

I've use the query to information on the index writing

with r as (select obj#,object_name from DBA_HIST_SEG_STAT_OBJ where OWNER='NG' and OBJECT_TYPE='INDEX' and object_name in (select index_name from dba_indexes where owner='NG' and table_name='ACTIVE_ALARM' )) select to_char(trunc(his.BEGIN_INTERVAL_TIME,'hh'),'yyyy/mm/dd hh24') start_time ,'write-'||r.object_name state , (sum(seg.PHYSICAL_WRITES_DELTA)) from DBA_HIST_SEG_STAT seg join
  DBA_HIST_SNAPSHOT his on seg.snap_id=his.snap_id join r on seg.obj#=r.obj#   where his.BEGIN_INTERVAL_TIME > sysdate-3   group by r.object_name ,trunc(his.BEGIN_INTERVAL_TIME,'hh') order by 1,2

got those results :

BAD system : (around 60K write per hour)

[cid:image002.jpg_at_01CCF754.88631490]

Index analysis :
Please enter a index to analyze:>ACTIVE_ALARM_INDX_5 Please enter a owner to analyze:>NG

Working, Please wait.....


                                    Name of the index = ACTIVE_ALARM_INDX_5
                                 Height of the b-tree = 3
                        Blocks allocated to the index = 43008
            Number of leaf rows (values in the index) = 944103
                  Number of leaf blocks in the b-tree = 41935
              Sum of the lengths of all the leaf rows = 33149709
                        Useable space in a leaf block = 7756
                                Number of branch rows = 41934
                Number of branch blocks in the b-tree = 242
Sum of lengths of all the branch blocks in the b-tree = 885143
                      Useable space in a branch block = 8028
             Number of deleted leaf rows in the index = 305091
        Total length of all deleted rows in the index = 10764262
                 Number of distinct keys in the index = 944103
     How many times the most repeated key is repeated = 1
        Total space currently allocated in the b-tree = 327190636
Totl space that is currently being used in the b-tree = 34034852
% of space allocated in the b-tree that is being used = 11
              Average number of rows per distinct key = 1
Expected number of consistent mode block gets per row = 4

Hit enter to continue



Index Name......... ACTIVE_ALARM_INDX_5
Leaf Rows..........                944,103                                Leaf Block Size........       7,756
Deleted Leaf Rows..                305,091                                Leaf Row Size..........          35
Branch Rows........                 41,934                                Leaf Rows Per Block....         221
Distinct Keys......                944,103                                Branch Block Size......       8,028
Max Common Key.....                      1                                Branch Row Size........          21
Avg Common Key.....                      1                                Branch Rows Per Block..         363
Height Of B-Tree...                      3
Reads Per Access...                   4.00

Index Meg................        336.00
Leaf Meg/Pct.............        327.62 /                               97.51
Branch Meg/Pct...........          1.89 /                                 .56
Unused Meg/Pct...........          6.49 /                                1.93
B-Tree Meg/Pct...........        329.51 /                               98.07
B-Tree Used Meg/Pct......         32.46 /                               11.00
B-Tree UnUsed Meg/Pct....        279.58 /                               89.00
B-Tree UnUsable Meg/Pct..         10.27 /                                3.12

SQL> Good system

[cid:image003.png_at_01CCF753.1CDBB9D0]

SQL> _at_c:\a.sql

Please enter a index to analyze:>ACTIVE_ALARM_INDX_5 Please enter a owner to analyze:>NG

Working, Please wait.....


                                    Name of the index = ACTIVE_ALARM_INDX_5
                                 Height of the b-tree = 4
                        Blocks allocated to the index = 66560
            Number of leaf rows (values in the index) = 1044332
                  Number of leaf blocks in the b-tree = 65440
              Sum of the lengths of all the leaf rows = 37874325
                        Useable space in a leaf block = 7756
                                Number of branch rows = 65439
                Number of branch blocks in the b-tree = 566
Sum of lengths of all the branch blocks in the b-tree = 1332526
                      Useable space in a branch block = 8028
             Number of deleted leaf rows in the index = 390679
       Total length of all deleted rows in the index = 14174178
                 Number of distinct keys in the index = 1044332
     How many times the most repeated key is repeated = 1
        Total space currently allocated in the b-tree = 512096488
Totl space that is currently being used in the b-tree = 39206851
% of space allocated in the b-tree that is being used = 8
              Average number of rows per distinct key = 1
Expected number of consistent mode block gets per row = 5

Hit enter to continue



Index Name......... ACTIVE_ALARM_INDX_5
Leaf Rows..........              1,044,332                                Leaf Block Size........       7,756
Deleted Leaf Rows..                390,679                                Leaf Row Size..........          36
Branch Rows........                 65,439                                Leaf Rows Per Block....         214
Distinct Keys......              1,044,332                                Branch Block Size......       8,028
Max Common Key.....                      1                                Branch Row Size........          20
Avg Common Key.....                      1                                Branch Rows Per Block..         376
Height Of B-Tree...                      4
Reads Per Access...                   5.00

Index Meg................        520.00
Leaf Meg/Pct.............        511.25 /                               98.32
Branch Meg/Pct...........          4.42 /                                 .85
Unused Meg/Pct...........          4.33 /                                 .83
B-Tree Meg/Pct...........        515.67 /                               99.17
B-Tree Used Meg/Pct......         37.39 /                                8.00
B-Tree UnUsed Meg/Pct....        450.98 /                               92.00
B-Tree UnUsable Meg/Pct..         13.52 /                                2.62


This e-mail message is intended for the recipient only and contains information which is CONFIDENTIAL and which may be proprietary to ECI Telecom. If you have received this transmission in error, please inform us by e-mail, phone or fax, and then delete the original and all copies thereof.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 29 2012 - 18:39:32 CST

Original text of this message