IndexClusteringFactorDeepDive指标聚类因子深俯冲

Title,Stufe 1,Stufe 2,Stufe 3,*,Tony Hasler December 2009,Index Clustering Factor Deep Dive Tony HaslerUKOUG Birmingham December 2021,Agenda,Actors reconstruction of a real-life performance problem,Discussion of the(in)accuracy of Oracles clustering factor estimate and why it is important,How to identify and correct clustering factor related performance issues,ACKNOWLEDGEMENT:,Chapter 5 of Cost Based Oracle Fundamentals by Jonathan Lewis was my source for 99%of the information in this talk,2,The Case Study,A large table was loaded with additional data once per month and was read-only the rest of the time,At the time of the issue the table held about two years worth of data,A column on the table called MONTH_DATE held the date of the first day of the month of the load,The data was queried along with other tables using Business Objects,3,DEMO,4,The concept of index clustering,Seven more,table blocks,10%Selectivity with weak clustering,10%Selectivity with strong clustering,Seven more,table blocks,5,Why Clustering Factor is important,Full table scans avoid access to the index structures,Full table scans use multi-block reads to the table data whereas access via an index uses single block reads,Each block is visited exactly once,BUT,Indexed access doesnt,necessarily,read all the blocks in the table,The previous example shows that selectivity is not sufficient to determine whether the index should be used,6,Cost of Indexed Access to a Table,The full formula is:,cost=blevel+,ceiling(leaf_blocks*effective index selectivity)+,ceiling(clustering_factor*effective index selectivity),The simplified formula,ignoring cost of traversing index and assuming,that,all predicates,are,managed by,the,index:,cost=ceiling(clustering_factor*index selectivity,),7,Clustering Factor For a Strongly Clustered Index,Index,Table Blocks,8,Clustering Factor For a Weakly Clustered Index,Index,Table Blocks,9,How to Interpret the Clustering Factor of an Index,Generally the clustering factor will be between the number of blocks and the number of rows in a table,A,low,number is,good,and reflects,strong,clustering,A,high,number is,bad,and reflects,weak,clustering,The Clustering Factor may be lower than the number of blocks if there are empty blocks in the table below the HWM and/or there are many rows that have null values for the indexed column(s),The Clustering Factor can never be greater than the number of rows in a table,The clustering factor for a bitmap index is just a copy of the number of rows and is not used,10,DEMO,11,Why isnt my index being used?,An index is unlikely to be used if the clustering factor is close to the number of rows unless the selectivity is very strong(e.g.a unique scan),The clustering factor of an index will,not,change if you rebuild an index,The clustering factor of an index,will,change if you rebuild the,table,so that the data is physically ordered by the index key,You cannot have a low clustering factor for two independent indexes of the same table(unless you set them by hand),12,Clustering Factor Killers,Concurrent inserts with:,Multiple freelists,Multiple freelist groups(RAC),ASSM,Reverse key indexes,In other words steps to reduce contention also harm the clustering factor,But none of this applies to our test case so what went wrong?,13,Multi Column Indexes,Non-unique single column indexes are sorted by:,Indexed column,Rowid,All index entries for the same value of the indexed column in the same block will be adjacent,Non-unique multi-column indexes are sorted by:,Leading indexed column,Next column,.,Rowid,Index entries for the same value of the,leading,indexed column in the same table block,may not,be adjacent in the index,but will,be close!,14,DEMO,15,The Big Tip,Lowering the clustering factor using DBMS_STATS.SET_INDEX_STATS will increase the likelihood of your index being used,One way to obtain a decent value is to build an index just on the leading column temporarily and copy the value from that,BUT only do this if you have good reason.Dont do this all the time as indexed access is frequently slower than a full table scan,16,Questions,:/,17,