Oracle DBA – A lifelong learning experience

Invisible Indexes – a real world example and a bit of LogMiner

Posted by John Hallas on February 11, 2014

We had a requirement to perform a one-off update on a table of around 1.2M rows. There were 11K statements similar to the one below, with varying values and predicates updating a variety of rows from 20 to 500. The potential elapsed time was around 44 hours due to the volume of full table scans involved.

The obvious thing to do was to create an index but this was production and it was urgent so no time to go through the full route to live test cycle.

This seemed a great opportunity to use an invisible index with the theory being that the optimiser would not use it for any other of the currently running work and only our session would see it.

This demo shows how it was tested and what the benefits were along with a couple of thoughts on invisible indexes.

I must admit I wish I could get a consistent method of showing explain plans, sometimes they come out lined up properly, other times they are a mess. This entry is somewhere in between.

Firstly lets run an example update statement to see what access path it uses

SQL> Update JOHN.TEST_TABLE Set WK_WAC_RATE=0.5338 Where SKU_ITEM_KEY=4878 AND TXN_WK_KEY=20140127;

24 rows updated.

Elapsed: 00:00:00.46

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |                      |    16 |   256 | 13696   (1)| 00:02:45 |       |       |
|   1 |  UPDATE              | TEST_TABLE |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                      |    16 |   256 | 13696   (1)| 00:02:45 |     1 |   118 |
|*  3 |    TABLE ACCESS FULL | TEST_TABLE |    16 |   256 | 13696   (1)| 00:02:45 |     1 |   118 |
-------------------------------------------------------------------------------------------------------------

Create an index and re-run to see that the index will be used and the results are better, don’t forget  to gather stats.

CREATE INDEX “JOHN”.”INDX_1″ ON “JOHN”.”TEST_TABLE” (“SKU_ITEM_KEY”, “TXN_WK_KEY”);

execute dbms_stats.gather_table_stats(‘JOHN’, TEST_TABLE’,null,10,cascade=>true);

SQL> Update JOHN.TEST_TABLE Set WK_WAC_RATE=0.5338 Where SKU_ITEM_KEY=4878 AND TXN_WK_KEY=20140127;

24 rows updated.

Elapsed: 00:00:00.01

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |                      |    27 |   432 |    20   (0)| 00:00:01 |
|   1 |  UPDATE           | TEST_TABLE |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| INDX_1               |    27 |   432 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Note the index can be made invisible from a command line statement but it does not seem possible if creating using the OEM12 gui

 alter index indx_1 invisible;

SQL> select index_name,visibility from user_indexes;

TEST_TABLE_PK        VISIBLE

INDX_1                         INVISIBLE

Run the query to prove that the invisible index is not used by the optimizer

 SQL> Update JOHN.dwd_rtl_sl_line_item Set WK_WAC_RATE=0.5338 Where SKU_ITEM_KEY=4878 AND TXN_WK_KEY=20140127;

24 rows updated.

-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |                      |    16 |   256 | 13696   (1)| 00:02:45 |       |       |
|   1 |  UPDATE              | TEST_TABLE |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                      |    16 |   256 | 13696   (1)| 00:02:45 |     1 |   118 |
|*  3 |    TABLE ACCESS FULL | TEST_TABLE |    16 |   256 | 13696   (1)| 00:02:45 |     1 |   118 |
-------------------------------------------------------------------------------------------------------------

Now alter the session so only that session id can take advantage of the index

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL>  Update JOHN.TEST_TABLE Set WK_WAC_RATE=0.5338 Where SKU_ITEM_KEY=4878 AND TXN_WK_KEY=20140127;

24 rows updated.

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |                      |    27 |   432 |    20   (0)| 00:00:01 |
|   1 |  UPDATE           | TEST_TABLE |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| INDX_1               |    27 |   432 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

The code is running much more efficiently and yet we have not impacted the execution of any other sql that may be running against this table, although the index will need updating with every I,U,D statement, whether or not the index is visible  or not.

Moving to thoughts around how you can tell whether an index is visible or not and when it was made visible. USER_INDEXES will show the state of the index

SQL> select index_name,visibility from user_indexes;

TEST_TABLE_PK        VISIBLE

INDX_1                         INVISIBLE

