Top SQL Statements Finder (vsqltopfind.sql)

What This Script Does

Analyzes top SQL statements in Oracle’s shared SQL area, identifying the most resource-intensive queries based on buffer gets. Provides detailed performance metrics with interactive filtering to focus on problematic SQL statements that may require tuning.

The Script

rem  vsqltop.sql
rem
set lines 220
set long 2000
rem
ttitle 'Shared SQL Area - Top Unbound SQL Statements'
rem
col SQL_FULLTEXT format a200 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 99999999 heading 'EXEC'
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 9999999 heading 'LOG/|EXE'
col sorts format 99 heading 'Sorts'
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 elapsed_time format 9,990 heading 'Elapsed|Secs'
col elapsed_per_exec format 9990.0000 heading 'Elapsed|Secs|Per|Exec'
col cpu_per_exec format 9990.0000 heading 'CPU|Sec|Per|Exec'
rem
rem break on report
rem compute sum of parse_calls executions disk_reads buffer_gets on report
rem


select
       rows_processed,
       rows_processed/decode(executions, 0, 1, executions) rows_exe,
--       address,
       sql_id,
       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,
       cpu_time / 1000000 / decode(executions,0,1,executions) cpu_per_exec,
       elapsed_time / 1000000 / decode(executions,0,1,executions) elapsed_per_exec,
       module
--     , sql_text
     , SQL_FULLTEXT
  from v$sqlarea
 where upper(sql_fulltext) like nvl(upper('&sql_fulltext'),'%')
   and hash_value like nvl('&hash_value','%')
   and module not in ('SQL Developer','DBMS_SCHEDULER','TOAD 9.7.0.51','SQL*Plus','TOAD 10.1.1.8','TOAD background query session')
--   and module is not null
--   and (module like 'java@ssz0004%'
--        or
--        module like 'JDBC Connect Client%'
--       )
 order by buffer_gets desc
/

Usage

-- Basic usage - shows all SQL statements
@vsqltopfind.sql

-- When prompted, press Enter to see all statements or provide filters:
Enter value for sql_fulltext: SELECT%FROM%LARGE_TABLE%
Enter value for hash_value:

-- Filter by specific SQL pattern
@vsqltopfind.sql
Enter value for sql_fulltext: %ORDER BY%
Enter value for hash_value:

-- Filter by hash value
@vsqltopfind.sql
Enter value for sql_fulltext:
Enter value for hash_value: 1234567890

Parameters

The script prompts for:

sql_fulltext: SQL pattern to search for (use % wildcards, case insensitive)
hash_value: Specific hash value to find (exact match)

Both parameters are optional - press Enter to skip filtering.

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on V$SQLAREA

Sample Output

                    Shared SQL Area - Top Unbound SQL Statements

    ROWS     ROWS/    SQL_ID        HASH_VALUE Sorts      EXEC PARSE|    LOG/     DISK    PHY/ BUFFER      LOG/ CPU    Elapsed
PROCESSED       EXE                                               CALLS    PHY    READS     EXE   GETS      EXE Sec    Secs
                                                                                                              Per    Per
                                                                                                             Exec   Exec MODULE
--------- --------- ------------- ----------- ----- --------- -------- ------ --------- ------- ---------- ------- ------ ------ ------
  2145678    214568 8xkj9m3nv8qrs   987654321     0        10       10     15    125000   12500 1875000000  187500  2.500  3.125 MyApp.exe

select * from large_table where status = 'ACTIVE' order by created_date desc

    45612      4561 fgh67nm2k1pqs   456789123     5       100      100      2      8500      85   170000     1700  0.125  0.180 java.exe

select count(*) from user_sessions where last_activity > sysdate - 1/24

    12000     12000 abc123def456g   123456789     0         1        1      1       250     250      250      250  0.050  0.075 JDBC Thin Client

select name, value from v$parameter where name like '%memory%'

Key Output Columns

