RE: How to Reduce Index clustering Factor without downtime

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 4 Jun 2016 08:54:13 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D8335_at_EXMBX01.thus.corp>



The value of the clustering_factor affects Oracle's choice of execution plan - but the clustering_factor has always been derived using an unrealistic algorithm that usually makes it much higher than it should be, which is why the optimizer can easily pick the wrong index, or decide to use a tablescan, or choose a bad join order when your testing shows that a particular index to access a particular table in a particular join order is clearly a good idea.

Changing the clustering_factor (number stored in the database) can help the optimizer choose the right path. Changing the physical arrangement of the data in the database to achieve the change in value is a bad idea.

Historically we could simple use dbms_stats.set_index_stats() to change the value stored for a given index, but in 11.2.0.4 (backported from 12c) Oracle introduced a method of making the clustering_factor algorithm produce a more realistic result.

Set the table preference 'table_cached_blocks' to a value like 16 (or 16 * node count, for RAC, or "realistic degree of concurrency of inserts" for highly concurrent systems) and gather index stats and you will find that the clustering_factor for indexes on columns that show "weak" clustering will drop significantly.

See: https://richardfoote.wordpress.com/2013/05/08/important-clustering-factor-calculation-improvement-fix-you/  (the first of 3 posts), and http://jonathanlewis.wordpress.com/?s=table_cached_blocks<https://jonathanlewis.wordpress.com/?s=table_cached_blocks>

I'm going to take a look at the note you quoted and point out the omission.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of sumit Tyagi [dba.tyagisumit_at_gmail.com] Sent: 04 June 2016 08:01
To: Oracle L
Subject: How to Reduce Index clustering Factor without downtime

Hi Oracle-L team ,

How can Clustering Factor be Reduced : My Question is how to reduce CF in a production OLTP environment where table data is constantly changing and we can't afford a downtime .

Clustering Factor (Doc ID 39836.1)

The only method to affect the clustering factor is to sort and then store the rows in the table in the same order as in they appear in the index. Exporting rows and putting them back in the same order that they appeared originally will have no affect. Remember that ordering the rows to suit one index may have detrimental effects on the choice of other indexes.

--

--

BR
Sumit Tyagi
+91-7829543355

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jun 04 2016 - 10:54:13 CEST

Original text of this message