Top SQL Statements by User (vsqltopu.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 identifies top SQL statements in the shared pool based on multiple resource consumption metrics. It:

• Filters by username (with wildcard support)
• Shows statements exceeding any of four threshold parameters
• Calculates efficiency ratios (logical/physical, reads per execution)
• Orders by parse calls and buffer gets to highlight problematic SQL

The Script

rem  vsqltop.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
rem
set linesize 115
rem
col username format a11 heading 'USERNAME'
rem col sql_text format a80 heading 'SQL Text'
col sql_text format a115 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 9999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
col address format a8 heading 'ADDRESS'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
rem
break on report
compute sum of parse_calls executions disk_reads buffer_gets on report
rem
select
       u.username,
/*
       s.sharable_mem,
       s.persistent_mem,
       s.runtime_mem,
       s.version_count,
       s.loaded_versions,
       s.open_versions,
       s.kept_versions,
       s.users_opening,
       s.users_executing,
       s.loads,
       s.invalidations,
       s.first_load_time,
       s.command_type,
       s.optimizer_mode,
       s.parsing_user_id,
       s.parsing_schema_id,
       s.hash_value,
       s.module,
       s.module_hash,
       s.action,
       s.action_hash,
       s.serializable_aborts,
       s.rows_processed,
       s.rows_processed/decode(s.executions, 0, 1, s.executions) rows_exe,
*/
       s.address,
       s.sorts,
       s.executions,
       s.parse_calls,
/*
       s.parse_calls*100/decode(s.executions,0,1,s.executions) parse_pct,
*/
       s.buffer_gets/decode(s.disk_reads,0,1,s.disk_reads) log_phy,
       s.disk_reads,
       s.disk_reads/decode(s.executions,0,1,s.executions) phy_exe,
       s.buffer_gets,
       s.buffer_gets/decode(s.executions,0,1,s.executions) log_exe,
       s.first_load_time,
       s.sql_text
  from v$sqlarea s, dba_users u
 where u.username like upper('&user')
   and s.parsing_user_id=u.user_id
   and ( s.parse_calls >= &parse
         or s.executions >= &execute
         or s.disk_reads >= &physical
         or s.buffer_gets >= &logical )
 order by s.parse_calls desc, s.buffer_gets desc;

Usage

-- Basic usage
@vsqltopu.sql

-- When prompted, enter:
-- user: Username pattern (% for all)
-- parse: Minimum parse calls threshold
-- execute: Minimum executions threshold
-- physical: Minimum disk reads threshold
-- logical: Minimum buffer gets threshold

Parameters

The script prompts for:

• &user - Username pattern (use % for wildcard)
• &parse - Minimum parse calls to include
• &execute - Minimum executions to include
• &physical - Minimum disk reads to include
• &logical - Minimum buffer gets to include

Required Privileges

SELECT ON V$SQLAREA
SELECT ON DBA_USERS

Sample Output

Shared SQL Area - Top SQL Statements

USERNAME    ADDRESS  S   EXECUTE PARSE     LOG/   DISK     PHY/     BUFFER     LOG/ SQL Text
                                 CALLS     PHY   READS     EXE       GETS     EXE
----------- -------- -- --------- -------- ----- --------- ------ ----------- ------- ------------------------
HR          3C8AF420  0    125000   125000     8   1562500     13   12500000     100 SELECT * FROM employees WHERE employee_id = :1
APP_USER    3C8AF890  1     50000    45000    15    666667     13   10000000     200 SELECT COUNT(*) FROM orders WHERE status = :1
HR          3C8AF123  0     10000    10000   100    100000     10   10000000    1000 SELECT e.*, d.* FROM employees e, departments d WHERE e.department_id = d.depar
                       --------- -------- ----- --------- ------ -----------
sum                       185000   180000        2329167         32500000

Key Output Columns

• USERNAME - User who parsed the SQL
• ADDRESS - SQL address in shared pool
• S - Number of sorts
• EXECUTE - Total executions
• PARSE CALLS - Total parse calls
• LOG/PHY - Logical reads per physical read ratio
• DISK READS - Total physical reads
• PHY/EXE - Physical reads per execution
• BUFFER GETS - Total logical reads
• LOG/EXE - Logical reads per execution
• SQL Text - First 115 characters of SQL

Common Use Cases

Find Inefficient SQL

@vsqltopu.sql
-- Enter: % for user
-- Enter: 0 for parse, execute, physical
-- Enter: 1000000 for logical (high buffer gets)

Identify Parse-Heavy SQL

@vsqltopu.sql
-- Enter: % for user
-- Enter: 10000 for parse (high parse calls)
-- Enter: 0 for other thresholds

Find I/O Intensive Queries

@vsqltopu.sql
-- Enter: % for user
-- Enter: 0 for parse, execute, logical
-- Enter: 100000 for physical (high disk reads)

Performance Indicators

Parse Efficiency:

• Parse calls should be much less than executions
• High parse-to-execution ratio indicates cursor sharing issues

I/O Efficiency:

• High PHY/EXE indicates missing indexes or full scans
• Low LOG/PHY ratio suggests poor buffer cache efficiency

Overall Efficiency:

• High LOG/EXE with low PHY/EXE is ideal
• Indicates good use of buffer cache

Related Scripts

• Top SQL Statements Finder - Alternative top SQL analysis
• SQL by Session ID - Find SQL for specific session
• SQL Text Retrieval - Get full SQL text