ROWS PROCESSED: Total rows returned by this SQL statement
ROWS/EXE: Average rows returned per execution
SQL_ID: Unique identifier for the SQL statement
HASH_VALUE: Hash value for the SQL text
Sorts: Number of sort operations performed
EXEC: Total number of executions
PARSE CALLS: Number of times statement was parsed
LOG/PHY: Ratio of logical to physical reads (higher is better)
DISK READS: Total physical disk reads
PHY/EXE: Physical reads per execution
BUFFER GETS: Total logical reads (blocks accessed in memory)
LOG/EXE: Logical reads per execution
CPU Sec Per Exec: CPU seconds consumed per execution
Elapsed Secs Per Exec: Total elapsed time per execution
MODULE: Client application or tool that executed the SQL

Performance Analysis Guidelines

High Resource Consumers

Focus on statements with highest BUFFER GETS values
Look for high PHY/EXE ratios indicating excessive physical I/O

Inefficient SQL Patterns

Low LOG/PHY ratios suggest poor buffer utilization
High CPU Sec Per Exec indicates computation-heavy queries
Large difference between elapsed and CPU time suggests I/O waits

Execution Frequency

High EXEC counts with moderate resource usage may still impact system
Single executions with very high resource usage need immediate attention

Common Use Cases

Performance Tuning Session

-- Find top resource consumers
@vsqltopfind.sql
-- Analyze execution patterns and resource usage
-- Identify candidates for SQL tuning

Troubleshooting Slow Performance

-- Search for specific problematic SQL patterns
@vsqltopfind.sql
Enter value for sql_fulltext: %FULL TABLE SCAN%
-- Focus on statements causing performance issues

Application Analysis

-- Filter by application module
@vsqltopfind.sql
-- Review SQL patterns from specific applications
-- Identify optimization opportunities

Capacity Planning

-- Regular monitoring of SQL workload patterns
@vsqltopfind.sql
-- Track resource consumption trends
-- Plan for system scaling

SQL Tuning Identification

-- Find statements with high physical I/O
@vsqltopfind.sql
-- Look for LOW LOG/PHY ratios
-- Identify indexing opportunities

Filtering Logic

The script excludes common development tools to focus on application SQL:

module not in ('SQL Developer','DBMS_SCHEDULER','TOAD 9.7.0.51','SQL*Plus','TOAD 10.1.1.8','TOAD background query session')

This filtering ensures results highlight actual application workload rather than administrative or development activity.

Performance Impact

Low to Medium Impact: Queries V$SQLAREA system view
Memory Usage: Sorts results by BUFFER_GETS in memory
Network Traffic: Large result sets may generate significant output
Shared Pool: No impact on shared pool or cursor cache

Analysis Tips

Resource Utilization

Buffer Gets per Execution: Primary tuning focus
Physical vs Logical Reads: Index effectiveness indicator
CPU vs Elapsed Time: Wait event implications

SQL Patterns

High Parse Counts: May indicate cursor sharing issues
Zero Executions: Parsed but never executed statements
High Sort Operations: ORDER BY or GROUP BY optimization needs

Application Behavior

Module Analysis: Identify applications generating load
Execution Frequency: Balance between resource per execution and total impact
Time Patterns: Consider scheduling and peak usage periods

Troubleshooting

No Results Returned

Check if applications are actively running
Verify filter criteria aren’t too restrictive
Confirm shared pool has SQL statements cached

Very Large Result Sets

Use SQL pattern filtering to narrow results
Focus on specific hash values for detailed analysis
Consider time-based filtering for recent activity

Unexpected SQL Statements

May indicate new application behavior
Could reveal unauthorized or inefficient code
Useful for security and compliance monitoring

SQL by Session ID - Find SQL for specific sessions
SQL Text Retrieval - Get full SQL text by hash value
SQL Plan Finder - Execution plan analysis
Active Session Analysis - Current session activity
High Wait Time Analysis - Wait event investigation