Oracle-DB: Identify non-relevant indexes for secure deletion

A sensitive issue for DBAs and developers is often the detection and removal of indexes in Oracle databases that are not or no longer needed.
In practice systems can be found that allocate more than 50% of the storage with indexes without any productive relevance.
Nevertheless, this potential for a simple reduction of storage requirements and system load is regularly not used, freely after "never touch a running system" or "why should I burn my fingers here and then this index is still needed somewhere".
This article demonstrates how to securely determine irrelevant indexes using the freely available analysis tool "Panorama".

Why define indexes on tables

  1. Optimizing accesses from User-SQL
    - Reduction of access to the result-relevant records when reading table data
  2. Guarantee of uniqueness
    - Declaration of unique indexes or use of the index for primary key or unique constraints
  3. Protection of Foreign Key Constraints
    - Preventing full table scans when deleting or updating on the referenced tables
    - Prevent propagation of DML locks of the referenced table via Foreign Key Constraints
  4. Ensure structural identity for Partition Exchange
    - Table for partition exchange must be indexed identically to the partitioned target table
Conversely to the above list, the following applies: 
If an existing index does not fulfill any of the four roles mentioned, it can actually be removed.

What is the benefit of removing unnecessary indexes?

  • Reduction of storage requirements of the database
  • Reduction of the effort for index maintenance in DML operations
  • More efficient use of the DB cache 
  • Improvement of the runtime and response time behavior of applications

Why is there nevertheless often no active evaluation and removal of unnecessary indexes?

  • There is no role in the project/product that performs this task
  • The DBAs have only limited insight into the function of the software
  • Developers lack the skills and technical means for reliable evaluation
  • Never touch a running system: removing indexes has a latent risk of burning your fingers and causing malfunctions

Can an index be removed? Requires evaluation of the four possible roles.

Role 1: Optimization of accesses from User-SQL
- Clearly exclude the use of an index in User-SQL for a longer period of time

Role 2 Ensuring uniqueness
- Make sure that Index is NonUnique and is not used for Primary Key or Unique Constraint

Role 3: Securing Foreign Key Constraints
- Exclude that index is used to hedge a foreign key or
- Ensure that no index hedging is required for the specific foreign key

Role 4 Identical indexing for Partition Exchange
- Test for structure-identical tables that could potentially be used for partition exchange

This article shows how, for all four criteria mentioned, it can be clearly determined whether or not an index fulfills one of these roles. 

Role 1: Identification of index usage by user SQL

Using ALTER INDEX <IndexName> MONITORING USAGE, the monitoring of the usage of an index in SQL statements is activated. The start of the monitoring and the status of the usage (YES/NO) are logged, but not the time of usage. The usage status can be evaluated using the system views V$OBJECT_USAGE (only for the current schema) or sys.OBJECT_USAGE (for the entire DB).

MONITORING USAGE only logs the use of the index in direct SQLs. The use of an index in recursive SQLs of the DB itself is not logged. This means that the implicit use of an index for checking a foreign key constraint (for example, lookup when deleting on a referenced table) is not recorded.

Activating MONITORING USAGE on an index does not result in a measurable performance impact, but: Executing ALTER INDEX ... MONITORING USAGE as DDL causes all SQL cursors using this index to be parsed again the next time it is executed.

The next execution of ALTER INDEX ... MONITORING USAGE resets the start time of the monitoring and the usage status to NO.

For permanent monitoring of a system, it is essential that you regularly reset the usage status of indexes marked as used, so that you can also recognize indexes that were used once in the past but not for a long time.

Scripts for permanent tracking of the usage status

-- Set or reset the usage status for the current schema
DECLARE
  DAYS_BACK_BEFORE_RESET CONSTANT NUMBER := 8;
  v_Table_Name User_Indexes.Table_Name%TYPE := '-';
