Q:Index fragmentation ?? interesting challenge ?
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 = 1Expected 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 = 1Expected 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-lReceived on Wed Feb 29 2012 - 18:39:32 CST