Re: Change in Index costing because of blevel change

Thank you so much Jonathan. That helped.

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

>
> For the quick and dirty workaround I would adjust the blevel on the
> partition and at the table level. Any time anything triggers a stats
> collection on the table you MIGHT find that the blevel would be
> recalculated, but if you've set the blevel on the partition to 2 then the
> problem could only re-appear if the stats on that partition were
> re-collected (and the collection was set to cascade to the indexes). This
> happens because the global (table level) blevel is set to be the maximum of
> the partition blevel values (See:
> https://jonathanlewis.wordpress.com/2010/11/14/local-indexes-2/ )
>
> Given this behaviour it would probably be sensible at some point to split
> the over-large partition into two pieces so that an unexpected stats gather
> won't accidentally re-introduced the higher blevel).
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
> On Tue, 22 Jun 2021 at 06:20, Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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