BEGIN
  FOR Rec IN (
    WITH Plan_Objects AS (SELECT /*+ NO_MERGE MATERIALIZE */ DISTINCT Object_Name 
                      FROM   gv$SQL_Plan 
                      WHERE  Object_Owner = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') 
                      AND    Object_Name IS NOT NULL
                      AND    Options != 'SAMPLE FAST FULL SCAN' /* Don't rely on SQLs from DBMS_STATS */
                     ),
         Object_Usage AS (SELECT /*+ NO_MERGE MATERIALIZE */ Table_Name, Index_Name, Start_Monitoring, Used, Monitoring
                          FROM   v$Object_Usage
                         ) 
    SELECT i.Table_Name, i.Index_Name
    FROM   User_Indexes        i
    LEFT OUTER JOIN Object_Usage u ON i.Table_Name = u.Table_Name AND i.Index_Name = u.Index_Name
    LEFT OUTER JOIN Plan_Objects p ON p.Object_Name = i.Index_Name
    WHERE  i.Index_Type NOT IN ('IOT - TOP', 'LOB')
    /* Index never monitored or last monioring start before limit */
    AND    (u.Used IS NULL OR u.Monitoring = 'NO' OR (u.Used = 'YES' AND TO_DATE(u.Start_Monitoring, 'MM/DD/YYYY HH24:MI:SS') < SYSDATE - DAYS_BACK_BEFORE_RESET))
    /* Index not in any current SQL plan for repeated resetting usage status */
    AND    (u.Used IS NULL OR p.Object_Name IS NULL)  
    ORDER BY i.Table_Name /* Ensure all indexes of a table are touched at once */
  ) LOOP
    IF v_Table_Name != Rec.Table_Name THEN
      -- Optionally place a sleep after each tables indexes to flatten hard parse peak after invalidation of existing plans for this table 
      -- DBMS_LOCK.SLEEP(1);
      v_Table_Name := Rec.Table_Name;
    END IF;
    EXECUTE IMMEDIATE 'ALTER INDEX '||Rec.Index_Name||' MONITORING USAGE';
  END LOOP;
END;
/


-- Set or reset the usage status for all user schemas
DECLARE
  DAYS_BACK_BEFORE_RESET CONSTANT NUMBER := 8;
  v_Table_Name User_Indexes.Table_Name%TYPE := '-';
BEGIN
  FOR Rec IN (
    SELECT i.Table_Name, i.Owner, i.Index_Name 
    FROM   DBA_Indexes i
    LEFT OUTER JOIN (SELECT /*+ NO_MERGE */ u.UserName Owner, io.name Index_Name, t.name Table_Name,
                            decode(bitand(i.flags, 65536), 0, 'NO', 'YES') Monitoring,
                            decode(bitand(ou.flags, 1), 0, 'NO', 'YES') Used,
                            ou.start_monitoring, ou.end_monitoring
                     FROM   sys.object_usage ou
                     JOIN   sys.ind$ i  ON i.obj# = ou.obj#
                     JOIN   sys.obj$ io ON io.obj# = ou.obj#
                     JOIN   sys.obj$ t  ON t.obj# = i.bo#
                     JOIN   DBA_Users u ON u.User_ID = io.owner#
                    ) u ON u.Owner = i.Owner AND u.Index_Name = i.Index_Name AND u.Table_Name = i.Table_Name
    JOIN   DBA_Users du on du.UserName = i.Owner                
    LEFT OUTER JOIN (SELECT /*+ NO_MERGE */ DISTINCT Object_Owner, Object_Name
                     FROM   gv$SQL_Plan
                     WHERE  Object_Name IS NOT NULL 
                     AND    Options != 'SAMPLE FAST FULL SCAN' /* Don't rely on SQLs from DBMS_STATS */    
                    ) p ON p.Object_Owner = i.Owner AND p.Object_Name = i.Index_Name 
    WHERE  (u.Used IS NULL OR
            u.Monitoring = 'NO' OR
            (u.Used = 'YES' AND TO_DATE(u.Start_Monitoring, 'MM/DD/YYYY HH24:MI:SS') < SYSDATE - DAYS_BACK_BEFORE_RESET)
           )
    AND    i.Index_Type NOT IN ('IOT - TOP', 'LOB')
    AND    (u.Used IS NULL OR (p.Object_Owner IS NULL AND p.Object_Name IS NULL)) -- Index not used in current execution plans for repeated resetting usage status
    AND    du.Oracle_Maintained = 'N' -- Version for starting with 12.1
    -- AND    du.Default_Tablespace NOT IN ('SYSTEM', 'SYSAUX') -- Version for before 12.1
    ORDER BY i.Owner, i.Table_Name
  ) LOOP
    BEGIN
      IF v_Table_Name != Rec.Table_Name THEN
        -- Optionally place a sleep after each tables indexes to flatten hard parse peak after invalidation of existing plans for this table 
        -- DBMS_LOCK.SLEEP(1);
        v_Table_Name := Rec.Table_Name;
      END IF;
      EXECUTE IMMEDIATE 'ALTER INDEX '||Rec.Owner||'.'||Rec.Index_Name||' MONITORING USAGE';
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20999, 'Error '||SQLERRM||' for index '||Rec.Owner||'.'||Rec.Index_Name);  
    END;  
  END LOOP;
