SQL Analysis by Address (vsqladdr.sql)

What This Script Does

This script retrieves detailed information about SQL statements based on their memory address in the shared pool. It provides execution statistics, resource consumption metrics, and identifies which sessions currently have cursors open for the SQL statement, making it valuable for SQL performance analysis and troubleshooting.

Script

rem  vsqladdr.sql
rem
set linesize 132
rem
ttitle 'SQL by Address'
rem
col sid format 9999 heading 'SID'
col executions format 99999999 heading 'EXECUTIONS'
col parse_calls format 9999999 heading 'PARSE|CALLS'
col disk_reads format 9999999 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 sql_text format a60 heading 'SQL TEXT'
rem
select c.sid,
       s.executions,
       s.parse_calls,
       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.sql_text
  from v$sqlarea s,
       v$open_cursor c
 where s.address = '&address'
   and c.address(+) = s.address;
rem
set linesize 80

Usage

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

-- When prompted, enter:
-- address: SQL statement address from v$sql or v$sqlarea

-- Example
Enter value for address: 00000000BC123456

Parameters

&address: Memory address of the SQL statement (from v$sql, v$sqlarea, or v$session)

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 DISK   PHY/ LOGICAL    LOG/
              CALLS READS  EXE     READS     EXE SQL TEXT
----- ---------- ------- ----- ------ --------- ------- --------------------------------------------------------
  145      45678    1234   890     19   2345678      51 SELECT * FROM sales.orders WHERE order_date >= :1
  234      45678    1234   890     19   2345678      51 SELECT * FROM sales.orders WHERE order_date >= :1
             NULL    NULL  NULL   NULL      NULL    NULL

Key Output Columns

SID: Session ID with open cursor (NULL if no open cursors)
EXECUTIONS: Total number of times the SQL was executed
PARSE CALLS: Number of parse calls (hard and soft combined)
DISK READS: Physical disk reads performed
PHY/EXE: Average physical reads per execution
LOGICAL READS: Buffer gets (logical I/O)
LOG/EXE: Average logical reads per execution
SQL TEXT: Beginning of the SQL statement text

Understanding the Metrics

Execution Statistics

High EXECUTIONS: Frequently executed SQL
Parse/Execute Ratio: PARSE_CALLS vs EXECUTIONS indicates parsing efficiency
Multiple SIDs: SQL statement currently active in multiple sessions

Resource Consumption

PHY/EXE ratio: Physical I/O efficiency per execution
LOG/EXE ratio: Logical I/O per execution (buffer gets)
High DISK READS: I/O intensive SQL requiring optimization

Cursor Management

SID present: Sessions with active cursors for this SQL
NULL SID rows: SQL in shared pool but no active cursors
Multiple cursors: Same SQL shared across sessions

Performance Analysis

SQL Efficiency Indicators

Low PHY/EXE: Good buffer cache hit ratio
High LOG/EXE: May need index optimization
High PARSE CALLS: Potential hard parsing issues
Multiple active sessions: Popular or problematic SQL

Troubleshooting Targets

PHY/EXE > 100: Review execution plan and indexing
LOG/EXE > 10000: Consider SQL optimization
PARSE_CALLS = EXECUTIONS: Hard parsing every execution

Common Use Cases

SQL Performance Investigation

-- Analyze specific problem SQL
@vsqladdr.sql
-- Enter address from v$session.sql_address
-- Review resource usage patterns

Cursor Analysis

-- Find which sessions use specific SQL
@vsqladdr.sql
-- Identify shared cursor usage

Resource Consumption Tracking

-- Monitor high-resource SQL statements
@vsqladdr.sql
-- Track execution patterns over time

Finding SQL Addresses

From Active Sessions

-- Get SQL address from current sessions
SELECT sid, sql_address, sql_hash_value
FROM v$session
WHERE sql_address IS NOT NULL
AND username IS NOT NULL;

From SQL Area

-- Find addresses of high-resource SQL
SELECT address, sql_text, executions, buffer_gets
FROM v$sqlarea
WHERE buffer_gets > 1000000
ORDER BY buffer_gets DESC;

From AWR History

-- Get historical SQL addresses
SELECT sql_id, plan_hash_value, executions_delta
FROM dba_hist_sqlstat
WHERE snap_id = &snap_id;

Advanced Analysis

Compare Current vs Historical Performance

-- After running vsqladdr.sql, compare with AWR data
SELECT sql_id, executions_delta,
       buffer_gets_delta, disk_reads_delta
FROM dba_hist_sqlstat
WHERE sql_id IN (
  SELECT sql_id FROM v$sql
  WHERE address = '&address'
);

Session Context Analysis

-- Get session details for active cursors
SELECT s.sid, s.username, s.program, s.machine,
       s.status, s.last_call_et
FROM v$session s, v$open_cursor c
WHERE c.address = '&address'
AND s.sid = c.sid;

Execution Plan Retrieval

-- Get execution plans for the SQL
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Troubleshooting Common Issues

High Physical I/O

Check execution plan:

-- Review for full table scans
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Analyze indexing:
- Missing indexes on WHERE clause columns
- Index selectivity issues
- Outdated statistics

High Logical I/O

Review SQL logic:
- Unnecessary joins
- Inefficient WHERE conditions
- Missing WHERE clauses
Check for data skew:
- Uneven data distribution
- Partition pruning opportunities

Parsing Issues

Hard parsing symptoms:
- PARSE_CALLS = EXECUTIONS
- High CPU usage
- Library cache contention
Solutions:
- Use bind variables
- Improve cursor sharing
- Pin frequently used SQL

SQL by Hash Value (vsqlhash.sql) - Alternative SQL lookup method
SQL Text Retrieval (sqltext.sql) - Get full SQL text
Top SQL Analysis (vsqltopu.sql) - High-resource SQL identification
Active Session Analysis (gvsess.sql) - Session activity monitoring