Who is using this index?

Or, to put it another way, I want to change or drop this index, who and what will I impact?

The Challenge 

The problem that I am going to outline is certainly not exclusive to PeopleSoft, but I am going to illustrate it with examples from PeopleSoft. I often find tables with far more indexes than are good for them.

• The Application Designer tool makes it very easy for developers to add indexes to tables. Sometimes, too easy!
• Sometimes, DBAs are too quick to unquestioningly follow the advice of the Oracle tuning advisor to add indexes.

Recently, I have been working on 3 different PeopleSoft Financials systems where I have found major tables with a host of indexes.

There are several concerns:

• Indexes are maintained during data modification. The more indexes you have, the greater the overhead. 
• The more indexes you have, particularly if they lead on the same columns, the more likely Oracle is to use the wrong one, resulting in poorer performance.
• There is of course also a space overhead for each index, but this is often of less concern. 

If you can get rid of an index, Oracle doesn't store, maintain or use it. 

In some cases, I have wanted to remove unnecessary indexes, and in others to adjust indexes. However, this immediately raises the question of where are these indexes used, and who will be impacted by the change. Naturally, I turn to the Active Session History (ASH) to help me find the answers. 

Index Maintenance Overhead during DDL 

ASH reports the object number, file number, block number, and (from 11g) row number within the block being accessed by physical file operations. However, the values reported in v$active_session_history (and later other views) are not reliable for other events because they are merely left over from the previous file event that reported them. So, we can profile the amount of time spent on physical I/O on different tables and indexes, but not for other forms of DB Time, such as CPU time, spent accessing the blocks in the buffer cache.

Let me take an extreme example from PeopleSoft Global Payroll. The table PS_GP_RSLT_ACUM is one of the principal result tables. It has only a single unique index (with the same name). The table is populated with the simplest of insert statements.

INSERT /*GPPRDMGR_I_ACUM*/ INTO PS_GP_RSLT_ACUM
(CAL_RUN_ID ,EMPLID ,EMPL_RCD ,GP_PAYGROUP ,CAL_ID ,RSLT_SEG_NUM ,PIN_NUM ,EMPL_RCD_ACUM ,ACM_FROM_DT ,ACM_THRU_DT ,USER_KEY1 ,USER_KEY2 ,USER_KEY3 ,USER_KEY4 ,USER_KEY5 ,USER_KEY6 ,SLICE_BGN_DT ,SLICE_END_DT ,COUNTRY ,ACM_TYPE ,ACM_PRD_OPTN ,CALC_RSLT_VAL ,CALC_VAL ,USER_ADJ_VAL ,PIN_PARENT_NUM ,CORR_RTO_IND ,ORIG_CAL_RUN_ID ,SEQ_NUM8 ,VALID_IN_SEG_IND ,CALLED_IN_SEG_IND )
VALUES
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30)

I can profile the ASH data for just this statement over the last week on a production system. Note that DBA_OBJECTS and DBA_DATA_FILES are outer joined to the ASH data and only matched for events like 'db file%'

SELECT o.object_type, o.object_name
,      f.tablespace_name, NVL(h.event,'CPU+CPU Wait') event
,      SUM(10) ash_Secs
FROM dba_hist_Active_sess_history h
  LEFT OUTER JOIN dba_objects o
    ON o.object_id = h.current_obj#
   AND h.event like 'db file%'
  LEFT OUTER JOIN dba_data_files f
    ON f.file_id = h.current_file#
   AND h.event like 'db file%'
WHERE h.sql_id = '4ru0618dswz3y'
AND   h.sample_time >= sysdate-7
GROUP BY o.object_type, o.object_name, h.event, f.tablespace_name
ORDER BY ash_secs DESC
/

A full payroll calculation inserts over 3 million rows on this particular system. The calculation is run incrementally several times per week during which old rows are deleted and newly recalculated rows inserted.  Looking at just this insert statement:

