Excellent
pawan, March 12, 2002 - 7:16 pm UTC
I think Tom has gone over this atleast 10 times and I totally agree with him. I used to awake at 2 AM reorganizing Indexes and never kept any metrices so am not sure whether it helped. My question though is if there exists with Tom a script/method to gather statistics on a regular basis ( before and after).
Thanks
March 13, 2002 - 6:57 am UTC
things like statspack (IO's and waits), application logs (showing transactions processed, response times), tkprof reports are what you need.
LMT
Dusan Valasek, March 13, 2002 - 1:36 am UTC
"How
to stop defragmenting and start living: the definitive world of fragmentation" - there is the only answer: Start living in Locally Managed Tablespaces.
Index fragmented in LMT
KJ, March 05, 2004 - 6:45 am UTC
Hi Tom
This is a DW database - 9.2.0.4
analyze index NON_TRADE_ACCOUNT_BALANCE_PK validate structure;
select trunc(100*(del_lf_rows/lf_rows)) pct_deleted, pct_used from index_stats - gave a 47%
It is a LMT - Automatic Allocation and Auto Segment Space management. Should we check for fragmentation even with LMT's? I see few other indexes with the same issue too.
Thanks
March 05, 2004 - 8:37 am UTC
that is not fragmented.
that might be very very very very normal for that index.
rebuilding it might just result in increased redo
or
rebuilding it might result in reduced LIO's during the day
All this means is you apply inserts, updates and deletes against this index.
Try alter index iname coalesce; that might be all you need (an online operation).
Index fragmented in LMT
KJ, March 05, 2004 - 7:02 am UTC
Hi Tom
This is a DW database - 9.2.0.4
analyze index NON_TRADE_ACCOUNT_BALANCE_PK validate structure;
select trunc(100*(del_lf_rows/lf_rows)) pct_deleted, pct_used from index_stats - gave a 47%
It is a LMT - Automatic Allocation and Auto Segment Space management. Should we check for fragmentation even with LMT's? I see few other indexes with the same issue too.
Thanks
height is 4
READER, May 17, 2004 - 8:53 pm UTC
validate index xxx
select * from index_stats reveals that the height is 4 .
What does this mean ?
root,branch,leaf ...
Is it bad ?
Thanks
May 18, 2004 - 11:14 am UTC
root -> branch -> branch -> leaf
means it'll take 4lio's to get from a key value to a leaf node.
is it bad? insufficient data to comment. size of key? number of distinct vlaues of key? block size? ....
OEM Reorganization Features
reader, May 18, 2004 - 11:38 am UTC
With 9.2 OEM allows for online reorganization of tables
and indexes that include function-based and reverse-key
indexes. You can validate the structure of an object while
users are making changes to a table.
It will generate an Impact and Job Summary Report.
Haven't made use of this feature myself so can't offer any concrete evidence of it's usefulness and performance.
May 18, 2004 - 6:16 pm UTC
well, the database has allowed for online stuff for a while, doesn't need a gui or anything.
but -- just because you see the number "4" doesn't mean *anything*
Height=4
Reader, May 18, 2004 - 7:52 pm UTC
select height,blocks,lf_rows,lf_blks,br_rows,distinct_keys from index_stats
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- -------------
4 158981 42478952 154898 154897 5666
Block size = 8k
Thanks
May 19, 2004 - 7:46 am UTC
hows about showing the entire index_stats view -- but with 42million entries, it doesn't seem unreasonable yet.
READER, May 19, 2004 - 9:05 pm UTC
PRE_ROWS_LEN HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS
0 4 158981 42478952 154898 1061973800 7996 154897 847 4117920 8028 0 0 5666 90747 1245364124 1066091720 86 7497.16766678433 3753.08383339216 0
Thanks
May 20, 2004 - 10:18 am UTC
you have no real "whitespace" (0 deleted leaf rows). You are 86% utilized (pretty packed).
It is what it is, the only real way to reduce the height would be to partition it into N smaller indexes.
but -- since each key retrieved 7k plus rows -- that really doesn't matter - so what if it takes 4lios to get to the leaf, if it is going to have to range scan for 7k rows after that. even reducing the height of the index by 1 or 2 would remove only 1 or 2 lios from the range scan (which on average is going to have 3753 LIOs anyway)
this looks fine.
Range Scan --LIO's
Reader, May 21, 2004 - 8:24 am UTC
Tom,
When it is doing a Range scan to find a particular Row Why will it do 3753 LIO's ?
Is it true that while doing a Range Scan Oracle reads only one block at a time.
Why can't it do multi-block reads at this point .
Thanks for clarifying .
May 21, 2004 - 10:57 am UTC
3753.08383339216
because the estimated block gets per access said it would.
indexes are stored in blocks, blocks all over the place. Leaf blocks are not stored "next to eachother" (cannot be). to get from leaf block 1 to leaf block 2 -- you have to do an IO of another block somewhere else.
Also, there is the table access by index rowid to be considered as well (each entry on the index could be pointing to a different block in the table itself).
indexes on LMT
Sudip Sarkar, August 15, 2007 - 11:37 am UTC
Hi Tom,
My DBA says "Now that all data is on a SAN, the need for separation of data and index is no longer required from a hardware perspective.
And, with locally managed tablespaces with automatic extent sizing and automatic segment space management, fragmentation is supposed to be a thing of the past. "
Is this true that we dont need to create a separate tablespaces for Indexes anymore?
Thanks and Regards
August 20, 2007 - 7:18 pm UTC
SAN - spam - nam - ugh
SAN has nothing to do with it
NAS has nothing to do with it
It was never true, never, that table data and index data needed to be separate. It was always a myth.
It had nothing to do with fragmentation either.
It has nothing to do with extents and how they are allocated.
It never did.
You always and forever should have used tablespaces to make YOUR LIFE EASIER, BETTER, MORE HAPPY.
Do whatever is right for you.
Use tablespaces to administer, book keep, organize. that is all.
Fragmented Index
A reader, April 02, 2009 - 1:41 am UTC
Hi Tom
We have a large table of about 100 millions records. It was determined that the ninety percent of the records of the table were not useful. The tables has a unique sid as its primary key/unique key. Now when I delete the table and its depended table the keys would like the below. There are large gaps in the unique key.
Before
Unique Key
1
2
3
4
.
.
After
Unique Key
1
7
19
35
.
.
Before Delete Statistics
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX
ACC_CUST_START_END NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 506265 460494 1 58 26812095 VALID 97659470 19531894 3/28/2009 2:41 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX1 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 268435456 1048576 1 2147483645 0 0 NO 3 565090 16077089 1 3 60398500 VALID 97321855 19464371 3/28/2009 2:44 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX2 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 268435456 1048576 1 2147483645 0 0 NO 3 538750 7240761 1 12 87569530 VALID 97224330 19444866 3/28/2009 2:46 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX3 NORMAL COS ACC TABLE UNIQUE DISABLED USERS_IDX 2 255 67108864 1048576 1 2147483645 0 0 NO 2 199720 97169825 1 1 1586450 VALID 97169825 19433965 3/28/2009 2:40 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX4 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 10 NO 3 443750 13348 33 112 1496360 VALID 97432125 19486425 3/28/2009 2:48 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX5 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 222000 6208 35 172 1072645 VALID 77145765 15429153 3/28/2009 2:49 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX6 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 67108864 1048576 1 2147483645 0 0 NO 3 268480 11345 23 124 1415675 VALID 97274285 19454857 3/28/2009 2:49 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX1 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 67108864 2097152 1 2147483645 0 0 NO 2 142045 57942560 1 1 4678395 VALID 58706975 11741395 3/28/2009 4:47 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX2 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 33554432 1048576 1 2147483645 0 10 NO 2 50370 16819865 1 1 557860 VALID 16819905 3363981 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX3 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 10 NO 0 1 102 1 1 72 VALID 103 103 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX4 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 16384 1048576 1 2147483645 0 10 NO 0 1 33 1 1 32 VALID 33 33 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX5 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 40 NO 2 585 144929 1 1 6809 VALID 144948 144948 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX6 NORMAL COS ITEMS TABLE UNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 279335 76396450 1 1 1805805 VALID 76396450 15279290 3/28/2009 4:43 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX7 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 236505 171166 1 4 845735 VALID 76257745 15251549 3/28/2009 4:48 1 1 NO N N N DEFAULT NO YES NO
After Delete Statistics
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX
ACC_CUST_START_END NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 3 74980 254243 1 37 9579550 VALID 16273950 3254790 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX1 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 74750 1689094 1 7 12703050 VALID 16237870 3247574 3/25/2009 16:36 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX2 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 3 77200 705438 1 21 14876310 VALID 16293015 3258603 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX3 NORMAL COS ACC TABLE UNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 37205 16481695 1 1 1114500 VALID 16481695 3296339 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX4 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 57040 9367 6 41 385830 VALID 16370660 3274132 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX5 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 23925 3498 6 61 214405 VALID 10723425 2144685 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX6 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 37040 7774 4 62 485965 VALID 16618285 3323657 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX1 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 67108864 2097152 1 2147483645 0 0 NO 2 131430 14005580 1 1 2123965 VALID 14291660 2858332 3/25/2009 16:41 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX2 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 33554432 1048576 1 2147483645 0 10 NO 2 50345 16853370 1 1 559325 VALID 16853370 3370674 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX3 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 10 NO 0 1 102 1 1 72 VALID 103 103 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX4 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 16384 1048576 1 2147483645 0 10 NO 0 1 33 1 1 32 VALID 33 33 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX5 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 40 NO 2 585 144929 1 1 6809 VALID 144948 144948 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX6 NORMAL COS ITEMS TABLE UNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 254560 31011190 1 1 1091925 VALID 31011190 6202238 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX7 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 207535 165018 1 4 700995 VALID 31252925 6250585 3/25/2009 16:42 1 1 NO N N N DEFAULT NO YES NO
I am seeing a performance degradation after the delete.
Thanks a lot for the valuable help to the community.
How do I improve the performance?
April 02, 2009 - 9:45 am UTC
you do understand that no one could make any sort of comment regarding this right?
so what if there are gaps. big deal.
I see a ton of information (wholly unreadable, but lots of it)
And then a "it runs slow"
with no information about what "it" is, what "it" does, how fast "it" used to be, how slow "it" is now, what you have done yourself to research "it" and so on...
A reader, April 02, 2009 - 2:30 pm UTC
Sorry about the above email. I didnt know what columns were relevant to answer the question.
I have started rerunning the processes to gather the trace files to know where the access paths have changed.
Index rebuild,fragmentation
Bhaskar, April 30, 2012 - 7:08 am UTC
Hi Tom,
I have read your book as well as all your posts regarding index rebuild.
From all of them now i'm confirmed the index rebuild is not a permanent solutio n of a slow running query or well mannerd index.
Now i have below questions
1. What actually happens when we rebuild an index?Does we re-create an index from the old one?
2. Then new one would be the same as old one only extra time we spent and some unnecessary redo,undo got generated.
3. Also in OS we usually have fragmentation so does the same thing happen to index also? I mean if we do massive deletion every day on differnt data condition is there any chance massive de fragmentation will happen?
I think i'm stating something wrong in point 3 but i have no clear idea how massive deletion in every day will cause fragmentation in index.
4. As we know index is a balanced tree will index rebuild can cause index depth reduction ? I know in index copression sometimes it may do.
can please help me in clearing all these concepts.
Thanks in advance.
April 30, 2012 - 8:42 am UTC
1) in general - an index rebuild will read the old index IF POSSIBLE (not always possible) and write a new, compact structure from that.
if the index was set unusable and more data was loaded - that won't be possible and the index rebuild will read the existing table - sort the data and build a new index from that.
2) well, it would not be unnecessary - it would be necessary redo. But you would also have the extra IO that took place, you would have the extra CPU that was used, you would have the increased query response times during the rebuild due to this, you would have (pre-11g) a lock taken before and right at the end of the rebuild contending with your users.
3) huh? I don't know what you mean. If you are talking about 'disk fragmentation' that you would run a disk defragger on - it has no bearing or relevance to storage management within an index.
If you find yourself doing massive deletions every day - please try to think of a way NOT to do that. Delete is the single most expensive DML you can do (well, short of an update that moves a row - like a partition key update - that is a delete+insert)
Look into partitioning for example and use truncate or drop
4) a rebuild could cause a temporary reduction in the height of an index, yes.
index rebuild,fragmentation
Bhaskar, April 30, 2012 - 7:12 am UTC
Sorry in point 3 it should be fragmentation.
index rebuild,fragmentation
Bhaskar, May 02, 2012 - 12:51 am UTC
Hi Tom,
Thanks for the information.
"1) in general - an index rebuild will read the old index IF POSSIBLE (not always possible) and write a new, compact structure from that. "
As mentioned below for point 1 only if index is in unusable state then index rebuild will read the data from table and in in all othere cases ir will read teh old index for rebuild.
Also as you mentioned it will compact the structure - does it mean it will remove holes craeted in the index?
Also can you please explain how it will compact the structure?
"3) huh? I don't know what you mean.."
Actually i was thinking if we delete some entries from an index the space owned by those entires will no longer be used so there will be hole.
I know if an index is created on a sequenced column and we delete some sequences the space owned by those deleted sequences will create a hole because
we will not be able to regain those spaces.Is it correct?
So will rebuild be in the perfect choice for this above scenarion?
Also say we have an index in name column which contains data like "ram","shyam","nick".Now if we delete "shyam" the space owned by "shyam" entry how will
be used again if we put a new value "john" after deleting "shyam".
Thanks in advance.
May 02, 2012 - 1:28 pm UTC
1) if the index is valid and usable - a rebuild will read the existing index to create a new one.
It writes out a perfectly compact - filled to pctfree - set of blocks. There will be not extraneous space.
3) you just described what I call a "sweeper" index.
search this site for "sweeper"
a coalesce might be a better option than a rebuild.
For data that is monotonically increasing/decreasing (sequences, dates, timestamps for example) - where you delete a lot but NOT ALL of the old data - you may end up with an index where the left hand side is very sparsely populated but the right hand side is very dense. these indexes may need to be coalesced or rebuilt from time to time in order to correct this.
For data that arrives randomly - like NAME - you typically do not need to perform this operation since the entire breadth of the index is constantly being used.
index rebuild,fragmentation
Bhaskar, May 02, 2012 - 1:26 am UTC
In addition to above for point 3 i have read in a documentation that deleted space will not be automatically reclaimed if an exact match key is inserted.
In my scenarion if we delete "shyam" and do not put values "shyam" and put new value "john" then index on ename column will have a blank space created
by "shyam".
So if we rebuild the index then this deleted space will be reclaimed. Is it right Tom?
Thanks in advance.
May 02, 2012 - 1:29 pm UTC
that is not true at all.
we can reuse "shyam" to store "john"
Bhaskar, May 08, 2012 - 6:56 am UTC
Thnaks Tom.
Index deleted space reuse
Bhaskar, May 09, 2012 - 6:36 am UTC
Hi Tom,
Thanks for all the suggestions.
Still i have one doubt perhaps it's my wrong concept but can you please help me in understanding the below issue.
"we can reuse "shyam" to store "john" "
So in this case in if we delete row with value "John" data block which was storing the value "John" will be empty.
The word "Shyam" comes after "John" by binary value.
Then If we don't insert "John" but "Shyam" how that particular block which was previously containing value "John" will now have the value "Shyam"
Thanks in advance.
May 10, 2012 - 3:16 am UTC
let's say the index leaf block was:
Isabel, John, Larry, Paul, Sally, Tom
And that block was 'full'
You delete John, there is now more room on it. So, you go to insert Shyam. Shyam has to go in between Sally and Tom.
There is room on the leaf block for that now, we'll put Shyam right in between the two.
Bhaskar, May 10, 2012 - 4:28 am UTC
Hi Tom,
Thanks.
Then you are shyam will be put in the deleted space of john?
May 10, 2012 - 8:54 am UTC
not necessarily in the same exact place - we might move the bytes around on the block. But that space *can* and *will* be reused.
Bhaskar, May 11, 2012 - 12:55 am UTC
Thanks Tom.
Doubt
A reader, August 31, 2012 - 12:10 pm UTC
so without reorg you are saying that oracle will reuse the space.
in that case why do we need to deal high water mark of a block
Please advise
September 10, 2012 - 6:46 pm UTC
I never said that, please point to where I said that.
the space *will be reused*.
you said it
A reader, December 15, 2012 - 8:20 am UTC
Here you said it..
Followup May 10, 2012 - 8am Central time zone:
not necessarily in the same exact place - we might move the bytes around on the block. But that space *can* and *will* be reused.
December 18, 2012 - 7:44 am UTC
sorry, I misread you, i thought you meant the space would not be reused - that should have been obvious from me emphatically stating that the space would be reused.
You worry about high water marks sometimes if you are
a) doing full scans, we always scan to the high water mark.
b) just finished a purge of data that dramatically reduced the space needed by this segment and you could reuse that space elsewhere
c) do a ton of indexed reads into this table with lots of free space - you'll be able to cache the table blocks better in the cache if they were more 'dense'
index stats
A Reader, January 02, 2013 - 2:33 am UTC
Tom,
Table t11 and indexes on table t11 are detailed below.
SQL> desc t11;
Name Null? Type
------------------------------------------------------------------- -------- ---------------------------------------------
PARAMETERINSTANCEID NUMBER
PARAMINSTANCE2PARAMSETVERSION NUMBER
PARAMINSTANCE2PARAMDEFINITION NUMBER
PARAMINSTANCE2PARENTPARAM NUMBER
SEQUENCE NUMBER
X_CTCV_CONV_ID VARCHAR2(50 CHAR)
X_CONV_RUN_NO NUMBER
SQL> select dic.index_name, dic.table_name, dic.column_name, dic.column_position, di.index_type from dba_ind_columns dic, dba_indexes di
2 where di.table_name ='t1' and DIC.index_NAME = di.index_name;
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION INDEX_TYPE
--------------- ------------------------------ ------------------------------ --------------- ----------
PI_UK t11 PARAMINSTANCE2PARAMSETVERSION 1 NORMAL
PI_UK t11 PARAMINSTANCE2PARAMDEFINITION 2 NORMAL
PI_PK t11 PARAMETERINSTANCEID 1 NORMAL
PI_PAD_FK_I t11 PARAMINSTANCE2PARAMDEFINITION 1 NORMAL
PI_PI_FK_I t11 PARAMINSTANCE2PARENTPARAM 1 NORMAL
SQL> select sum(bytes/1024/1024/1024) size_gb from dba_segments where segment_name = 't11';
SIZE_GB
----------
371.40625
SQL> select sum(bytes/1024/1024/1024) size_gb from dba_segments where segment_name = 'PI_UK';
SIZE_GB
----------
411.938477
SQL> select sum(bytes/1024/1024/1024) size_gb from dba_segments where segment_name = 'PI_PK';
SIZE_GB
----------
243.816406
SQL> select sum(bytes/1024/1024/1024) size_gb from dba_segments where segment_name = 'PI_PAD_FK_I';
SIZE_GB
----------
470.986328
SQL> select sum(bytes/1024/1024/1024) size_gb from dba_segments where segment_name = 'PI_PI_FK_I';
SIZE_GB
----------
.010742188
we have 02 indexes here more than the table size...
table t11 is range partitioned.
since all table data is sitting in single partition so is the index partition.
INDEX_STATS for PI_UK is as follows.
SQL> exec print_table('select * from bde_index_stats where partition_name=''PI_P01''');
HEIGHT : 5
BLOCKS : 53992320
NAME : PI_UK
PARTITION_NAME : PI_P01
LF_ROWS : 1120039324
LF_BLKS : 53804613
LF_ROWS_LEN : 213287053602
LF_BLK_LEN : 7396
BR_ROWS : 53804612
BR_BLKS : 187204
BR_ROWS_LEN : 825251980
BR_BLK_LEN : 8028
DEL_LF_ROWS : 10727072
DEL_LF_ROWS_LEN : 235895058
DISTINCT_KEYS : 1120039324
MOST_REPEATED_KEY : 1
BTREE_SPACE : 399441791460
USED_SPACE : 214112305582
PCT_USED : 54
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 6
PRE_ROWS : 0
PRE_ROWS_LEN : 0
OPT_CMPR_COUNT : 0
OPT_CMPR_PCTSAVE : 0
Question/comments
a) By re-partitioning( at the moment table is range partitioned but all the data flowing into 1st partition.. range is so high ) the table would it help in reducing the index height it is 5 at the moment. Would reducing height by 1 or 2 would be really helpful?
b) PCT_USED value of index_stats is 54, - this signifies the index is not compact.. also there are over 10million DEL_LF_ROWS - so would you recommed coalsce or index rebuild in this case?
c) we have BTREE_SPACE around 400gb and USED_SPACE around 214gb .. again it points to index not compact !
regards
b)
January 04, 2013 - 2:00 pm UTC
a) depends - do you do range scans or mostly just single row fetches from this index.
a single row fetch would do ~32% less IO if you reduced the index from 5 to 3. It would do ~16% less IO if you reduced the index from 5 to 4.
if you fetch lots of rows - say 20, then reducing from 5 to 3 would reduce IOs by about ~8%, from 5 to 4 about ~4%. If you fetch 200 rows, then about 0.9% and 0.4%.
it depends on where you spend your IO's...
b) probably, this index is probably what I call a sweeper index (search this site for that phrase to read about that)
c) see (b)
..index stats
A Reader, January 04, 2013 - 4:16 pm UTC
Thanks Tom,
I will look for sweeper indexes which you refrred to .
next thing which is puzzling me is.
a) Index LF_rows count is 1/8 of the count(*) in the table.
index is on 02 column c1,c2 -- c1 being the partition key.
select count(*) from t -- 8 billion
select sum(lf_rows) from index_stats where index_name = 'that index' -- 1 billion + 11
select count(*) from t where c1 is NULL -- 11
select count(*) from t where c1 is null and c2 is null -- 0
why index rows and table rows are matching ( i have accounted for NULLs as above )
b) if table T has index t_idx on ( c1,c2 ) column of t
so size of index t_idx ( when no nulls on c1 and c2) > size of index t_idx ( when there are 80% nulls on c1 and c2) . this would be True? -- as nulls entried wount be keyed in index.
January 14, 2013 - 10:05 am UTC
a) because index stats only ever has but one row. It'll be for the last partition unless you tell us what partition to do.
ops$tkyte%ORA11GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 -- subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
13 )
14 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;
48 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx on t(dt,x) local;
Index created.
ops$tkyte%ORA11GR2> analyze index t_idx validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;
PARTITION_NAME LF_ROWS
------------------------------ ----------
JUNK 16
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> analyze index t_idx partition(part1) validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;
PARTITION_NAME LF_ROWS
------------------------------ ----------
PART1 16
ops$tkyte%ORA11GR2> analyze index t_idx partition(part2) validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;
PARTITION_NAME LF_ROWS
------------------------------ ----------
PART2 16
ops$tkyte%ORA11GR2> analyze index t_idx partition(junk) validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;
PARTITION_NAME LF_ROWS
------------------------------ ----------
JUNK 16
you'd have to run it for each partition, save the results after each run and then add them up.
b) probably - not guaranteed - but probably... Since 80% of the rows would not be in the second index...
(but think about a case where it might not be true :) )
ops$tkyte%ORA11GR2> create table t1 ( x varchar2(80), y varchar2(80) );
Table created.
ops$tkyte%ORA11GR2> insert into t1 select <b><fill in the blanks!></b> from all_objects;
72998 rows created.
ops$tkyte%ORA11GR2> create index t1_idx on t1(x,y);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t2 ( x varchar2(80), y varchar2(80) );
Table created.
ops$tkyte%ORA11GR2> insert into t2 select <b><fill in the blanks!></b> from all_objects;
73000 rows created.
ops$tkyte%ORA11GR2> create index t2_idx on t2(x,y);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select count(x||y), count(*) from t1;
COUNT(X||Y) COUNT(*)
----------- ----------
72998 72998
ops$tkyte%ORA11GR2> select count(x||y), count(*) from t2;
COUNT(X||Y) COUNT(*)
----------- ----------
14600 73000
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> analyze index t1_idx validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select name, btree_space, used_space from index_stats;
NAME BTREE_SPACE USED_SPACE
------------------------------ ----------- ----------
T1_IDX 1231416 1097553
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> analyze index t2_idx validate structure;
Index analyzed.
ops$tkyte%ORA11GR2> select name, btree_space, used_space from index_stats;
NAME BTREE_SPACE USED_SPACE
------------------------------ ----------- ----------
T2_IDX 2926824 2588084
..index stats
A Reader, January 10, 2013 - 1:44 am UTC
Sorry Tom,
Just a typo above
the line
...why index rows and table rows are matching ( i have accounted for NULLs as above )
is corrected to
why index rows and table rows are *NOT* matching ( i have accounted for NULLs as above )
..Index Stats
A Reader, January 20, 2013 - 11:02 pm UTC
Sorry Tom
for not making the things clear in the begining.
when you said :
....a) because index stats only ever has but one row. It'll be for the last partition unless you tell us what partition to do.
we did calculated for each of the parition and stored those stats into a a table bde_index_stats.
The given output is for the 1st partition.
SQL> exec print_table('select * from bde_index_stats where partition_name=''PI_P01''');
HEIGHT : 5
BLOCKS : 53992320
NAME : PI_UK
PARTITION_NAME : PI_P01
LF_ROWS : 1120039324
LF_BLKS : 53804613
LF_ROWS_LEN : 213287053602
LF_BLK_LEN : 7396
BR_ROWS : 53804612
BR_BLKS : 187204
BR_ROWS_LEN : 825251980
BR_BLK_LEN : 8028
DEL_LF_ROWS : 10727072
DEL_LF_ROWS_LEN : 235895058
DISTINCT_KEYS : 1120039324
MOST_REPEATED_KEY : 1
BTREE_SPACE : 399441791460
USED_SPACE : 214112305582
PCT_USED : 54
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 6
PRE_ROWS : 0
PRE_ROWS_LEN : 0
OPT_CMPR_COUNT : 0
OPT_CMPR_PCTSAVE : 0
and other partitions have NO data ( except the maxva partition which has 11 rows) . so I have included that output above.
Here is the count partition_name, lf_rows from table bde_index_stats;
PARTITION_NAME LF_ROWS
PI_P01 1,120,039,324
PI_P02 0
PI_P03 0
PI_P04 0
PI_P05 0
PI_P06 0
PI_P07 0
PI_P08 0
PI_P09 0
PI_P10 0
PI_PMAXVALUE 11
So could you please suggest on the earlier question ..
as mentioned below
a) Index LF_rows count is 1/8 of the count(*) in the table.
index is on 02 column c1,c2 -- c1 being the partition key.
select count(*) from t -- 8 billion
select sum(lf_rows) from index_stats where index_name = 'that index' -- 1 billion + 11
regards
January 21, 2013 - 7:38 am UTC
show your work step by step by step.
this doesn't match up with what you said the first time, show all steps.
cut and paste.
11g differences in log function?
A reader, January 22, 2013 - 9:06 am UTC
Keeping in mind that reorgs are 99% a waste of time, I ran this out of curiousity and got the followingin 11.2.0.3:
SQL> @indexfrag
decode (sign(ceil(log(br_blk_len/(br_rows_len/br_rows),
*
ERROR at line 4:
ORA-00909: invalid number of arguments
January 23, 2013 - 1:00 am UTC
who ever posted their query originally had a cut and paste error in it - that query (which I never ran or tested) never worked.
log(br_blk_len/(br_rows_len/br_rows),
lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows)
+1 - height), -1,'YES','NO') is not a valid call to log, they lost some closing parens there.
A reader, March 14, 2025 - 11:33 am UTC
Hi Tom , your response is contradiction to the actual performance and benefits of index rebuilds post mass data purging as it can be viewed in practical critical systems.
March 14, 2025 - 1:41 pm UTC
While there can be benefit in these cases, how often do you do mass data purges?