END;
/

The above scripts are used to reset the usage status only for indexes that:

  • are already marked as used
  • have not been reset for more than x days
  • do not occur in current execution plans in the SGA (except when MONITORING USAGE is activated for the first time).
    This eliminates the risk of reparse bursts of existing SQL cursors by executing these scripts.

Evaluation of the usage status of indexes via SQL

-- Determination of unused indexes of the current schema
SELECT ou.*, i.Num_Rows, i.Distinct_Keys, seg.MBytes, i.Tablespace_Name, i.Uniqueness, i.Index_Type,
       (SELECT IOT_Type FROM DBA_Tables t WHERE t.Owner = i.Table_Owner AND t.Table_Name = i.Table_Name) IOT_Type,
       c.Constraint_Name foreign_key_protection,
       rc.Owner||'.'||rc.Table_Name  Referenced_Table,
       rt.Num_Rows     Num_Rows_Referenced_Table
FROM   v$Object_Usage ou
JOIN   User_Indexes i ON i.Index_Name = ou.Index_Name
LEFT OUTER JOIN User_Ind_Columns ic   ON ic.Index_Name = i.Index_Name AND ic.Column_Position = 1
LEFT OUTER JOIN DBA_Cons_Columns cc   ON cc.Owner = i.Table_Owner AND cc.Table_Name = i.Table_Name AND cc.Column_Name = ic.Column_Name AND cc.Position = 1
LEFT OUTER JOIN DBA_Constraints c     ON c.Owner = cc.Owner AND c.Constraint_Name = cc.Constraint_Name AND c.Constraint_Type = 'R'
LEFT OUTER JOIN DBA_Constraints rc    ON rc.Owner = c.R_Owner AND rc.Constraint_Name = c.R_Constraint_Name
LEFT OUTER JOIN DBA_Tables rt         ON rt.Owner = rc.Owner AND rt.Table_Name = rc.Table_Name
LEFT OUTER JOIN (SELECT Segment_Name, ROUND(SUM(bytes)/(1024*1024),1) MBytes FROM DBA_Segments WHERE Owner=USER GROUP BY Segment_Name
                ) seg ON seg.Segment_Name = ou.Index_Name
WHERE  ou.Used = 'NO'
AND    ou.Monitoring = 'YES'
AND    TO_DATE(ou.Start_Monitoring, 'MM/DD/YYYY HH24:MI:SS') < SYSDATE-:Min_Ununsed_Days
ORDER BY seg.MBytes DESC
;


-- Determination of unused indexes of the entire DB
SELECT u.*, i.Num_Rows, i.Distinct_Keys,
       seg.MBytes,
       i.Tablespace_Name, i.Uniqueness, i.Index_Type,
       (SELECT IOT_Type FROM DBA_Tables t WHERE t.Owner = u.Owner AND t.Table_Name = u.Table_Name) IOT_Type,
       c.Constraint_Name foreign_key_protection,
       rc.Owner||'.'||rc.Table_Name  Referenced_Table,
       rt.Num_Rows     Num_Rows_Referenced_Table
FROM   (
        SELECT /*+ NO_MERGE */ u.UserName Owner, io.name Index_Name, t.name Table_Name,
               decode(bitand(i.flags, 65536), 0, 'NO', 'YES') Monitoring,
               decode(bitand(ou.flags, 1), 0, 'NO', 'YES') Used,
               ou.start_monitoring, ou.end_monitoring
        FROM   sys.object_usage ou
        JOIN   sys.ind$ i  ON i.obj# = ou.obj#
        JOIN   sys.obj$ io ON io.obj# = ou.obj#
        JOIN   sys.obj$ t  ON t.obj# = i.bo#
        JOIN   DBA_Users u ON u.User_ID = io.owner#  --
        WHERE  TO_DATE(ou.Start_Monitoring, 'MM/DD/YYYY HH24:MI:SS') < SYSDATE-:Min_unused_Days
       )u
