How to see index usage (retrieve SQL)

In mine previous post How to see index usage (without "alter index ... monitoring usage"), I have shown how to start index usage analyze on general most top level.

After finding index candidates for any kind of action, before any action, best approach should be to make a proper drill down on any information connected with problematic index usage. The best starting point for that is SQLs that it are using this index.

Main idea is to be able to predict all consequences that our DBA drop task will produce on other database performance. Predicting and understanding is extremely important for any DBA task generally.

In mentioned topic, one of presented outputs was index usage output. This output shows indexes as well as number of execution in past period. From this huge output I'll focus on smaller example that I find interesting to be analyzed.:

Table name                     Index name                Index type     Size MB Index operation       Executions
------------------------------ ------------------------- ---------- ----------- --------------------- --------
NC_TRANSACTION_ULAZ_TT         TRUT_I                    NORMAL          774.00 RANGE SCAN                     1
                                                                                FULL SCAN                      1
                               TRUT_GRUPA_PROIZVODA_I    NORMAL          888.00 FULL SCAN                      1
                               TRUT_MATRICULE_I          NORMAL          848.00        -                       0
                               TRUT_RESEAU_GARE_I        NORMAL          784.00 FULL SCAN                      1
                               TRUT_RTRIM_I              NORMAL          888.00 FULL SCAN                      6
****************************** ************************* ********** -----------                       ----------
sum                                                                    4,956.00                               10

This part takes mine interest because of big index size (cca 5GB) and information that all indexes are used in FULL SCAN operation, the worst possible for index. So first one that took mine interest was one with most of executions TRUT_RTRIM_I.

The solution

First I did check index quality in a described way :

SQL> @index_q TRUT_RTRIM_I
Data represented for INDEX like "TRUT_RTRIM_I"

                             Table   Table                    Index Data Blks Leaf Blks        Clust Index
Table                         Rows  Blocks Index            Size MB   per Key   per Key       Factor Quality
---------------------- ----------- ------- -------------- --------- --------- --------- ------------ -------
NC_TRANSACTION_ULAZ_TT     840,179   27437 TRUT_RTRIM_I      888.00         5         1      839,704 1-Poor
SQL>

Get SQL (that use that index)

As espected, quality was poor, but still it was not clear why index was used at all? For that I need to check parent SQLs that CBO with this index was perfomed. To identify that I use next script:

SELECT 
      p.dbid, 
      p.sql_id, 
      P.OBJECT_OWNER  A_INDEX_OWNER, 
      p.object_name   A_INDEX_NAME, 
      p.options       A_INDEX_OPERATION, 
      COUNT(*) NR_EXEC 
 FROM dba_hist_sql_plan p 
WHERE p.object_name = '&&1' 
GROUP BY 
      P.dbid, 
      P.sql_id, 
      P.OBJECT_OWNER, 
      p.object_name, 
      p.options
;

Real result for TRUT_RTRIM_I index was:

DBID SQL_ID        A_INDEX_OWNER  A_INDEX_NAME     A_INDEX_OPERATION  NR_EXEC
---------- ------------- -------------- ---------------- ------------------ ----------
1234567890 1xku33j735usd TAB            TRUT_RTRIM_I     FULL SCAN           1
1234567890 4xc72tpm96zgm TAB            TRUT_RTRIM_I     FULL SCAN           1
1234567890 bncypfk1h0xd1 TAB            TRUT_RTRIM_I     FULL SCAN           1
1234567890 ftr0zpfdn9kfr TAB            TRUT_RTRIM_I     FULL SCAN           1
1234567890 b390nk5ca9brm TAB            TRUT_RTRIM_I     FULL SCAN           1
1234567890 9k183pk5rn2h9 TAB            TRUT_RTRIM_I     FULL SCAN           1

6 rows selected.

Here dbid is as insurance that some of result may not come from previous database incarnation, what might suggest to some unusual upgrades or recovers what really might change CBO plans.

In a case like presented (there is no grouped results-no reusable cursor!) you have two options to find parent SQL:

1. Find SQL for each execution (for each sql_id)
2. Find SQL in a better way.

For second option I use next query which helps me in such a cases. idea is to group SQL by first n signs, what gives you a good chance to see picture more clearly. In mine case n=100 chars. Here is the query definition:

SELECT 
       DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1) sql,
       count(*) NR_EXEC
  FROM dba_hist_sqltext t
 WHERE EXISTS ( SELECT 1 
                 FROM dba_hist_sql_plan p
                WHERE p.object_name = '&&1'
                  AND t.dbid=p.dbid
                  AND t.sql_id=p.sql_id
              )
 GROUP BY DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1);

When you pass TRUT_RTRIM_I index as parameter, the result was:

SQL                                                                                                   NR_EXEC
----------------------------------------------------------------------------------------------------- -------
SELECT                                           /*+ index (u1 TRUT_RTRIM_I)*/                           1
      ID_RESEAU,


