jump to navigation

Merry Christmas and Happy New Year!! (“The Jean Genie”) December 22, 2022

Posted by Richard Foote in Christmas, David Bowie, Richard's Blog, Richard's Musings, The Jean Genie.
1 comment so far

I would like to take this opportunity to wish all my readers who celebrate the festive season a very Merry Christmas and a most happy, peaceful and prosperous New Year.

2023 promises to be a big year for me, in that I’ll be officially retiring from work early in the year, after close to 40 years working in IT. I’ve had an absolute blast and met and worked with some truly amazing people, but it’s time to move on and enjoy other things that life has to offer.

But I’ll hopefully keep my hand in enough to perhaps attend the odd Oracle conference during the year and say my farewells and have a quiet drink with as many of my many Oracle-related friends and colleagues as I can. So you haven’t perhaps heard the last from me quite yet.

My Christmas gift this year is of course David Bowie related. 50 years ago, Bowie had a huge hit in the UK (and elsewhere) with the glam rock classic that is “The Jean Genie”. 50 years ago to the day, it was sitting in the UK 1972 Christmas charts at No. 16, but would reach as far as No. 2 in the coming weeks.

This is the official video, directed by the late, great Mick Rock. Enjoy !!

 

When Does A ROWID Change? Part IV (“Mass Production”) December 21, 2022

Posted by Richard Foote in Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Clustering Factor, Data Clustering, Flashback, Move Partitions, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Partitioning, Richard's Blog, ROWID.
add a comment

In Part II in this series, I discussed how the update of the partitioned key column of a row that results in the row being moved to a different partition, will result in the ROWID of such rows changing.

However, there a quite a number of other user initiated actions in which ROWIDs can easily change (as indeed discussed in Connor McDonald’s video on this subject).

Some of these include:

  • Moving a table or partition, as this results in the segment being reorganised, with all associated rows being physically relocated and their associated ROWIDs changing
  • Altering a non-partitioned table such that it be now be partitioned, which again results in the physical relocation of all rows and their ROWIDs changing (which BTW, can potentially occur on a Autonomous Database without any user intervention)
  • Altering the partitioning strategy of a partitioned table, again changes the physical location of all rows
  • Hybrid Columnar Compression (HCC), which by packing rows more tightly, can more likely result in the physical relocation of a row during subsequent DML statements
  • Altering a table to Shrink Space, which attempts to move rows between table blocks to pack rows more tightly, again potentially resulting in rows physically moving and the changing of their associated ROWIDs
  • Flashback of a table, which results in rows being deleted and inserted and hence the change of their associated ROWIDs

I’ll illustrate an example of all this, with one of the key reasons why you may want to re-organise a table (and implicitly change all the ROWIDs of a table).

I’ll start by creating and populating a simple little table, with a CODE column that has very poorly clustered data:

SQL> create table bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum, 500), 'DAVID BOWIE' from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

Let’s now create an index on this CODE column:

SQL> create index bowie_code_i on bowie(code);

Index created.

We take note of the ROWIDs of a few random rows:

SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;

        ID ROWID
---------- ------------------
        42 AAASn1AAMAAAgB2AAp
      4242 AAASn1AAMAAAgCHACL
    424242 AAASn1AAMAAAgbtAAJ

If we run a simple query with a predicate based on the CODE column:

SQL> select * from bowie where code=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 2000 | 42000 |    1004 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE | 2000 | 42000 |    1004 (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       3596 consistent gets
          0 physical reads
          0 redo size
      20757 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

We notice the CBO has chosen to ignore the index and use a FTS instead, even though only 2000 rows in a 1M row table (just 0.2%) are returned.

Why?

Because the clustering of the CODE data is terrible, with the required values littered throughout the table. If we look at the Clustering Factor of the index:

SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I';

INDEX_NAME   LEAF_BLOCKS CLUSTERING_FACTOR
------------ ----------- -----------------
BOWIE_CODE_I        2063           1000000

We notice the index has the worst possible Clustering Factor value of 1000000.

So to improve the performance of this (say critical) query, we can add a Clustering Attribute to this table based on the CODE column and then reorganise the table:

SQL> alter table bowie add clustering by linear order (code);

Table altered.

SQL> alter table bowie move online;

Table altered.

If we now look at the Clustering Factor of the index:

SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I';

INDEX_NAME   LEAF_BLOCKS CLUSTERING_FACTOR
------------ ----------- -----------------
BOWIE_CODE_I        2063              3568

We can see it has substantially improved, down to just 3568 from the previous 1000000 value, as the data is now perfectly clustered based on the CODE column.

If we now re-run the query:

SQL> select * from bowie where code=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              | 2000 | 42000 |      15 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        | 2000 | 42000 |      15 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | BOWIE_CODE_I | 2000 |       |       7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("CODE"=42) 

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         17 consistent gets
          0 physical reads
          0 redo size
      50735 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

The CBO now choses to use the index and the query is much more efficient as a result (consistent gets down to just 17 from the previous 3596).

So all is now much better, except for any application that was reliant on using ROWIDs to fetch the data, as all ROWIDs have now changed:

SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;

        ID ROWID
---------- ------------------
        42 AAASn6AAMAAAACvAEf
      4242 AAASn6AAMAAAiRaAA4
    424242 AAASn6AAMAAAiRWAEQ

So there are many ways in which the ROWID of a row can potentially change.

And now there’s another key manner in which a ROWID can very easily change in Oracle Autonomous Database environments, as I’ll next discuss…

When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”) December 13, 2022

Posted by Richard Foote in Autonomous Database, Changing ROWID, Index Internals, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Partitioning, Performance Tuning, Richard's Blog, ROWID, Secondary Indexes.
2 comments

In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.

One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.

To illustrate, I’m going to create and populate another simple little Partitioned Table:

SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;

Table created.

SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE2');

PL/SQL procedure successfully completed.

I’m now going to create a number of basic Global indexes on this table:

SQL> create index big_bowie2_id_i on big_bowie2(id);

Index created.

SQL> create index big_bowie2_code1_i on big_bowie2(code1);

Index created.

SQL> create index big_bowie2_code2_i on big_bowie2(code2);

Index created.

SQL> create index big_bowie2_code3_i on big_bowie2(code3);

Index created.

SQL> create index big_bowie2_code4_i on big_bowie2(code4);

Index created.

SQL> create index big_bowie2_code5_i on big_bowie2(code5);

Index created.

SQL> create index big_bowie2_code6_i on big_bowie2(code6);

Index created.

SQL> create index big_bowie2_code7_i on big_bowie2(code7);

Index created.

