Oracle Performance Issue: The Fake Index Trap

 Hi All,

In this post i am going to share about Oracle Performance Issue: The Fake Index Trap Virtual/Fake/Nosegment Indexes. 

Even though these fake indexes are not physically present, they can still create problems if you forget about them later.

To demonstrate this issue, I'll first create a new tablespace in lab session:

create tablespace RACS_TS datafile '/u01/app/oracle/oradata/GGDB1/pdb1/RACS_TS.DBF' size 100M;

I’ll create and populate a table in this RACS_TS tablespace:

I'll insert some data to this table 

insert into RACS_TS select rownum, 'RACS INFOTECH' from dual connect by level <=10000;

commit;

gather the status for thi table :

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

I’ll next create a Virtual/Fake index, using the NOSEGMENT option:

create index RACS_TS_id_i on RACS_TS(id) nosegment tablespace RACS_TS;

We note this Fake Index is NOT listed in either USER_INDEXES or USER_SEGMENTS:

select index_name, tablespace_name from user_indexes where table_name='RACS_TS';

SQL> select index_name, tablespace_name from user_indexes where table_name='RACS_TS';

no rows selected

SQL>

select segment_name, segment_type, tablespace_name from user_segments  where segment_name='RACS_TS_ID_I';

If we run a basic, highly selective query on this table:

select * from RACS_TS where id=42;

We notice the CBO uses a FTS. The Fake Index is NOT considered by default.

However, if we set the session as follows and re-run the query:

alter session set "_use_nosegment_indexes" = true;

 select * from RACS_TS where id=43;

Execution Plan

----------------------------------------------------------

Plan hash value: 43368621

 

--------------------------------------------------------------------------------

| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU) | Time     |

--------------------------------------------------------------------------------

|  0 | SELECT STATEMENT   |            |    1 |    16 |      11 (0) | 00:00:01 |

|* 1 |  TABLE ACCESS FULL | RACS_TEST |    1 |    16 |      11 (0) | 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

     1 - filter("ID"=43)

 

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

         38 consistent gets

          0 physical reads

          0 redo size

        648 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 CBO appears to now use the Fake Index, but as it doesn’t actually physically exist, actually uses a FTS behind the scenes (the number of consistent gets is evidence of this). But at least we now know the CBO would at least consider such an index if it physically existed.

We now decide to drop the tablespace and so first try to MOVE the table to another tablespace using the ONLINE option:

alter table RACS_TS move online tablespace users;

We try to move the table using the default OFFLINE method:

alter table RACS_TS move tablespace users;

We have now successfully moved the table to another tablespace.

If we check to see if we have any other segments within the tablespace to be dropped:

select segment_name from dba_segments where tablespace_name='RACS_TS';

Oracle tells us that no, we do NOT have any current segments in this tablespace.

So it’s now safe to purge and drop this tablespace (or so we think):

purge tablespace RACS_TS;

drop tablespace RACS_TS;

 

The tablespace has been successfully dropped.

However, if we now re-run the query on this table:

select * from RACS_TS where id=43;

We get this unexpected error that the tablespace RACS_TS does not exist.

BUT, we already know the tablespace doesn’t exist, we’ve just dropped it !!!

So why are we getting this error?

It’s all due to the damn Fake Index we created previously.

Although there is no physical index segment for our Fake Index, there are still some internal Data Dictionary links between the Fake Index and the tablespace it was associated with. The tablespace is gone, but NOT the Fake Index.

The only place where fake indexes can be easily found within Oracle, is within the USER_OBJECTS view:

select o.object_name, o.object_type, o.status from user_objects o left join user_indexes i on o.object_name=i.index_name where o.object_type='INDEX' and i.index_name is null;

To eliminate this error, we have to first drop the Fake Index associated with the dropped tablespace:

drop index RACS_TS_id_i;

We can now safely run the query without error:

select * from RACS_TS where id=43;

So if you do ever create Fake Indexes, don’t forget to drop them once you’ve finished experimenting with them.

 if you ever decide to drop the tablespace into which they were associated. This is explained in part in Oracle Support Doc ID 1361049.1.

Thanks,

Srini

CBO Execution Plan Variations Due to Row Migration in Oracle 19c Session 1

 Hi All,

In this post i am going to share about CBO vd Row Migration in oracle 19C database with example.

CBO Execution Plan Variations Due to Row Migration in Oracle 19c Session 1 . will keep post continuation in my next post .

Connecting the database and create and populating a tightly packed table . 

create table bowie(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(143)) PCTFREE 0;

insert into bowie SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

next create an index on the well clustered ID column 

next collecting index/table statistics, using the DBMS_STATS package as oracle recommended method.

the key index statistics here: BLEVEL=1, LEAF_BLOCKS=445 and the near perfect CLUSTERING_FACTOR=2871.

If we run the following query featuring a non-equality range predicate:

select * from bowie where id > 1 and id < 1001;




Explain plan










PLAN_TABLE_OUTPUT

_______________________________________________________________________________________________________________

SQL_ID bqw9mz995kds9, child number 0

-------------------------------------

select * from bowie where id > 1 and id < 1001

Plan hash value: 1845943507

------------------------------------------------------------------------------------------------------------

| Id | Operation                            | Name       | Starts | E-Rows | A-Rows | A-Time     | Buffers |

