RAC Top SQL Statements (gvsqltop.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 comprehensive SQL performance analysis across all RAC instances by:

• Querying GV$SQLAREA to show SQL statements from all instances
• Filtering statements based on multiple performance thresholds
• Calculating key performance ratios and per-execution metrics
• Displaying module and action context for application tracking
• Ordering results by buffer gets (logical I/O) to prioritize impact

Script

rem  vsqltop.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
rem
set lines 80
rem
col inst_id format 9999
col sql_text format a80 heading 'SQL Text'
col sharable_mem format 999999 heading 'SHARED|MEMORY'
col persistent_mem format 999999 heading 'PERSIST|MEMORY'
col runtime_mem format 999999 heading 'RUNTIME|MEMORY'
col loads format 9999 heading 'LOADS'
col invalidations format 9999 heading 'INVALID'
col parse_calls format 99999999 heading 'PARSE|CALLS'
col parse_pct format 999 heading 'PARSE|PCT'
col executions format 999999999 heading 'EXECUTE'
col log_phy format 9999 heading 'LOG/|PHY'
col disk_reads format 999999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 9999999999 heading 'BUFFER|GETS'
col log_exe format 99999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
col address format a8 heading 'ADDRESS'
col hash_value format 99999999999 heading 'HASH VALUE'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
col module format a40
col action format a39
rem
break on report
rem compute sum of parse_calls executions disk_reads buffer_gets on report
compute sum of executions disk_reads buffer_gets on report
rem
select
inst_id,
/*
       sharable_mem,
       persistent_mem,
       runtime_mem,
       version_count,
       loaded_versions,
       open_versions,
       kept_versions,
       users_opening,
       users_executing,
       loads,
       invalidations,
       first_load_time,
       command_type,
       optimizer_mode,
       parsing_user_id,
       parsing_schema_id,
       module,
       module_hash,
       action,
       action_hash,
       serializable_aborts,
       rows_processed,
       rows_processed/decode(executions, 0, 1, executions) rows_exe,
       address,
*/
       hash_value,
       sorts,
       executions,
       -- parse_calls,
       parse_calls*100/decode(executions,0,1,executions) parse_pct,
       buffer_gets/decode(disk_reads,0,1,disk_reads) log_phy,
       disk_reads,
       disk_reads/decode(executions,0,1,executions) phy_exe,
       buffer_gets,
       buffer_gets/decode(executions,0,1,executions) log_exe,
       module,
       action,
       sql_text
  from gv$sqlarea
 where parse_calls >= &parse
    or executions >= &execute
    or disk_reads >= &physical
    or buffer_gets >= &logical
 order by buffer_gets desc;

Usage

SQL> @gvsqltop.sql
Enter value for parse: 1000
Enter value for execute: 500
Enter value for physical: 10000
Enter value for logical: 100000

Parameters

• parse: Minimum parse calls threshold
• execute: Minimum executions threshold
• physical: Minimum physical reads (disk I/O) threshold
• logical: Minimum logical reads (buffer gets) threshold

Required Privileges

• SELECT on GV$SQLAREA

Sample Output

Shared SQL Area - Top SQL Statements

INST_ID  HASH VALUE    S  EXECUTE PARSE   LOG/   DISK READS   PHY/  BUFFER GETS   LOG/ MODULE
                          CALLS     PCT    PHY               EXE                 EXE
------- ----------- ---- -------- ----- ------ ----------- ------ ----------- ------- --------
      1  1234567890    5    2,567   125     25     456,789    178  89,456,789  34,821 MYAPP.EXE
      2  1234567890    5    1,432   143     23     234,567    164  52,345,678  36,542 MYAPP.EXE
      1  9876543210   12    5,432   156     15   1,234,567    227 156,789,012  28,865 BATCH.EXE

ACTION                               SQL Text
-----------------------------------  -------------------------------------------------------------------------------
UPDATE_CUSTOMER                      UPDATE customers SET last_update = SYSDATE WHERE customer_id = :1
UPDATE_CUSTOMER                      UPDATE customers SET last_update = SYSDATE WHERE customer_id = :1
MONTHLY_REPORT                       SELECT c.name, SUM(o.amount) FROM customers c, orders o WHERE c.id = o.cust_id

                                    -------- -----------         -----------
sum                                    9,431  1,925,923     298,591,479

Key Output Columns

• INST_ID: RAC instance number
• HASH VALUE: SQL statement hash value for identification
• S: Number of sort operations
• EXECUTE: Total executions across all instances
• PARSE PCT: Parse calls as percentage of executions
• LOG/PHY: Logical to physical read ratio
• DISK READS: Total physical disk reads
• PHY/EXE: Physical reads per execution
• BUFFER GETS: Total logical reads (buffer cache)
• LOG/EXE: Logical reads per execution
• MODULE: Application module name
• ACTION: Specific action within module
• SQL TEXT: First 80 characters of SQL statement

Understanding RAC-Specific Metrics

Instance Distribution

• Same HASH VALUE: SQL statement executed on multiple instances
• Different Workload: Compare execution patterns across instances
• Load Balancing: Analyze if workload is evenly distributed

Performance Ratios

• LOG/PHY Ratio: Higher values indicate better buffer cache efficiency
• PARSE PCT: Should be low (<10%) for optimal performance
• PHY/EXE: Lower values indicate better access path efficiency

Common Use Cases

1. RAC Performance Analysis

   • Compare SQL performance across instances
   • Identify instance-specific bottlenecks
   • Analyze workload distribution

2. Top Resource Consumer Identification

   • Find highest I/O consuming statements
   • Prioritize tuning efforts by impact
   • Track application module performance

3. Parse Ratio Analysis

   • Identify statements with excessive parsing
   • Find candidates for bind variable implementation
   • Monitor cursor sharing effectiveness

Performance Tuning Insights

High Parse Percentages

• Parse PCT > 50%: Missing bind variables
• Same SQL, High Parse: Hard-coded literal values
• Solution: Implement bind variables or enable cursor sharing

High Physical I/O

• High PHY/EXE: Inefficient access paths
• Poor LOG/PHY: Buffer cache misses
• Solution: Index optimization, SQL tuning

Instance Imbalance

• Uneven Distribution: Connection load balancing issues
• Instance Hotspots: Application design problems
• Solution: Review connection strategies

Advanced Analysis

Find SQL with bind variable issues:

SELECT hash_value, inst_id, parse_calls, executions,
       ROUND(parse_calls/GREATEST(executions,1)*100, 2) as parse_ratio
FROM gv$sqlarea
WHERE executions > 100
AND parse_calls/GREATEST(executions,1) > 0.1
ORDER BY parse_ratio DESC;

Compare instance performance:

SELECT hash_value, inst_id,
       executions, disk_reads, buffer_gets,
       ROUND(disk_reads/GREATEST(executions,1), 2) as phy_per_exec
FROM gv$sqlarea
WHERE hash_value IN (SELECT hash_value FROM gv$sqlarea GROUP BY hash_value HAVING COUNT(DISTINCT inst_id) > 1)
ORDER BY hash_value, inst_id;

RAC Considerations

Instance Affinity

• Module/Action: Track application connections
• Hash Distribution: Verify SQL is parsed on all nodes
• Performance Variance: Compare execution efficiency

Load Balancing

• Connection Distribution: Monitor application connections
• Workload Patterns: Analyze by time and module
• Resource Utilization: Balance across instances

Filtering Guidelines

Conservative Thresholds

-- For general analysis
parse: 100
execute: 50
physical: 1000
logical: 10000

Aggressive Thresholds

-- For top consumers only
parse: 5000
execute: 1000
physical: 100000
logical: 1000000

Best Practices

1. Regular Monitoring

   • Run during different time periods
   • Compare peak vs. off-peak patterns
   • Track trends over time

2. Threshold Tuning

   • Adjust based on environment workload
   • Start conservative, then narrow focus
   • Balance detail with relevance

3. Cross-Instance Analysis

   • Look for same SQL on different instances
   • Compare performance characteristics
   • Identify clustering effects

Related Scripts

• Single Instance SQL Analysis
• SQL by Session Analysis
• RAC Session Analysis