SQL> create index big_bowie2_code8_i on big_bowie2(code8);

Index created.

SQL> create index big_bowie2_code9_i on big_bowie2(code9);

Index created.

SQL> create index big_bowie2_code10_i on big_bowie2(code10);

Index created.

If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:

SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    16 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    16 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

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

We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.

If we now run a single row update of an indexed column:

SQL> update big_bowie2 set code1=42 where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |     8 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |     8 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

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

We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.

If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:

SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    64 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    64 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
         49 db block gets
          3 consistent gets
          0 physical reads
       5996 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
          1 rows processed

We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.

So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.

Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…

UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…

When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”) December 9, 2022

Posted by Richard Foote in Autonomous Database, CBO, Changing ROWID, Global Indexes, Multiple Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Partitioning, Performance Tuning, Richard's Blog, ROWID.
3 comments

In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change.

However, even before the changes now present with Oracle Autonomous Databases (to be discussed in future posts), there has always been (since Oracle 8) one classic scenario when this “ROWID never changes after an update” rule has not been true.

To illustrate, I’m going to create and populate a basic little Range-based Partitioned table, with the RELEASE_DATE column being the partitioned column:

SQL> CREATE TABLE big_bowie(id number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, sysdate-mod(rownum,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');

PL/SQL procedure successfully completed.

Let’s look at the current ROWIDs of a few random rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 08-OCT-21 AAASe9AAMAAAAj7ABU
       444 18-SEP-21 AAASe9AAMAAAAj7ABo
       482 11-AUG-21 AAASe9AAMAAAAj7ACO

I’m now going to try and update for these rows, the partitioned column value, such that they would now logically belong in the other partition:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);
update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482)
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

I now get a very key and important error. By default, Oracle does not allow you to update a row if it results in the row having to physically move to a different partition.

I suspect there are at least 3 good reasons for this default restriction.

One is to protect the business integrity of the data, where it might just not make any business sense for a row to be updated in this manner.

The second is that it protects any applications out there that explicitly uses ROWIDs and relies on the ROWIDs not suddenly changing behind the scenes.

And finally, it protects perhaps valuable database resources and ensures that the database does not have to incur any additional workloads, that would be necessary if such an operation were to proceed.

But we have the ability and control to override this default behaviour in the following manner with the ENABLE ROW MOVEMENT clause:

SQL> alter table big_bowie enable row movement;

Table altered.

If we now try and update these rows again:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);

3 rows updated.

SQL> commit;

Commit complete.

The updates are now successful.

As these rows no longer logically belong in the previous partition, they have to be physically moved to its new partition. This is effectively implemented by deleting the rows from the previous partition and then re-inserting them in the new partition segment.

If we now look the ROWIDs of these updated rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 06-DEC-22 AAASe+AAMAAAATQABR
       444 06-DEC-22 AAASe+AAMAAAATQABS
       482 06-DEC-22 AAASe+AAMAAAATQABT

We notice that they now all have different ROWIDs, because they indeed now all exist in a different physical location.

In my next post, I’ll highlight but one obvious disadvantage and consequence of allowing rows to be physically moved in this manner…

When Does A ROWID Change? Part I (“Fearless”) December 7, 2022

Posted by Richard Foote in Autonomous Database, Block Dumps, Changing ROWID, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Table Internals, Oracle19c, Pink Floyd, Richard's Blog, ROWID.
3 comments

Recently, my mate Connor McDonald caused a tad of a storm when he disclosed that the once sacred, (almost) unchangeable ROWID can now indeed potentially easily change, without the DBA doing a thing.

You can watch his excellent video on the subject here.

As the humble ROWID is a critical component of any index, I thought it worthwhile to have a deep dive discussion on when a ROWID can and can’t change and some of the key changes that have been introduced within Oracle’s Autonomous database environments.

If you’re a developer who explicitly uses the ROWID in your applications, you might want to pay extra attention to these changes.

I thought I’ll begin first though by discussing how the ROWID currently doesn’t generally change…

The ROWID is basically just a pointer stored in indexes that effectively points to the physical location of a row within a table that’s associated with the specific indexed key. It consists of the Data Object ID (if it’s a Global Index associated to a Partitioned Table, as the Relative File ID is no longer a unique value and so needs this to determine the appropriate tablespace), a Relative File ID, a Data Block ID within the Relative File and the Row Location ID within the Data Block.

Now although it has never been a completely risk free approach to manually store and directly use these ROWIDs to fetch a row from a table, it’s a technique that has been frequently used by developers for 3 very good reasons.

The first reason is that it’s one of the most efficient ways to fetch a required row, because the database can go directly to the physical location and directly access the required row, without having to even read a single index block.

The second reason is because it has always been fully supported by Oracle to do so, with the required syntax well documented. Indeed, Oracle APEX includes the base functionality to store and access rows directly via their associated ROWIDs.

The third reason is because it’s generally well understood that the ROWID doesn’t change, except for a very few (generally) well known scenarios, and so using the ROWID to access data within an application is viewed as being reasonable safe.

When a row is inserted into a table, each corresponding index on the table (generally) has a new index entry also inserted, including its associated ROWID. Now, if the row never moves from its current physical location, there is no need to ever worry about the ROWID subsequently changing.

So the big question is, when can a row physically move AND the associated ROWID change?

Logically, a scenario that springs to mind is when a row is updated and made bigger and there’s no longer room within the current data block to store the larger row. Does the row move to another block with sufficient free space and result in the ROWID to change?

To check out this scenario, I’ll create and populate a basic table, with PCTFREE set to 0, so once the blocks within the table are filled, there is precious little space for rows to subsequently grow:

SQL> create table bowie (id number, name varchar2(142)) pctfree 0 enable row movement;

Table created.

SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_id_i on bowie(id);

Index created.

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

PL/SQL procedure successfully completed.

 

For good measure, I’ve also included the ENABLE ROW MOVEMENT clause, which is usually associated with Partitioned Tables (as I’ll discuss in Part II in this series).

Let’s have a look at the ROWIDs of a few random rows:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

If we look at a partial block dump of one of the table blocks:

tab 0, row 0, @0x1312
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 42 4f 57 49 45
tab 0, row 1, @0x131e
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 42 4f 57 49 45
tab 0, row 2, @0x132a
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 5] 42 4f 57 49 45
tab 0, row 3, @0x1336
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 05
col 1: [ 5] 42 4f 57 49 45
tab 0, row 4, @0x1342
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06
col 1: [ 5] 42 4f 57 49 45

The above partial block dump shows the first 5 rows within the block, with the contents of the 2 table columns listed (in hex format).

