Identifying Space-Consuming Objects and Optimizing Index Usage in Oracle

Managing storage space in Oracle databases is crucial for maintaining performance and optimizing resource usage. In this post, we’ll explore practical queries to identify tables and indexes that consume significant space, and how to monitor index usage over time to ensure they are used efficiently. Additionally, we’ll cover generating index monitoring commands and referencing a more detailed article on index monitoring from my blog.

Listing Object Sizes in the Database

To get an overview of the space consumed by different types of objects (tables, indexes, LOBs, etc.), you can use the following query:

-- List the sizes of Oracle objects grouped by type
SELECT SEGMENT_TYPE, ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 3) AS SIZE_GB
FROM DBA_SEGMENTS
GROUP BY SEGMENT_TYPE
ORDER BY SIZE_GB DESC;

This query groups objects by type and shows the total size occupied in gigabytes, allowing you to quickly identify which types of objects are consuming the most space in the database.

Identifying the 10 Largest Tables

Once you have an overview of the objects, you can identify the largest tables in your database:

-- Top 10 largest tables
COL TABLE_NAME FOR A30
COL SIZE_GB FOR 999999
SELECT *
FROM (
    SELECT owner, segment_name AS table_name, ROUND(bytes / 1024 / 1024 / 1024, 0) AS SIZE_GB
    FROM dba_segments
    WHERE segment_type = 'TABLE'
    ORDER BY bytes DESC
)
WHERE ROWNUM <= 10;

This query returns the 10 largest tables in the database, showing their sizes in gigabytes. This helps you focus on the tables that may require optimization or partitioning.

Listing Indexes of a Specific Table

To drill down further, you can list all the indexes associated with a specific table and their corresponding sizes:

-- List all indexes of a table with their tablespace and size
SELECT i.OWNER, i.INDEX_NAME, i.INDEX_TYPE, i.UNIQUENESS, i.STATUS, i.TABLESPACE_NAME, 
       s.BYTES / 1024 / 1024 AS SIZE_MB
FROM DBA_INDEXES i
JOIN DBA_SEGMENTS s 
    ON i.INDEX_NAME = s.SEGMENT_NAME 
    AND i.OWNER = s.OWNER
WHERE i.TABLE_NAME = 'TABLE_A'
  AND i.TABLE_OWNER = 'OWNER_A'
  AND s.SEGMENT_TYPE = 'INDEX'
ORDER BY s.BYTES DESC;

This query provides an overview of the indexes tied to a particular table, allowing you to identify indexes that are consuming large amounts of space.

Calculating the Total Space Occupied by Indexes of a Table

If you need to know the total space occupied by all indexes related to a specific table, use the following query:

-- Total space occupied by all indexes of a table
SELECT ROUND(SUM(s.BYTES) / 1024 / 1024 / 1024, 2) AS INDEX_SIZE_GB
FROM DBA_SEGMENTS s
WHERE 
    s.SEGMENT_TYPE = 'INDEX'
    AND s.OWNER = 'OWNER_A'
    AND s.SEGMENT_NAME IN (
        SELECT i.index_name
        FROM dba_indexes i
        WHERE i.table_name = 'TABLE_B'
        AND i.owner = 'OWNER_A'
    );

This command calculates the total space occupied by indexes of a specific table, helping you assess their storage impact.

Listing the Largest Indexes in the Database

In addition to tables, you may want to identify the largest indexes in the database:

-- Top 10 largest indexes
COL INDEX_NAME FOR A30
COL SIZE_GB FOR 999999
SELECT *
FROM (
    SELECT owner, segment_name AS index_name, ROUND(bytes / 1024 / 1024 / 1024, 0) AS SIZE_GB
    FROM dba_segments
    WHERE segment_type = 'INDEX'
    ORDER BY bytes DESC
)
WHERE ROWNUM <= 10;

This query lists the 10 largest indexes in the database, giving you insight into which indexes may be occupying significant space and need review.

Monitoring Index Usage with INDEX MONITORING

One recommended practice is to monitor index usage to ensure that they are actively used in queries. To learn more about index monitoring, check out the full article on my blog here.

Below is a query to generate commands for enabling monitoring for all indexes in a table (excluding constraints):

-- Generate commands to reset and enable monitoring for all indexes (excluding constraints)
SET LINESIZE 120
COL DISABLE_COMMAND FOR A60
COL ENABLE_COMMAND FOR A60
SELECT 
    'ALTER INDEX OWNER_A.' || i.index_name || ' NOMONITORING USAGE;' AS disable_command,
    'ALTER INDEX OWNER_A.' || i.index_name || ' MONITORING USAGE;' AS enable_command
FROM dba_indexes i
LEFT JOIN dba_constraints c ON i.index_name = c.index_name AND i.owner = c.owner
WHERE i.owner = 'OWNER_A'
  AND i.table_name IN ('TABLE_C')
  AND c.constraint_type IS NULL
ORDER BY i.table_name, i.index_name;

After enabling monitoring, you can check index usage over time using the following query:

-- Check monitored indexes
COL INDEX_NAME FOR A35
COL TABLE_NAME FOR A25
SELECT * FROM DBA_OBJECT_USAGE 
WHERE used='YES' AND table_name <> 'TABLE_D';

Conclusion

These queries provide powerful tools for identifying and analyzing objects in your Oracle database that are consuming significant amounts of space. Additionally, index monitoring allows you to evaluate whether all indexes are necessary, helping optimize storage and database performance.