jump to navigation

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…

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part II ( Sleepwalk) September 21, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Data Skew, Dynamic Sampling, Exadata, Explain Plan For Index, Extended Statistics, Hints, Histograms, Index Access Path, Index statistics, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle19c, Performance Tuning.
1 comment so far

As I discussed in Part I of this series, problems and inconsistencies can appear between what the Automatic Indexing processing thinks will happen with newly created Automatic Indexing and what actually happens in other database sessions. This is because the Automatic Indexing process session uses a much higher degree of Dynamic Sampling (Level=11) than other database sessions use by default (Level=2).

As we saw in Part I, an SQL statement may be deemed to NOT use an index in the Automatic Indexing deliberations, where it is actually used in normal database sessions (and perhaps incorrectly so). Where the data is heavily skewed and current statistics are insufficient for the CBO to accurately detect such “skewness” is one such scenario where we might encounter this issue.

One option to get around this is to hint any such queries with a Dynamic Sampling value that matches that of the Automatic Indexing process (or sufficient to determine more accurate cardinality estimates).

If we re-run the problematic query from Part I (where a new Automatic Index was inappropriately used by the CBO) with such a Dynamic Sampling hint:

SQL> select /*+ dynamic_sampling(11) */ * from iggy_pop where code1=42 and code2=42;

100000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3288467

--------------------------------------------------------------------------------------
| Id | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |          |  100K|  2343K|    575 (15)| 00:00:01    |
|* 1 | TABLE ACCESS STORAGE FULL| IGGY_POP |  101K|  2388K|    575 (15)| 00:00:01    |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE1"=42 AND "CODE2"=42)
    filter("CODE1"=42 AND "CODE2"=42)

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      40964 consistent gets
      40953 physical reads
          0 redo size
    1092240 bytes sent via SQL*Net to client
        609 bytes received via SQL*Net from client
         21 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
     100000 rows processed

We can see that the CBO this time correctly calculated the cardinality and hence correctly decided against the use of the Automatic Index.

Although these parameters can’t be changed in the Oracle Autonomous Database Cloud services, on the Exadata platform if using Automatic Indexing you might want to consider setting the OPTIMIZER_DYNAMIC_SAMPLING parameter to 11 (and/or OPTIMIZER_ADAPTIVE_STATISTICS=true)  in order to be consistent with the Automatic Indexing process. These settings can obviously add significant overhead during parsing and so need to be set with caution.

In this scenario where there is an inherent relationship between columns which the CBO is not detecting, the creation of Extended Statistics can be beneficial.

We currently have the following columns and statistics on the IGGY_POP table:

SQL> select column_name, num_distinct, density, num_buckets, histogram
from user_tab_cols where table_name='IGGY_POP';

COLUMN_NAME          NUM_DISTINCT    DENSITY NUM_BUCKETS HISTOGRAM
-------------------- ------------ ---------- ----------- ---------------
ID                        9705425          0         254 HYBRID
CODE1                         100  .00000005         100 FREQUENCY
CODE2                         100  .00000005         100 FREQUENCY
NAME                            1 5.0210E-08           1 FREQUENCY

 

If we now collect Extended Statistics on both CODE1, CODE2 columns:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'IGGY_POP', method_opt=> 'FOR COLUMNS (CODE1,CODE2) SIZE 254');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density, num_buckets, histogram from user_tab_cols where table_name='IGGY_POP';

COLUMN_NAME                    NUM_DISTINCT    DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
ID                                  9705425          0         254 HYBRID
CODE1                                   100  .00000005         100 FREQUENCY
CODE2                                   100  .00000005         100 FREQUENCY
NAME                                      1 5.0210E-08           1 FREQUENCY
SYS_STU#29QF8Y9BUDOW2HCDL47N44           99  .00000005         100 FREQUENCY

 

The CBO now has some idea on the cardinality if both columns are used within a predicate.

If we re-run the problematic query without the hint:

 

SQL> select * from iggy_pop where code1=42 and code2=42;

100000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3288467