If we access a row via an index:

SQL> select * from bowie where id=42;

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
--------------------------------------------------------------------------------------------------
|  Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |    1 |    66 |       2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    66 |       2 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |    1 |       |       1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("ID"=42)

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

We can see in this example that Oracle requires 4 consistent gets, 3 of which are accesses to the index.

If we access this table via its ROWID:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

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

We can see that this only requires just the 1 consistent get (vs. 4 when using the index) to access the row.

So we can see the appeal of using the ROWID to access a row.

If we now update the rows within the table and make them substantially larger so they can no longer fit within the currently filled blocks:

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

10000 rows updated.

SQL> commit;

Commit complete.

And now look again at the ROWIDs of these selected rows:

SQL> select id, rowid from bowie where id in (42, 424, 4242) order by id;

ID         ROWID
---------- ------------------
42         AAASe4AAMAAAACHAAp
424        AAASe4AAMAAAACHAGn
4242       AAASe4AAMAAAACNAHV

We notice that the ROWIDs all remain the same.

This has always historically been the behaviour here. If we update a row and the updated row can no longer fit within the current block, the row “migrates” to another table block with sufficient free space. BUT the associated ROWIDs do NOT change and the associated indexes are NOT updated.

Rather, the row data within the updated table is replaced with a “pointer”, that points to the new physical location of the migrated row. The advantage here being that Oracle only has to update the table with this new pointer, rather having to update the associated ROWIDs of all the (possibly many) associated indexes (noting that such an update would actually result in a delete followed by a re-insert of each index entry).

The disadvantage of course is that to now access this migrated row via an index requires an extra hop, to first read the initial table block and then to follow the pointer and access the actual block that now contains the row. Note if this row is forced to be migrated again because it grows again and can’t be housed in the current block, this pointer in the initial block is updated to reflect the newer location, so at least there is only ever the one extra hop.

If we look at a new partial block dump of the previously accessed block:

tab 0, row 0, @0x1f8f
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4b
tab 0, row 1, @0x1f86
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4c
tab 0, row 2, @0x1f7d
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4d
tab 0, row 3, @0x1f74
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000bf.4e
tab 0, row 4, @0x1f6b
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x030000b8.0

We notice that the rows with their 2 columns have been replaced with a logical nrid pointer (consisting of a relative block address and row location within the block), that effectively points to the new physical location of the row.

Note we can still use the same, unchanged ROWID to access the same table rows:

SQL> select * from bowie where rowid='AAASe4AAMAAAACHAAp';

ID         NAME
---------- --------------------------------------------------------------
42         THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |       |    1 |    10 |       1 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY USER ROWID | BOWIE |    1 |    10 |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------

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

This ROWID access still works fine, except it has now increased to 2 consistent gets, one to access the initial block referenced by the ROWID and the extra consistent get to follow the pointer and access the new physical location of the row.

So historically, we haven’t had to worry about updates changing the ROWID of a row (except perhaps at looking at reducing the number of these migrated rows).

Well, except for one clear example as I’ll discuss in Part II

Automatic Indexing: Potential Locking Issues Part II (“Don’t Stop”) December 5, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes.
add a comment

In my previous post, I highlighted how a long transaction can potentially cause the creation of an Automatic Index to hang due to the inability of the Automatic Indexing process to obtain the necessary locks.

However, these locks can have a much wider consequence, as it’s the entire Automatic Indexing process that is forced to hang, not just the creation of a specific index. This is due to the fact that Automatic Indexing works in a serial fashion, working on one index at a time, in order to put the brakes on the amount of resources that Automatic Indexing can potentially consume.

Therefore, it’s not just the creation of the specifically locked automatic index that is impacted, but the subsequent creation of all Automatic Indexes. No other Automatic Index can be created until the locking issue is resolved.

To highlight, I’m going to create and populate other table:

SQL> create table david_bowie (id number, code number, name varchar2(42));

Table created.

SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID_BOWIE');

PL/SQL procedure successfully completed.

I’ll next run an SQL several times that is forced to perform a Full Table Scan because of a missing index:

SQL> select * from david_bowie where code=42; 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1390211489 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 230 | 6714 (2)| 00:00:01 | | * 1 | TABLE ACCESS FULL | DAVID_BOWIE | 10 | 230 | 6714 (2)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("CODE"=42) filter("CODE"=42) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 48130 consistent gets 38657 physical reads 0 redo size 885 bytes sent via SQL*Net to client 52 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed However, if we look at the current Automatic Indexing report: SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 01-DEC-2022 07:12:31 Activity end : 05-DEC-2022 12:15:42 Executions completed : 0 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- ERRORS -------------------------------------------------------------------------------- ------------- No errors found. -------------------------------------------------------------------------------- -------------

 

We can see that the Automatic Indexing process is STILL hanging days later from the still uncommitted transaction. Therefore, it’s impossible for an Automatic Index to be created for this new workload, or indeed ANY new workload, until the locking issue is resolved, with the completion of the associated locking transaction.

We can easily see the troublesome lock:

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE   MODE_HELD MODE_REQUESTED   LOCK_ID1   LOCK_ID2
--------------- -------------- --------------- -------------- ----------- --------- -------------- ---------- ----------
            164              3             167              3 Transaction Exclusive Share              327694      10623

 

As a consequence, no new Automatic Index can be created for this new workload:

SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks
from user_indexes where table_name='DAVID_BOWIE';

no rows selected

And the existing workload remains inefficient:

SQL> select * from david_bowie where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

---------------------------------------------------------------------------------
|  Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | DAVID_BOWIE |   10 |   230 |    6714 (2)| 00:00:01 |
---------------------------------------------------------------------------------

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

    1 - storage("CODE"=42)
        filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48130 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

Once the locking transaction is finally completed:

SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust');

1 row created.

SQL> commit;

Commit complete.

The Automatic Indexing process can again resume and the new Automatic Indexes can finally be created as necessary:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 05-DEC-2022 12:30:30
Activity end                : 05-DEC-2022 12:31:22
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                      : 0
Indexes created (visible / invisible) : 2 (0 / 2)
Space used (visible / invisible)      : 287.31 MB (0 B / 287.31 MB)
Indexes dropped                       : 0
SQL statements verified               : 3
SQL statements improved               : 0
SQL plan baselines created            : 0
Overall improvement factor            : 1x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner | Table       | Index                | Key  | Type   | Properties |
---------------------------------------------------------------------------
| BOWIE | BOWIE_BUSY  | SYS_AI_8pkdh6q096qvs | CODE | B-TREE | NONE       |
| BOWIE | DAVID_BOWIE | SYS_AI_czmkjhqr21732 | CODE | B-TREE | NONE       |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------

 

If you find that the Automatic Indexing process has hung, check to make sure there are no long locks on associated underlying tables that could be causing the whole Automatic Index process to freeze…

 

NOTE: This post is dedicated to the memory of Christine McVie, who recently passed away…

Automatic Indexing: Potential Locking Issues Part I (“Rattle That Lock”) December 1, 2022

Posted by Richard Foote in 19c, Automatic Indexing, Autonomous Database, Exadata, Full Table Scans, Invisible Indexes, Locking Issues, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.
1 comment so far

 

I’ve discussed previously locking issues associated with the creation of indexes. Although things have changed and improved over the years, even with the ONLINE option currently, an index creation process still requires (albeit brief and non-escalating) locks on the underlining table.

Basically, there needs to be a brief period where there isn’t an active transaction on the underlining table for the index creation process to complete, else it will forced to wait and hang. Oracle requires a table lock on the underlining table at the start of the CREATE or REBUILD process (to guarantee data dictionary information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

So how do these index lock requirements potentially impact the Automatic Indexing process?

To investigate, I’ll create and populate a basic table with a highly selective CODE column:

SQL> create table bowie_busy (id number constraint bowie_busy_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie_busy select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_BUSY');

PL/SQL procedure successfully completed.

 

In a second session, I’ll insert a new row but NOT commit the change, thereby creating a extended transaction:

SQL> insert into bowie_busy values (10000001, 42, 'Ziggy Stardust');

1 row created.

 

Back in the original session, I’ll run the following SQL numerous times:

 

SQL> select * from bowie_busy where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE_BUSY |   10 |   230 |    6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      71423 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

Without an associated index in place, the CBO currently has no choice but to perform a Full Table Scan. But with the SQL only returning 10 rows from the 10M table, clearly an index would be beneficial.

But how does the existing transaction and associated locks on table impact the Automatic Indexing process?

There’s nothing magical here. With the current transaction in place on the underlying table, the index creation process simply can’t be completed. If we look at the status of the Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, status, num_rows, leaf_blocks from user_indexes where table_
name='BOWIE_BUSY';

INDEX_NAME                     AUT CON VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS
------------------------------ --- --- --------- -------- ---------- -----------
BOWIE_BUSY_PK                  NO  YES VISIBLE   VALID      10000000       19856
SYS_AI_8pkdh6q096qvs           YES NO  INVISIBLE UNUSABLE   10000000       23058

It remains in its initial INVISIBLE/USABLE state.

If we look at the Automatic Indexing monitoring report, some 6 HOURS after the initial running of the Automatic Index process for this index:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 01-DEC-2022 07:12:31
Activity end                : 01-DEC-2022 13:05:53
Executions completed        : 0
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates           : 0
Indexes created            : 0
Space used                 : 0 B
Indexes dropped            : 0
SQL statements verified    : 0
SQL statements improved    : 0
SQL plan baselines created : 0
Overall improvement factor : 1x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

ERRORS
--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------
-------------

 

We notice that the whole Automatic Indexing process has been locked out and left in a hanging state (the times between the activity start/end times just keep climbing, with 0 executions of the Automatic Indexing process completed).

Without a VISIBLE/USABLE automatic index in place, if we re-run the SQL again:

SQL> select * from bowie_busy where code=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3896751453

--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   10 |   230 |    6714 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | BOWIE_BUSY |   10 |   230 |    6714 (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      71423 consistent gets
      38657 physical reads
          0 redo size
        885 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

 

The CBO has again no choice but to still perform the highly inefficient Full Table Scan.

And the required Automatic Index won’t be able to be created until the existing transaction on the underlying table has completed.

HOWEVER, as we’ll see in Part II, the possible ramifications of this locking transaction goes way past the impact it has on just this SQL or specific automatic index…

Automatic Indexing: Non-Equality Predicates Part IV (“Like A Rocket Man”) November 29, 2022

Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, LIKE Predicates, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, _EXADATA_FEATURE_ON.
add a comment

Forgive me, it’s been a while since I last posted, but life has so many distractions these days 🙂

I recently had a question on whether a LIKE predicate can generate an Automatic Index now that non-equality predicates are supported since Oracle Database 21c.

Now the answer I initially provided was “well, why don’t you just test it for yourself“. However, his subsequent responses highlighted to me that not everyone might necessarily know how to potentially play with many of the Exadata features, even if you don’t directly have access to an Exadata environment.

So the purpose of this post is not only to answer this question, but also to just highlight HOW to potentially test things out for yourself when you’re not lucky enough to work directly with Exadata.

One obvious manner in which to play on an Exadata environment is to simply create and connect to an Oracle Autonomous Database environment using Oracle’s Cloud services (which are all Exadata-based environments), where you can easily, FOR FREE, and WITH NO TIME RESTRICTIONS play with many Exadata database features. The “Always Free Cloud Services” is truly a fabulous resource provided by Oracle, where you can have a couple of Autonomous Database environments always at your disposal (and very very easily and quickly just drop an existing database environment and re-create a new one).

Follow the link for all the information you need on how to get started with Oracle’s Always Free Cloud Services: https://www.oracle.com/au/autonomous-database/free-trial/

If the version of Oracle Database you like to play with isn’t currently available on the Oracle Autonomous Database platforms, another option is to simply download the database version you want to play with and just make it think it’s actually on an Exadata platform, by setting the following hidden parameter:

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

and restart your database.

You can now at least play and learn about many of the Exadata database features (such as Automatic Indexing), without having an actual Exadata machine on hand.

OK, now that you have an Exadata (or Exadata-like) environment on hand, you can go about answering for yourself these types of questions…

So, does Automatic Indexing now work in the case of a LIKE predicate?

First, make sure Automatic Indexing is enabled:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

Begin by creating and populating a basic table structure to test. The following table just has a few basic columns, with the MIXED_STUFF column simply populated with the rownum concatenated with a constant string:

SQL> create table aladdin_sane (id number, code1 number, grade number, mixed_stuff varchar2(42), name varchar2(42));

Table created.

SQL> insert into aladdin_sane select rownum, mod(rownum,100000), mod(rownum,100), rownum || ' David Bowie ' || rownum, 'ZIGGY STARDUST' from dual connect by level <=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ALADDIN_SANE');

PL/SQL procedure successfully completed.

 

I then run the following query several times with a LIKE predicate that returns just the one row from my 10M row table:

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';

Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    1 |    57 |   14805 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | ALADDIN_SANE |    1 |    57 |   14805 (2)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - storage("MIXED_STUFF" LIKE '4242 %')
    filter("MIXED_STUFF" LIKE '4242 %')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     169813 consistent gets
      84940 physical reads
          0 redo size
        912 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

 

Without any indexes currently in place, the CBO has no choice but to use a FTS. But, with only 1 row returned from this 10M table, an appropriate index would almost certainly be beneficial. So what does Automatic Index do in this scenario?

Once we wait for the next running of the Automatic Indexing jobs to complete, we can check:

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 29-NOV-2022 12:52:30
Activity end                : 29-NOV-2022 12:53:50
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 1
Indexes created (visible / invisible)        : 1 (1 / 0)
Space used (visible / invisible)             : 452.98 MB (452.98 MB / 0 B)
Indexes dropped                              : 0
SQL statements verified                      : 6
SQL statements improved (improvement factor) : 1 (169815.2x)
SQL plan baselines created (SQL statements)  : 1 (1)
Overall improvement factor                   : 2x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
| Owner | Table        | Index                | Key         | Type   | Properties |
-----------------------------------------------------------------------------------
| BOWIE | ALADDIN_SANE | SYS_AI_dzhahcw1cf0mw | MIXED_STUFF | B-TREE | NONE       |
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE

SQL ID              : fgvdbfsfwb9jv

SQL Text            : select * from aladdin_sane where mixed_stuff like '4242%'

Improvement Factor  : 169815.2x

Execution Statistics:
-----------------------------
                  Original Plan                Auto Index Plan
                  ---------------------------- ----------------------------
Elapsed Time (s): 10869872                     433
CPU Time (s):     9778626                      433
Buffer Gets:      2377413                      4
Optimizer Cost:   14805                        4
Disk Reads:       1189160                      2
Direct Writes:    0                            0
Rows Processed:   14                           1
Executions:       14                           1

 

So, it appears that Automatic Indexing has indeed created a new index. We can now check out the new index details:

 

select index_name, auto, constraint_index, visibility, compression, status 
from user_indexes where table_name='ALADDIN_SANE';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS
-------------------- --- --- --------- ------------- --------
SYS_AI_bnyacywycxx8b YES NO  VISIBLE   DISABLED      VALID

SQL> select index_name, column_name, column_position 
from user_ind_columns where table_name='ALADDIN_SANE' order by index_name, column_position;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_AI_dzhahcw1cf0mw           MIXED_STUFF                                  1

 

Automatic Indexing has indeed created a VALID/VISIBLE index on the MIXED_STUFF column.

If we now re-run the query:

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';

Execution Plan
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

2 - access("MIXED_STUFF" LIKE '4242 %')
    filter("MIXED_STUFF" LIKE '4242 %')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     116204 consistent gets
      84940 physical reads
          0 redo size
        912 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

 

We can see the newly generated plan now uses the new Automatic Index.

But due to Deferred Invalidations (which I’ve discussed previously), which in Oracle 21c delay the invalidation of SQL cursors due to new indexes, we may need to (for example) flush the shared_pool for the new plan to actually be used (a safe enough option in our play/test environment):

SQL> alter system flush shared_pool;

System altered.

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';

Execution Plan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

2 - access("MIXED_STUFF" LIKE '4242 %')
    filter("MIXED_STUFF" LIKE '4242 %')

Statistics
----------------------------------------------------------
        263 recursive calls
          0 db block gets
        508 consistent gets
          0 physical reads
          0 redo size
        916 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         70 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';

Execution Plan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

2 - access("MIXED_STUFF" LIKE '4242 %')
    filter("MIXED_STUFF" LIKE '4242 %')

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

 

We can now see that the new plan has indeed been adopted with the substantial decrease in consistent gets, down to just 5 from the previous 169813 with the FTS.

So yes, Automatic Indexing does indeed now work with LIKE predicates, but most importantly, it’s very easy for you to test and see these things for yourself.

In which case, you won’t need blogs such as this in the future to show you the way… 🙂

Costing Concatenated Indexes With Range Scan Predicates Part II (Coming Back To Life) July 27, 2022

Posted by Richard Foote in Automatic Indexing, CBO, Column Statistics, Concatenated Indexes, Explain Plan For Index, Full Table Scans, Index Access Path, Index Column Order, Index Column Reorder, Index Internals, Index statistics, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Blog, Oracle Cost Based Optimizer, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Statistics, Performance Tuning, Richard Foote Training.
add a comment

In my previous Part I post, I discussed how the CBO basically stops the index leaf block access calculations after a non-equality predicate. This means that for an index with the leading indexed column being accessed via an unselective non-equality predicate, a large percentage of the index’s leaf blocks might need to be scanned, making the index access path unviable.

In the example in Part I, an index on the ID, CODE columns was too expensive due to the unselective range-scan predicate based on the leading ID column.

To provide the CBO a potentially much more efficient access path, we need an index with the more selective CODE predicate to be the leading column:

SQL> CREATE INDEX radiohead_code_id_i ON radiohead(code, id);

Index created.

SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor

FROM user_indexes WHERE index_name = 'RADIOHEAD_CODE_ID_I';

INDEX_NAME                        BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
----------------------------- ---------- ----------- -----------------
RADIOHEAD_CODE_ID_I                    1         265             98619

If we now re-run the previous query:

SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;

Execution Plan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     4 |    72 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD           |     4 |    72 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RADIOHEAD_CODE_ID_I |     4 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        806  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

We notice the CBO is now using this new index, as the costs for this index-based plan have dropped significantly, down to just 6 (from the previous 116). This overall cost of 6 is lower than the cost of 105 for the Full Table Scan and hence the reason why this index-based plan is now chosen by the CBO.

This is all due entirely to the significant drop in costs in accessing the index itself, now just 2 (from the previous 112).

Importantly, these much lower costs are accurate as we can tell via the reduced number of consistent reads, now just 7 (from 114 from the previous index-based plan).

If we now look at the associated costings:

Effective Index Selectivity = CODE selectivity x ID selectivity

= (1/10000) x ((5000-1000)/(10000-1) + 2 x (1/10000))

= 0.0001 x ((4000/9999) + 0.0002)

= 0.0001 x 0.40024)

= 0.000040024

Effective Table Selectivity = same as Index Selectivity

= 0.000040024

 

The effective index selectivity of 0.000040024 is now much lower than the previous (0.40024), as the CBO can now consider the product of the selectivities of both columns).

If we now plug this improved effective index selectivity into the index path costing calculations:

Index IO Cost = blevel +

ceil(effective index selectivity x leaf_blocks) +

ceil(effective table selectivity x clustering_factor)

 

Index IO Cost = 1  +  ceil(0.000040024 x 265) + ceil(0.000040024 x 99034)

= 1 + 1  + 4

= 2 + 4

= 6

Index Access Cost  = IO Costs + CPU Costs (in this plan, 0% of total costs and so unchanged from the IO costs)

= 2 + 4

= 6

We can see how the respective 2 and 6 improved CBO index costings are derived.

So again, it’s important to note that Automatic Indexing is doing entirely the correct thing with these examples, when it creates an index with the equality based predicate columns as the leading columns of the index…

Costing Concatenated Indexes With Range Scan Predicates Part I (Nothing To Be Desired) July 22, 2022

Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Concatenated Indexes, Index Access Path, Index Column Order, Index Column Reorder, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning, Richard Foote Consulting, Richard Foote Training, Richard's Blog.
1 comment so far

In my previous post, I discussed how Automatic Indexing ordered columns when derived from SQLs containing both equality and non-equality predicates.

I’ve since had offline questions asking why indexes are more effective with leading columns addressing the equality predicates rather than the leading columns addressing non-equality predicates. Based on the theory that for everyone who asks a question, there are likely numerous others wondering the same thing, I thought I’ll try to explain things with these posts.

I’ll start by creating the following simple table that has two columns (ID and CODE) that are both highly selective (they both have 10,000 distinct values in a 100,000 rows table, so 10 rows approximately per value):

SQL> CREATE TABLE radiohead (id NUMBER, code NUMBER, name VARCHAR2(42));

Table created.

SQL> INSERT INTO radiohead SELECT mod(rownum,10000)+1,

ceil(dbms_random.value(0,10000)), 'RADIOHEAD' FROM dual CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

I’ll next create an index based on the ID, CODE columns, with importantly the ID column as the leading column:

SQL> CREATE INDEX radiohead_id_code_i ON radiohead(id, code);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'RADIOHEAD',

estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

 

When it comes to costing index accesses, some of the crucial statistics including the Blevel, Leaf_Blocks and often most crucial of all, the Clustering_Factor:

SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_ID_CODE_I';

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
RADIOHEAD_ID_CODE_I           1         265             99034

 

We begin by running the following query, with an equality predicate on the ID column and a relatively large, non-selective range predicate on the CODE column:

SQL> SELECT * FROM radiohead WHERE id = 42 AND CODE BETWEEN 1000 AND 5000;

Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     4 |    72 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD           |     4 |    72 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RADIOHEAD_ID_CODE_I |     4 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

As (perhaps) expected, the CBO uses the index to retrieve the small number of rows (just 5 rows).

However, if we run the following query which also returns a small number of rows  (just 4 rows) BUT with the relatively unselective, non-equality predicate based on the leading indexed ID column:

SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;

Execution Plan
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     4 |    72 |   105  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| RADIOHEAD |     4 |    72 |   105  (11)| 00:00:01 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

We notice (perhaps unexpectedly) that the CBO now ignores the index and uses a Full Table Scan, even though only 4 rows are returned from a 100,000 row table.

This is a common area of confusion. Why does Oracle not use the index when both columns in the index are referenced in the SQL predicates and only a tiny number of rows are returned?

The answer comes down to the very unselective non-equality predicate (ID BETWEEN 1000 AND 5000) being serviced by the leading column (ID) of the index.

The “ID BETWEEN 1000 AND 5000” predicate basically covers 40% of all known ID values, which means Oracle must now read 40% of all Leaf Blocks within the index (one leaf block at a time), starting with ID =1000 and ending with ID = 5000. Although there are very few rows that then subsequently match up with the other (CODE = 140) predicate based on the second column (CODE) of the index, these relatively few values could exist anywhere within the 40% ID range.

Therefore, when costing the reading of the actual index, the CBO basically stops its calculations after the non-equality predicate on this leading ID column and indeed estimates that a full 40% of the index itself must be scanned.

If we force the CBO into a range scan via a basic index hint:

SQL> SELECT /*+ index(r) */ * FROM radiohead r WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;

Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     4 |    72 |   116   (4)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD           |     4 |    72 |   116   (4)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RADIOHEAD_ID_CODE_I |     4 |       |   112   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        114  consistent gets
          0  physical reads
          0  redo size
        806  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

We notice that the overall cost of this index based plan is 116, greater than the 105 cost of the Full Table Scan (and hence why the Full Table Scan was selected). We also notice that the vast majority of this 116 cost can be attributed to the index scan itself in the plan, which has a cost of 112.

If you have a calculator handy, this is basically how these costs are derived.

Range Selectivity = (Max Range Value–Min Range Value)/(Max Column Value–Min Column Value)

Effective Index Selectivity = Range Selectivity + 2 x ID density (as a BETWEEN clause was used which is inclusive of Min/Max range)

= (5000-1000)/(10000-1) + 2 x (1/10000)

= 0.40004 + 0.0002

= 0.40024

Effective Table Selectivity = ID selectivity (as above) x CODE selectivity

= 0.40024 x (1/10000)

= 0.40024 x 0.0001

= 0.000040024

These selectivities are then inserted into the following index costing formula:

Index IO Cost = blevel +

ceil(effective index selectivity x leaf_blocks) +

ceil(effective table selectivity x clustering_factor)

 

Index IO Cost = 1  +  ceil(0.40024 x 265) + ceil(0.000040024 x 99034)

= 1 + 107 + 4

= 108 + 4 = 112.

 

Index Access Cost = IO Costs + CPU Costs (in this plan, 4% of total costs)

= (108 + (112 x 0.04)) + (4 + (4 x 0.04))

= (108 + 4) + (4 + 0)

= 112 + 4

= 116

 

So we can clearly see how the CBO has made its calculations, come up with its costs and has decided that the Full Table Scan is indeed the cheaper alternative with the current index in place.

So Automatic Indexing is doing the right thing, by creating an index with the leading column based on the equality predicate and the second indexed column based on the unselective non-equality predicate.

I’ll expand on this point in an upcoming Part II post.

Automatic Indexing 21c: Non-Equality Predicate Anomaly (“Strangers When We Meet”) July 14, 2022

Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Exadata X8, Full Table Scans, Index Column Order, Invisible Indexes, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning, Richard Foote Training, Richard's Blog.
3 comments

I’m currently putting together some Exadata related training for a couple of customers and came across a rather strange anomaly with regard the status of Automatic Indexes, when created in part on unselective, non-equality predicates.

As discussed previously, Oracle Database 21c now allows the creation of Automatic Indexes based on non-equality predicates (previously, Automatic Indexes were only created on equality-based predicates).

But one appears to get rather odd resultant Automatic Indexes in the scenario where the non-equality predicate is not particularly selective but other predicates are highly selective.

To illustrate, I’ll create a basic table that has two columns (ID and CODE) that are both highly selective:

SQL> create table ziggy_new (id number, code number, name varchar2(42));

Table created.

SQL> insert into ziggy_new select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY_NEW');

PL/SQL procedure successfully completed.

So there are currently no indexes on this table.

I’ll next run the following SQL (and others similar) a number of times:

SQL> select * from ziggy_new where code=42 and id between 1 and 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |    1 |    23 |    6738 (2) | 00:00:01 |
| * 1 |  TABLE ACCESS STORAGE FULL | ZIGGY_NEW |    1 |    23 |    6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
       filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      38605 consistent gets
      38600 physical reads
          0 redo size
        725 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

Without any indexes, the CBO currently has no choice but to use a Full Table Scan.

But only 1 row is returned. The first equality predicate on the CODE column is highly selective and on its own would only return 10 rows out of the 10M row table. The second, non-equality range-based predicate on the ID column is nowhere near as selective and offers limited additional filtering.

The CBO stops calculating index related costs after a non-equality predicate column (as subsequent column values could exist anywhere within the preceding range), and so the more effective index here is one based on (CODE, ID) with the non-equality predicate column second,  or potentially just on the CODE column only, as the ID range offers minimal filtering benefits.

So what does Automatic Indexing make of things?

If we look at the subsequent Automatic Indexing report:

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 3
Indexes created (visible / invisible)        : 1 (0 / 1)
Space used (visible / invisible)             : 209.72 MB (0 B / 209.72 MB)
Indexes dropped                              : 0
SQL statements verified                      : 44
SQL statements improved (improvement factor) : 12 (64.7x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 1.6x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table     | Index                | Key     | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE       |
----------------------------------------------------------------------------

So Automatic Indexing has indeed created an index based on CODE,ID (a common Automatic Indexing trait appears to be to create an index based on all available predicates).

BUT the index is created as an INVISIBLE Index and so can not generally be used by database sessions.

SQL> select index_name, auto, visibility, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY_NEW';

INDEX_NAME                     AUT VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --------- -------- ---------- ----------- -----------------
SYS_AI_75j16xff1ag3j           YES INVISIBLE VALID      10000000       25123          10000000

SQL> select index_name, column_name, column_position
     from user_ind_columns where table_name='ZIGGY_NEW';

INDEX_NAME                     COLUMN_NAME  COLUMN_POSITION
------------------------------ ------------ ---------------
SYS_AI_75j16xff1ag3j           CODE                       1
SYS_AI_75j16xff1ag3j           ID                         2

 

So re-running the previous SQL statements continues to use a Full Table Scan:

SQL> select * from ziggy_new where code=42 and id between 1 and 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525

----------------------------------------------------------------------------------------
|  Id | Operation                  | Name      | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |    1 |    23 |    6738 (2) | 00:00:01 |
| * 1 |  TABLE ACCESS STORAGE FULL | ZIGGY_NEW |    1 |    23 |    6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
       filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      38605 consistent gets
      38600 physical reads
          0 redo size
        725 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

 

Automatic Indexing appears to only create Invisible indexes when there is an inefficient non-equality predicate present. It won’t create the index as a Visible index, even though it would significantly benefit these SQL statements that caused its creation. And Automatic Indexing won’t create an index on just the highly selective CODE equality predicate, which would also be of much benefit to these SQL statements.

If we now run similar queries, but with much more selective non-equality predicates, such as:

SQL> select * from ziggy_new where code=1 and id between 1 and 10;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525

----------------------------------------------------------------------------------------
|  Id | Operation                  | Name      | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |    1 |    23 |    6738 (2) | 00:00:01 |
| * 1 |  TABLE ACCESS STORAGE FULL | ZIGGY_NEW |    1 |    23 |    6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - storage("CODE"=1 AND "ID"<=10 AND "ID">=1)
       filter("CODE"=1 AND "ID"<=10 AND "ID">=1)

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

Again, with no (Visible) index present, the CBO currently has no choice but to use the Full Table Scan.

But during the next cycle, after Automatic Indexing kicks in again:

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 5
Indexes created (visible / invisible)        : 1 (1 / 0)
Space used (visible / invisible)             : 209.72 MB (209.72 MB / 0 B)
Indexes dropped                              : 0
SQL statements verified                      : 89
SQL statements improved (improvement factor) : 31 (71.9x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 1.7x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table     | Index                | Key     | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE       |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : d4znwcu4h52ca
SQL Text            : select * from ziggy_new where code=42 and id between 1 and 10
Improvement Factor  : 38604x

Execution Statistics:
-----------------------------
                    Original Plan                Auto Index Plan
                    ---------------------------- ----------------------------
Elapsed Time (s):   3398605                      68
CPU Time (s):       3166824                      68
Buffer Gets:        463250                       3
Optimizer Cost:     6738                         4
Disk Reads:         463200                       0
Direct Writes:      0                            0
Rows Processed:     0                            0
Executions:         12                           1

PLANS SECTION
--------------------------------------------------------------------------------
-------------

- Original
-----------------------------
Plan Hash Value : 3165184525

--------------------------------------------------------------------------------
| Id | Operation                  | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |           |      |       | 6738 |          |
|  1 |  TABLE ACCESS STORAGE FULL | ZIGGY_NEW |    1 |    23 | 6738 | 00:00:01 |
--------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value : 1514586396

-------------------------------------------------------------------------------------------------------
|  Id | Operation                            | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    23 |    4 | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW            |    1 |    23 |    4 | 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_75j16xff1ag3j |    1 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

 

But this time, the index on the CODE,ID columns is created as a Visible index.

INDEX_NAME                     AUT VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --------- -------- ---------- ----------- -----------------
SYS_AI_75j16xff1ag3j           YES VISIBLE   VALID      10000000       25123          10000000

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='ZIGGY_NEW';

INDEX_NAME                     COLUMN_NAME  COLUMN_POSITION
------------------------------ ------------ ---------------
SYS_AI_75j16xff1ag3j           CODE                       1
SYS_AI_75j16xff1ag3j           ID                         2

So this index can be generally used, both by the newer SQLs that generated the now Visible index:

SQL> select * from ziggy_new where code=42 and id between 1 and 10;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396

------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                      |    1 |    23 |       4 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW            |    1 |    23 |       4 (0) | 00:00:01 |
|* 2 |   INDEX RANGE SCAN                   | SYS_AI_75j16xff1ag3j |    1 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)

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

And also used by the SQLs with the unselective non-equality predicates, that Automatic Indexing would only create as Invisible indexes:

SQL> select * from ziggy_new where code=42 and id between 1 and 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396

------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                      |    1 |    23 |       4 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW            |    1 |    23 |       4 (0) | 00:00:01 |
|* 2 |   INDEX RANGE SCAN                   | SYS_AI_75j16xff1ag3j |    1 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("CODE"=42 AND "ID">=1 AND "ID"<=100000)

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

 

Automatic Indexing appears to currently not quite do the right thing with SQL statements that have unselective non-equality predicates, by creating such indexes as only Invisible Indexes, inclusive of the unselective columns.

Although an edge case, I would recommend looking through the list of created Automatic Indexes to see if any such Invisible/Valid indexes exists, as it can suggest there are current inefficient SQL statements that could benefit from such indexes being Visible.

Upcoming Webinar Series Now Sold Out!! (“White Light White Heat”) July 13, 2022

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training.
add a comment

My upcoming webinars now have the maximum number of attendees I’m comfortable in having in one session and are now officially sold out!!

Taking my inspiration from David Bowie’s famous 1973 farewell speech, not only is this the last webinar series of the year, but it’s likely the last webinar series I’ll ever do.

A huge thank you to everyone who has enrolled, I’m absolutely sure we’re all going to have a blast 🙂

If you were interested in attending this training, please contact me to get yourself on a waiting list (although I unfortunately have no timeframe in which I’m likely to run another series) : richard@richardfooteconsulting.com.

Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.
1 comment so far

In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).

The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.

In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.

I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:

SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');

PL/SQL procedure successfully completed.

I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:

 

SQL> create index album_id_i on big_bowie(album_id);

Index created.

SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local;

Index created.

 

If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):

SQL> alter table big_bowie move partition albums_2017;

Table altered.

This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:

SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS
------------------------------ --------
ALBUM_ID_I                     UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     N/A

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:

SQL> select * from big_bowie where album_id=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 2000 | 52000 |    7959 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE ALL |           | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
| * 2 |  TABLE ACCESS FULL  | BIG_BOWIE | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("ALBUM_ID"=42)
  - filter("ALBUM_ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48593 consistent gets
      42881 physical reads
          0 redo size
      44289 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:

SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    1 |    26 |     986 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE |           |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
| * 2 |  TABLE ACCESS FULL     | BIG_BOWIE |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
   - filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

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

Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.

So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?

If we wait until the next AI task completes and check out the indexes on the table:

SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS   PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I     N/A      YES
ALBUM_ID_I                     UNUSABLE NO
SYS_AI_aw2825ffpus5s           VALID    NO
SYS_AI_2hf33fpvnqztw           VALID    NO

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BIG_BOWIE';

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
ALBUM_ID_I                     ALBUM_ID                           1
RELEASE_DATE_TOTAL_SALES_I     RELEASE_DATE                       1
RELEASE_DATE_TOTAL_SALES_I     TOTAL_SALES                        2
SYS_AI_aw2825ffpus5s           ALBUM_ID                           1
SYS_AI_aw2825ffpus5s           RELEASE_DATE                       2
SYS_AI_2hf33fpvnqztw           TOTAL_SALES                        1
SYS_AI_2hf33fpvnqztw           RELEASE_DATE                       2

As we can see, AI has logically replaced both unusable indexes.

The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.

The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.

Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.

But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.

A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).

If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…

As always, this restriction may change in future releases…

Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”) May 25, 2022

Posted by Richard Foote in 18c New Features, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Tricks, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Seminar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Seminar, Performance Tuning Webinar, Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training, Richard Presentations.
add a comment

The registration links for my upcoming webinars running in August are now open!!!

The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button).

(Note: Do NOT use the links if you’re an Australian resident. Please contact me at richard@richardfooteconsulting.com for additional payment info and tax invoice that includes additional GST).

Just click the below “Buy Now” buttons to book your place for these unique, highly acclaimed Oracle training events (see some of my testimonials for feedback by previous attendees to these training events):

 

Oracle Indexing Internals Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

Oracle Performance Diagnostics and Tuning Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

Special Combo Price for both August 2022 Webinars$2,750 AUD: SOLD OUT!!

 

The links allow you to book a place using either PayPal or a credit card. If you wish to pay via a different method or have any questions at all regarding these events, please contact me at richard@richardfooteconsulting.com.

As I mentioned previously, for those of you on my official waiting list, I will reserve a place for you for a limited time.

As this will probably be the last time I will run these events, remaining places are likely to go quickly. So please book your place ASAP to avoid disappointment…

 

Read below a brief synopsis of each webinar:

Oracle Indexing Internals

This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This webinar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the webinar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this webinar and is not generally available in Oracle documentation or in Oracle University courses.

