Re: Q:Index fragmentation ?? interesting challenge ?
Date: Thu, 1 Mar 2012 09:27:18 -0000
Message-ID: <1CBE179C815E4ADFAAD6922CE8CBDA79_at_Primary>
There's obviously a lot of empty space in the index - but it's not size that matters, it's what you do with it.
If you check the figures and play the averages game, the leaf blocks that are in use are virtually full, so perhaps this is a sequence/time-based index with a completely empty tail and a few (5,000) blocks which are full, and if the only blocks you touch are from the full section then the index isn't a performance problem.
Even if you have more writes on one index than on the other it may be a side effect of the rate of change, rather than the space allocated. Perhaps some other job is reading more blocks on the bad system, causing more calls for free buffers, and forcing more writes to take place - and if these indexes are constantly being updated the same small number of blocks could then be the target of forced writes due to other read activity.
At first sight, I might guess that the index could be rebuilt once, and then coalesced every 24 hours - but I'd want to check the activity first. I'd also want to check if the function of this index could be made more effective by using a function-based index with a change in the driving code - tables with words like "alarm" in their name conjure all sorts of ideas for efficient design strategies.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Amihay Gonen" <Amihay.Gonen_at_ecitele.com> To: <oracle-l_at_freelists.org> Sent: Thursday, March 01, 2012 12:39 AM Subject: Q:Index fragmentation ?? interesting challenge ?
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 /3.12
.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 /
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 /2.62
.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 /
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 ----- No virus found in this message. Checked by AVG - www.avg.com Version: 2012.0.1913 / Virus Database: 2114/4842 - Release Date: 02/29/12 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 01 2012 - 03:27:18 CST