--------------------------------------------------------------------------------------
| Id | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |          |  100K|  2343K|    575 (15)| 00:00:01    |
|* 1 | TABLE ACCESS STORAGE FULL| IGGY_POP |  100K|  2343K|    575 (15)| 00:00:01    |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE1"=42 AND "CODE2"=42)
    filter("CODE1"=42 AND "CODE2"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      40964 consistent gets
      40953 physical reads
          0 redo size
    1092240 bytes sent via SQL*Net to client
        581 bytes received via SQL*Net from client
         21 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
     100000 rows processed

 

Again, the CBO is correctly the cardinality estimate of 100K rows and so is NOT using the Automatic Index.

However, we can still get ourselves in problems. If I now re-run the query that returns no rows and was previously correctly using the Automatic Index:

SQL> select code1, code2, name from iggy_pop where code1=1 and code2=42;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3288467

--------------------------------------------------------------------------------------
| Id | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time       |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |          | 50000 |  878K |   575 (15) | 00:00:01   |
|* 1 | TABLE ACCESS STORAGE FULL| IGGY_POP | 50000 |  878K |   575 (15) | 00:00:01   |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE1"=1 AND "CODE2"=42)
    filter("CODE1"=1 AND "CODE2"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      40964 consistent gets
      40953 physical reads
          0 redo size
        368 bytes sent via SQL*Net to client
        377 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

We see that the CBO is now getting this execution plan wrong and is now estimating incorrectly that 50,000 rows are to be returned (and not the 1000 rows it estimated previously). This increased estimate is now deemed too expensive for the Automatic Index to retrieve and is now incorrectly using a FTS.

This because with a Frequency based histogram now in place, Oracle assumes that 50% of the lowest recorded frequency within the histogram is returned (100,000 x 0.5 = 50,000) if the values don’t exist but resided within the known min-max range of values.

So we need to be very careful HOW we potentially collect any additional statistics and its potential impact on other SQL statements.

 

As I’ll discuss next, another alternative to get more consistent behavior with Automatic Indexing in these types of scenarios is to make the Automatic Indexing processing session appear more like other database sessions…

Unique Indexes Force Hints To Be “Ignored” Part II (One Of The Few) February 19, 2019

Posted by Richard Foote in CBO, Hash Join, Hints, Oracle Indexes, Transitive Closure, Unique Indexes.
5 comments

Final Cut

In Part I, I showed a demo of how the introduction of a Unique Index appears to force a hint to be “ignored”. This is a classic case of what difference a Unique Index can make in the CBO deliberations.

So what’s going on here?

When I run the first, un-hinted query:

SQL> select * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;
we notice something a little odd in the Predicate Information section of the execution plan:
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BOWIE1"."ID"=1)
6 - access("BOWIE2"."CODE"=1)
Where the hell is the join condition, it’s not listed? Additionally, where does the BOWIE2.CODE=1 condition come from, it’s not a predicate within the above SQL statement?
The answer is “Transitive Closure“, whereby the CBO can automatically infer that BOWIE2.CODE must equal 1, if BOWIE2.CODE=BOWIE1.ID and BOWIE1.ID=1. This is something that the CBO master Jonathan Lewis has blogged about a number of times, including this post on Cartesian Merge Join.
Because the CBO is picking up the fact (based on the column statistics) that BOWIE1.ID is basically a unique column, it can effectively drop the join condition and simply use a  Merge Join Cartesian to get all rows from BOWIE1 that match the BOWIE1.ID=1 predicate (just 1 row), with all those rows from BOWIE2 that match the BOWIE2.CODE=1 predicate (estimated 50 rows).
-------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |   Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  1 | MERGE JOIN CARTESIAN                |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |   00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |   00:00:01 |
|  4 | BUFFER SORT                         |               |   50 |   350 |       3 (0) |   00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |   00:00:01 |
|* 6 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |   00:00:01 |
-------------------------------------------------------------------------------------------------------
The CBO is picking up the fact that a Merge Join Cartesian is not as bad as it might sound if only 1 row is likely to be returned from one side of this process that can be combined with just the rows of interest from the other table, with no unnecessary row throwaways.
However, the CBO might not get this right, the efficiency of this depends somewhat on there really only being the one row returned from the BOWIE1.ID=1 condition. If there were many more than one row possible, then this can become relatively inefficient.
The second hinted query is therefore directing Oracle to perform a Hash Join, as I potentially know my data better than Oracle and think it a better option in this case:
SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;
The hint is directing Oracle to access the BOWIE2 table to be the probe table in a Hash Join operation.
We notice that the join predicate is now listed in the Predicate Information of the execution plan:
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BOWIE1"."ID"="BOWIE2"."CODE")
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)
The key point being that there could be a many to many join operation that needs to be performed and Oracle can’t perform a Hash Join unless there is a join predicate listed.
As such, the CBO uses a Hash Join as requested by the hint:
------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |  Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |  00:00:01 |
|* 1 | HASH JOIN                           |               |   50 |  1150 |       5 (0) |  00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |  00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |  00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |  00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------------
Note currently, there is no Unique constraint on the BOWIE1.ID column and the index on BOWIE1.ID is non-unique. Although the column statistics suggests the ID is basically unique (the number of distinct values matches the number of rows in the table), there is no certainly that this is correct. The statistics might not be accurate and there could be a bunch of duplicate IDs that would result in a many to many join operation whereby a Hash Join might be preferable.
But by replacing the non-unique index on BOWIE1.ID with a unique index, the CBO now knows there is indeed just the one viable row from the BOWIE1 side of the join, with the BOWIE1.ID=1 predicate. As such, the CBO goes back to using Transitive Closure to again effectively eliminate the join predicate.

 

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)
The CBO can now safely get just the required row from BOWIE1 table via the BOWIE1.ID=1 predicate and the required data from BOWIE2 directly via the BOWIE2.CODE=1 predicate. The CBO makes this decision before considering the most appropriate join strategy, which can now not possibly be the Hash Join, as the Hash Join is only possible with a join predicate in place.
------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  1 | NESTED LOOPS                        |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID         | BOWIE1        |    1 |    16 |       2 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN                   | BOWIE1_ID_I   |    1 |       |       1 (0) | 00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------
As such, the USE_HASH hint is now “ignored”, because it’s simply now not a viable option for the CBO. A Nested Loop is now performed instead, in which the row for the Outer Table (BOWIE1) can be retrieved and all “corresponding” rows for the Inner Table (BOWIE2) can be likewise accessed via just the BOWIE2.CODE=1 predicate.
A Nested Loop is the join type you can have when you’re not necessarily performing a join…

