Evaluate Index Usage on Exadata

Exadata is well-known with great performance in scanning large amount of data. If need to pull huge amount of data from Exadata in the join operation, full table scan is more efficient than index scan. This is especially true in the data warehouse environment on Exadata.

The smartscan feature on Exadata allows database query to offload the full scan to cell nodes instead of the regular database nodes. But do you want to drop all indexes and in favor of full table scan for everything? Well, although this strategy is used by many database warehouse appliance vendors, you might not want to go for this path, especially when you have a mixed workload environment. For example, in OLTP environment, you might just need to get a few rows or a small amount of data from a large table. In this case, index access might be faster than the full table scan.

I have been to many Exadata clients and one of the top questions from my clients is always related to index. Many Exadata clients migrate their databases from other non-Exadata environment to Exadata. Ideally you want to take this opportunity to clean up the tables and indexes in the database and migrate only the portion of data needed. The tables are relatively easy to identified while indexes are not. You have no idea which index Exadata is going to pick it up in the execution plan before the migration. In addition, many large databases have way too many indexes for the purpose “in case” Oracle optimizer want to use it. Going through each of them before the Exadata migration is going to be painful and time-consuming task. So many companies do the migration first, then go back to evaluate index usage later. So the popular question I get is which index I can keep and which one I can drop.

It is not easy to evaluate whether you want to keep every index, but relatively easier just focus on the large indexes. Just like the “new version” of Mission Impossible below, identifying index usage is the way to help you to determine which one to keep and which one to drop.
mission-impossible
Here are a few ways to determine index usage as follows.

V$OBJECT_USAGE

The first way is using V$OBJECT_USAGE. By default, index monitoring is disabled and you need to run the command as follows to enable the monitoring.

alter index your_index_name monitoring usage;

WZHOU@dbm2> select * from v$object_usage;
no rows selected

WZHOU@dbm2> alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME        TABLE_NAME	MON USE START_MONITORING    END_MONITORING
----------------- ------------- --- --- ------------------- ------------------
BIGTAB_OLTP_PK    BIGTAB_OLTP   YES NO 02/17/2014 10:57:10

WZHOU@dbm2> select owner, object_name from bigtab_oltp where id = 30;
OWNER        OBJECT_NAME
------------ ------------------------------
SYS          I_PROXY_DATA$

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME      TABLE_NAME     MON USE START_MONITORING    END_MONITORING
--------------- -------------- --- --- ------------------- -------------------
BIGTAB_OLTP_PK  BIGTAB_OLTP    YES YES 02/17/2014 10:57:10

Please note. If you rerun the monitoring usage command, the previous usage information is wiped out and the USE flag is reset to NO.

WZHOU@dbm2>  alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME        TABLE_NAME     MON USE START_MONITORING    END_MONITORING
----------------- -------------- --- --- ------------------- ---------------
BIGTAB_OLTP_PK    BIGTAB_OLTP    YES NO  02/17/2014 11:01:01

WZHOU@dbm2> select owner, object_name from bigtab_oltp where id = 31;
OWNER        OBJECT_NAME
------------ ------------------------------
SYS          I_OBJ1

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    YES YES 02/17/2014        11:01:01

If you rebuild the index, the index is reported to be used.

WZHOU@dbm2> alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    YES NO  02/17/2014 12:04:21

WZHOU@dbm2> alter index bigtab_oltp_pk rebuild;

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    NO  YES 02/17/2014 12:04:21

To disable the monitoring, run the following command:
alter Index your_index_name nomonitoring usage;

WZHOU@dbm2>  alter index BIGTAB_OLTP_PK nomonitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    NO  YES 02/17/2014 11:01:01 02/17/2014 11:03:37

Although this approach can tell you whether the index is used or not, it does not tell you how many times the index has been used. There is a big difference between 1 time usage and 1,000 times usage. I also do not like the way to run alter index command first to enable usage and have to remember to run the alter index to disable the monitor after the analysis. In addition, this alter index has to be performed on every indexes you want to monitor.

DBA_HIST_SQL_PLAN
Another way is to use dba_hist_sql_plan to find out the index usage. This is not cover 100% usage, but majority of the usage. For the purpose to get a big picture of an index, the information is good enough.

As there are tons of indexes in the database, I usually starts from the index with largest size. Here is the script and sample output.

