jump to navigation

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.
trackback

 

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…

Comments»

1. Automatic Indexing: Potential Locking Issues Part II (“Don’t Stop”) | Richard Foote's Oracle Blog - December 5, 2022

[…] my previous post, I highlighted how a long transaction can potentially cause the creation of an Automatic Index to […]

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: