Monitor Index in Oracle Database

 

1. Basic index information:

sql
SELECT index_name, table_name, uniqueness, status, visibility
FROM all_indexes
WHERE index_name = '****'
AND owner = '********';

2. Index column details:

sql
SELECT index_name, column_name, column_position, descend
FROM all_ind_columns
WHERE index_name = '*******'
AND index_owner = '******'
ORDER BY column_position;

3. Index usage statistics:

sql
SELECT i.index_name, i.table_name, 
       s.leaf_blocks, s.distinct_keys, s.clustering_factor,
       s.last_analyzed, s.num_rows
FROM all_indexes i
JOIN all_ind_statistics s ON i.index_name = s.index_name AND i.owner = s.owner
WHERE i.index_name = '*********'
AND i.owner = '*******';

4. Check if the index is actually being used:

sql
SELECT o.owner, o.object_name, o.object_type, 
       o.last_ddl_time, s.executions, s.last_active_time
FROM all_objects o
LEFT JOIN v$segment_statistics s ON o.object_name = s.object_name AND o.owner = s.owner
WHERE o.object_name = '*********'
AND o.owner = '********'
AND s.statistic_name = 'logical reads';

5. To see index monitoring (if enabled):

sql
SELECT * FROM v$object_usage
WHERE index_name = '*********'
AND owner = '***********';

 

SQL Tuning Advisor in Oracle Database  

Step 1: Connect to the Database

sql
-- Connect as a user with appropriate privileges
CONNECT username/password@database

Step 2: Create a SQL Tuning Task

sql
-- Method 1: Create a tuning task for a specific SQL ID
DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => 'your_sql_id',
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 3600,
    task_name   => 'SQL_TUNE_TASK_' || 'your_sql_id',
    description => 'Tuning task for SQL ID: your_sql_id'
  );
  
  DBMS_OUTPUT.PUT_LINE('SQL Tuning Task created: ' || l_sql_tune_task_id);
END;
/

Step 3: Execute the SQL Tuning Task

sql
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
    task_name => 'SQL_TUNE_TASK_' || 'your_sql_id'
  );
END;
/

Step 4: Check the Status of the Tuning Task

sql
-- Monitor the status of the task
SELECT task_name, status, advisor_name, created, last_modified
FROM dba_advisor_tasks
WHERE task_name = 'SQL_TUNE_TASK_' || 'your_sql_id';

Step 5: Generate and Review the Recommendations

sql
-- Get the tuning recommendations
SET LONG 100000
SET PAGESIZE 1000
SET LINESIZE 130
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
  task_name => 'SQL_TUNE_TASK_' || 'your_sql_id',
  section   => 'ALL',
  level     => 'ALL',
  type      => 'TEXT'
) AS recommendations
FROM DUAL;

Step 6: Implement the Recommendations (Optional)

sql
-- Implement a specific recommendation (if desired)
BEGIN
  -- Accept SQL profile recommendation
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name  => 'SQL_TUNE_TASK_' || 'your_sql_id',
    name       => 'PROF_your_sql_id',
    force_match => TRUE  -- Will use this profile for similar statements
  );
END;
/

Step 7: Clean Up (Optional)

sql
-- Drop the tuning task when you're done
BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK(
    task_name => 'SQL_TUNE_TASK_' || 'your_sql_id'
  );
END;
/

Additional Options

To Run Tuning Advisor on Multiple SQL Statements

sql
DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    begin_snap  => 123,  -- Beginning snapshot ID
    end_snap    => 456,  -- Ending snapshot ID
    top_sql     => 'ALL',  -- Or specify a number like 10
    task_name   => 'WORKLOAD_TUNING_TASK',
    description => 'Tuning task for top SQL statements in workload repository'
  );
  
  DBMS_OUTPUT.PUT_LINE('SQL Tuning Task created: ' || l_sql_tune_task_id);
END;
/