set line 160 pages 100
col object_owner for a20
col object_name for a35
col index_operation for a30
col total_megs for 999,999,999,999
col total_exec for 999,999,999

break on total_megs skip 1

with top_index as
(
    select * from (
        select
            owner,
            segment_name,
            sum(bytes/1024/1024) as total_megs,
            tablespace_name
        from dba_segments
        where
            segment_name in (
                select index_name
                from dba_indexes
                where
                    owner not in ('SYS', 'SYSTEM', 'SYSMAN','TSMSYS','DBSNMP','OUTLN'))
        group by owner, segment_name, tablespace_name
        order by total_megs desc
    )
    where
        rownum <= 100
)
select
    sp.object_owner,
    sp.object_name,
    decode( options, null, '   -', options) index_operation,
    ti.total_megs,
    count(sp.operation) total_exec,
    min(sp.timestamp) min_exec_date,
    max(sp.timestamp) max_exec_date
from dba_hist_sql_plan sp, top_index ti
where
    sp.object_owner = ti.owner
    and sp.object_name = ti.segment_name
group by
    sp.object_owner,
    sp.object_name,
    decode( options, null, '   -', options),
    ti.total_megs
order by
    ti.total_megs desc,
    sp.object_owner,
    sp.object_name,
    total_exec desc;

Let’s run a test.
WZHOU@dbm2> select /*+ index(bigtab_oltp BIGTAB_OLTP_PK) */ * from bigtab_oltp where id = 31;

ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
———- ——————– ———————————– —————————— ———- ————– ——————- ———
LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
——— ——————- ——- – – – ———- ——————————
31 SYS I_OBJ1 36 36 INDEX 12-MAR-13
12-MAR-13 2013-03-12:14:00:57 VALID N N N 4

Here is the result.

OBJECT_OWNER  OBJECT_NAME        INDEX_OPERATION TOTAL_MEGS TOTAL_EXEC MIN_EXEC_ MAX_EXEC_
------------- ------------------ --------------- ---------- ---------- --------- ---------
WZHOU         TESTCP_OBJID_IDX   RANGE SCAN       176	    2          17-FEB-14 17-FEB-14
WZHOU         TESTCP_OBJID_IDX   FULL SCAN                  1          17-FEB-14 17-FEB-14

WZHOU         TESTCP_PK          UNIQUE SCAN      175       1          17-FEB-14 17-FEB-14

WZHOU         BIGTAB_OLTP_PK     UNIQUE SCAN      117	    2          17-FEB-14 17-FEB-14
WZHOU         BIGTAB_OLTP_PK     RANGE SCAN                 2          21-FEB-14 21-FEB-14

WZHOU         BIGTAB_PK          UNIQUE SCAN      121       1          17-FEB-14 17-FEB-14   

If you could not find any rows from the above rows. Replace dba_hist_sql_plan with V$SQL_PLAN_STATISTICS_ALL.

Once you identify the indexes with less usage, you could make the index invisible first. Then after some period of time, if no negative impact on your system, you can safely drop these indexes.

Here is the command to make index invisible.
WZHOU@dbm2> alter index TESTCP_OBJID_IDX invisible;

Query to verify which indexes are in invisible state.

column tname format a39
column index_name for a30
column index_type format a15
select table_owner||’.’||table_name tname , index_name, index_type, status, visibility
from dba_indexes
where owner like nvl(‘&owner’,owner)
and table_name like nvl(‘&table_name’,table_name)
and index_name like nvl(‘&index_name’,index_name)
and visibility = ‘INVISIBLE’
order by 1,2
/

Enter value for owner: WZHOU
Enter value for table_name:
Enter value for index_name:

TNAME INDEX_NAME INDEX_TYPE STATUS VISIBILITY
————– ——————- ————— ——– ———
WZHOU.TESTCP TESTCP_OBJID_IDX NORMAL VALID INVISIBLE

There is a scenario that you have mixed workload in the same database. Sometimes for data warehouse type of queries, you would like not use the index, but do the full table scan. But for OLTP type queries, you want to use the index. One solution to handle this scenario is to make the index invisible, so optimizer will go for full table scan for the data warehouse type of queries. For OTLP queries, set OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at session level and will pick up this invisible index for the OLTP queries.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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