6

CBO Costing Plans With Migrated Rows Part II (“New Killer Star”)

 1 year ago
source link: https://richardfoote.wordpress.com/2023/03/28/cbo-costing-plans-with-migrated-rows-part-ii-new-killer-star/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

CBO Costing Plans With Migrated Rows Part II (“New Killer Star”) March 28, 2023

Posted by Richard Foote in CBO, Index Access Path, 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

new-killer-star.jpg?w=460

I’ve spent the past few months discussing Migrated Rows, in large part thanks to an excellent 15 minute video by Connor McDonald on how ROWIDs can now be updated on the fly in Oracle Autonomous databases.

Well 14 such posts later, I have finally reached the end of this topic (for now at least). So, an average of about 1 post per minute of video 🙂

In my previous post, I discussed how the CBO costs execution plans with tables that have migrated rows, when the statistics are collected as recommended via the DBMS_STATS package. In summary, migrated rows are basically just ignored, with the CBO blissfully unaware of the existence of any such migrated rows.

As I discussed, if I want to easily see how many migrated rows I have in a table, I can potentially use the ANALYZE command as follows:

SQL> analyze table bowie compute statistics;
Table BOWIE analyzed.
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables
where table_name='BOWIE';
TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4906              86          415            170        56186

As you can now see, the table currently has 56186 migrated rows (yes CHAIN_CNT can potentially count rows that simply can’t fit within a single block, but all these rows are definitely migrated rows as per the demo in my previous post).

Now, it had always been my belief that although you can use the ANALYZE command to count out these migrated rows, the CBO would simply ignore this statistic in its calculations.

But I was wrong.

If we now re-run the query from the previous post:

SQL> select * from bowie where id > 1 and id < 1001;
999 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |    999 |    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |    999 |    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
9193 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
664 buffer is not pinned count
1662 buffer is pinned count
323 bytes received via SQL*Net from client
171333 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
666 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
666 consistent gets from cache
665 consistent gets pin
665 consistent gets pin (fastpath)
2 execute count
1 index range scans
5455872 logical read bytes from cache
665 no work - consistent read gets
39 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 hits
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
999 table fetch by rowid
327 table fetch continued row
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |   302 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |    999 |   141K|   302   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |    999 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)

We can see that the cost of the plan has now changed.

Although the cost of reading the index itself is still the same with a cost of 4, the overall cost of the plan has increased to 302 (previously it was 21).

So the difference in plan costs is 302 – 21 = 281. And it’s pretty easy to see where this extra comes from…

The extra costs is basically query selectivity x no of migrated rows

Extra costs = 0.005 x 56186 = 281.

So the index scan costing formula should really be updated to be:

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

Now, IMHO, this new cost is actually more accurate and better matches the true cost of now using the index, which requires 666 Consistent Gets (previously, before the rows migrated, the index plan required just 18 Consistent Gets).

So in some respects, this new cost might not be a bad thing. But then again, a sudden change in such costings due to a flood of new migrated rows might result in an unexpected and undesired plan changes that have been carefully crafted for statistics generated with the conventional DBMS_STATS collection method.

However, it’s not sufficient to simply collect fresh statistics using DBMS_STATS to get the previous CBO costings where migrated rows are ignored:

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';
TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4906              86          415            167        56186

Simply collecting fresh statistics does NOT clear out the CHAIN_CNT statistic and so the CBO costings remain the same as with ANALYZE command:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |   302 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |    999 |   141K|   302   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |    999 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)

You need to first delete the table statistics to remove the CHAIN_CNT statistic (which of course comes with obvious dangers now there are no table statistics present) before you collect fresh statistics using DBMS_STATS:

SQL> execute dbms_stats.delete_table_stats(ownname=>null, tabname=>'BOWIE');
PL/SQL procedure successfully completed.
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';
TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
BOWIE              200000      4906               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            473                 3250

The CHAIN_CNT statistic has finally been cleared to 0 and the CBO costings now returned to as it was previously when such migrated rows were ignored:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'b1vwpu2rgn8p5',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID b1vwpu2rgn8p5, child number 0
-------------------------------------
select * from bowie where id > 1 and id < 1001
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |      1 |        |       |    21 (100)|    999 |00:00:00.01 |     666 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |   163K|    21   (0)|    999 |00:00:00.01 |     666 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |       |     4   (0)|    999 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">1 AND "ID"<1001)

The CBO costs are now back to the previous 21.

So I’m a little in two minds about this. I think the statistics generated and used by the CBO are better with the ANALYZE command, but would still suggest collecting the necessary statistics using the recommended DBMS_STATS approach. Perhaps Oracle giving us the option to collect these additional statistics using DBMS_STATS might be a useful enhancement… 🤷‍♂️

Now, at one point in time, a long long time ago, I’m reasonably sure the CBO previously didn’t use the CHAIN_CNT statistic. However, it came as no real surprise when I researched when the CBO had started using the CHAIN_CNT statistic in its calculations, that Jonathan Lewis had already written on this subject way way back in April 2009 🙂

So Oracle definitely had this behaviour all the way back to at least 9i and continues to behave this way in 21c. Ah well, better late than never I guess to finally realise how all this actually works…


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK