How to identify slowness - using Queries

 

In Slowness in query execution, especially for an older query that used to run efficiently, can be caused by various factors like changes in data volume, statistics, indexing, or database parameters. :

Below are steps to troubleshoot and resolve the issue:

### *1. Analyze Query Execution Plan*
   - Use EXPLAIN PLAN or DBMS_XPLAN to analyze the query execution path.
   
   EXPLAIN PLAN FOR 
   <your_query>;

   SELECT * 
   FROM TABLE(DBMS_XPLAN.DISPLAY);
   
   - Look for *full table scans, **unnecessary joins*, or inefficient access paths (e.g., high-cost operations).

### *2. Check Query Statistics with SQL Monitoring*
   - Use V$SQL or V$SQL_MONITOR to gather runtime statistics:
   
   SELECT
       sql_id,
       elapsed_time / 1000000 AS elapsed_sec,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed
   FROM
       v$sql
   WHERE
       sql_text LIKE '%<your_query_keywords>%';
   
   - Focus on *buffer gets, **disk reads, and **elapsed time*.

### *3. Investigate Plan Changes*
   - Check if the execution plan has changed due to *statistics updates* or other factors:
   
   SELECT
       sql_id,
       child_number,
       plan_hash_value,
       elapsed_time / 1000000 AS elapsed_sec,
       executions,
       first_load_time
   FROM
       v$sql
   WHERE
       sql_id = '<your_sql_id>';
   
   - Compare the plan_hash_value for historical and current executions. Use *SQL Plan Baselines* to stabilize the plan if needed.

### *4. Verify Table Statistics*
   - Ensure the table statistics are current:
   
   EXEC DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name');
   
   - For larger datasets, gather statistics with the following:
   
   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema_name', CASCADE => TRUE);
   
   - Check for stale or missing statistics:
   
   SELECT table_name, num_rows, last_analyzed 
   FROM user_tables
   WHERE table_name = '<table_name>';

### *5. Identify Index Issues*
   - Verify if indexes are used and properly maintained:
   
   SELECT index_name, table_name, last_analyzed, status
   FROM user_indexes
   WHERE table_name = '<table_name>';
   
   - Rebuild indexes if necessary:
   
   ALTER INDEX <index_name> REBUILD;

### *6. Monitor System Performance*
   - Check for resource bottlenecks in the database:
     
     SELECT * FROM v$system_event WHERE event NOT LIKE 'SQL*Net%';
     
   - Look for *CPU, memory, or disk I/O contention* using *AWR* or *Statspack* reports.

### *7. Review Bind Variables*
   - Ensure the query uses *bind variables* to prevent hard parsing and ensure consistent performance.
   - Check for bind variable usage:
     
     SELECT sql_text, bind_data
     FROM v$sql_bind_capture
     WHERE sql_id = '<your_sql_id>';

### *8. Examine Query Logic*
   - Check for inefficiencies in the query itself:
     - Avoid unnecessary joins or subqueries.
     - Use indexes properly (e.g., indexed columns in WHERE clauses).
     - A

### *9. Use Parallel Query (If Applicable)*
   - For large datasets, enable parallel execution:
   
   ALTER SESSION ENABLE PARALLEL QUERY;

### *10. Evaluate Historical Query Execution*
   - Check *ASH (Active Session History)* or *AWR (Automatic Workload Repository)* to identify when performance degraded:
   
   SELECT * 
   FROM dba_hist_sqlstat
   WHERE sql_id = '<your_sql_id>';

### *11. Test Query Performance Manually*
   - Run the query with hints to test specific access paths:
   
   SELECT /*+ INDEX(table index_name) */ col1, col2
   FROM table
   WHERE condition;

### *12. Optimize Database Configuration*
   - Check database parameters like pga_aggregate_target, db_cache_size, or optimizer_mode.
   - Look for resource contention:
     
     SELECT * FROM v$resource_limit;

By following these steps systematically, you can identify and resolve query slowness.