How do you know when it was made invisible/visible though. Does the LAST_DDL time tell you?

OBJECT_NAM OBJECT_TYPE         CREATED                  LAST_DDL                 VISIBILIT

———- ——————- ———————— ———————— ———

INDX_1     INDEX               10-FEB-14 08:38          10-FEB-14 08:38          INVISIBLE

SQL> alter index indx_1 visible;

 select object_name,object_type, to_char(CREATED,’DD-MON-YY HH24:MI’) Created,

to_char(LAST_DDL_TIME,’DD-MON-YY HH24:MI’) Last_DDL,  Visibility

from user_indexes U, user_objects O where o.object_name = u.index_name

and index_name  = ‘INDX_1′

OBJECT_NAM OBJECT_TYPE         CREATED                  LAST_DDL                 VISIBILIT

———- ——————- ———————— ———————— ———

INDX_1     INDEX               10-FEB-14 08:38          10-FEB-14 10:41          VISIBLE

So yes, if the last DDL on the index was to change it’s visibility then the time can be identified. However what the DDL was cannot be identified and an alternative option is to use logminer functionality.

Ensure supplementary logging is set, perform some actions and then log mine and see if you can identify them.

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

SQL> alter index john.indx_1 invisible;

Index altered.

SQL> alter index john.indx_1 visible;

Index altered.

SQL> alter index john.indx_1 invisible;

Index altered.

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS';

execute dbms_start_logmnr( -

starttime => ’10-Feb-2014 12:00:00′, -

endtime => ’10-Feb-2014 12:34:00′, -

options => dbms_logmnr.dict_fom_online_catalog + -

dbms_logmnr.continuous_mine);

select operation, sql_redo,sql_undo from v$logmnr_contents

where seg_owner = ‘JOHN’ and seg_name = ‘INDX_1′ ;

select substr(sql_redo,1,100) sql_redo, to_char(timestamp,’DD-MON-RRRR HH24:MI:SS’) timestamp

from v$logmnr_contents

where seg_owner = upper(‘JOHN’)

and sql_redo like ‘%vis%';

SQL_REDO          TIMESTAMP

—————————–

alter index john.indx_1 invisible;              10-FEB-2014 12:32:01

alter index john.indx_1 visible;                  10-FEB-2014 12:32:19

alter index john.indx_1 invisible;              10-FEB-2014 12:32:24

This last section on identifying when the index visibility was changed came from an Oracle-L discussion and the suggestion of log mining came from David Fitzjarrel. In fact it was because I had recently read the post that it gave me the idea to use an invisible index.

About these ads

6 Responses to “Invisible Indexes – a real world example and a bit of LogMiner”

  1. Dom Brooks said

    The obvious downside of not doing the invisible bit as part of the index creation is that in a busy system something might nip in there and start using your index repeatedly before you’ve had a chance to make it invisible and you might have to wait a while to be able to get the exclusive lock to make it invisible. Had this before in UAT systems.

    • John Hallas said

      Good call Dom – it absolutely needs to be used with care, especially in a situation I described on a live system with little or no testing but it is yet another weapon in the DBA armourary.
      I am going to do a more bit more investigation in this area as I think there are lots of benefits that can help in non-performant situations.

      The other aspect of my blog which I discussed with the DBAs on site is that we do not, as standard, enable supplementary logging. I think we need to rethink that and look at the pros and cons – with the major con being the performance overhead and to a lesser degree the additional redo log activity and storage generated.

      • Dom Brooks said

        Yep – it’s a good weapon.
        I use invisible indexes a lot in test systems to turn indexes on/off for tuning purposes without the overhead of drop/create.

        Could also be especially handy in conjunction with a sql patch – http://orastory.wordpress.com/2012/03/06/sql-patch-i/ – to turn on optimizer_use_invisible_indexes via the injection of an opt_param hint to enable specific embedded application sql to use a new index without changing the source code and without making the index visible to anything else – similar pros and cons to your example above.

  2. good article, and a simple addition use cursor sharing set to force in your session to save flooding your production shared pool with these statements if bind variables cannot be used :)

  3. […] >’D’ and adding an index on ACTION and seeing how that works out. Looks like my invisible index blog entry will come in useful for […]

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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 210 other followers

%d bloggers like this: