SQL Text Retrieval by Hash Value (qsqlhash.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 the complete SQL text for a specific SQL statement using its hash value from Oracle’s shared SQL area. Hash values are unique identifiers for SQL statements in the library cache, making this script essential for SQL investigation, performance analysis, and troubleshooting. It reconstructs the complete SQL statement by ordering the text pieces, which is particularly useful when working with long SQL statements that span multiple rows in V$SQLTEXT.

The Script

rem  qsqlhash.sql
rem
ttitle 'SQL Text'
rem
col sql_text format a64 heading 'SQL TEXT'
rem
select sql_text
  from v$sqltext
 where hash_value = '&hash_value'
 order by piece;

Key Features

• Hash Value Lookup: Retrieves SQL using unique hash identifier
• Complete Text Reconstruction: Orders pieces to show full SQL statement
• Performance Investigation: Essential for SQL performance analysis
• Simple Interface: Single parameter input for easy use
• Troubleshooting Support: Helps identify problematic SQL statements
• Library Cache Integration: Works with Oracle’s shared SQL area

Usage

@qsqlhash.sql

Input Parameters:

• hash_value: The hash value of the SQL statement to retrieve

Required Privileges

SELECT on V$SQLTEXT

Sample Output

                                            SQL Text

SQL TEXT
----------------------------------------------------------------
SELECT e.employee_id, e.first_name, e.last_name, d.department_n
ame FROM employees e JOIN departments d ON e.department_id = d.d
epartment_id WHERE e.salary > :1 AND d.location_id = :2 ORDER B
Y e.last_name, e.first_name

Understanding Hash Values

Hash Value Purpose

Unique SQL Identification

-- Hash values provide:
-- Unique identifier for each distinct SQL statement
-- Fast lookup mechanism in library cache
-- Consistent identification across sessions
-- Performance monitoring correlation

SQL Statement Variations

-- Different statements have different hash values:
-- Different text = different hash
-- Different bind variable names = different hash
-- Different whitespace/case = different hash
-- Identical text = same hash value

Hash Value Sources

Common Sources of Hash Values

-- Hash values found in:
-- V$SQL.HASH_VALUE
-- V$SESSION.SQL_HASH_VALUE
-- V$ACTIVE_SESSION_HISTORY.SQL_HASH_VALUE
-- AWR reports and analysis
-- Performance monitoring tools

Performance Analysis Context

-- Hash values used for:
-- Correlating SQL across different views
-- Tracking SQL performance over time
-- Identifying problematic statements
-- Supporting tuning efforts

Common Use Cases

1. SQL Performance Investigation

   • Identify SQL text from performance reports
   • Correlate hash values from monitoring tools
   • Support SQL tuning efforts
   • Investigate problematic statements

2. Troubleshooting Support

   • Find SQL causing performance issues
   • Investigate blocking or resource-intensive statements
   • Support incident resolution
   • Validate SQL changes

3. Development and Testing

   • Verify SQL statement content
   • Support application debugging
   • Validate query generation
   • Analyze application SQL patterns

4. Monitoring Integration

   • Correlate with performance monitoring data
   • Support automated analysis
   • Investigate alert conditions
   • Analyze historical performance

Advanced Usage

Hash Value Discovery

Finding Hash Values

-- Find hash values from active sessions:
SELECT sid, sql_hash_value, status
FROM v$session
WHERE sql_hash_value IS NOT NULL
  AND status = 'ACTIVE';

-- Find hash values from SQL area:
SELECT hash_value, executions, disk_reads
FROM v$sql
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

Historical Hash Value Analysis

-- Find hash values from AWR:
SELECT DISTINCT sql_hash_value
FROM dba_hist_active_sess_history
WHERE sample_time > sysdate - 1
  AND event = 'db file sequential read';

SQL Text Analysis

Long SQL Statement Handling

-- For very long SQL statements:
-- V$SQLTEXT breaks long statements into pieces
-- Script automatically orders pieces correctly
-- Each piece is up to 64 characters
-- ORDER BY piece ensures correct reconstruction

SQL Text Variations

-- Compare similar SQL statements:
-- Use multiple hash values to compare
-- Identify differences in SQL text
-- Support standardization efforts
-- Analyze SQL generation patterns

Integration with Performance Analysis

SQL Performance Correlation

Performance Metrics Correlation

-- Correlate SQL text with performance:
SELECT s.hash_value, s.executions, s.disk_reads,
       s.buffer_gets, s.cpu_time
FROM v$sql s
WHERE s.hash_value = 'your_hash_value';

Execution Plan Analysis

-- Find execution plans for the SQL:
SELECT plan_hash_value, child_number
FROM v$sql
WHERE hash_value = 'your_hash_value';

Monitoring Tool Integration

AWR Report Integration

-- Use with AWR analysis:
-- Hash values from AWR reports
-- Top SQL identification
-- Historical performance tracking
-- Trend analysis support

Real-Time Monitoring

-- Real-time SQL identification:
-- Active session monitoring
-- Performance dashboard integration
-- Alert investigation support
-- Incident response assistance

Troubleshooting Applications

SQL Not Found Issues

Common Reasons for Missing SQL

-- SQL may not be found if:
-- Statement aged out of shared pool
-- Hash value is incorrect or outdated
-- Insufficient shared pool retention
-- Statement was in different instance (RAC)

Alternative Lookup Methods

-- Alternative SQL lookup approaches:
-- Use SQL_ID instead of hash_value (newer versions)
-- Check DBA_HIST_SQLTEXT for historical statements
-- Use V$SQL for currently cached statements
-- Check application logs for SQL text

Hash Value Validation

Verifying Hash Values

-- Validate hash value exists:
SELECT COUNT(*)
FROM v$sqltext
WHERE hash_value = 'your_hash_value';

-- Check for variations:
SELECT DISTINCT hash_value
FROM v$sql
WHERE sql_text LIKE '%your_partial_text%';

Best Practices

Hash Value Management

Documentation

-- Document hash values for:
-- Important application SQL statements
-- Frequently tuned queries
-- Performance baseline statements
-- Critical business processes

Version Control

-- Track hash value changes:
-- Application deployments may change hash values
-- SQL modifications create new hash values
-- Performance baseline updates
-- Tuning impact assessment

Performance Analysis Workflow

Systematic Investigation

-- Investigation process:
-- 1. Identify hash value from monitoring
-- 2. Retrieve SQL text using this script
-- 3. Analyze SQL for optimization opportunities
-- 4. Implement and validate improvements

Related Scripts

• vsqltext.sql - SQL text analysis
• sql-id.sql - SQL ID based lookup
• vsqltop.sql - Top SQL analysis
• sqlbysid.sql - SQL by session ID

Enhanced Analysis

SQL Text Processing

Complete SQL Reconstruction

-- Enhanced version with line numbers:
SELECT piece, sql_text
FROM v$sqltext
WHERE hash_value = '&hash_value'
ORDER BY piece;

-- Single line concatenation:
SELECT LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS complete_sql
FROM v$sqltext
WHERE hash_value = '&hash_value';

SQL Metadata Retrieval

-- Get additional SQL information:
SELECT sql_text, executions, disk_reads, buffer_gets,
       cpu_time, elapsed_time, first_load_time
FROM v$sql
WHERE hash_value = '&hash_value';

Historical Analysis

AWR Historical Lookup

-- Find SQL in AWR repository:
SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_hash_value = '&hash_value';

Performance History

-- Analyze SQL performance over time:
SELECT snap_id, executions_delta, disk_reads_delta
FROM dba_hist_sqlstat
WHERE sql_hash_value = '&hash_value'
ORDER BY snap_id;

Security Considerations

Data Sensitivity

SQL Text Content

-- Consider security implications:
-- SQL text may contain sensitive information
-- Bind variable names may reveal data structure
-- Business logic exposure through SQL
-- Access control for SQL text viewing

Privacy Protection

-- Protect sensitive SQL:
-- Limit access to authorized personnel
-- Consider data masking for sensitive environments
-- Audit SQL text access when required
-- Implement proper security controls

Modern Alternatives

SQL_ID Usage

Newer Oracle Versions

-- Use SQL_ID instead of hash_value (10g+):
SELECT sql_text
FROM v$sql
WHERE sql_id = '&sql_id';

-- SQL_ID provides better uniqueness
-- More reliable across Oracle versions
-- Used in newer monitoring tools

Summary

This script is essential for:

1. SQL Investigation - Retrieving complete SQL text using hash values from performance analysis
2. Performance Troubleshooting - Identifying specific SQL statements causing performance issues
3. Development Support - Supporting application debugging and SQL analysis efforts
4. Monitoring Integration - Correlating hash values from monitoring tools with actual SQL text
5. Tuning Support - Providing foundation for SQL optimization and tuning efforts