JOIN DBA_Indexes i                    ON i.Owner = u.Owner AND i.Index_Name = u.Index_Name AND i.Table_Name=u.Table_Name
LEFT OUTER JOIN DBA_Ind_Columns ic    ON ic.Index_Owner = u.Owner AND ic.Index_Name = u.Index_Name AND ic.Column_Position = 1
LEFT OUTER JOIN DBA_Cons_Columns cc   ON cc.Owner = ic.Table_Owner AND cc.Table_Name = ic.Table_Name AND cc.Column_Name = ic.Column_Name AND cc.Position = 1
LEFT OUTER JOIN DBA_Constraints c     ON c.Owner = cc.Owner AND c.Constraint_Name = cc.Constraint_Name AND c.Constraint_Type = 'R'
LEFT OUTER JOIN DBA_Constraints rc    ON rc.Owner = c.R_Owner AND rc.Constraint_Name = c.R_Constraint_Name
LEFT OUTER JOIN DBA_Tables rt         ON rt.Owner = rc.Owner AND rt.Table_Name = rc.Table_Name
LEFT OUTER JOIN (SELECT Owner, Segment_Name, ROUND(SUM(bytes)/(1024*1024),1) MBytes FROM DBA_Segments GROUP BY Owner, Segment_Name
                ) seg ON seg.Owner = u.Owner AND seg.Segment_Name = u.Index_Name
WHERE u.Used='NO' AND u.Monitoring='YES'
ORDER BY seg.MBytes DESC NULLS LAST
;

Evaluation of the usage status of indexes via Panorama

Based on sys.OBJECT_USAGE, the analysis tool Panorama offers an evaluation of the index usage in role 1 combined with statements on:
- Role 2: Use of indexes for securing uniqueness
- Role 3: Use of indexes for hedging foreign keys
- Role 4: Use of indexes for partition exchange

The above screenshot shows an example with statements on:
- the number of days without using the index in User-SQL
- the possible use of the index as a unique index or for the enforcement of unique or primary constraints
- the existence of foreign key constraints that are hedged by this index
- the number of rows in the table referenced by the foreign key constraint
- The time of the last analysis of the table referenced by the foreign key constraint and the number of its DML operations (insert/update/delete) since the last analysis
- etc. ...

From this list of unused indexes, you can call up the structure definition of the table of the index as well as the table possibly referenced via Foreign Key Constraint with one click.

Alternative identification of index usage by User-SQL starting with Release 12.2

The view DBA_Index_Usage contains more detailed information on index usage starting with Rel. 12.2:
  • Does not have to be activated separately, logs the usage of indexes by default
  • Contains time of last use and quantity distribution of accesses and result sets
Limitations on the usability of this feature:
  • If the data is only sampled cyclically, there is a latent risk that actual use of an index is not logged.
  • Switching to hard logging of each access brings significant performance restriction
    ALTER SESSION SET "_iut_stat_collection_type"=ALL; instead of SAMPLED
  • The memory data is flushed to disk every 15 minutes and then visible in DBA_Index_Usage
  • Only use of the index in direct SQLs is logged.
    The use of an index in recursive SQLs of the DB itself is not logged (analogous to ALTER INDEX .. MONITORING USAGE ).
  • An analysis of the index (for example, dbms_stats.gather_index_stats) also counts as usage
Therefore this feature does not allow to safely exclude index usage, but gives helpful support in identifying the detailed usage of an index.

Role 2: Relevance of an index for securing uniqueness

In the list of unused indexes in the above screenshot, the UNIQUENESS column shows whether an index is declared unique or serves as the basis of unique or primary key constraints and therefore has a right to exist even if it is not used in SQLs.

As a special case, however, it is possible to remove a multi-column unused unique index without any problems in such a scenario, if the uniqueness of a single column of this index is already ensured with another unique index/constraint.

Role 3: Relevance of an index for protecting foreign key constraints

Reasons for an index on the referencing table of a foreign key constraint can be

  • Preventing a full table scan on the referencing table when deleting or updating the reference
  • Prevent lock propagation of DML locks for DML on the referenced table

This means that even if a foreign key constraint exists, it is not necessary to secure it with an index if no DML takes place on the referenced table.
This is regularly the case, for example, with foreign key constraints against static master data tables.

Tolerability of DML on referenced table