Unique Indexes Force Hints To Be “Ignored” Part I (What’s Really Happening) February 5, 2019

Posted by Richard Foote in CBO, Hash Join, Hints, Non-Unique Indexes, Oracle Indexes, Unique Indexes.
2 comments

hours album

As I was compiling my new “Oracle Diagnostics and Performance Tuning” seminar, I realised there were quite a number of indexing performance issues I haven’t discussed here previously.

The following is a classic example of what difference a Unique Index can have over a Non-Unique index, while also covering the classic myth that Oracle sometimes chooses to “ignore” hints that might be present in your SQL.

To set the scene, I’m going to create two simple little tables, but importantly initially create only non-unique indexes for columns of interest (Note: I’ve had to remove the “<” in the “<=” predicate when populating the table to avoid formatting issues):

SQL> create table bowie1 as
select rownum id, 'David Bowie' name from dual connect by level = 1000;

Table created.

SQL> create table bowie2 as
select rownum id, mod(rownum,20)+1 code from dual connect by level = 1000;

Table created.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=>'BOWIE1', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=>'BOWIE2', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> create index bowie1_id_i on bowie1(id);

Index created.

SQL> create index bowie2_id_i on bowie2(id);

Index created.

SQL> create index bowie2_code_i on bowie2(code);

Index created.

I’m now going to run the following query which does a simple join between the two tables and filters on the ID column from the BOWIE1 table:

 

SQL> select * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4266778954

-------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |   Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  1 | MERGE JOIN CARTESIAN                |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |   00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |   00:00:01 |
|  4 | BUFFER SORT                         |               |   50 |   350 |       3 (0) |   00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |   00:00:01 |
|* 6 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |   00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("BOWIE1"."ID"=1)
6 - access("BOWIE2"."CODE"=1)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   8 consistent gets
   0 physical reads
   0 redo size
1815 bytes sent via SQL*Net to client
 641 bytes received via SQL*Net from client
   5 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
  50 rows processed

 

The query uses a MERGE JOIN which I (incorrectly) think is a concern and decide that a HASH JOIN should be a better option. So I now put in a basic USE_HASH hint:

SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1413846643

------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |  Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |  00:00:01 |
|* 1 | HASH JOIN                           |               |   50 |  1150 |       5 (0) |  00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |  00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |  00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |  00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("BOWIE1"."ID"="BOWIE2"."CODE")
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  15 consistent gets
   0 physical reads
   0 redo size
1815 bytes sent via SQL*Net to client
 641 bytes received via SQL*Net from client
   5 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  50 rows processed

And the hint has worked as I had hoped.

I then decide that perhaps a Unique Index on the ID column might be a good idea (perhaps because I read up on all the advantages on Unique Indexes in this blog). So I drop and recreate the index as a Unique Index:

SQL> drop index bowie1_id_i;

Index dropped.

SQL> create unique index bowie1_id_i on bowie1(id);

Index created.

I now re-run my hinted query to again use the Hash Join:

SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4272245076

------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  1 | NESTED LOOPS                        |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID         | BOWIE1        |    1 |    16 |       2 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN                   | BOWIE1_ID_I   |    1 |       |       1 (0) | 00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  15 consistent gets
   0 physical reads
   0 redo size
1815 bytes sent via SQL*Net to client
 641 bytes received via SQL*Net from client
   5 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  50 rows processed

And we notice the hint is now being “ignored”. The hint isn’t really ignored, it’s just no longer relevant to how the CBO has now constructed the query and associated plan with the Unique Index now making a key difference (no pun intended).

I’ll discuss in Part II why the Unique Index has made such a difference and why the hint is no longer viable.

Of course, to learn all this and a lot lot more, you can always attend my new Oracle Diagnostics and Performance Tuning” seminar one day 🙂