For full details, see: https://richardfooteconsulting.com/indexing-seminar/

 

Oracle Performance Diagnostics and Tuning

This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/

 

If you have any questions about these events, please contact me at richard@richardfooteconsulting.com

 

Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”) May 19, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Myth, Oracle, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Webinar, Richard Foote Seminars, Webinar.
add a comment

As promised last week, I have now finalised the dates for my upcoming webinars.

They will be run as follows (UPDATED):

Oracle Indexing Internals Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!

Oracle Performance Diagnostics and Tuning Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily): SOLD OUT!!

Special Combo Price for both August 2022 Webinars“: SOLD OUT!!

I’ll detail costings and how to register for these events in the coming days.

 

There is already quite a waiting list for both of these webinars and so I anticipate available places will likely go quickly. Sorry to all those who have been waiting for so long and thank you for your patience. Please note for those on the waiting list, I already have places reserved for you.

It’s highly likely these will be the last time I’ll ever run these highly acclaimed training events (yes, I’m getting old)…

So don’t miss this unique opportunity to learn important skills in how to improve the performance and scalability of both your Oracle based applications and backend Oracle databases, in the comfort of your own home or office.

Read below a brief synopsis of each webinar:

Oracle Indexing Internals

This is a must attend webinar of benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.

For full details, see: https://richardfooteconsulting.com/indexing-seminar/

 

Oracle Performance Diagnostics and Tuning

This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  The webinar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real world scenarios and numerous actual examples and test cases, this webinar will show participants how to confidently and reliably diagnose performance issues. The webinar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports, where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/

 

Keep an eye out in the coming days on costings and how to register for these events.

If you have any questions about these events, please contact me at richard@richardfooteconsulting.com