Even if the referenced table has a small DML, index hedging is often not necessary:
  • A full table scan on the referencing table when deleting on the reference may be acceptable for sporadic DML activities. For example, a one-time full table scan every x weeks is often less time-consuming than keeping an index permanently.
  • Lock propagation is only a problem in special constellations.
    Especially when updates are made to primary key columns, which is not necessary when technical primary keys (IDs) are used.
In this blog post the relevance of lock propagation via foreign key constraints is examined in more detail:
  • A list of different competing DML operations on two tables
  • The respective relevance of an index on the Foreign Key Constraint to prevent blocking locks

Evaluation of DML operations on referenced table

The existence and size of DML on a table can be traced using the view DBA_TAB_MODIFICATIONS.
This view logs the number of inserts, updates and deletes since the last analysis. According to Oracle documentation up to 19c, the prerequisite for logging in DBA_TAB_MODIFICATIONS is the status MONITORING=YES at table level. In reality, however, as of Rel.11, DML operations are also recorded with status NOMONITORING.

This number of DML operations since the last analysis allows you to assess whether the frequency and frequency of the DML operations on the referenced table require that a foreign key constraint on the referencing table be hedged by index.

Steps in Panorama for evaluating DML on referenced table


One part of the table structure display is the above list of indexes for a table. Column "FK" shows the protection of a Foreign Key constraint by this index, column "Use" shows the usage status of the index since the last activation of MONITORING USAGE. Further details are displayed by mouseover hint, e.g. the time of the last activation of MONITORING USAGE.





A click in the column "FK" of the index details leads to the details of the Foreign Key constraint. The number of records (column "Num. rows") of the referenced table allows a first evaluation of the possible relevance of the index.


A click in column "Referenced table name" of the Foreign Key constraint leads to the above structure description of the referenced table. Here, the number of DML operations since the last analysis (columns "Inserts"/"Updates"/"Deletes") allows a more precise assessment of whether an index on the Foreign Key Constraint is relevant for preventing lock propagation from the referenced table. 

Role 4: Relevance of an index for securing structural identity for Partition Exchange

Column "Partition Exchange Possible" of the list in the first image of this post shows if there are possible partner for partition exchange existing.
This post also shows how to scan the whole database for tables valid for partition exchange.

Removability of currently actually used indexes

Despite proven usage by User-SQL, it can still be useful to remove an index. Some possible constellations for this are:
  • Columns of an index are already indexed elsewhere by the leading columns of a multi-column index. After removing an index, the other index with the leading columns of the first one takes over its function.
  • The partitioning of the table has the same filter effect as the index. A reduction of the hit list through partitioning is more effective than an index access if the partitioning criterion is unique.
  • Changing the order of the columns of another multi-column index makes an index with the identical leading columns unnecessary
  • An access via Index Fast Full Scan can also be executed via another index of the table. If the relevant column(s) are also indexed elsewhere, or if only the number itself is relevant (for example, for COUNT(*) ), you can also use another index.

For the identification of further possibly dispensable indexes, Panorama offers various other "dragnet search" queries.

About the Panorama analysis tool used

Panorama is the author's Swiss Army Knife, a freely usable tool for Oracle-DB performance analysis. It is based on read-only SQL-accessible information of the DB, including data from the AWR.
For use with Standard Edition or without licensing the Diagnostics Pack, Panorama has its own function for sampling historical data.

Conclusion

The presented procedure shows how simple and low-risk to risk-free often a drastic reduction of the storage requirement and the system load of Oracle-DB is possible.

Remark: 
In Panorama up to version 2.9.30 there was a bug that did not show all unused indices according to the monitoring usage. Fixed now.

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more

Oracle-DB: Identify excessive logon/logoff operations with short-running sessions

Image
Creating and destroying database sessions in loop may lead to significant database load. This blog post contains more about quantifying this load. Applications should use connection pooling at client or database side to prevent excessive session creation. But how can I identify the client processes causing this sessions? Scan DBA_Audit_Trail to identify context info If auditing logon/logoff-operations is activated for your database, scan DBA_Audit_Trail. You can do this by 2 was: Alternative 1: Use my tool "Panorama" to evaluate audit trail Panorama is available here. Go to menu "DBA general" / "Audit Trail": Select start and end time for selection Filter column "Action" for "LOGON" Set "Grouping" to "Minute" Execute selection via button "Show audit trail" Add columns of your choice to diagram by context menu (right mouse button) This way you can identify the machine, DB-user ...
Read more