SQL Performance by ID Analysis (sql-id.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database functionality via the sql-id.sql script.

The Script

col sorts format 99 heading 'SORT'
col FETCHES format 999999D99 heading 'FETCHES'
col logical format 999999D99 heading 'LOGICAL|READS'
col PREADS format 999999D99 heading 'PHYS|READS'
col ROWS format 999999D99 heading 'ROWS|PROC'
col CPU format 999999D99 heading 'CPU'
col ELAPSED format 999999D99 heading 'ELAPSED'


select
SQL_ID  ,
sum(SORTS) sorts,
(SUM(FETCHES)/SUM(EXECUTIONS)) FETCHES,
sum(EXECUTIONS) exes,
(sum(BUFFER_GETS) / SUM(EXECUTIONS))   LOGICAL,
(sum(DISK_READS) / SUM(EXECUTIONS))   PREADS  ,
((sum(CPU_TIME)/1000000)/SUM(EXECUTIONS))      cpu ,
((sum(ELAPSED_TIME)/1000000)/SUM(EXECUTIONS))      ELAPSED
from gv$sqlarea
where sql_id in
(
'31fxa8p7cgbdg'
)
group by sql_id
order by sql_id
;



--select distinct ''' || sql_id || '',' from gv$sqlarea where UPPER(SQL_TEXT) LIKE '%UPDATE TASK %';

What This Script Does

The sql-id.sql script analyzes SQL statement performance metrics for specific SQL_IDs across all instances in a RAC environment. It provides detailed per-execution statistics including sorts, fetches, logical/physical reads, CPU time, and elapsed time, making it invaluable for SQL performance tuning and analysis.

Usage

Modify the script to include your target SQL_ID(s):

-- Edit the WHERE clause to include your SQL_ID(s)
where sql_id in
(
'31fxa8p7cgbdg',
'5dbf8j7mq9wnx',
'8kx9p2n5m3qrt'
)

Then run:

SQL> @sql-id.sql

Finding SQL_IDs

Use the commented query at the bottom to find SQL_IDs by text pattern:

-- Find SQL_IDs containing specific text
select distinct sql_id from gv$sqlarea
where UPPER(SQL_TEXT) LIKE '%UPDATE CUSTOMER%';

Required Privileges

• SELECT privilege on GV$SQLAREA (usually requires DBA role or specific grants)

Sample Output

                 SORT   FETCHES      EXES   LOGICAL    PREADS      CPU  ELAPSED
SQL_ID
------------- -------- --------- --------- --------- --------- -------- --------
31fxa8p7cgbdg        2    125.50      1250   2435.75    145.25     0.85     1.23
5dbf8j7mq9wnx        0     45.80       890    856.32     12.45     0.12     0.25
8kx9p2n5m3qrt       15    245.60       456   5234.75    892.15     2.45     3.85

Key Output Columns

• SQL_ID: Unique identifier for the SQL statement
• SORT: Total number of sorts performed by all executions
• FETCHES: Average number of fetches per execution
• EXES: Total number of executions across all instances
• LOGICAL: Average logical reads (buffer gets) per execution
• PREADS: Average physical reads (disk reads) per execution
• CPU: Average CPU time per execution (in seconds)
• ELAPSED: Average elapsed time per execution (in seconds)

Understanding the Metrics

Performance Indicators

• High Logical Reads: May indicate inefficient SQL or missing indexes
• High Physical Reads: Could suggest buffer cache issues or large table scans
• CPU vs Elapsed Time: Large difference may indicate wait events
• High Sorts: May benefit from index optimization or increased sort area

Per-Execution Analysis

All metrics except SORTS and EXES are calculated as averages per execution:

• Fetches/Execution: Shows data retrieval efficiency
• Logical/Physical Ratio: Indicates buffer cache effectiveness
• CPU/Elapsed Ratio: Reveals time spent waiting vs. processing

Common Use Cases

1. SQL Performance Tuning

   • Identify resource-intensive SQL statements
   • Compare before/after metrics for tuning efforts

2. RAC Performance Analysis

   • Analyze SQL performance across all instances
   • Identify statements with high inter-node costs

3. Resource Consumption Analysis

   • Quantify CPU and I/O consumption by specific SQL statements
   • Identify candidates for query optimization

4. Baseline Comparison

   • Establish performance baselines for critical SQL statements
   • Track performance changes over time

Performance Analysis Techniques

Efficiency Ratios

• Buffer Hit Ratio: (LOGICAL - PREADS) / LOGICAL * 100
• CPU Efficiency: CPU / ELAPSED (closer to 1.0 is better)
• Fetch Efficiency: Low fetches per execution indicates efficient access

Red Flags

• LOGICAL > 10,000: Very high logical reads per execution
• PREADS > 1,000: High physical I/O per execution
• CPU << ELAPSED: Significant wait time (investigate wait events)
• High SORTS: May indicate missing indexes or inefficient ORDER BY

Script Customization

Multiple SQL_IDs

where sql_id in
(
'31fxa8p7cgbdg',
'5dbf8j7mq9wnx',
'8kx9p2n5m3qrt',
'4mq8x5n9p2wrt'
)

Pattern-Based Selection

-- Replace the IN clause with pattern matching
where sql_text like '%UPDATE CUSTOMER%'
  and parsing_schema_name = 'SALES'

Additional Filters

-- Add filtering by schema or execution count
and parsing_schema_name = 'APPUSER'
and executions > 100

Troubleshooting High Resource Usage

High Logical Reads

1. Check Execution Plans: Use EXPLAIN PLAN or DBMS_XPLAN
2. Index Analysis: Verify appropriate indexes exist
3. Statistics: Ensure table/index statistics are current

High Physical Reads

1. Buffer Cache Size: Consider increasing buffer cache
2. Query Patterns: Analyze if full table scans are appropriate
3. Storage: Check for I/O bottlenecks

High CPU Time

1. Execution Plans: Look for inefficient operations
2. Function Usage: Check for expensive PL/SQL functions
3. Data Types: Verify appropriate data type usage

Related Scripts

• gvsqlplanfind.sql - SQL execution plan analysis
• sqltext.sql - SQL text retrieval by ID
• gvplanid.sql - Plan analysis across instances

Script Features

• RAC-Aware: Aggregates metrics across all instances using GV$SQLAREA
• Per-Execution Metrics: Calculates meaningful averages for comparison
• Flexible Filtering: Easy to modify for different SQL_IDs or patterns
• Time Conversion: Converts microseconds to seconds for readability

Best Practices

1. Regular Monitoring: Track performance of critical SQL statements regularly
2. Baseline Documentation: Maintain historical performance data
3. Tuning Validation: Use before/after comparisons to validate tuning efforts
4. Pattern Analysis: Look for trends in resource consumption over time

Limitations

• Shows aggregated data since instance startup (not historical trends)
• Requires SQL statements to be in shared pool
• May not capture infrequently executed statements
• Memory-based view, data lost on instance restart