SQL Performance Analysis with Timing (vsqltime.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 provides comprehensive SQL statement performance analysis by:

• Analyzing SQL statements in the shared SQL area
• Calculating elapsed time per execution
• Showing buffer cache efficiency ratios
• Identifying resource-intensive SQL statements
• Displaying parse-to-execution ratios

Script

rem  vsqltop.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
rem
set lines 80
rem
col sql_text format a80 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 99999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
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 module format a40
col action format a39
rem
break on report
rem compute sum of parse_calls executions disk_reads buffer_gets on report
compute sum of executions disk_reads buffer_gets on report
rem
select
/*
       sharable_mem,
       persistent_mem,
       runtime_mem,
       version_count,
       loaded_versions,
       open_versions,
       kept_versions,
       users_opening,
       users_executing,
       loads,
       invalidations,
       first_load_time,
       command_type,
       optimizer_mode,
       parsing_user_id,
       parsing_schema_id,
       module,
       module_hash,
       action,
       action_hash,
       serializable_aborts,
       rows_processed,
       rows_processed/decode(executions, 0, 1, executions) rows_exe,
       address,
*/
       hash_value,
       elapsed_time,
       elapsed_time / decode(executions, 0, 1, executions) elapsed_per_exec,
       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,
       module,
       action,
       sql_text
  from v$sqlarea
 where parse_calls >= &parse
    or executions >= &execute
    or disk_reads >= &physical
    or buffer_gets >= &logical
 order by buffer_gets desc;

Usage

SQL> @vsqltime.sql
Enter value for parse: 1000
Enter value for execute: 1000
Enter value for physical: 10000
Enter value for logical: 100000

Parameters

• 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

Sample Output

Shared SQL Area - Top SQL Statements

  HASH VALUE ELAPSED_TIME ELAPSED_PER_EXEC S  EXECUTE PARSE  LOG/ DISK READS   PHY/  BUFFER GETS   LOG/
                                               PCT    PHY              EXE                  EXE MODULE
----------- ------------ ---------------- -- -------- ----- ---- --------- ------ ----------- ------ --------
 1234567890   45,678,901               45  2  1,000,000    12   25   456,789    456 123,456,789  12345 MYAPP.EXE
 2345678901   23,456,789               78  5    300,000    25   15   234,567    781  45,678,901  15226 SQL*Plus

SQL TEXT
--------------------------------------------------------------------------------
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = :1 ORDER BY LAST_NAME
SELECT DISTINCT CUSTOMER_ID FROM ORDERS WHERE ORDER_DATE > :1

Key Output Columns

• HASH VALUE: SQL statement hash identifier
• ELAPSED_TIME: Total elapsed time in microseconds
• ELAPSED_PER_EXEC: Average elapsed time per execution
• S: Number of sorts performed
• EXECUTE: Total number of executions
• PARSE PCT: Parse calls as percentage of executions
• 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
• MODULE: Application module name

Understanding the Metrics

Performance Indicators

• High elapsed_per_exec: Resource-intensive statements
• High parse_pct: Statements not using bind variables
• Low log/phy ratio: Inefficient buffer cache usage
• High phy/exe: Statements causing excessive I/O

Efficiency Ratios

• LOG/PHY > 10: Good buffer cache efficiency
• LOG/PHY < 5: Poor cache utilization
• Parse PCT < 10%: Good bind variable usage
• Parse PCT > 50%: Excessive parsing

Common Use Cases

1. Performance Tuning

   • Find slowest SQL statements
   • Identify I/O intensive queries
   • Locate parsing bottlenecks

2. Resource Analysis

   • Track buffer cache efficiency
   • Monitor physical I/O patterns
   • Assess application behavior

3. SQL Optimization

   • Find candidates for index creation
   • Identify missing bind variables
   • Locate expensive operations

Tuning Recommendations

High Elapsed Time

• Review execution plans
• Check for missing indexes
• Consider SQL rewriting

High Parse Percentage

• Implement bind variables
• Use prepared statements
• Enable cursor sharing

Poor Buffer Ratios

• Increase buffer cache size
• Add appropriate indexes
• Optimize table access

Related Scripts

• SQL Text Retrieval
• Top SQL Finder
• Execution Plan Analysis