----------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                     |            |      1 |        |    999 |00:00:00.01 |      18 ||  1

|  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |      1 |   1000 |    999 |00:00:00.01 |      18 |

|* 2 |   INDEX RANGE SCAN                   | BOWIE_ID_I |      1 |   1000 |    999 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by

operation id):

---------------------------------------------------

   2 - access("ID">1 AND "ID"<1001)

Statistics

----------------------------------------------------------         

1 CPU used by this session         

1 CPU used when call started         

1 DB time      

7678 RM usage        

3 Requests to/from client         

2 SQL*Net roundtrips to/from client       

16 buffer is not pinned count      

1983 buffer is pinned count       

323 bytes received via SQL*Net from client    

171383 bytes sent via SQL*Net to client         

2 calls to get snapshot scn: kcmgss         

2 calls to kcmgcs        

18 consistent gets         

1 consistent gets examination         

1 consistent gets examination (fastpath)        

18 consistent gets from cache        

17 consistent gets pin        

17 consistent gets pin (fastpath)         

2 execute count         

1 index range scans    

147456 logical read bytes from cache        

17 no work - consistent read gets

40 non-idle wait count

2 opened cursors cumulative         

1 opened cursors current         

2 parse count (total)         

2 process last non-idle time         

1 session cursor cache count         

1 session cursor cache hits        

18 session logical reads         

1 sorts (memory)   

2024 sorts (rows)      

999 table fetch by rowid      

3 user calls

notice that the CBO indeed uses the index. They key statistic to note here is that Consistent Gets is just 18, which is extremely low considering we’re returning 999 rows. This is due to the fact the index is currently extremely efficient as it can fetch multiple rows by visiting the same table block due to the excellent clustering/ordering of the required ID column values (and also due to my high arraysize session setting). If we look at the CBO costings for this plan: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'bqw9mz995kds9',format=>'ALLSTATS LAST +cost +bytes'));




The KEY statistic the CBO has to determine is the estimated Selectivity of the query (the estimated percentage of rows to be returned), as this is the driver of all the subsequent CBO calculations.

The Selectivity of this range-based predicate query is calculated as follows:

Selectivity = (Highest Bound Value – Lowest Bound Value) / (Highest Value – Lowest Value)
= (1001-1) /(200000-1)
= 1000/199999
=  approx. 0.005

Once Oracle has the selectivity, it can calculate the query Cardinality (estimated number of rows) as follows:

Cardinality = Selectivity x No of Rows

Cardinality = 0.005 x 200000 = 1000 rows




The CBO cardinality estimate in the above plan is 1000 rows, whereas the number of rows actually returned is 999 rows.

So indeed, the CBO has got the cardinality almost spot on (except for a trivial rounding error) and so we have a high degree of confidence that the CBO is using the correct selectivity estimates when they get plugged into the following CBO formula for costing an index range scan (using this selectivity of 0.005 and the index statistics listed above):

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

= (1 + ceil(0.005 x 467)) + ceil(0.005 x 3250)
= (1 + 3) + 17
= 4 + 17 = 21

So we can clearly see where the CBO gets its costings for both reading the index during the Index Range Scan (4) and for the plan as a whole (21).

The CBO cost of 21 very closely resembles the 18 consistent gets accessed when the plan is executed. This to me suggests that the CBO has indeed costed this plan very accurately and appropriately.

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

200000 rows updated.

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

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables

where table_name='BOWIE';  2

TABLE_NAME

--------------------------------------------------------------------------------

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT

---------- ---------- ------------ ---------- ----------- ----------

BOWIE

    200000       4642            0          0         167          0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE';

INDEX_NAME

--------------------------------------------------------------------------------

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

---------- ----------- -----------------

BOWIE_ID_I

         1         445              2871

if we only INCREASE the cost of a FTS (via having more Table Blocks) but keep intact all the previous index related statistics, then the CBO is certainly going to again select the same Index Range Scan plan, as the plan will have the same (cheaper than FTS) costings as before.

If we re-run the query again:

We notice that as expected (as none of the index-related statistics have changed), that despite being much more expensive to now use this index, the costs of this plan (4 for reading the index and 21 overall) remain unchanged.

I would argue that these CBO costs are no longer as accurate as the 21 total CBO cost does not so closely represent the actual 666 consistent gets now required.

Now, the 327 table fetch continued row statistics from the previous run is clear proof we indeed have migrated rows following the Update statement.

But if we want to confirm how many migrated rows we now have in the table, we can use the ANALYZE command to collect these additional statistics:

SQL> analyze table bowie compute statistics;

Table analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt from user_tables

where table_name='BOWIE';  2

TABLE_NAME

--------------------------------------------------------------------------------

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT

---------- ---------- ------------ ---------- ----------- ----------

BOWIE

    200000       4642           93        103         171      70096

SQL>

We notice that we now have a CHAIN_CNT of 70096.

Now this statistic can represent any row that is not housed inside a single table block (for which there could be a number of possible reasons, such as a row simply being too long to fit in a single table block), but as all rows are still relatively tiny, we can be certain that indeed all 56186 chained rows represent migrated rows.

Now that I’ve gone and used ANALYZE, primarily to generate this CHAIN_CNT statistic,

Thanks,

Srini