• 30% of the time is spent on CPU operations, we cannot profile that time further with ASH.
• 38% of the time is spent reading from the table and index, yet this is a simple INSERT … VALUES statement.

OBJECT_TYPE         OBJECT_NAME        TABLESPACE_NAME EVENT                      ASH_SECS
------------------- ------------------ --------------- ------------------------ ----------
                                                       CPU+CPU Wait                   1040
                                       UNDOTBS1        db file sequential read         900
INDEX SUBPARTITION  PS_GP_RSLT_ACUM    GP201408IDX     db file sequential read         750
TABLE SUBPARTITION  PS_GP_RSLT_ACUM    GP201408TAB     db file sequential read         550
                                                       gc current grant 2-way           70
                                                       cursor: pin S wait on X          60
                                                       db file sequential read          10
                                                       buffer exterminate               10
                                                       row cache lock                   10
                                                                                ----------
                                                                                      3400 

More time is spent reading the index than the table.  That is not a surprise.  When you insert a row into a table, you also insert it into the index. Rows in index leaf blocks are ordered by the key columns, and the new entry must go into the right place, so you have to read down the index from the root block, through the branch blocks, to find the correct leaf block for the new entry.
[Digression: Counter-intuitively index compression can improve DML performance. It does for this index.  The overhead of the compression processing can be outweighed by the savings in physical I/O.  It depends.]

Profile Physical I/O by Object 

I can twist this query around and profile DB_TIME by object for 'db file%' events

SELECT o.object_type, o.object_name, sum(10) ash_secs
FROM   dba_hist_active_sess_history h
,      dba_objects o
WHERE  o.object_id = h.current_obj#
AND    h.event LIKE 'db file%'
AND    h.sample_time > sysdate-7
GROUP  BY o.object_type, o.object_name
ORDER BY ash_Secs DESC

Now I can see upon which objects the most time is spent on physical I/O.

OBJECT_TYP OBJECT_NAME          ASH_SECS
---------- ------------------ ----------
TABLE      PS_ITEM                101130
INDEX      PS_WS_ITEM              98750
TABLE      PS_PROJ_RESOURCE        97410
TABLE      PS_BI_LINE              85040
INDEX      PSAPSAPMSGSUBCON        75070
TABLE      PS_BI_HDR               37230
TABLE      PS_RS_ASSIGNMENT        29460
INDEX      PS_PSAPMSGPUBHDR        23230
INDEX      PS_BI_ACCT_ENTRY        21490
TABLE      PS_VOUCHER              21330
TABLE      PS_VCHR_ACCTG_LINE      21250
TABLE      PS_BI_ACCT_ENTRY        18860
…
                              ----------
sum                              1382680

This is a worthwhile exercise, it shows the sources of physical I/O in an application.

However, if you want to find where an index is used, then this query will also identify SQL_IDs where the index is either used in the query or maintained by DML. If I am interested in looking for places where changing or deleting an index could have an impact then I am only interested in SQL query activity. ASH samples that relate to index maintenance are a false positive. Yet, I cannot simply eliminate ASH samples where the SQL_OPNAME is not SELECT because the index may be used in a query within the DML statement.

Another problem with this method is that it matches SQL to ASH by object ID. If someone has rebuilt an index, then its object number changes.

A different approach is required.

Index Use from SQL Plans Captured by AWR 

During an AWR snapshot the top-n SQL statements by each SQL criteria in the AWR report (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count) , see dbms_workload_repository. The SQL plans are exposed by the view DBA_HIST_SQL_PLAN.

On PeopleSoft systems, I generally recommend decreasing the snapshot interval from the default of 60 minutes to 15. The main reason is that SQL gets aged out of the library cache very quickly in PeopleSoft systems. They generate lots of dynamic code, often with literal values rather than bind variables. Cursor sharing is not recommended for PeopleSoft, so different bind variables result in different SQL_IDs. The dynamic code also results in different SQL IDs even with cursor sharing. Therefore, increasing the snapshot frequency means that will capture more SQL statements. This will increase the total volume of the AWR repository simply because there are more snapshots. However, the overall volume of ASH data captured does not change, it just gets copied to the repository earlier.

