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 = 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-lReceived on Wed Feb 29 2012 - 18:39:32 CST