SQL scripts for index statistics and monitoring in Oracle

advertisements

_____________________________________________________________________________________________________________________

1. Gather Index Statistics for a Specific Index or All Indexes 

These scripts use the DBMS_STATS package to gather index statistics. The first script gathers statistics for a specific index, where you need to replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names. The second script gathers statistics for all indexes in a schema using the GATHER_SCHEMA_STATS procedure. The options => 'GATHER' parameter ensures that statistics are collected for all objects, including indexes, and estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ensures that Oracle automatically determines the sample size.

-- Gather index statistics for a specific index
EXEC DBMS_STATS.GATHER_INDEX_STATS('YOUR_SCHEMA_NAME', 'YOUR_INDEX_NAME');

-- Gather index statistics for all indexes in a schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA_NAME', options => 'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

advertisements

 

2. Monitor Index Usage and Performance using AWR 

This script queries the dba_hist_sqlstat view in the Automatic Workload Repository (AWR) to monitor index usage and performance for a specific index. Replace 'YOUR_INDEX_NAME' with the name of the index you want to monitor. The script retrieves SQL performance statistics related to the specified index, including SQL ID, Plan Hash Value, number of executions, elapsed time, buffer gets, and rows processed.

 

-- Query to monitor index usage and performance using AWR
SELECT sql_id, plan_hash_value, executions, elapsed_time, buffer_gets, rows_processed
FROM dba_hist_sqlstat
WHERE object_type = 'INDEX'
  AND object_name = 'YOUR_INDEX_NAME'
ORDER BY sql_id, plan_hash_value;

3.View Index-Related Information in Oracle Enterprise Manager (OEM) 

This script queries the dba_indexes view to view index-related information in Oracle Enterprise Manager (OEM). Replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names. The script provides details about the specified index, such as its status, B-level, number of leaf blocks, number of rows, sample size for statistics, and the date when it was last analyzed. These advanced scripts should help you with index statistics gathering, monitoring index usage and performance using AWR, and viewing index-related information in Oracle Enterprise Manager. Make sure you have appropriate privileges to access the required views and packages for accurate results.

-- Query to view index-related information in Oracle Enterprise Manager (OEM)
SELECT object_name, status, blevel, leaf_blocks, num_rows, sample_size, last_analyzed
FROM dba_indexes
WHERE owner = 'YOUR_SCHEMA_NAME'
  AND object_name = 'YOUR_INDEX_NAME';

_____________________________________________________________________________________________________________________

Website Stats