On DBA_HIST_SQL_PLAN the object ID, owner, type, and name are recorded, so I can find the plans which referenced a particular object. I am going to carry on with the example from a PeopleSoft Financials system and look at indexes on the PS_PROJ_RESOURCE table.

These are some of the indexes on PS_PROJ_RESOURCE. We have 4 indexes that all lead on PROCESS_INSTANCE. I suspect that not all are essential, but I need to work out what is using them, and which one I should retain.

                          Col
INDEX_NAME                Pos COLUMN_NAME          COLUMN_EXPRESSION
------------------ ---------- -------------------- ----------------------------------
…
PSJPROJ_RESOURCE            1 PROCESS_INSTANCE
                            2 BUSINESS_UNIT_GL
                            3 BUSINESS_UNIT
                            4 PROJECT_ID
                            5 ACTIVITY_ID
                            6 CUST_ID

PSLPROJ_RESOURCE            1 PROCESS_INSTANCE
                            2 EMPLID
                            3 EMPL_RCD
                            4 TRANS_DT

PSMPROJ_RESOURCE            1 PROCESS_INSTANCE
                            2 BUSINESS_UNIT
                            3 PROJECT_ID
                            4 ACTIVITY_ID
                            5 RESOURCE_ID

PSNPROJ_RESOURCE            1 PROCESS_INSTANCE
                            2 BUSINESS_UNIT
                            3 TIME_RPTG_CD
…

I find it easier to extract the ASH data to my own working storage table. For each index on PS_PROJ_RESOURCE, I am going to extract a distinct list of plan hash values. I will then extract all ASH data for those plans. Note, that I have not joined the SQL_ID on DBA_HIST_SQL_PLAN. That is because different SQL_IDs can produce the same execution plan. The plan is equally valid for all SQL_IDs that produce the plan, not just the one where the SQL_ID also matches.

DROP TABLE my_ash purge
/
CREATE TABLE my_ash COMPRESS AS
WITH p AS (
  SELECT DISTINCT p.plan_hash_value, p.object#, p.object_owner, p.object_type, p.object_name
  FROM      dba_hist_sql_plan p
  WHERE     p.object_name like 'PS_PROJ_RESOURCE'
 AND p.object_type LIKE 'INDEX%'
  AND p.object_owner = 'SYSADM'
 )
SELECT p.object# object_id, p.object_owner, p.object_type, p.object_name
,      h.*
FROM   dba_hist_active_sess_history h
,      p
WHERE  h.sql_plan_hash_value = p.plan_hash_value
/ 

I am fortunate that PeopleSoft is a well-instrumented application. Module and Action are set to fairly sensible values that will tell me the whereabouts in the application to which the ASH sample relates. In the following query, I have omitted any ASH data generated by SQL*Plus, Toad, or SQL Developer, and also any generated by Oracle processes to prevent statistics collection jobs from being included.

Set pages 999 lines 150 trimspool on
break on object_name skip 1 
compute sum of ash_secs on object_name
column ash_secs heading 'ASH|Secs' format 9999999
column module format a20
column action format a32
column object_name format a18
column max_sample_time format a19 heading 'Last|Sample'
column sql_plans heading 'SQL|Plans' format 9999
column sql_execs heading 'SQL|Execs' format 99999
WITH h AS (
  SELECT   object_name
  ,  CASE WHEN h.module IS NULL THEN REGEXP_SUBSTR(h.program,'[^.@]+',1,1)
                 WHEN h.module LIKE 'PSAE.%' THEN REGEXP_SUBSTR(h.module,'[^.]+',1,2)
                         ELSE REGEXP_SUBSTR(h.program,'[^.@]+',1,1)
   END as module
  ,  CASE WHEN h.action LIKE 'PI=%' THEN NULL
        ELSE h.action
                    END as action
  , CAST(sample_time AS DATE) sample_time
  ,  sql_id, sql_plan_hash_value, sql_exec_id
  FROM    my_ash h
)
SELECT  object_name, module, action
, sum(10) ash_secs
, COUNT(DISTINCT sql_plan_hash_value) sql_plans
, COUNT(DISTINCT sql_id||sql_plan_hash_value||sql_exec_id) sql_execs
, MAX(sample_time) max_sample_time
FROM     h
WHERE NOT LOWER(module) IN('oracle','toad','sqlplus','sqlplusw')
AND       NOT LOWER(module) LIKE 'sql%'
GROUP BY object_name, module, action
ORDER BY SUBSTR(object_name,4), object_name, ash_Secs desc
/
Spool off

