SQL scripts for index monitoring and statistics in Oracle

advertisements

_____________________________________________________________________________________________________________________

1. Check the Last Analyzed Date of an Index 

This script checks the last analyzed date of a specific index. Replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names you want to query. The script queries the all_indexes data dictionary view to retrieve the last_analyzed date for the specified index.

-- Query to check the last analyzed date of an index
SELECT index_name, last_analyzed
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME'
  AND index_name = 'YOUR_INDEX_NAME';

advertisements

 

2. View Index Statistics - Leaf Blocks and Distinct Keys 

This script shows index statistics, such as the number of leaf blocks and distinct keys, for a specific index. Replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names you want to query. The script retrieves information from the all_indexes data dictionary view.

-- Query to view the index statistics, such as the number of leaf blocks and distinct keys
SELECT index_name, leaf_blocks, distinct_keys
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME'
  AND index_name = 'YOUR_INDEX_NAME';

3. Gather Index Statistics using the DBMS_STATS Package 

 This script gathers index statistics for a specific index using the DBMS_STATS package. Replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names. The GATHER_INDEX_STATS procedure collects statistics such as the number of leaf blocks, distinct keys, and clustering factor for the specified index. Please customize 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names in the scripts. Adjust the queries as needed for schema-specific information or additional details. Ensure that you have appropriate privileges to access the necessary data dictionary views and perform index statistics gathering.

-- Query to gather index statistics using the DBMS_STATS package
EXEC DBMS_STATS.GATHER_INDEX_STATS('YOUR_SCHEMA_NAME', 'YOUR_INDEX_NAME');

-- Sample Output: (No output is generated for this statement)

_____________________________________________________________________________________________________________________

Website Stats