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 analyzes top SQL statements for specific users by:

• Filtering SQL statements by username pattern
• Showing comprehensive performance metrics
• Calculating efficiency ratios (logical/physical I/O)
• Ordering results by parse calls and buffer gets
• Providing thresholds for significant statements only

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

SQL> @vsqltopu.sql
Enter value for user: SCOTT
Enter value for parse: 100
Enter value for execute: 1000
Enter value for physical: 10000
Enter value for logical: 100000

Parameters

• user: Username pattern to filter (use % for multiple users)
• parse: Minimum parse calls threshold
• execute: Minimum executions threshold
• physical: Minimum physical reads threshold
• logical: Minimum logical reads (buffer gets) threshold

Required Privileges

• SELECT on V$SQLAREA
• SELECT on DBA_USERS

Sample Output

Shared SQL Area - Top SQL Statements

USERNAME    ADDRESS       S  EXECUTE PARSE CALLS   LOG/   DISK READS   PHY/  BUFFER GETS   LOG/ FIRST_LOAD
                                      CALLS          PHY               EXE                 EXE
----------- -------- ---- -------- ---------- ---- ----------- ------ ----------- ------- ----------
SCOTT       12AB34CD    2    1,250      1,350   25     456,789    365 123,456,789  98,765 2024-01-15/09
SCOTT       56EF78GH    5    2,100      2,200   15     234,567    112  45,678,901  21,752 2024-01-15/08

SQL Text
-------------------------------------------------------------------------------
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > :1
UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = :1 AND HIREDATE < :2
                -------- ----------          -----------         -------
sum                3,350      3,550            691,356     169,134,690

Key Output Columns

• USERNAME: Database username executing the SQL
• ADDRESS: SQL statement address in shared pool
• S: Number of sort operations
• EXECUTE: Total number of executions
• PARSE CALLS: Total number of parse operations
• LOG/PHY: Logical to physical read ratio
• DISK READS: Total physical reads from disk
• PHY/EXE: Physical reads per execution
• BUFFER GETS: Total logical reads (buffer cache)
• LOG/EXE: Logical reads per execution
• FIRST_LOAD: When SQL was first loaded into shared pool
• SQL TEXT: First 115 characters of SQL statement

Understanding the Metrics

Performance Indicators

• High LOG/PHY ratio: Good buffer cache efficiency (>10 is good)
• Low PHY/EXE: Efficient per-execution I/O
• High PARSE CALLS: May indicate missing bind variables
• High LOG/EXE: Potential inefficient access paths

Efficiency Analysis

• Parse to Execute ratio: Should be low for good performance
• Buffer hit ratio: (BUFFER_GETS - DISK_READS) / BUFFER_GETS
• I/O efficiency: Compare physical vs logical reads

Common Use Cases

1. User-Specific Performance Analysis

   • Identify problematic SQL by application user
   • Focus tuning efforts on specific schemas
   • Validate application SQL efficiency

2. Application Troubleshooting

   • Find resource-intensive operations per user
   • Identify users with poor SQL patterns
   • Analyze specific application modules

3. Resource Usage Analysis

   • Track I/O consumption by user
   • Monitor parsing behavior
   • Identify heavy SQL consumers

Tuning Recommendations

High Parse Calls

1. Implement Bind Variables

   • Replace literal values with bind variables
   • Use prepared statements in applications
   • Enable cursor sharing if appropriate

2. Connection Pooling

   • Reuse database connections
   • Keep cursors open where possible
   • Implement statement caching

High Physical I/O

1. Index Optimization

   • Add missing indexes
   • Review execution plans
   • Consider index usage patterns

2. SQL Rewriting

   • Optimize WHERE clauses
   • Improve join conditions
   • Consider query restructuring

Poor Buffer Ratios

1. Memory Tuning
   • Increase buffer cache size
   • Review SGA allocation
   • Consider automatic memory management

Advanced Analysis

Find users with highest I/O:

SELECT u.username,
       SUM(s.disk_reads) total_physical_reads,
       SUM(s.buffer_gets) total_logical_reads,
       COUNT(*) statement_count
FROM v$sqlarea s, dba_users u
WHERE s.parsing_user_id = u.user_id
GROUP BY u.username
ORDER BY SUM(s.disk_reads) DESC;

Identify bind variable issues:

SELECT username, sql_text, parse_calls, executions,
       ROUND(parse_calls/GREATEST(executions,1),2) parse_ratio
FROM v$sqlarea s, dba_users u
WHERE s.parsing_user_id = u.user_id
AND parse_calls > executions * 0.1  -- High parse ratio
ORDER BY parse_ratio DESC;

Related Scripts

• SQL Performance Analysis
• Top SQL Finder
• User Session Analysis