I now have a profile of how much each index is used. In this particular case, I found something using every index.  It is possible that you will not find anything that uses some indexes.

                                                                             ASH   SQL    SQL Last
OBJECT_NAME        MODULE               ACTION                              Secs Plans  Execs Sample
------------------ -------------------- -------------------------------- ------- ----- ------ -------------------
…
PSJPROJ_RESOURCE   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step24.S        7300     1     66 06:32:57 27/08/2014
                   PC_PRICING           GFCPBINT_AE.CallmeA.Step24.S          40     1      2 08:38:57 22/08/2014
******************                                                       -------
sum                                                                         7340

PSLPROJ_RESOURCE   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step28.S        1220     1     53 06:33:17 27/08/2014
******************                                                       -------
sum                                                                         1220

PSMPROJ_RESOURCE   PC_TL_TO_PC          GFCPBINT_AE.XxBiEDM.Step07.S          60     2      6 18:35:18 20/08/2014
******************                                                       -------
sum                                                                           60

PSNPROJ_RESOURCE   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step26.S        6720     1     49 18:53:58 26/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step30.S        3460     1     60 06:33:27 27/08/2014
                   GFCOA_CMSN           GFCOA_CMSN.01INIT.Step01.S          2660     1     47 19:19:40 26/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step06.S        1800     1     52 18:53:28 26/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeG.Step01.S        1740     1     61 06:34:17 27/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step02.S        1680     1     24 18:53:18 26/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step10.S        1460     1     33 17:26:26 22/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step08.S         920     1     26 17:26:16 22/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step36.S         460     1     18 18:26:38 20/08/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Step09.S         420     1     16 06:33:07 27/08/2014
                   PC_PRICING           GFCPBINT_AE.CallmeG.Step01.S         200     1     10 08:09:55 22/08/2014
                   PC_AP_TO_PC          GFCPBINT_AE.CallmeH.Step00A.S        170     1     17 21:53:26 21/08/2014
                   PC_PRICING           GFCPBINT_AE.CallmeA.Step36.S          20     1      1 08:02:46 05/08/2014
                   PC_PRICING           GFCPBINT_AE.CallmeA.Step30.S          20     1      1 13:42:48 04/08/2014
                   PC_PRICING           GFCPBINT_AE.CallmeA.Step06.S          20     1      1 15:58:35 28/07/2014
                   PC_TL_TO_PC          GFCPBINT_AE.CallmeA.Pseudo.S          20     1      1 19:45:11 06/08/2014
******************                                                       -------
sum                                                                        21770
…

The next stage is to look at individual SQL statements This query looks for which SQL statement is using a particular index on PROJ_RESOURCE. If I can't find the SQL that cost the most time, then just choose another SQL with the same plan

• I have found that sometimes a plan is captured by AWR, but the SQL statement is not. Personally, I think that is a bug. Working around it has made the following query quite complicated.

Break on object_name skip 1 
column ash_secs heading 'ASH|Secs' format 9999999
Set long 50000
Column cmd Format a200
Spool dmk

