CBO Execution Plan Variations Due to Row Migration in Oracle 19c Session 1

 Hi All,

In this post i am going to share about CBO vd Row Migration in oracle 19C database with example.

CBO Execution Plan Variations Due to Row Migration in Oracle 19c Session 1 . will keep post continuation in my next post .

Connecting the database and create and populating a tightly packed table . 

create table bowie(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(143)) PCTFREE 0;

insert into bowie SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

next create an index on the well clustered ID column 

next collecting index/table statistics, using the DBMS_STATS package as oracle recommended method.

the key index statistics here: BLEVEL=1, LEAF_BLOCKS=445 and the near perfect CLUSTERING_FACTOR=2871.

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

select * from bowie where id > 1 and id < 1001;




Explain plan










PLAN_TABLE_OUTPUT

_______________________________________________________________________________________________________________

SQL_ID bqw9mz995kds9, child number 0

-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1845943507

------------------------------------------------------------------------------------------------------------

| Id | Operation                            | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers |

----------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                     |            |      1 |        |    999 |00:00:00.01 |      18 ||  1

|  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |    999 |00:00:00.01 |      18 |

|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |    999 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by

operation id):

---------------------------------------------------

   2 - access("ID">1 AND "ID"<1001)

Statistics

----------------------------------------------------------         

1 CPU used by this session         

1 CPU used when call started         

1 DB time      

7678 RM usage        

3 Requests to/from client         

2 SQL*Net roundtrips to/from client       

16 buffer is not pinned count      

1983 buffer is pinned count       

323 bytes received via SQL*Net from client    

171383 bytes sent via SQL*Net to client         

2 calls to get snapshot scn: kcmgss         

2 calls to kcmgcs        

18 consistent gets         

1 consistent gets examination         

1 consistent gets examination (fastpath)        

18 consistent gets from cache        

17 consistent gets pin        

17 consistent gets pin (fastpath)         

2 execute count         

1 index range scans    

147456 logical read bytes from cache        

17 no work - consistent read gets

40 non-idle wait count

2 opened cursors cumulative         

1 opened cursors current         

2 parse count (total)         

2 process last non-idle time         

1 session cursor cache count         

1 session cursor cache hits        

18 session logical reads         

1 sorts (memory)   

2024 sorts (rows)      

999 table fetch by rowid      

3 user calls

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: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bqw9mz995kds9',format=>'ALLSTATS LAST +cost +bytes'));




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




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.

SQL> update bowie set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS';

200000 rows updated.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables

where table_name='BOWIE';  2

TABLE_NAME

--------------------------------------------------------------------------------

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT

---------- ---------- ------------ ---------- ----------- ----------

BOWIE

    200000       4642            0          0         167          0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE';

INDEX_NAME

--------------------------------------------------------------------------------

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

---------- ----------- -----------------

BOWIE_ID_I

         1         445              2871

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

SQL> analyze table bowie compute statistics;

Table analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables

where table_name='BOWIE';  2

TABLE_NAME

--------------------------------------------------------------------------------

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT

---------- ---------- ------------ ---------- ----------- ----------

BOWIE

    200000       4642           93        103         171      70096

SQL>

We notice that we now have a CHAIN_CNT of 70096.

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,

Thanks,

Srini