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.2 comments
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):
SQL> 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(142)) PCTFREE 0; Table BOWIE created. SQL> 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; 200,000 rows inserted. SQL> commit; Commit complete.
I’ll next create an index on the well clustered ID column (as the rows are inserted in ID column order within the table):
SQL> create index bowie_id_i on bowie(id); Index BOWIE_ID_I created.
Next, we’ll use the Oracle recommended method of collecting table/index statistics, by using the DBMS_STATS package:
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 3268 0 0 111 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
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:
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 | 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
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:
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 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 108K| 21 (0)| 999 |00:00:00.01 | 18 |
|* 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)
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:
SQL> update bowie set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete.
If we now collect fresh statistics again using DBMS_STATS:
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
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:
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 | 1000 | 999 |00:00:00.01 | 666 |
|* 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
7709 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
171500 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
1 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
999 table fetch by rowid
327 table fetch continued row
3 user calls
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:
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)
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 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
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…
Possible Impact To Clustering Factor Now ROWIDs Are Updated When Rows Migrate Part I (“Growin’ Up”) March 1, 2023
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, BLEVEL, CBO, Changing ROWID, Clustering Factor, Data Clustering, Hints, Index Access Path, Index Block Splits, Index Delete Operations, Index Height, Index Internals, Index Rebuild, Index statistics, Leaf Blocks, Migrated Rows, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Statistics, Oracle19c, Performance Tuning, Richard Foote Training, Richard's Blog, ROWID.2 comments
In my previous post I discussed how an index can potentially be somewhat inflated in size after ROWIDs are updated on the fly after a substantial number of rows are migrated.
However, there’s another key “factor” of an index that in some scenarios can be impacted by this new ROWID behaviour with regard migrated rows.
To highlight this scenario, I’ll again start by creating and populating a table with ENABLE ROW MOVEMENT disabled:
SQL> 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(142)) PCTFREE 0; Table BOWIE created. SQL> 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; 200,000 rows inserted. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed.
I’ll next create an index on the ID column. The important aspect with the ID column is that the data is entered monotonically in ID column order, so the associated index will have an excellent (very low) Clustering Factor:
SQL> create index bowie_id_i on bowie(id); Index BOWIE_ID_I created.
If we look at some key statistics of the table and index:
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE 200000 3268 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
We can see that the number of table blocks is 3268, the number of index leaf blocks is 473 and we indeed have a near perfect Clustering Factor of 3250.
If we run a couple of queries:
SQL> select * from bowie where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
7353 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
16 buffer is not pinned count
1985 buffer is pinned count
324 bytes received via SQL*Net from client
171305 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
38 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
18 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
We can see for this first query that returns 1000 rows, it requires just 18 consistent gets, thanks primarily due to the efficient index with the perfect Clustering Factor.
If we look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
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)| 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 108K| 21 (0)| 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We can see the plan has an accurate cost of just 21.
If we now run a similar query that returns a few more rows:
SQL> select * from bowie where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
11353 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
59 buffer is not pinned count
8342 buffer is pinned count
324 bytes received via SQL*Net from client
461834 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
68 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
68 consistent gets from cache
67 consistent gets pin
67 consistent gets pin (fastpath)
2 execute count
1 index range scans
557056 logical read bytes from cache
67 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
68 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
We can see that it only required just 68 consistent gets to return 4200 rows, thanks to the excellent data clustering and associated very low Clustering Factor.
If we look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 455K| 80 (0)| 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
We can see the cost of the plan is currently a relatively accurate 80.
OK, let’s now perform an update on this table that generates a bunch of migrated rows:
SQL> update bowie set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete.
If we now look at the table and index statistics:
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 from user_tables where table_name='BOWIE'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE 200000 4906
We can see that the table blocks value has increased to 4906 (previously 3268). This as explained previously is to due in large part to the increased NAME column values and also due to the pointers in the original table blocks that point to the new locations of the migrated rows.
This relates to approximately a 50% increase in table blocks.
If we look at the current index statistics:
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
We can see that these values are all unchanged, as the ROWIDs in indexes remain unchanged when a row migrates, when ENABLE ROW MOVEMENT is not set.
Therefore, when we re-run these same queries:
SQL> select * from bowie where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 1000 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 DB time
7967 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
664 buffer is not pinned count
1664 buffer is pinned count
324 bytes received via SQL*Net from client
171419 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
37 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
666 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
327 table fetch continued row
3 user calls
The number of consistent gets has increased significantly to 666 (previously it was just 18).
Now we can attributed an increase of approximately 50% of the previous consistent gets (18 x 0.50 = 9) due to the 50% increase in table blocks required now to store the rows due to the increased row size.
We can also attribute an additional 327 consistent gets for the table fetch continued row value listed in the statistics, representing the extra consistent gets required to access the migrated rows from their new physical location.
But 18 + 9 + 327 = 354 still leaves us short of the new 666 consistent gets value.
The problem with having to visit another table block to get a row from its new location is that it means Oracle has to re-access again the original table block to get the next row (rather than reading multiple rows with the same consistent get).
So it’s actually approximately 2 x table fetch continued row, by which the number of consistent gets is going to increase when accessing migrated rows (noting that the last migrated row in a block will only incur a additional consistent get as the next table block accessed will differ regardless).
If we look at the new CBO cost for this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gz5u92hmjwz1h',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________________________________________
SQL_ID gz5u92hmjwz1h, child number 0
-------------------------------------
select * from bowie where id between 1 and 1000
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)| 1000 |00:00:00.01 | 666 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 1000 | 163K| 21 (0)| 1000 |00:00:00.01 | 666 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We notice the CBO cost for this plan remains unchanged at 21.
This is totally to be expected, as the index statistics by which the cost of an index scan is calculated are unchanged.
Considering the rough “rule of thumb” is that the CBO cost of an index scan should be in the ball-park of the number of possible IOs, the fact the plan now uses 666 consistent gets highlights this cost of just 21 is no longer as accurate…
If we look at the second SQL that returns 4200 rows:
SQL> select * from bowie where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 2771 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 4200 |00:00:00.01 | 2771 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
2 CPU used by this session
2 CPU used when call started
2 DB time
14103 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
2762 buffer is not pinned count
7005 buffer is pinned count
324 bytes received via SQL*Net from client
461947 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
2771 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
2771 consistent gets from cache
2770 consistent gets pin
2770 consistent gets pin (fastpath)
2 execute count
1 index range scans
22700032 logical read bytes from cache
2770 no work - consistent read gets
72 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
2771 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
1366 table fetch continued row
3 user calls
We again notice consistent gets has increased significantly to 2771 (previously it was just 68). Again, these additional consistent gets can not be attributed to the extra size of the table and the additional approximate 2 x 1366 table fetch continued row gets.
If we now look at the cost of this plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'c376kdhy5b0x9',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________________
____________
SQL_ID c376kdhy5b0x9, child number 0
-------------------------------------
select * from bowie where id between 1 and 4200
Plan hash value: 1405654398
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 2771 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 1 | 4200 | 684K| 80 (0)| 4200 |00:00:00.01 | 2771 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
We again notice the CBO cost for this plan remains unchanged at 80, again totally expected as the underlying index statistics have remain unchanged after the update statement.
But again, not necessary as accurate a cost as it was previously…
If we repeat this demo, but this time on a table with ENABLE ROW MOVEMENT enabled:
SQL> create table bowie2(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(142)) PCTFREE 0 ENABLE ROW MOVEMENT;
Table BOWIE2 created.
SQL> insert into bowie2 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;
200,000 rows inserted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2', estimate_percent=> null, no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> create index bowie2_id_i on bowie2(id);
Index BOWIE2_ID_I created.
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2';
TABLE_NAME NUM_ROWS BLOCKS
_____________ ___________ _________
BOWIE2 200000 3268
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
BOWIE2_ID_I 1 473 3250
The table and index statistics are currently identical to the previous demo.
If we run the same two equivalent queries:
SQL> select * from bowie2 where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
7909 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
16 buffer is not pinned count
1985 buffer is pinned count
325 bytes received via SQL*Net from client
171306 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
37 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
18 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 21 (100)| 1000 |00:00:00.01 | 18 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 108K| 21 (0)| 1000 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | | 4 (0)| 1000 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
SQL> select * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
2 DB time
13157 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
59 buffer is not pinned count
8342 buffer is pinned count
325 bytes received via SQL*Net from client
461838 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
68 consistent gets
1 consistent gets examination
1 consistent gets examination (fastpath)
68 consistent gets from cache
67 consistent gets pin
67 consistent gets pin (fastpath)
2 execute count
1 index range scans
557056 logical read bytes from cache
67 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
68 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 80 (100)| 4200 |00:00:00.01 | 68 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 455K| 80 (0)| 4200 |00:00:00.01 | 68 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | | 11 (0)| 4200 |00:00:00.01 | 11 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
With identical table/index statistics, we notice as expected that both SQLs have the same consistent gets and CBO costs as with the previous demo.
If we now repeat the equivalent Update statement:
SQL> update bowie2 set name='THE RISE AND FALL OF BOWIE STARDUST AND THE SPIDERS FROM MARS'; 200,000 rows updated. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2', estimate_percent=> null, no_invalidate=>false); PL/SQL procedure successfully completed.
If we look at the table statistics:
SQL> select table_name, num_rows, blocks from user_tables where table_name='BOWIE2'; TABLE_NAME NUM_ROWS BLOCKS _____________ ___________ _________ BOWIE2 200000 4654
We notice the number of table blocks has increased to 4654 due to the increased row lengths, but not as much as with the previous demo (where table blocks increased to 4906) as in this scenario, Oracle does not have to store the row location pointers in the original blocks for the migrated rows.
If we look at the index statistics:
SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
______________ _________ ______________ ____________________
BOWIE2_ID_I 2 945 109061
We notice that these are substantially different from the first demo, where ROWIDs for migrated rows are not updated on the fly.
By now updating the ROWIDs, the indexes can possibly increase in size as they have to store both the previous and new ROWIDs in separate index entries and hence Oracle is more likely to perform additional index block splits (as I discussed in my previous post).
The LEAF_BLOCKS are now 945 (previously 473) and even the BLEVEL has increased from 1 to 2.
Additionally, and perhaps importantly for specific key indexes, the Clustering Factor value of indexes can also be impacted. By migrating rows and physically storing them in different locations, this can potentially detrimentally impact the tight clustering of rows based on specific column values.
The Clustering Factor for the index on the monotonically increased ID column has now increased significantly to 109061, up from the previously perfect 3250.
So columns that have naturally good clustering (e.g.: monotonically increasing values such as IDs and dates) or have been manually well clustered for performance purposes, can have the Clustering Factor of associated indexes detrimentally impacted by migrated rows.
If we re-run the first query:
SQL> select * from bowie2 where id between 1 and 1000;
1,000 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 639 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 1000 |00:00:00.01 | 639 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | 1000 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
15262 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
634 buffer is not pinned count
1367 buffer is pinned count
325 bytes received via SQL*Net from client
171421 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
639 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
639 consistent gets from cache
637 consistent gets pin
637 consistent gets pin (fastpath)
2 execute count
1 index range scans
5234688 logical read bytes from cache
637 no work - consistent read gets
38 non-idle wait count
1 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 process last non-idle time
2 session cursor cache count
639 session logical reads
1 sorts (memory)
2024 sorts (rows)
1000 table fetch by rowid
3 user calls
I discussed in a previous post how by updating the ROWIDs of migrated rows we can improve performance, as Oracle can go directly to the correct new physical location of a migrated row.
But for some specific indexes, where data clustering is crucial, and we have a significant number migrated rows, this might not necessarily be the case.
We can see consistent gets here has increased to 639 (previously is was just 21), and so not hugely different from the 666 consistent gets required to fetch the migrated rows when the ROWIDs were not updated in the first demo.
If we look at the CBO costings:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'gtkw2704bxj7q',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID gtkw2704bxj7q, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 1000
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 553 (100)| 1000 |00:00:00.01 | 639 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 1000 | 163K| 553 (0)| 1000 |00:00:00.01 | 639 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 1000 | | 7 (0)| 1000 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=1000)
We can see the CBO cost has increased significantly to 553 (previously it was just 21).
With a much increased Clustering Factor, this will obviously impact the CBO costs of associated index scans.
In very extreme cases, these possible changes in the Clustering Factor can even impact the viability of using the index.
If we re-run the second query returning the 4200 rows:
SQL> select * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 1495904576
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.02 | 4572 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE2 | 1 | 4200 | 4200 |00:00:00.02 | 4572 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("ID"<=4200 AND "ID">=1))
filter(("ID"<=4200 AND "ID">=1))
We can see that the CBO has now chosen to perform a Full Table Scan (FTS), rather than use the now less efficient index to return this number of rows.
If we look at the CBO costings of this FTS plan:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'25qktyn35b662',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________
SQL_ID 25qktyn35b662, child number 0
-------------------------------------
select * from bowie2 where id between 1 and 4200
Plan hash value: 1495904576
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1264 (100)| 4200 |00:00:00.02 | 4572 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE2 | 1 | 4200 | 684K| 1264 (1)| 4200 |00:00:00.02 | 4572 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(("ID"<=4200 AND "ID">=1))
filter(("ID"<=4200 AND "ID">=1))
The cost of the FTS plan is 1264.
If we compare this is a plan that used the index:
SQL> select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200;
4,200 rows selected.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0
-------------------------------------
select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4200 |00:00:00.01 | 2665 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 4200 |00:00:00.01 | 2665 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | 4200 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
Statistics
-----------------------------------------------------------
2 CPU used by this session
2 CPU used when call started
2 DB time
14531 RM usage
3 Requests to/from client
2 SQL*Net roundtrips to/from client
2646 buffer is not pinned count
5755 buffer is pinned count
348 bytes received via SQL*Net from client
462143 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
2665 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
2665 consistent gets from cache
2663 consistent gets pin
2663 consistent gets pin (fastpath)
2 execute count
1 index range scans
21831680 logical read bytes from cache
2663 no work - consistent read gets
73 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
3 process last non-idle time
2 session cursor cache count
2665 session logical reads
1 sorts (memory)
2024 sorts (rows)
4200 table fetch by rowid
3 user calls
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bzm2vhchqpq7w',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________________________________________
SQL_ID bzm2vhchqpq7w, child number 0
-------------------------------------
select /*+ index (bowie2) */ * from bowie2 where id between 1 and 4200
Plan hash value: 3243780227
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2314 (100)| 4200 |00:00:00.01 | 2665 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2 | 1 | 4200 | 684K| 2314 (1)| 4200 |00:00:00.01 | 2665 |
|* 2 | INDEX RANGE SCAN | BOWIE2_ID_I | 1 | 4200 | | 22 (0)| 4200 |00:00:00.01 | 21 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=4200)
The cost of using the index to retrieve the 4200 rows is 2310, more than the 1264 of the FTS.
For the vast majority of indexes, updating the ROWIDs for migrated rows will result in better performance, as such indexes will be able to directly access the correct new physical location of migrated rows, rather than having to visit the original table block and then follow the stored pointer to the new table block.
But for some very specific indexes, where data clustering is crucial, AND we have a significant number migrated rows, this might not necessarily be the case. The performance benefit might be minimal at best.
That’s more than enough for one post 🙂
In my next post, I’ll discuss how to potentially remedy these performance implications, both for tables with or without ENABLE TABLE MOVEMENT enabled…
Larger Block Tablespace For Indexes Revisited Part III (Prove Yourself) March 2, 2009
Posted by Richard Foote in Index Block Size, Index Height, Index Internals, Index Rebuild, Oracle Indexes, Oracle Myths.10 comments
Time to look a little at an Index Fast Full Scan (IFFS) with respect to moving indexes into a larger block tablespace.
To start, a few points about an IFFS. When performing an IFFS, Oracle will read each and every block in the index structure, including all branch and leaf blocks (up to the HWM), using multi-block read operations. So although an IFFS has its place, it’s a relatively expensive operation, especially if the index is large, as it needs to read all blocks in the current index structure. Therefore, it’s not generally considered a “common” operation, as in traditional index range scans, especially in OLTP environments. Effectively, an IFFS is the index equivalent of a Full Table Scan (FTS) and is performed in a similar fashion. During an IFFS, Oracle basically treats the index as if it were an smaller version of the table from which it can extract the necessary data.
So if an IFFS were indeed to be very common and could be performed so much more effectively in a larger block tablespace, this begs the question why not move tables in a larger block tablespace as well, so FTS can have the same benefits as the index. And if you move both tables and indexes into a larger block tablespace, why not create the database in the larger block and have done with it? Which brings us around to the question of selecting an appropriate block size for the database, which is a different discussion to the so-called benefits of just moving indexes into a larger block tablespace. For another day perhaps …
Now, although Robin’s demo clearly shows consistent gets are basically halved when an index is rebuilt with double the block size (which all sounds very impressive), what it doesn’t highlight however is that during an IFFS, approximately the same amount of data is still actually being read. Although there are certainly some efficiencies in having fewer logical reads, if each consistent gets is more expensive while the overall data being read is similar, you’re simply not going to get an enormous performance boost that simply halving consistent gets might suggest. Claims that things will suddenly run twice as fast (or 120 times as fast) purely by moving indexes into a larger block tablespace are shall we say “exaggerated” to say the least. However, as all index blocks within an index are being read with no subsequent table accesses, an IFFS is going to be as good as it gets when we talk about any possible performance improvements of moving indexes into a larger block tablespace.
Again, it’s all very simple to “give it a go” and see for yourself exactly what improvements one might expect. Move an index into a larger block tablespace and see those IFFS fly. Using exactly the same setup from Robin’s demo as in Parts I and II, let’s see the actual differences …
First, get some current session stats:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 35856 consistent gets 10685006 physical reads 2582 index fast full scans (full) 33003
Now execute 1000 IFFS using the index in an 8K block tablespace …
SQL> declare
2 v_count number;
3 begin
4 for i in 1..1000 loop
5 select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.23
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 36952 consistent gets 11106006 physical reads 2582 index fast full scans (full) 34003
Note that response times are roughly 11.23 secs, mostly consisting of CPU at 10.96 secs. Note also consistent gets are 1000 x 421 as one would expect from Robin’s demo. Now lets see how things differ when we move the index into a 16K block tablespace.
SQL> alter index bowie_idx rebuild tablespace ts_16k;
Index altered.
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 43192 consistent gets 12372513 physical reads 2789 index fast full scans (full) 40003
SQL> declare
2 v_count number;
3 begin
4 for i in 1..1000 loop
5 select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.14
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ----------------------------- ---------- CPU used by this session 44260 consistent gets 12583513 physical reads 2789 index fast full scans (full) 41003
Note there’s only a slight improvement in response times and CPU usage of a couple of percentage points, even though the number of consistent gets has halved. Now your milage may vary a tad but what you will not see is things suddenly using 1/2 the resources or running twice as fast (or running 120 times as fast). Because at the end of the day, both sets of IFFS are reading and processing approximately the same amount of data.
Note also that any such possible minor improvments in IFFS performance is likely going to be cancelled out or more likely overtaken by the increased costs associated with more typical index range scans due to the index not actually reducing in height as discussed in Part II. Again, what’s more common, smaller index range scans or IFFS ?
Robin’s demo and the demo above however assumes the index is fully cached, with no physical I/O (PIO) operations being performed. Now as an IFFS needs to read the entire index, it’s quite likely that an IFFS would require some physical I/O, especially if the index is large. Surely then, if an index is in a larger block tablespace, such physical I/Os would be more efficient because Oracle would be able to read more data with a multi-block read based on a larger block size?
The answer is unfortunately no. Another (rather poor) analogy to get the point across.
You go to the bank to take some money out. You’re only allowed to take out a maximum of $1000 per day from your account. You ask for this in $50 notes and you get 20 x $50 = $1000 in total. Your mate wants to take out more money and asks for the money in $100 notes. However, as the limit is set to $1000 a day, regardless of the denomination of the bank notes, he simply gets 10 x $100 = $1000. You both end up with exactly the same amount of money, it’s just that you have more bank notes.
An Oracle multi-block read works in exactly the same manner. There’s a maximum amount of data you can read which is calculated by the default block size x db_file_multiblock_read_count. If the default block size is 8K and the db_file_multiblock_read_count = 8, then the maximum size of a multiblock read is 8K x 8 = 64K.
If you attempt to perform a multi-block read using a non-default block size, Oracle simply divides the 64K by the default block size to determine how many blocks to read. So if you now attempt to perform a multi-block read from a 16K tablespace, Oracle will only read 16K x 4 blocks = 64K. The I/O size is the same regardless of the blocksize.
And this makes perfect sense. If you’ve tuned the size of a multi-block read perfectly with the default block size in mind, why would you want to (say) suddenly double this perfect size when you double the block size in another tablespace. If doubling a multi-block read size were to suddenly improved things, why not also double the multi-block read size for the default block size as well …
Again, it’s very easy and simple to test how changing the block size in a specific tablespace makes no difference to the behaviour of an associated multi-block read.
First flush the buffer cache and trace a session, while performing a multi-block read via an IFFS , first with a default 8K block size index:
SQL> show parameter db_file_m
NAME TYPE VALUE ----------------------------- ------- ----- db_file_multiblock_read_count integer 8
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set events ‘10046 trace name context forever, level 12’;
Session altered.
SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;
The trace file will show that blocks=8 are being read during the IFFS multiblock read operation as the following extract from the trace file highlights:
WAIT #2: nam=’db file scattered read’ ela= 22897 file#=7 block#=38929 blocks=8 obj#=95742 tim=1217521768059
WAIT #2: nam=’db file scattered read’ ela= 47158 file#=7 block#=38937 blocks=8 obj#=95742 tim=1217521816003
WAIT #2: nam=’db file scattered read’ ela= 37776 file#=7 block#=38945 blocks=8 obj#=95742 tim=1217521854530
However, when you trace a session as it performs the IFFS with a 16K block
WAIT #1: nam=’db file scattered read’ ela= 55181 file#=6 block#=1030 blocks=4obj#=95742 tim=867071895617
WAIT #1: nam=’db file scattered read’ ela= 68932 file#=6 block#=1034 blocks=4obj#=95742 tim=867071965238
WAIT #1: nam=’db file scattered read’ ela= 67136 file#=6 block#=1038 blocks=4obj#=95742 tim=867072142114
We notice that Oracle is now only reading 4 blocks at a time, ensuring that the same 64K is read each time. So increasing the blocksize of an index doesn’t suddenly impact the manner or efficiency in which multi-block PIOs are performed.
Next, we’ll perform a similar test as before, comparing the difference between the 8K and 16K block index during an IFFS but this time with PIOs introduced. There are various ways one could do this. Make the buffer cache too small to fit an index or make the index too big for the buffer cache. Keeping with Robin’s demo, I’ll use exactly the same index definitions but this time run the following procedure in another session that constantly flushes the buffer caches. So in one session:
SQL> begin
2 for i in 1..10000 loop
3 execute immediate (‘alter system flush buffer_cache’);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Meanwhile in another session, run a number of IFFS (200 this time) with an 8K block size index:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 4851 consistent gets 1536024 physical reads 423992 index fast full scans (full) 3205
SQL> declare
2 v_count number;
3 begin
4 for i in 1..200 loop
5 select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:35.92
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 5248 consistent gets 1620224 physical reads 507651 index fast full scans (full) 3405
Note the response time is 35.92 secs and that we used approximately 3.97 secs. This time we have performed a how bunch of PIO operations.
Same thing, this time with the 16K index:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 6695 consistent gets 1789531 physical reads 633216 index fast full scans (full) 4205
SQL> declare
2 v_count number;
3 begin
4 for i in 1..200 loop
5 select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:36.59
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);
NAME VALUE ---------------------------- --------- CPU used by this session 7118 consistent gets 1831731 physical reads 674861 index fast full scans (full) 4405
We now note that the 16K block index has the slightly slower response time at 36.59 secs and uses a tad more CPU at 4.23 secs. As soon as we introduce PIOs, the PIOs contribute significantly to the overall costs and may well result in the larger block index being more costly depending on how the PIO is performed. So even in Robin’s example, depending on the nature of PIOs, even an IFFS can potentially run slower with an index in a larger block tablespace. Remembering of course that larger indexes are less likely to be fully cached and are more likely to incur PIOs during an IFFS.
Here’s a demo I’ve used before that uses a different approach to highlight the same issue (which doesn’t rely on a different session “interfering” with things). Another excellent example is this one by Greg Rahn on this OTN thread where he shows an IFFS performing a tad slower in a larger block tablespace. As I’ve been saying, just give it a go and see for yourself.
Perhaps, simply halving the consistent gets when doubling the index block size doesn’t tell the whole story …
The next time you see someone reference Robin’s demo as some sort of “proof” that by moving indexes into a larger block tablespace and (perhaps) reducing the number of consistent reads, the index will somehow be flatter and/or more efficient, just remember that the index may not necessarily be flatter, it might actually have the same height and that subsequent index operations, including index range scans and IFFS could very well be more expensive, not less expensive, to perform.
The next time you see someone claim that by moving indexes into a larger block tablespace, performance has suddenly improved by 100% or that things suddenly run 120 times faster, just ask one simple question. Why ?Ask what else has changed, what else might be contributing to the incredible performance improvements, because when you actually test things out for yourself, you’ll noticed results are not anywhere near as “impressive”.
Larger Block Tablespace For Indexes Revisted: Part II (Money) February 23, 2009
Posted by Richard Foote in Index Block Size, Index Height, Index Rebuild, Oracle Indexes, Oracle Myths.7 comments
In Part I I looked at Robin Schumacher’s “classic” example of the “so-called” benefits of rebuilding an index into a larger block tablespace. I started by highlighting that by simply rebuilding an index in a larger block tablespace and (say) halving the number of associated index blocks, it doesn’t necessarily mean the index will result in a “flatter structure” , that the index height will reduce. Robin’s demo is in fact a perfect example of this.
Let me start Part II by following this up with a little story …
“I went with a friend of mine to get some cash from the bank the other day in order to buy the latest David Bowie Box-Set. I got my cash in $50 notes but being an efficient, cost saving sort of bloke, my mate got out the same amount of cash in $100 notes. Although we both had the same amount of cash, his wallet was that little bit more compact and “efficient” than mine as he had less actual bank notes to carry.
However, when we got to the record store, we were surprised to discover that the actual “cost” of the David Bowie Box-Set was 2 bank notes of any domination, but with no change being given. Therefore, it cost me 2 x $50 notes to make my purchase. Unfortunately for my mate, it cost him 2 x $100 for the same thing as $100 notes were the smallest denomination he could use. Yes, I was a little bit mean not lending him some of my $50 notes but I was a little disappointed myself for not having any $5 notes on me at the time 😉 ”
OK, it’s not a perfect analogy but you perhaps get the point …
If you have to pay with a quantity of bank notes and you only have larger bank notes, you end up paying more than you would if you could only have paid with the same number of smaller denomination bank notes.
If you have to pay for an index scan in database blocks and you’re forced to use larger index blocks, you actually end up paying more if you have to read the same number of index blocks anyways.
This is one of the potential dangers with rebuilding indexes in a larger block tablespace. And like I said, Robin’s little demo is a perfect example of this. You might indeed reduce and halve the number of index blocks but this might not be sufficient to actually flatten the index structure and reduce the actual height of the index. The height of the index after rebuilding the index can remain the same, so the minimum cost of performing a small range scan increases. Even if you reduce the index height, you can still end up paying more if the savings do not compensate you enough for the additional overhead associated with now having to read and process larger index blocks.
Therefore, you potentially start paying more for smaller index range scans because you might not actually reduce the number of index blocks you visit for these types of index scans.
Taking exactly the same table/index definitions and data used to replicate Robin’s example in Part I, let’s see if there’s any difference in the costs associated with performing a number of small index range scans after rebuilding the index in a 16K block tablespace.
Again, it’s a simple case of just giving it a go and see for yourself. What resources are used if you perform a series of small index scans ? Do things really run faster ? Do we really use less resources ? Having rebuilt such an index in a larger block tablespace and halved the number of associated leaf blocks, are we really better off ?
Let’s begin by setting up the same example as Robin’s demo as before …
SQL> create table bowie (id number not null, value varchar2(10));
Table created.
SQL> insert into bowie select rownum, ‘BOWIE’ from dual connect by level <=187200;
187200 rows created.
SQL> commit;
Commit complete.
SQL> create index bowie_idx on bowie(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=>true, estimate_percent=>null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);
PL/SQL procedure successfully completed.
SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;
INDEX_NAME BLEVEL ---------- ------ BOWIE_IDX 1
SQL> analyze index bowie_idx validate structure;
Index analyzed.
SQL> select height, btree_space, used_space from index_stats;
HEIGHT BTREE_SPACE USED_SPACE ------ ----------- ---------- 2 3336032 2988168
Note the index height when built in an 8K block tablespace is 2 …
Let’s now capture the current amount of CPU used by the session:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);
NAME VALUE ------------------------ ---------- CPU used by this session 36555 consistent gets 22520369 physical reads 3750
Now we run a series of small index range scans …
SQL> set timing on
SQL> declare
2 v_id number;
3 v_value varchar2(10);
4 begin
5 for o in 1..10 loop
6 for i in 1..187200 loop
7 select id, value into v_id, v_value from bowie where id = i;
8 end loop;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:28.42
Let’s see how our resource stats have changed …
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);
NAME VALUE ------------------------ ---------- CPU used by this session 45346 consistent gets 28140519 physical reads 3750
We note we have used approximately 87.91 CPU seconds. (Note: You can run this a number of times and determine an average figure).
Let’s now rebuild the index again in a 16K block tablespace:
SQL> alter index bowie_idx rebuild tablespace ts_16k;
Index altered.
SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;
INDEX_NAME BLEVEL ---------- ------ BOWIE_IDX 1
SQL> analyze index bowie_idx validate structure;
Index analyzed.
SQL> select height, btree_space, used_space from index_stats;
HEIGHT BTREE_SPACE USED_SPACE ---------- ----------- ---------- 2 3351776 2985662
Note the index height remains at 2 …
If we run the same series of small index scans:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);
NAME VALUE ------------------------ ---------- CPU used by this session 45381 consistent gets 28142640 physical reads 3957
SQL> declare
2 v_id number;
3 v_value varchar2(10);
4 begin
5 for o in 1..10 loop
6 for i in 1..187200 loop
7 select id, value into v_id, v_value from bowie where id = i;
8 end loop;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:42.44
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);
NAME VALUE ------------------------ ---------- CPU used by this session 54484 consistent gets 33760690 physical reads 3957
We note that elapsed times have increased and we have now increased our overall CPU consumption to 91.03 CPU seconds as well.
As we can see, there has been no advantage with rebuilding the in index in the 16K block tablespace for these smaller index scans. In fact, there’s actually been an increase in the overall elapsed times and an increase in the overall CPU. Performance has not improved but has in fact worsened overall for these queries after rebuilding the index in a larger block tablespace.
You begin to get the point …
And of course, indexes are generally far more typically to be used in small index range scan operations than they are in performing Index Fast Full Index Scans. Just compare the numbers of index fast full scans vs. index fetch by key operations in your databases if you want some indication.
However, Robin’s specific example used a SQL statement that performed an Index Fast Full Scan. Surely, such operations would improve dramatically if indexes were only rebuilt in a larger block tablespace ? Surely such operations would be able to read data more quickly, especially if we have to go to disk, as we would be able to read more data with each associated multiblock read if the index were built in a larger block tablespace ? Surely it would be worth all the extra effort and management considerations ?
Things will run at least 2 times faster, maybe even 150 times faster, right 😉
We’ll see how the results can be a tad “disappointing” in the next post …
Larger Block Tablespace For Indexes Revisited: Part I (The Tourist) February 18, 2009
Posted by Richard Foote in Index Block Size, Index Height, Oracle Indexes, Oracle Myths.15 comments
I’ve previously discussed the various issues and myths relating to the so-called benefits of creating a separate, larger block tablespace for indexes and why it’s not recommended and generally a bad idea:
Store Indexes In A Larger Block Tablespace: Some Thoughts (Big Brother)
Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth (Karma Police)
Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth Part II (The Fly)
Store Indexes In A Larger Block Tablespace: Height Reduction 1/2 Myth (Five Foot One)
Larger Block Tablespace and Small Index Scans – Performance Improvement ? (Let Down)
However, some myths have a habit of lingering 😉
A recent question on reverse indexes (which could so easily have been answered by the person asking the question if they had only just “given it a go”) had me thinking that so many of these myths and misconceptions can be easily challenged and unproven.
Perhaps the most repeated example I’ve seen where the “so-called” benefits of moving indexes into larger block size tablespace is misunderstood is this one by Robin Schumacher. He demonstrates how by moving an index from an 8K block size to a 16K block size, “the amount of logical reads has been reduced in half”, with consistent gets reducing from 421 to 211, during an Index Fast Full Scan operation.
Sounds impressive, but only if one doesn’t understand what the numbers actually represent and if one doesn’t understand what Oracle actually does under the covers. In actual fact, the reduction in consistent gets in this specific example is somewhat meaningless …
Some folks even go on to say that “When can we “prove” a benefit from an index rebuild? Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure”.
So I thought I might demonstrate just how easy it is to “give it a go” and see for yourself whether or not these sorts of claims are actually true.
In Part I, I’m just going to focus on the specific claim that this example somehow proves indexes have a “flatter structure” when rebuilt in a larger block tablepsace. That by recreating an index in a larger block tablespace and halving the consistent gets from 421 to 211, the index will somehow have a “flatter structure” as result.
The key message I want to convey however is how easy it is to actually determine the accuracy of these sorts of claims yourself, simply by “giving it a go”. Trust but verify (or in some cases just verify). I’ll revisit some of the other misconceptions with these claims, such as why the reduction in consistent gets is not as impressive as it sounds, in later posts.
The first thing we need to do is reproduce the example and test the results for ourselves. Unfortunately we don’t have a script to reproduce the data used by Robin but we have enough clues at hand to reproduce the same demonstration. We need to basically create an index in an 8K block that performs 421 consistent gets during an Index Fast Full Scan when performing a count(*)SQL operation. So with a little bit of experimenting with different volumes of data, inserting 187,200 numbers into an index produced the necessary volume of data to replicate the scenario. Note: your mileage may vary slightly depending on database version, tablespace options, the specific query you use to test the results, etc.
SQL> create table bowie (id number not null, value varchar2(10));
Table created.
SQL> insert into bowie select rownum, ‘BOWIE’ from dual connect by level <=187200;
187200 rows created.
SQL> commit;
Commit complete.
SQL> create index bowie_idx on bowie(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=>true, estimate_percent=>null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);
PL/SQL procedure successfully completed.
As in Robin’s example, running the following simple count(*) SQL statement a couple of times to cache the data produced the following results:
SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;
Execution Plan ------------------------------------------- Plan hash value: 1410776261 ------------------------------------------- | Id | Operation | Name | ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FAST FULL SCAN| BOWIE_IDX | ------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 421 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
OK, so now we have an index that produces 421 consistent gets when performing an Index Fast Full Scan when performing a count(*) SQL operation.
Let’s now see look at the height and size of such an index …
SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;
INDEX_NAME BLEVEL ---------- ------ BOWIE_IDX 1
SQL> analyze index bowie_idx validate structure;
Index analyzed.
SQL> select height, btree_space, used_space from index_stats;
HEIGHT BTREE_SPACE USED_SPACE ------ ----------- ---------- 2 3336032 2988168
OK, so the index has a height of 2 (or a blevel of 1).
So would rebuilding such an index in a 16K block tablespace really give the index a “flatter structure” ? Will it really reduce the height of the index ? Well, let’s give it a go and see …
SQL> alter index bowie_idx rebuild tablespace ts_16k;
Index altered.
Let’s ensure our test case matches the one used by Robin and see if the number of consistent gets drops as expected by running the same select count(*) statement a number of times:
SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;
Execution Plan ---------------------------------------------------------- Plan hash value: 1410776261 ------------------------------- | Id | Operation | ------------------------------- | 0 | SELECT STATEMENT | | 1 | SORT AGGREGATE | | 2 | INDEX FAST FULL SCAN| ------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 211 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Indeed it does, consistent gets have indeed reduced from 421 down to 211, exactly as in Robin’s example. Exciting stuff !! Well, not really, I’ll demonstrate later why these numbers don’t actually mean as much as they appear…
Let’s see if indeed the index does have a “flatter structure” …
SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;
INDEX_NAME BLEVEL ---------- ------ BOWIE_IDX 1
SQL> select height, btree_space, used_space from index_stats;
HEIGHT BTREE_SPACE USED_SPACE ---------- ----------- ---------- 2 3351776 2985662
No !!
The index in the 16K block tablespace does not have a flatter structure. In fact the index has exactly the same height of 2 as it did previously and practically the same amount of index space.
As you can see, it’s very easy to give it a go, to test and validate these types of claims yourself. So no, even Robin’s “infamous” test case does not in fact “prove” indexes will have a “flatter structure” if rebuilt in a larger block tablespace. This is the first of the key points I want to get across. Just because you rebuild an index in a larger block tablespace, it doesn’t necessarily mean the index height will reduce or that the resultant index will have a flatter structure. In many many cases, depending on the size of the index and the increase in block size, the height of an index will not reduce at all. I’ve explained why this is the case in some detail in this previous post.
In fact, in some ways, the index height has now INCREASED, not decreased as a result of moving the index to a larger block size. The index had a “flatter structure” when it was in the 8K block tablespace than it does after it was rebuilt in the 16K block tablespace. Robin’s demo is actually a perfect example of this !!
Why ?
Well previously, we had an index structure that had a height of 2 with each “level” being 8K. Now we have an index structure that also has a height of 2 but each level now consists of 16K index blocks. Previously to perform an Index range scan we had to read at least 2 x 8K index blocks or 16K in total. Now we have to read at least 2 x 16K or 32K in total when performing an index range scan.
We’ve just rebuilt the index with larger sized blocks. Imagine a new building that has the same number of floors as the older building but each floor is now double the “size” (height) than it was previously. Although the new building is still a 2 storey building, the actual physical height of the building has just doubled …
And guess what ? For many many common queries and processes, there’s now potentially an additional overhead associated with having to always read in an index block that is double the size.
To be discussed next …
So When Does An Oracle B-Tree Index Increase In Height ? (Almost Grown) April 3, 2008
Posted by Richard Foote in Index Height, Index statistics, Oracle General, Oracle Indexes, Oracle Myths.60 comments
So when does an Oracle B-Tree index actually increase in height ?
I’ve basically been asked this same question a number of times over the past few days with regard to the discussions on indexes and different block sized tablespaces, so I thought it might be worth quickly sharing the answer to a wider audience.
Imagine a new, empty table and a corresponding new, empty index. At this stage, the index structure basically consists of one, empty block. The index has a BLEVEL of 0 (from DBA_INDEXES) and a HEIGHT of 1 (from INDEX_STATS), yes it can be confusing 😉 This block is basically the Root block of the index as it’s the first (and currently only) block to be accessed during an index scan, but at this stage is used to also store the actual index entries as well (and so can kinda be viewed as being a Leaf block as well).
We now start to insert rows into the table and thus row entries into the index. These index entries basically consist of the indexed column(s) and its corresponding ROWID, and are sorted based on the indexed column values.
Eventually, this single index block will fill; Oracle simply can’t add any more index entries into it. Now comes the fun bit.
When Oracle wants to insert a new index entry but it can’t as this Root index block is full, Oracle will allocate two new index blocks. If the new index entry is the maximum value currently to be indexed, Oracle will move all the index entries from the full block and put it into one of the new index blocks and place the new index entry into the other block. This is known as a 90-10 index block split.
If the new index entry isn’t the maximum value, Oracle will place the lower 1/2 valued index entries into one new block and the other 1/2 into the other new block. This is known as a 50-50 index block split.
These two new blocks are now the new leaf blocks in the index structure.
The contents of the previously single filled block is now totally replaced with pointers to the two new blocks. This block therefore remains the Root block in the index structure. These pointers basically consist of the Relative Block Address (RBA) to the new index blocks and a value which represents the lowest indexed value found in the specific referenced leaf block. These indexed values in the Root block are now used by Oracle as the method by which it can navigate the index structure to find the specific index leaf block containing a required indexed entry.
The index has just increased in height and now has a BLEVEL of 1 and a HEIGHT of 2.
As we continue to add more rows into the table, we add more index entries into our 2 leaf blocks. Eventually they will fill again and will again perform either a 90-10 or 50-50 block split depending on the new index value to be inserted. With a non Root block split, only one additional index block is allocated and the index entries are distributed between the full and new index block. Each time a leaf block splits in a BLEVEL 1 index, a new entry is also added into the Root block to point to the new Leaf block.
Once we have enough Leaf blocks, the Root block will again eventually fill. At this point, Oracle will again allocate two new blocks and distribute the contents of the Root block into these two new blocks, again 90-10 or 50-50 depending on the new indexed value to be inserted. The contents of the Root block is now totally replaced with pointers to these 2 new “Branch” blocks which of course in turn now contain the pointers to the Leaf blocks.
The index has again increased in height and we now have an index with a BLEVEL of 2 and a HEIGHT of 3.
As the leaf blocks continue fill and split, a new entry is added to the corresponding Branch block each time. When these Branch blocks fill and split, a new entry is added to the Root block. When the Root block eventually fills, it will again allocate 2 new blocks and so the index grows in height again.
So basically, an index increases in height whenever the index Root block splits and the two new allocated blocks result in a new level within the index structure. Note the index Root block remains the same throughout the entire life of the index, no matter the index height.
Note also a Root block split is the only time an index increases in height. Therefore, the number of levels between the Root block and any/all of the Leaf blocks is always and must always be the same. Hence, an Oracle B-Tree index is always structurally height balanced, always.
Store Indexes In A Larger Block Tablespace: Height Reduction 1/2 Myth (Five Foot One) March 26, 2008
Posted by Richard Foote in Index Block Size, Index Height, Index Internals, Oracle General, Oracle Indexes, Oracle Myths, Tablespace Management.14 comments
A common misconception with using a larger block tablespace specifically for indexes is that this will result in a reduction in the height of indexes and hence “flatten” index structures.
However, this is only partly true.
A few little generalisations to begin with.
First, most databases out there have a default block size of 8K. I won’t go into a big discussion on what the database block size should be set to (maybe another time), however I will say most databases these days have a default block size of 8K and that it’s certainly questionable to have the database block size set to 2K.
Note also in many common platforms (e.g. Windows, Linux) the maximum block size limit is 16K. So in many environments, when we talk about moving indexes into a bigger sized block, it specifically involves moving from an 8K to a 16K block size.
Yes, in theory you could move an index from (say) a 2K block size up as high as a 32K block size but you would need to question why the default is so low to begin with and whether the upper value is actually supported in your environment.
I make this point because the difference between block sizes makes a huge difference in the probability of the index height actually being reduced.
So let’s start with an example of moving indexes between an 8K default block size to a 16K block tablespace, not least because the arithmetic is easier and extrapolate out as we go along.
So in our example, the new block size is double or 2 times that of the default one. By doubling the block size, we effectively 1/2 the number of necessary leaf blocks in the index structure. By having fewer leaf blocks we also therefore reduce the overall associated block level overheads so the actual reduction in leaf blocks could be a tad more, but we’ll say a 1/2 reduction to keep the numbers nice and simple.
Note the reduction in leaf blocks in therefore simply 1 / the ratio of block increase (1/2). Moving from a 2K block to a 32K block is 16 times larger so we’ll have approximately 1/16 the number of leaf blocks.
So how does (say) halving the number of leaf blocks impact the overall height of the index ?
We obviously can’t reduce the height of an index with a height of just 1. The index consists of just the one block so a larger block would simply mean the block having more free space.
To reduce the height of an index with a height of 2 (back to 1), we therefore must be able to store all index entries within a single block. Therefore, in the 8K to 16K example, the index can only have 2 full leaf blocks for this to be possible. If an index has 3 or more “filled” leaf blocks, the index must remain at a height of 2 as we can’t fit all the index entries into the single larger index block.
Importantly therefore, all indexes with a height of 2 with more than 2 full leaf blocks would not reduce in height by simply doubling the block size. This could very well be the vast majority of indexes at this level.
For an index with a height of 2, the index must have less full leaf blocks than the ratio of block increase for a height reduction to be possible. In our best case scenario, 2K block to 32K block, any index with more than 16 full leaf blocks would not reduce in height.
To reduce the height of an index with a height of 3 (back to 2), we must therefore be able to store all intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks and 1/2 again the necessary branch blocks. Therefore the necessary branch blocks is 1/(2×2) = 1/4 that of the default block size. Therefore any index with a height of 3 that has more than 4 full intermediate branch blocks will again not reduce in height as again all the necessary branch information would not fit in one root block.
Importantly therefore, all indexes with a height of 3 with more than 4 full intermediate branch blocks would not reduce in height by simply doubling the block size. Again, this could very well be a significant proportion of all indexes at this level. Note also in many databases, the vast majority of indexes have a height of 3 or less so by simply doubling the index block size, most indexes would not reduce in height …
For an index with a height of 3, the index must have less full intermediate branch blocks than the ratio of block increase to the power of 2 for a height reduction to be possible. In our best case scenario, the 2K block to 32K block, only those indexes with more than 16×16=256 full intermediate branch blocks will reduce in height. This is therefore likely to be a far higher proportion of all such indexes.
You see the pattern …
To reduce the height of an index with a height of 4 (back to 3), we must therefore store all first level intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks, 1/2 again the second level intermediate branch blocks and 1/2 again the first level intermediate branch blocks. Therefore the necessary first level intermediate branch blocks is 1/(2x2x2) = 1/8 that of the default block size. Therefore any index with a height of 4 that has more than 8 full intermediate first level branch blocks would again not reduce in height as again all the necessary first level branch information would not fit in the one root block.
Importantly therefore, all indexes with a height of 4 with more than 8 full intermediate branch blocks will not reduce in height by simply doubling the block size. However, as the index height increases, the ratio of indexes where this is likely to be the case decreases.
For our best case scenario, 2K to 32K, we now start hitting very large numbers 16x16x16=4096 so the likelihood of a index height reduction is very very high.
And so on …
The important point being that by simply doubling the index block size, in most databases, the vast majority of indexes are actually quite unlikely to reduce in height as the index needs to be within very limited size boundaries for the index height to reduce. The greater the index height however, the greater the index size boundaries whereby an index height reduction is possible.
Also, the greater the index block increase, proportionally the fewer the index blocks and so greater the likelihood of an index height reduction.
This demo on the Impact Of Block Size On Index Height illustrates that by simply doubling the index block size, the height of an index (in various sizes) rarely decreases.
One final point. With our height 4 index example, note the index can only have a maximum of 8 first level branch blocks for the height to reduce. Therefore, in effect, we’re replacing a maximum of 9 x 8K branch blocks with 1 x 16K block. If this index is frequently accessed, these 9 branch blocks are likely cached and we only need to read two of these blocks anyways for an index range scan (for a total of 16K). After the rebuild, we still need to read this block (16K again) anyways so from a purely performance perspective with regard to just simply reducing the index height, the so-called performance benefits are often very much exaggerated.
As we’ll see in the next epic episode of this series, performance can actually decrease 😦
Next time someone claims moving indexes into a larger block size will decrease the height and flatten an index, remember it really does depend. In many databases, especially when the index block size is just doubled, it’s actually quite surprising just how unlikely it is for an index to actually decrease in height.
Index Internals – Rebuilding The Truth December 11, 2007
Posted by Richard Foote in Index Coalesce, Index Height, Index Internals, Index Rebuild, Index Shrink, Index statistics, Oracle Indexes, Oracle Myths, Oracle Opinion, Richard's Musings.14 comments
The issue of when to rebuild indexes crops up again and again and again. Unfortunately, so do the same incorrect, myth-filled uninspired responses which considering how important and “key” (no pun intended) indexes are to database design and performance generally, is very puzzling indeed.
In the coming days I’ll post why I believe these index related myths simply refuse to go away …
This presentation was originally written several years ago but is still as relevant today as it’s always been.
Recently updated version: Index Internals – Rebuilding The Truth
Richard Foote's Oracle Blog 