select /*+ no_parallel_index(t,"TRUT_RTRIM_I") dbms_stats cursor_sharing_exact use_weak_name_resl dy     1
SELECT /*+ index (u1 TRUT_RTRIM_I)*/ ID_RESEAU , ID_GARE , VOIE , DATE_TRANSACTION , DATUM_ULAZA , R     2
SELECT /*+ index (u1 TRUT_RTRIM_I)*/ ID_RESEAU , ID_GARE , VOIE , DATE_TRANSACTION , RTRIM_CONTENU_I     2

It is more then obvious that SQL that interest me must start with SELECT /*+ index (u1 TRUT_RTRIM_I)*/ ID_RESEAU part. So let me find those sql_id's:

SELECT sql_id
  FROM dba_hist_sqltext t
 WHERE EXISTS ( SELECT 1 
                 FROM dba_hist_sql_plan p
                WHERE p.object_name = '&&1'
                  AND t.dbid=p.dbid
                  AND t.sql_id=p.sql_id
              )
    AND INSTR (DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1),'SELECT /*+ index (u1 TRUT_RTRIM_I)*/')>0

Result was:

SQL_ID
-------------
ftr0zpfdn9kfr
9k183pk5rn2h9
4xc72tpm96zgm
bncypfk1h0xd1

Ok let me take "4xc72tpm96zgm" and see the query:

select t.sql_text
  from dba_hist_sqltext t
 where sql_id ='4xc72tpm96zgm';

Result for that sql_id was:

SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ index (u1 TRUT_RTRIM_I)*/ ID_RESEAU , ID_GARE , VOIE , DATE_TRANSACTI
ON , RTRIM_CONTENU_ISO FROM NC_TRANSACTION_ULAZ_TT U1 WHERE U1.RTRIM_CONTENU_ISO
 IS NOT NULL AND U1.RTRIM_CONTENU_ISO != '0' AND U1.RTRIM_CONTENU_ISO != '00000'
 AND U1.RTRIM_CONTENU_ISO != '0000000000' AND U1.RTRIM_CONTENU_ISO != '000000000
000' AND LENGTH(RTRIM_CONTENU_ISO) <= 5 AND IZLAZ_DATE_TRANSACTION IS NULL

Regardless this index has many illogical parts (maybe that came from some smart generator like Oracle designer!!): 

• Why to use U1.RTRIM_CONTENU_ISO IS NOT NULL
• Why to use U1.RTRIM_CONTENU_ISO != '0000000000' AND U1.RTRIM_CONTENU_ISO != '000000000000' when at the end was LENGTH (RTRIM_CONTENU_ISO) <= 5

This has nothing in this context, so let us forget it for a moment. So, after checking all others SQLs, in a similar way, I found out that all start similarly with index hint /*+ index (u1 TRUT_RTRIM_I)*/. Only one (select /*+ no_parallel_index(t,"TRUT_RTRIM_I") dbms_stats cursor_sharing_exact ...), was different but I find it as remains of some non user process ... however not applicable for normal use.

Analyze/Tune SQL

Let us see CBO plan for that (not from AWR but directly from sqlplus):

explain plan for
SELECT                                           /*+ index (u1 TRUT_RTRIM_I)*/
      ID_RESEAU,
       ID_GARE,
       VOIE,
       DATE_TRANSACTION,
       RTRIM_CONTENU_ISO
  FROM NC_TRANSACTION_ULAZ_TT U1
 WHERE     U1.RTRIM_CONTENU_ISO IS NOT NULL
       AND U1.RTRIM_CONTENU_ISO != '0'
       AND U1.RTRIM_CONTENU_ISO != '00000'
       AND U1.RTRIM_CONTENU_ISO != '0000000000'
       AND U1.RTRIM_CONTENU_ISO != '000000000000'
       AND LENGTH (RTRIM_CONTENU_ISO) <= 5
       AND IZLAZ_DATE_TRANSACTION IS NULL;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3823061142

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |  1309 | 44506 | 44860   (1)| 00:08:59 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| NC_TRANSACTION_ULAZ_TT |  1309 | 44506 | 44860   (1)| 00:08:59 |
|*  2 |   INDEX FULL SCAN           | TRUT_RTRIM_I           | 42008 |       |  2849   (1)| 00:00:35 |
------------------------------------------------------------------------------------------------------

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

   1 - filter("IZLAZ_DATE_TRANSACTION" IS NULL)
   2 - filter(LENGTH("RTRIM_CONTENU_ISO")<=5 AND "U1"."RTRIM_CONTENU_ISO"<>'0' AND
              "U1"."RTRIM_CONTENU_ISO"<>'00000' AND "U1"."RTRIM_CONTENU_ISO"<>'0000000000' AND
              "U1"."RTRIM_CONTENU_ISO"<>'000000000000' AND "U1"."RTRIM_CONTENU_ISO" IS NOT NULL)

17 rows selected.

When index hint was used, cost was 44506. I tried to execute query and found out that time was 27 seconds (average in 3 measurements). Here is prove with Toad picture:

Because index has poor quality, when Oracle use it, it was making terrible mistake, sequentially run through index blocks was a pointer to get table blocks (data). If index is same size as table this is totally unacceptable. So mine first logical reaction was to remove hint.   Let us see the explain plan for that:

explain plan for
SELECT
      ID_RESEAU,
       ID_GARE,
       VOIE,
       DATE_TRANSACTION,
       RTRIM_CONTENU_ISO
  FROM NC_TRANSACTION_ULAZ_TT U1
 WHERE     U1.RTRIM_CONTENU_ISO IS NOT NULL
       AND U1.RTRIM_CONTENU_ISO != '0'
       AND U1.RTRIM_CONTENU_ISO != '00000'
       AND U1.RTRIM_CONTENU_ISO != '0000000000'
       AND U1.RTRIM_CONTENU_ISO != '000000000000'
       AND LENGTH (RTRIM_CONTENU_ISO) <= 5
       AND IZLAZ_DATE_TRANSACTION IS NULL;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 967985896

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |  1309 | 44506 |  6134   (3)| 00:01:14 |
|*  1 |  TABLE ACCESS FULL| NC_TRANSACTION_ULAZ_TT |  1309 | 44506 |  6134   (3)| 00:01:14 |
--------------------------------------------------------------------------------------------

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

   1 - filter("IZLAZ_DATE_TRANSACTION" IS NULL AND LENGTH("RTRIM_CONTENU_ISO")<=5
              AND "U1"."RTRIM_CONTENU_ISO"<>'0' AND "U1"."RTRIM_CONTENU_ISO"<>'00000' AND
              "U1"."RTRIM_CONTENU_ISO"<>'0000000000' AND "U1"."RTRIM_CONTENU_ISO"<>'000000000000'
              AND "U1"."RTRIM_CONTENU_ISO" IS NOT NULL)
---------------------------------------------------------------------------------------------------
16 rows selected.

Now cost was 6134. I tried to execute query and time was 0.063 seconds. This was 428,57 times faster then original! Here is prove again with Toad picture:

The end

So after removing hint part from problematic SQL (grep dba_source or forms/reports binaries) there was no need to have that index so it was dropped. Similar first one was with all other indexes, what frees up 5 GB in underlying datafiles, reduce datafiles fragmentation and speed up all future DML operations on this table.  The whole index_usage_sql.sql script (that I've used in this post) is here:

/* ---------------------------------------------------------------------------
 CR/TR#  :
 Purpose : Shows index usage against involved sql

 Date    : 28.03.2009.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : run as privileged user
           Must have AWR run because sql joins data from there
           works on 10g > 
           
           first parameter is "index name"
           
 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */
set linesize 120
set timi off

col OWNER for a10

SELECT 
      p.dbid, 
      p.sql_id, 
      P.OBJECT_OWNER OWNER, 
      p.object_name A_INDEX_NAME, 
      p.options INDEX_OPERATION, 
      COUNT(*) NR_EXEC 
 FROM dba_hist_sql_plan p 
WHERE p.object_name = '&&1' 
GROUP BY 
      P.dbid, 
      P.sql_id, 
      P.OBJECT_OWNER, 
      p.object_name, 
      p.options
;

col sql for a101
col nr_exec for 999G990

SELECT 
       DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1) sql,
       count(*) NR_EXEC
  FROM dba_hist_sqltext t
 WHERE EXISTS ( SELECT 1 
                 FROM dba_hist_sql_plan p
                WHERE p.object_name = '&&1'
                  AND t.dbid=p.dbid
                  AND t.sql_id=p.sql_id
              )
 GROUP BY 
       DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1)
;

PROMPT Showed SQLs for &&1 index in period:
SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
       || '-' ||
       to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;
SET HEAD ON;

PROMPT index_usage.sq for overall usage, index_stat.sql or index_frag.sql shows statistic for bad index (validating indexes)
PROMPT Run this script to get sql_id:
PROMPT SELECT sql_id
PROMPT   FROM dba_hist_sqltext t
PROMPT  WHERE EXISTS ( SELECT 1 
PROMPT                  FROM dba_hist_sql_plan p
PROMPT                 WHERE p.object_name = '1'
PROMPT                   AND t.dbid=p.dbid
PROMPT                   AND t.sql_id=p.sql_id
PROMPT               )
PROMPT     AND INSTR (DBMS_LOB.SUBSTR (ltrim(rtrim(t.sql_text)), 100, 1),'2')>0

PROMPT Change "1" with index name and "2" start of sql
set timi on

Hope this will help someone in this kinds of tasks.

Cheers!