WITH h AS (
  SELECT  h.object_name
  ,       CASE WHEN h.module IS NULL THEN REGEXP_SUBSTR(h.program,'[^.@]+',1,1)
               WHEN h.module LIKE 'PSAE.%' THEN REGEXP_SUBSTR(h.module,'[^.]+',1,2)
               ELSE REGEXP_SUBSTR(h.program,'[^.@]+',1,1)
          END as module
  ,       CASE WHEN h.action LIKE 'PI=%' THEN NULL
               ELSE h.action
          END as action
  ,       h.sql_id, h.sql_plan_hash_value
  ,       t.command_type –-not null if plan and statement captured
  FROM    my_ash h
  LEFT OUTER JOIN (
    SELECT t1.*
    FROM dba_hist_sqltext t1
    ,    dba_hist_sql_plan p1
   WHERE t1.sql_id = p1.sql_id
    AND p1.id = 1
    ) t
  ON   t.sql_id = h.sql_id
   AND  t.dbid = h.dbid
  WHERE   h.object_name IN('PSMPROJ_RESOURCE')
  AND     h.object_Type = 'INDEX'
  AND     h.object_owner = 'SYSADM'
  And     NOT LOWER(module) IN('oracle','toad','sqlplus','sqlplusw')
  AND     NOT LOWER(module) LIKE 'sql%'
), x AS ( --aggregate DB time by object and statement
SELECT    object_name, sql_id, sql_plan_hash_value
, sum(10) ash_secs
,  10*COUNT(command_type) sql_secs  --DB time for captured statements only
FROM      h
WHERE NOT LOWER(module) IN('oracle','toad','sqlplus','sqlplusw')
AND       NOT LOWER(module) LIKE 'sql%'
GROUP BY  object_name, sql_id, sql_plan_hash_value
), y AS ( --rank DB time per object and plan
SELECT  object_name, sql_id, sql_plan_hash_value
,  ash_secs
, SUM(ash_secs) OVER (PARTITION BY object_name, sql_plan_hash_value) plan_ash_secs
, row_number() OVER (PARTITION BY object_name, sql_plan_hash_value ORDER BY sql_Secs DESC) ranking
FROM x
), z AS (
SELECT object_name
, CASE WHEN t.sql_text IS NOT NULL THEN y.sql_id
       ELSE (SELECT t1.sql_id
             FROM   dba_hist_sqltext t1
             ,      dba_hist_sql_plan p1
             WHERE  t1.sql_id = p1.sql_id
             AND    p1.plan_hash_value = y.sql_plan_hash_value
             AND    rownum = 1) --if still cannot find statement just pick any one
  END AS sql_id
, y.sql_plan_hash_value, y.plan_ash_secs
, CASE WHEN t.sql_text IS NOT NULL THEN t.sql_text 
       ELSE (SELECT t1.sql_Text
             FROM   dba_hist_sqltext t1
             ,      dba_hist_sql_plan p1
             WHERE  t1.sql_id = p1.sql_id
             AND    p1.plan_hash_value = y.sql_plan_hash_value
             AND    rownum = 1) --if still cannot find statement just pick any one
  END AS sql_text
from y
 left outer join dba_hist_sqltext t
 on t.sql_id = y.sql_id
WHERE ranking = 1 --captured statement with most time
)
SELECT *
--'SELECT * FROM table(dbms_xplan.display_awr('''||sql_id||''','||sql_plan_hash_value||',NULL,''ADVANCED''))/*'||object_name||':'||plan_ash_Secs||'*/;' cmd
FROM z
ORDER BY object_name, plan_ash_secs DESC
/
Spool off

So now I can see the individual SQL statements.

PSJPROJ_RESOURCE   f02k23bqj0xc4          3393167302          7340 UPDATE PS_PROJ_RESOURCE C SET (C.Operating_Unit, C.CHARTFIELD1, C.PRODUCT, C.CLA
                                                                   SS_FLD, C.CHARTFIELD2, C.VENDOR_ID, C.contract_num, C.contract_line_num, …

PSLPROJ_RESOURCE   2fz0gcb2774y0           821236869          1220 UPDATE ps_proj_resource p SET p.deptid = NVL (( SELECT j.deptid FROM ps_job j WH
                                                                   ERE j.emplid = p.emplid AND j.empl_rcd = p.empl_rcd AND j.effdt = ( SELECT MAX (…

PSMPROJ_RESOURCE   96cdkb7jyq863           338292674            50 UPDATE PS_GFCBI_EDM_TA04 a SET a.GFCni_amount = ( SELECT x.resource_amount FROM
                                                                   PS_PROJ_RESOURCE x WHERE x.process_instance = …

                   1kq9rfy8sb8d4          4135884683            10 UPDATE PS_GFCBI_EDM_TA04 a SET a.GFCni_amount = ( SELECT x.resource_amount FROM
                                                                   PS_PROJ_RESOURCE x WHERE x.process_instance = …

PSNPROJ_RESOURCE   ga2x2u4jw9p0x          2282068749          6760 UPDATE PS_PROJ_RESOURCE P SET (P.RESOURCE_TYPE, P.RESOURCE_SUB_CAT) = …

                   9z5qsq6wrr7zp          3665912247          3500 UPDATE PS_PROJ_RESOURCE P SET P.TIME_SHEET_ID = …

If I replace the last select clause with the commented line, then I can generate the commands to extract the statement and plan from the AWR repository.

SELECT * FROM table(dbms_xplan.display_awr('45ggt0yfrh5qp',3393167302,NULL,'ADVANCED'))/*PSJPROJ_RESOURCE:7340*/;

SELECT * FROM table(dbms_xplan.display_awr('8ntxj3694r6kg',821236869,NULL,'ADVANCED'))/*PSLPROJ_RESOURCE:1220*/;

SELECT * FROM table(dbms_xplan.display_awr('96cdkb7jyq863',338292674,NULL,'ADVANCED'))/*PSMPROJ_RESOURCE:50*/;

SELECT * FROM table(dbms_xplan.display_awr('1kq9rfy8sb8d4',4135884683,NULL,'ADVANCED'))/*PSMPROJ_RESOURCE:10*/;

SELECT * FROM table(dbms_xplan.display_awr('ga2x2u4jw9p0x',2282068749,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:6760*/;
SELECT * FROM table(dbms_xplan.display_awr('9z5qsq6wrr7zp',3665912247,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:3500*/;
SELECT * FROM table(dbms_xplan.display_awr('b28btd6k3x8jt',1288409804,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:3060*/;
SELECT * FROM table(dbms_xplan.display_awr('avs70c19khxmw',2276811880,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:2660*/;
SELECT * FROM table(dbms_xplan.display_awr('b78qhsch85g4a',1019599680,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:1960*/;
SELECT * FROM table(dbms_xplan.display_awr('65kq2v1ubybps',3138703971,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:1820*/;
SELECT * FROM table(dbms_xplan.display_awr('1dj17ra70c801',1175874548,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:1460*/;
SELECT * FROM table(dbms_xplan.display_awr('3w71v896s7m5d',3207074729,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:500*/;
SELECT * FROM table(dbms_xplan.display_awr('35mz5bw7p5ubw',2447377432,NULL,'ADVANCED'))/*PSNPROJ_RESOURCE:170*/;

Ultimately, I have needed to look through the SQL plans that use an index to decide whether I need to keep that index or decide whether the statement would perform adequately using another index. In this case, on this particular system, I think the index PSMPROJ_RESOURCE would be adequate for this statement, and I would consider dropping PSLPROJ_RESOURCE.

>SELECT * FROM table(dbms_xplan.display_awr('8ntxj3694r6kg',821236869,NULL,'ADVANCED'))/*PSLPROJ_RESOURCE:1220*/;
--------------------
UPDATE ps_proj_resource p SET p.deptid = NVL (( SELECT j.deptid FROM
ps_job j WHERE j.emplid = p.emplid AND j.empl_rcd = p.empl_rcd AND
j.effdt = ( SELECT MAX (j1.effdt) FROM ps_job j1 WHERE j1.emplid =
j.emplid AND j1.empl_rcd = j.empl_rcd AND j1.effdt <= p.trans_dt) AND
j.effseq = ( SELECT MAX (j2.effseq) FROM ps_job j2 WHERE j2.emplid =
j.emplid AND j2.empl_rcd = j.empl_rcd AND j2.effdt = j.effdt)),
p.deptid ) 
WHERE p.process_instance = … 
AND EXISTS ( SELECT
j.deptid FROM ps_job j WHERE j.emplid = p.emplid AND j.empl_rcd =
p.empl_rcd AND j.effdt = ( SELECT MAX (j1.effdt) FROM ps_job j1 WHERE
j1.emplid = j.emplid AND j1.empl_rcd = j.empl_rcd AND j1.effdt <=
p.trans_dt) AND j.effseq = ( SELECT MAX (j2.effseq) FROM ps_job j2
WHERE j2.emplid = j.emplid AND j2.empl_rcd = j.empl_rcd AND j2.effdt =
j.effdt))

Plan hash value: 821236869

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |                  |       |       | 63104 (100)|          |
|   1 |  UPDATE              | PS_PROJ_RESOURCE |       |       |            |          |
|   2 |   INDEX RANGE SCAN   | PSLPROJ_RESOURCE |   365 | 11315 |    22   (0)| 00:00:01 |
|   3 |    INDEX RANGE SCAN  | PSAJOB           |     1 |    23 |     3   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE   |                  |     1 |    20 |            |          |
|   5 |      INDEX RANGE SCAN| PSAJOB           |     1 |    20 |     3   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE   |                  |     1 |    23 |            |          |
|   7 |      INDEX RANGE SCAN| PSAJOB           |     1 |    23 |     3   (0)| 00:00:01 |
|   8 |   INDEX RANGE SCAN   | PSAJOB           |     1 |    29 |     3   (0)| 00:00:01 |
|   9 |    SORT AGGREGATE    |                  |     1 |    20 |            |          |
|  10 |     INDEX RANGE SCAN | PSAJOB           |     1 |    20 |     3   (0)| 00:00:01 |
|  11 |    SORT AGGREGATE    |                  |     1 |    23 |            |          |
|  12 |     INDEX RANGE SCAN | PSAJOB           |     1 |    23 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
…

I carried on with the examination of SQL statements and execution plans to determine whether each index is really needed or another index (or even no index at all) would do as well.  This decision also requires some background knowledge about the application. Eventually, I decided that I want to drop the J, L, and N indexes on PROJ_RESOURCE and just keep M. 

Limitations of Method

AWR does not capture all SQLs, nor all SQL plans. First the SQL has to be in the library cache and then it must be one of the top-n. A SQL that is efficient because it uses an appropriate index may not be captured, and will not be detected by this approach. This might lead you to erronously believe that the index could be dropped.

ASH data is purged after a period of time, by default 31 days. If an index is only used by a process that has not run within the retention period, then it will not be detected by this approach. This is another reason to retain ASH and AWR in a repository for a longer period. I have heard 400 days suggested so that you have ASH for a year and a month.

• However, this also causes the SYSAUX tablespace to become very large, so I would suggest regularly moving the data to a separate database. I know one customer who has built a central AWR repository for all their production and test databases and automated regular transfer of data. That repository has been of immense diagnostic value.

[Update] This analysis will not detect index use in support of constraint validation (PeopleSoft doesn't use database referential integrity constraints).  As Mark Farnham points out below, that may be a reason for retaining a particular index.

Getting Rid of Indexes 

Obviously, any index changes need to be tested carefully in all the places that reference the index, but on the other hand, it is not viable to do a full regression test every time you want to change an index.

Therefore, if all the testing is successful and you decide to go ahead and drop the index in production, you might prefer to make it invisible first for a while before actually dropping it. It is likely that the indexes you choose to examine are large and will take time to rebuild. An invisible index will not be used by the optimizer, but it will continue to be maintained during DML. If there are any unfortunate consequences, you can immediately make the index visible without having to rebuild it.