Top SQL by Execution Count (vsqlexe.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 the most frequently executed SQL statements by:

• Ranking SQL statements by execution count
• Filtering out system queries and monitoring statements
• Calculating performance ratios and per-execution metrics
• Providing comprehensive execution statistics
• Computing totals for key metrics

Script

rem  vsqlexe.sql
rem
ttitle 'Shared SQL Area - Top Execute Count'
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 9999999 heading 'DISK|READS'
col phy_exe format 9999 heading 'PHY/|EXE'
col buffer_gets format 9999999999 heading 'BUFFER|GETS'
col log_exe format 999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
col address format a8 heading 'ADDRESS'
rem
break on report
compute sum of parse_calls 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,
*/
       address,
       sorts,
       executions,
       parse_calls,
       parse_calls*100/decode(executions,0,null,executions) parse_pct,
       buffer_gets/decode(disk_reads,0,null,disk_reads) log_phy,
       disk_reads,
       disk_reads/decode(executions,0,null,executions) phy_exe,
       buffer_gets,
       buffer_gets/decode(executions,0,null,executions) log_exe,
       sql_text
  from v$sqlarea
 where executions >= &execute
 AND SQL_TEXT NOT LIKE '%file#%'
 AND SQL_TEXT NOT LIKE '%user#%'
 AND SQL_TEXT NOT LIKE '%TTSERVER.TTSQLLOG%'
 AND SQL_TEXT NOT LIKE '%sys.job$%'
 AND SQL_TEXT NOT LIKE '%hist_head$%'
 AND SQL_TEXT NOT LIKE '%TTSERVER.%'
 AND SQL_TEXT NOT LIKE '%sysauth$%'
 AND SQL_TEXT NOT LIKE '%v$%'
 and sql_text NOT LIKE '%DBMS_OUTPUT.GET_LINES%'
 AND SQL_TEXT NOT LIKE '%TTSQLMATRIX%'
 AND SQL_TEXT NOT LIKE '%V$%'
 AND SQL_TEXT NOT LIKE '%ALTER SESSION SET NLS_%'
 AND SQL_TEXT NOT LIKE '%ALTER SESSION SET TIME_%'
 AND SQL_TEXT NOT LIKE '%view$%'
 AND SQL_TEXT NOT LIKE '%ccol$%'
 AND SQL_TEXT NOT LIKE '%col$%'
 AND SQL_TEXT NOT LIKE '%BEGIN DBMS_OUTPUT.%'
 AND SQL_TEXT NOT LIKE '%SYSTEM.PRODUCT_PRIVS%'
 AND SQL_TEXT NOT LIKE '%seq$%'
 AND SQL_TEXT NOT LIKE '%obj$%'
 order by executions desc, buffer_gets desc;

Usage

SQL> @vsqlexe.sql
Enter value for execute: 1000

Parameters

• execute: Minimum execution count threshold to display

Required Privileges

• SELECT on V$SQLAREA

Sample Output

Shared SQL Area - Top Execute Count

ADDRESS     S  EXECUTE PARSE CALLS PARSE   LOG/   DISK READS   PHY/ BUFFER GETS   LOG/
                               CALLS   PCT    PHY               EXE                EXE
-------- ---- -------- ----------- ----- ------ ----------- ------ ----------- ------
12AB34CD    2   45,678      47,123   103     15     123,456      3  12,345,678    270

SQL Text
--------------------------------------------------------------------------------
SELECT customer_id, order_date, total_amount FROM orders WHERE status = :1

56EF78GH    1   23,456      23,567   100     25     456,789     19   5,678,901    242

SQL Text
--------------------------------------------------------------------------------
UPDATE inventory SET quantity = quantity - :1 WHERE product_id = :2

                -------- -----------           ----------- -----------
sum               69,134      70,690               580,245  18,024,579

Key Output Columns

• ADDRESS: SQL statement address in shared pool
• S: Number of sort operations performed
• EXECUTE: Total number of executions
• PARSE CALLS: Total number of parse operations
• PARSE PCT: Parse calls as percentage of executions
• LOG/PHY: Logical to physical read ratio
• DISK READS: Total physical disk reads
• PHY/EXE: Physical reads per execution
• BUFFER GETS: Total logical reads (buffer cache hits)
• LOG/EXE: Logical reads per execution
• SQL TEXT: First 80 characters of SQL statement

Understanding the Metrics

Execution Analysis

• High Execution Count: Frequently used statements
• Parse Percentage: Should be low (<10%) for good performance
• Parse = Execution: Indicates missing bind variables

I/O Efficiency

• LOG/PHY Ratio: Higher is better (>10 ideal)
• PHY/EXE: Lower is better (indicates good caching)
• LOG/EXE: Consistent values indicate stable access patterns

Excluded SQL Patterns

The script filters out common system and monitoring queries:

• Data Dictionary: file#, user#, obj$, col$
• System Jobs: sys.job$, hist_head$
• Application Monitoring: TTSERVER.*, TTSQLMATRIX
• Dynamic Views: v$, V$
• Session Settings: ALTER SESSION SET
• DBMS Output: DBMS_OUTPUT.GET_LINES

Common Use Cases

1. Application Performance Analysis

   • Identify most frequently executed queries
   • Find statements that may benefit from optimization
   • Analyze execution patterns

2. Bind Variable Analysis

   • High parse percentages indicate missing bind variables
   • Identify hard-coded SQL statements
   • Plan application code improvements

3. Resource Usage Analysis

   • Find high-volume statements consuming resources
   • Prioritize tuning efforts based on frequency
   • Monitor application efficiency

Performance Indicators

Good Performance Signs

• Parse PCT < 10%: Good bind variable usage
• High LOG/PHY: Effective buffer cache usage
• Low PHY/EXE: Efficient I/O patterns
• Consistent LOG/EXE: Stable access patterns

Performance Issues

• Parse PCT > 50%: Missing bind variables
• Low LOG/PHY: Poor buffer cache hit ratio
• High PHY/EXE: Inefficient queries or indexing
• Variable LOG/EXE: Inconsistent data access

Tuning Recommendations

High Parse Percentages

1. Implement Bind Variables

   -- Bad: Hard-coded values
   SELECT * FROM orders WHERE customer_id = 12345;
   
   -- Good: Bind variables
   SELECT * FROM orders WHERE customer_id = :customer_id;

2. Application Changes

   • Use prepared statements
   • Implement connection pooling
   • Enable cursor sharing (if appropriate)

High Physical I/O

1. Indexing Review

   -- Check for missing indexes
   SELECT sql_id, executions, disk_reads
   FROM v$sql
   WHERE disk_reads/executions > 100;

2. SQL Optimization

   • Review execution plans
   • Consider query rewriting
   • Analyze WHERE clause efficiency

Advanced Analysis

Find statements with bind variable issues:

SELECT sql_text, executions, parse_calls,
       ROUND(parse_calls/executions*100, 2) as parse_pct
FROM v$sqlarea
WHERE executions > 100
AND parse_calls/executions > 0.1
ORDER BY parse_calls/executions DESC;

Analyze I/O efficiency:

SELECT sql_text, executions,
       ROUND(disk_reads/executions, 2) as phy_per_exec,
       ROUND(buffer_gets/disk_reads, 2) as hit_ratio
FROM v$sqlarea
WHERE executions > 100
AND disk_reads > 0
ORDER BY disk_reads/executions DESC;

Monitoring Over Time

Create baseline:

CREATE TABLE sql_execution_baseline AS
SELECT sysdate snap_time, sql_id, executions, parse_calls,
       disk_reads, buffer_gets
FROM v$sqlarea
WHERE executions > 1000;

Compare changes:

SELECT b.sql_id,
       s.executions - b.executions as new_executions,
       s.parse_calls - b.parse_calls as new_parses
FROM sql_execution_baseline b, v$sqlarea s
WHERE b.sql_id = s.sql_id
AND s.executions - b.executions > 0;

Best Practices

1. Regular Monitoring

   • Run weekly to identify trends
   • Focus on top 20-50 statements
   • Track changes over time

2. Threshold Setting

   • Adjust execution threshold based on environment
   • Start with 1000 executions
   • Lower for low-activity systems

3. Action Planning

   • Prioritize by execution count * resource usage
   • Address bind variable issues first
   • Focus on statements with growth trends

Related Scripts

• SQL Performance Analysis
• Top SQL by User
• Library Cache Analysis