Re: Q:Index fragmentation ?? interesting challenge ?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 1 Mar 2012 09:27:18 -0000
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@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@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@01CCF753.1CDBB9D0]
SQL> @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.
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
