Aw: Re: Change in Index costing because of blevel change

Thanks a lot.

 

In regards to get/set_index_stats, I am thinking of the stats gathering(which I believe is currently happening in an INCREMENTAL way) which has turned the blevel at global level to -3 because of that one partition mess. So if i forcibly set the BLEVEL for the 15th jun index partition to -2 manually(whose value is currently-3) using set_index_stats, will the subsequent stats gather revert the BLEVEL of the index at global level i.e. in dba_indexes automatically to -2? 

Or it will do the opposite, i.e. it will regather the stats on that 15th jun index partition and make it -3 along with global blevel to -3? 

Or are you pointing towards forcibly get/set_index_stats the blevel to-2 for both local i.e the 15th jun partition along with global blevel for that index?

 

Regards

Lok

 

On Tue, Jun 22, 2021 at 12:50 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

 

You might get away with two calls to dbms_stats, one to "get_index_stats" the other to "set_index_stats". modifying the variable holding the blevel between the calls.

Example here: https://jonathanlewis.wordpress.com/2010/01/06/copy-stats/

 

As far as the split partition is concerned - if the 15th June is now in a read-only stats you could do the following steps (to hide the time, rather than getting rid of it).

create table temp14 as select * from15th June partition where data = 14th june, create indexes

create table temp15 as select * from15th June partition where data = 15th june, create indexes

 

truncate 15th June partition

split partition into 14th june and 15th june

exchange partition for 14th june and temp14 including indexes

exchange partition for 15th june and temp15 including indexes

 

The users would only see brief interruptions on the exchange - unless you had some global indexes to maintain as well.

 

Regards

Jonathan Lewis

 

 

 

 

On Mon, 21 Jun 2021 at 19:55, Lok P <loknath.73_at_gmail.com> wrote:

Hi All, It's version 11.2.0.4 of Oracle. 

 

We are having a situation in which multiple queries suddenly changed the execution path and deviated from the local indexed access path to the FTS path(partition range all) for a daily range partitioned table. And then after debugging into the issue we found that the COST of the index access actually has been increased because of the increase in BLEVEL of the index. And the BLEVEL has been increased from 2 to 3 in global level(dba_indexes) because of one of the missing daily partitions. For e.g. 14th jun partition was missing , so 15th jun partition now holds both 14th june and 15th jun data, so its twice as bigger as a normal daily partition.

 

The missing day range partition caused the subsequent day partitions to actually hold two partitions/days worth of data and thus the blevel of that specific local index increased from 2 to -3 for that index partition. and since then, in global level(dba_indexes)  the blevel is staying as-3 for that index, costing that index access higher and thus making plan change for many queries. 

 

My question is how should we handle such a situation ? Should we go back and break the specific partition(15th Jun partition) into two daily range partitions i.e. 14th and 15th (which i believe is going to take lot of time and resources considering this table has ~8 different local indexes in it) and gather stats again or is there any other way to just tweak the stats for that one big partition such that the global index BLEVEl can be back to -2 , and get this issue fixed?

 

Regards

Lok