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
>>
>

--
http://www.freelists.org/webpage/oracle-l