Deep drive Indexes tuning query tuning-30

When do we rebuild an Index and Table

https://mwidlake.wordpress.com/2009/11/18/depth-of-indexes-on-vldbs/

https://community.oracle.com/thread/589369?tstart=0

http://www.orafaq.com/node/2903 – SQL vs Oracle rebuild

 

Depth/height of index – Blevel + 1. Blevel = branch+root, not leaf.

Ex: Blevel 3 = Depth is 4, which has a rowid pointer to original table, So the IO taken to read a data is 5. For IOT only 4, since leaf and data is same.

select max(blevel) from dba_indexes;

select index_name,blevel,leaf_blocks from dba_indexes where owner=’PNNORPT’ and index_name like ‘SLM_STEP%’;

select owner,index_name from dba_indexes where blevel=3;

 

SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor,max(blevel)

FROM DBA_tables t, DBA_indexes i

WHERE t.table_name = i.table_name AND i.index_name like ‘SLM_STEP’

group by t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor;

 

SELECT name,height, lf_blks, br_blks, pct_used FROM index_stats where name like ‘SLM_STEP’;

 

 

Index Rebuild – Richard Foote Myths

 

• The vast majority of indexes do not require rebuilding
• Oracle B-tree indexes can become “unbalanced” over time and need to be rebuilt

No need, it always balanced.

• If an index has a poor clustering factor, the index needs to be rebuilt

ANS: NO need to rebuild, since the rebuild does not match the index and table columns orders.

How to improve CF

To improve the CF, it’s the table that must be rebuilt (and reordered)

 

EXEC dbms_stats.gather_index_stats(ownname=>’SYS’, indname=>’CF_TEST_BAD_I’, estimate_percent=> null);

 

SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor

FROM user_tables t, user_indexes i

WHERE t.table_name = i.table_name AND i.index_name=’CF_TEST_BAD_I’;

 

• Deleted space in an index is “deadwood” and over time requires the index to be rebuilt

ANS: Deleted space most definitely is reused by delayed block cleanouts

 

• If an index reaches “x” number of levels, it becomes inefficient and requires the index to be rebuilt

ANS: No, after a rebuild also, it is in same level.

 

• To improve performance, rebuild indexes regularly

ANS: It depends on the following.

 

Conditions for Rebuilds

 

• Large free space (generally 50%+), which indexes rarely reach, and
• Large selectivity, which most index accesses never reach, and
• Response times are adversely affected, which rarely are.
• Note requirement of some free space anyways to avoid insert and subsequent free space issues
• Benefit of rebuild based on various dependencies which include:

–Size of index

–Clustering Factor

–Caching characteristics

–Frequency of index accesses

–Selectivity (cardinality) of index accesses

–Range of selectivity (random or specific range)

–Efficiency of dependent SQL

–Fragmentation characteristics (does it affect portion of index frequently used)

–I/O characteristics of index (serve contention or I/O bottlenecks)

–The list goes on and on ….

 

Index Statistics:

• DBA_INDEXES
• INDEX_STATS
• INDEX_HISTOGRAMS
• V$SEGMENT_STATISTICS

 

Conditions for Coalesce

 

Coalesce is most effective when approximately 25% or less of an index has less than 50% of used space

• If used space is generally greater than 50%, Coalesce will be ineffective
• If more than approximately 25% of an index has significant fragmentation issues, a rebuild is less costly and more effective
• However, locking issues need to be considered (Prior to 11g)

 

Shrink will mostly give the same result as coalesce.

 

Query tuning