CBO Costing Plans With Migrated Rows Part I (“Ignoreland”)

CBO Costing Plans With Migrated Rows Part I (“Ignoreland”) March 21, 2023

Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Data Clustering, Index Access Path, Index Height, Index statistics, Leaf Blocks, Migrated Rows, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard's Blog, ROWID.
trackback

Whilst recently blogging about Migrated Rows and specifically changes to how ROWIDs are now maintained on the fly in Oracle Autonomous Databases, I made a discovery regarding how the Cost-Based Optimizer (CBO) costs such plans. This is one of the key reasons why I blog, not only to try and share odd titbits about how Oracle works, but also to hopefully learn much myself in the process.

Imagine my surprise in not only learning that Oracle and the CBO works differently to how I had always thought Oracle worked in this respect, but that this behaviour has been the case since at least Oracle 9i.

In Part I, I’ll use the same example of migrated rows as I’ve used in the past few blog posts and initially show how the CBO generally costs such plans (and by which I had incorrectly assumed ALWAYS costed such plans).

Let’s start by creating and populating a tightly packed table (in an environment where ROWIDs are NOT updated on the fly):

I’ll next create an index on the well clustered ID column (as the rows are inserted in ID column order within the table):

Next, we’ll use the Oracle recommended method of collecting table/index statistics, by using the DBMS_STATS package:

Note the key index statistics here: BLEVEL=1, LEAF_BLOCKS=473 and the near perfect CLUSTERING_FACTOR=3250.

If we run the following query featuring a non-equality range predicate:

We notice that the CBO indeed uses the index.

They key statistic to note here is that Consistent Gets is just 18, which is extremely low considering we’re returning 999 rows. This is due to the fact the index is currently extremely efficient as it can fetch multiple rows by visiting the same table block due to the excellent clustering/ordering of the required ID column values (and also due to my high arraysize session setting).

If we look at the CBO costings for this plan:

I’ve previously discussed many times how the CBO costs index access paths, but it’s always useful to go over this again, as it’s the most common question I get asked when I visit customer sites.

The KEY statistic the CBO has to determine is the estimated Selectivity of the query (the estimated percentage of rows to be returned), as this is the driver of all the subsequent CBO calculations.

The Selectivity of this range-based predicate query is calculated as follows:

Selectivity = (Highest Bound Value – Lowest Bound Value) / (Highest Value – Lowest Value)
= (1001-1) /(200000-1)
= 1000/199999
=  approx. 0.005

Once Oracle has the selectivity, it can calculate the query Cardinality (estimated number of rows) as follows:

Cardinality = Selectivity x No of Rows

Cardinality = 0.005 x 200000 = 1000 rows

This is our visual window into the likelihood that the CBO has made an accurate decision with its execution plan. If the cardinality estimates are reasonably accurate, then the CBO is likely to generate a good plan. If the cardinality estimates are way off, then the CBO is more likely to generate an inappropriate plan.

The CBO cardinality estimate in the above plan is 1000 rows, whereas the number of rows actually returned is 999 rows.

So indeed, the CBO has got the cardinality almost spot on (except for a trivial rounding error) and so we have a high degree of confidence that the CBO is using the correct selectivity estimates when they get plugged into the following CBO formula for costing an index range scan (using this selectivity of 0.005 and the index statistics listed above):

Index Scan Cost = (blevel + ceil(effective index selectivity x leaf_blocks)) + ceil(effective table selectivity x clustering_factor)

= (1 + ceil(0.005 x 467)) + ceil(0.005 x 3250)
= (1 + 3) + 17
= 4 + 17 = 21

So we can clearly see where the CBO gets its costings for both reading the index during the Index Range Scan (4) and for the plan as a whole (21).

The CBO cost of 21 very closely resembles the 18 consistent gets accessed when the plan is executed. This to me suggests that the CBO has indeed costed this plan very accurately and appropriately.

It’s interesting to note in the above execution plan that Oracle is attributing 100% of this cost of 21 to CPU (21 (100)). That will be a discussion for another day…

OK, let’s now perform an update on the table, increasing the size of the rows such that I generate a bunch of migrated rows:

If we now collect fresh statistics again using DBMS_STATS:

We notice that none of the key statistics have changed, except for the number of Table Blocks (now 4906, previously it was 3268) and the Average Row Length has also increased (now 167, previously it was 111). Both of these can of course be attributed to the increase in the size of the values now stored in the NAME column following the Update.

Importantly, notice that collecting statistics via DBMS_STATS does NOT collect data for the CHAIN_CNT statistic, it remains at 0 even though many migrated rows were actually generated by the Update statement (as we’ll see below).

Increasing the Table Blocks will result in an associated increase in the cost of reading this table via a Full Table Scan (FTS).

We notice that none of the index-related statistics changed following the Update statement (as in this example, Oracle does NOT update the ROWIDs of any of the migrated rows, Oracle simply stores a pointer in the original block to denote the new physical location of the migrated rows as previously discussed).

So if we only INCREASE the cost of a FTS (via having more Table Blocks) but keep intact all the previous index related statistics, then the CBO is certainly going to again select the same Index Range Scan plan, as the plan will have the same (cheaper than FTS) costings as before.

If we re-run the query again:

We notice that indeed it’s the same Index Range Scan plan as before.

But we notice that the number of Consistent Gets has increased substantially to 666 (previously it was just 18). The reason for this large jump is due to the now 327 table fetch continued rows that need to be accessed due to the newly migrated rows following the Update. This number is then doubled (so 2 x 327 = 654) to represent the approximate additional Consistent Gets we now need to perform, as Oracle needs to read the additional table block to access the migrated row’s new physical location AND to now re-read the original table block to access the next row to be fetched (previously Oracle could read all the required consecutive rows required from the same table block within the one consistent get).

So it’s now actually substantially more expensive to read the required 1000 rows via this index due to this increase in necessary consistent gets.

But if we look at the actual cost of this plan now:

We notice that as expected (as none of the index-related statistics have changed), that despite being much more expensive to now use this index, the costs of this plan (4 for reading the index and 21 overall) remain unchanged.

I would argue that these CBO costs are no longer as accurate as the 21 total CBO cost does not so closely represent the actual 666 consistent gets now required.

Now, the 327 table fetch continued row statistics from the previous run is clear proof we indeed have migrated rows following the Update statement.

But if we want to confirm how many migrated rows we now have in the table, we can use the ANALYZE command to collect these additional statistics:

We notice that we now have a CHAIN_CNT of 56186.

Now this statistic can represent any row that is not housed inside a single table block (for which there could be a number of possible reasons, such as a row simply being too long to fit in a single table block), but as all rows are still relatively tiny, we can be certain that indeed all 56186 chained rows represent migrated rows.

Now that I’ve gone and used ANALYZE, primarily to generate this CHAIN_CNT statistic, my previous understanding of how the CBO costs migrated rows crumbles away, as I’ll discuss in my next post…

Comments»

1. Jonathan Lewis - March 21, 2023

Leave a Reply Cancel reply