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

  • From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 22 Jun 2021 13:13:55 +0100

I'd suspect that there was never any explicit discussion about whether
lock_index_stats() should be implemented or not.
Even though we can (e.g.) restore_index_stats(),
gather_table_stats(cascade=>false) and so on, it may not have crossed
anyone's mind to consider the possibility that someone would want to lock
the index stats and while continuing to change the table stats.
It's the type of default (non-)decision that could have been made in the
dim and distant past that is now too messy to unwind.

Regards
Jonathan Lewis


On Tue, 22 Jun 2021 at 10:57, <rogel@xxxxxx> wrote:

Hi Jonathan,

interestingly we can manually mess around with set_table_stats,
set_index_stats and lock those stats on table level via lock_table_stats or
even on table partition/table subpartition level via lock_partition_stats.
However, Oracle doesn't seem to allow us locking index stats or locking
index partition stats. (I never noticed that before.) Do you have any idea
why ?

Regards,
Matthias





*Gesendet:* Dienstag, 22. Juni 2021 um 11:20 Uhr
*Von:* "Jonathan Lewis" <jlewisoracle@xxxxxxxxx>
*An:* "Lok P" <loknath.73@xxxxxxxxx>
*Cc:* "Oracle L" <oracle-l@xxxxxxxxxxxxx>
*Betreff:* Re: Change in Index costing because of blevel change

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


Other related posts:

  • » Re: Re: Change in Index costing because of blevel change - Jonathan Lewis