SQL Analysis by Hash Value (vsqlhash.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 retrieves detailed SQL statement information using the hash value as the lookup key. It provides comprehensive execution statistics including timing metrics, row processing counts, and module information, making it excellent for detailed SQL performance analysis and troubleshooting.

Script

rem  vsqlhash.sql
rem
set linesize 132
rem
ttitle 'SQL by Address'
rem
col sid format 9999 heading 'SID'
col executions format 99999999 heading 'EXECUTIONS'
col rows_processed format 9999999999 heading 'ROWS PROCESSED'
col parse_calls format 9999999 heading 'PARSE|CALLS'
col disk_reads format 999999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 999999999 heading 'LOGICAL|READS'
col log_exe format 9999999 heading 'LOG/|EXE'
col module format a10 heading 'MODULE'
col sql_text format a60 heading 'SQL TEXT'
rem
select c.sid,
       s.executions,
       s.parse_calls,
       s.rows_processed,
       s.disk_reads,
       s.disk_reads / decode( s.executions, 0, null, s.executions ) phy_exe,
       s.buffer_gets,
       s.buffer_gets / decode( s.executions, 0, null, s.executions ) log_exe,
       s.elapsed_time,
       s.elapsed_time / decode(s.executions, 0, 1, s.executions ) elapsed_per_exec,
       s.module,
       s.sql_text
  from v$sqlarea s,
       v$open_cursor c
 where s.hash_value = '&hash_value'
   and c.address(+) = s.address;
rem

Usage

-- Run the script in SQL*Plus or SQLcl
@vsqlhash.sql

-- When prompted, enter:
-- hash_value: SQL statement hash value

-- Example
Enter value for hash_value: 1234567890

Parameters

• &hash_value: Hash value of the SQL statement (from v$sql, v$session, or AWR)

Required Privileges

• SELECT on V$SQLAREA
• SELECT on V$OPEN_CURSOR
• Generally available to most database users

Sample Output

SQL by Address

  SID EXECUTIONS   PARSE ROWS        DISK   PHY/ LOGICAL    LOG/ ELAPSED_TIME ELAPSED_PER_EXEC MODULE     SQL TEXT
              CALLS PROCESSED    READS  EXE     READS     EXE
----- ---------- ------- ---------- ----- ------ --------- ------- ------------ ---------------- ---------- --------------------------------------------------------
  145      12456    1234    4567890   890     72   2345678     188     45678912              366 JDBC Thin  SELECT o.order_id, c.customer_name FROM orders o, customers c
  234      12456    1234    4567890   890     72   2345678     188     45678912              366 JDBC Thin  WHERE o.customer_id = c.customer_id AND o.order_date >= :1

Key Output Columns

• SID: Session ID with open cursor (NULL if no active cursors)
• EXECUTIONS: Total number of executions
• PARSE CALLS: Number of parse operations (hard + soft)
• ROWS PROCESSED: Total rows returned/affected by the SQL
• DISK READS: Physical disk reads performed
• PHY/EXE: Average physical reads per execution
• LOGICAL READS: Buffer gets (logical I/O operations)
• LOG/EXE: Average logical reads per execution
• ELAPSED_TIME: Total elapsed time in microseconds
• ELAPSED_PER_EXEC: Average elapsed time per execution in microseconds
• MODULE: Application module that executed the SQL
• SQL TEXT: Beginning of the SQL statement

Understanding the Metrics

Performance Indicators

• Low ELAPSED_PER_EXEC: Well-performing SQL
• High PHY/EXE: I/O intensive operations needing optimization
• ROWS/EXECUTION ratio: Data processing efficiency
• PARSE/EXEC ratio: Parsing overhead analysis

Resource Consumption

• DISK READS: Physical I/O impact on storage
• LOGICAL READS: Memory and CPU consumption
• ELAPSED_TIME: Overall response time impact
• ROWS PROCESSED: Data volume handling

Application Context

• MODULE: Identifies source application
• Active SIDs: Current usage patterns
• Multiple sessions: Concurrent execution analysis

Performance Analysis

Efficiency Metrics

1. Rows per Physical Read: ROWS_PROCESSED / DISK_READS
2. Execution Efficiency: Low ELAPSED_PER_EXEC values
3. Parse Efficiency: PARSE_CALLS vs EXECUTIONS ratio
4. I/O Efficiency: PHY/EXE vs LOG/EXE ratios

Optimization Targets

• High PHY/EXE (>100): Index optimization needed
• High LOG/EXE (>10000): SQL rewrite consideration
• High ELAPSED_PER_EXEC: Overall performance tuning
• PARSE_CALLS = EXECUTIONS: Hard parsing issues

Common Use Cases

Performance Troubleshooting

-- Analyze specific problem SQL by hash
@vsqlhash.sql
-- Enter hash from v$session or AWR reports
-- Review all performance metrics

Application Analysis

-- Identify SQL from specific applications
@vsqlhash.sql
-- Review MODULE column for application identification

Resource Usage Investigation

-- Analyze high-resource consuming SQL
@vsqlhash.sql
-- Focus on timing and I/O metrics

Finding Hash Values

From Active Sessions

-- Get hash values from current sessions
SELECT sid, sql_hash_value, username, module
FROM v$session
WHERE sql_hash_value != 0
AND username IS NOT NULL;

From SQL Performance Views

-- Find hash values of expensive SQL
SELECT hash_value, sql_text, executions, elapsed_time
FROM v$sqlarea
WHERE elapsed_time > 10000000  -- 10 seconds total
ORDER BY elapsed_time DESC;

From AWR Reports

-- Historical hash values from AWR
SELECT sql_id, plan_hash_value,
       executions_delta, elapsed_time_delta
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN &start_snap AND &end_snap
ORDER BY elapsed_time_delta DESC;

Advanced Analysis

Timing Analysis

-- Convert elapsed time to readable format
SELECT hash_value,
       ROUND(elapsed_time/1000000, 2) total_elapsed_sec,
       ROUND(elapsed_time/executions/1000, 2) avg_elapsed_ms,
       executions
FROM v$sqlarea
WHERE hash_value = &hash_value;

Resource Ratios

-- Calculate key performance ratios
SELECT hash_value,
       ROUND(rows_processed/NULLIF(executions,0), 2) rows_per_exec,
       ROUND(buffer_gets/NULLIF(rows_processed,0), 2) gets_per_row,
       ROUND(disk_reads/NULLIF(buffer_gets,0)*100, 2) physical_read_pct
FROM v$sqlarea
WHERE hash_value = &hash_value;

Session Correlation

-- Find sessions currently executing this SQL
SELECT s.sid, s.username, s.status, s.program,
       s.last_call_et, s.blocking_session
FROM v$session s
WHERE s.sql_hash_value = &hash_value;

Troubleshooting Guidelines

High Elapsed Time Per Execution

1. Check execution plan:

   SELECT plan_table_output
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
     (SELECT sql_id FROM v$sql WHERE hash_value = &hash_value)
   ));

2. Analyze wait events:

   SELECT event, total_waits, time_waited
   FROM v$sql_monitor
   WHERE sql_id = (SELECT sql_id FROM v$sql WHERE hash_value = &hash_value);

High Physical I/O

1. Index analysis:

   • Missing indexes on join/filter columns
   • Index unusability
   • Statistics outdated

2. SQL rewrite opportunities:

   • Eliminate unnecessary joins
   • Add selective WHERE clauses
   • Consider materialized views

Parsing Issues

1. Identify hard parsing:

   • PARSE_CALLS equals EXECUTIONS
   • Check for literal values instead of bind variables

2. Cursor sharing problems:

   • Review CURSOR_SHARING parameter
   • Check for optimizer parameter differences

Related Scripts

• SQL by Address (vsqladdr.sql) - Alternative SQL lookup method
• SQL Text Retrieval (sqltext.sql) - Get complete SQL text
• Top SQL Statements (vsqltopu.sql) - High-resource SQL identification
• Execution Plan Analysis (explain-sqlid.sql) - SQL plan analysis