Current Session Statistics Analysis (session_stats.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 Oracle database functionality via the session-stats.sql script.

The Script

rem  session_stats.sql
rem
set linesize 200
rem
ttitle 'Session tatistics'
rem
select  s.value,
  substr(n.name,1,35) name,
  s.sid
from   v$mystat s,
  v$statname n
where(n.statistic# = s.statistic#)
and s.value <> 0 order by s.sid, n.name;
rem
set linesize 80

What This Script Does

This script displays current session statistics for your own database session using the V$MYSTAT view. It shows all non-zero statistics with their values, providing insight into session-level resource consumption, I/O patterns, and performance characteristics.

Key Features

• Session-Specific: Shows statistics only for your current session
• Non-Zero Values: Filters out zero values to focus on active metrics
• Comprehensive Coverage: Displays all Oracle session statistics
• Real-Time Data: Shows current session state
• Performance Insight: Helps understand session resource usage

Usage

@session_stats.sql

No parameters required - automatically shows your current session.

Required Privileges

SELECT on V$MYSTAT
SELECT on V$STATNAME

Sample Output

                                    Session Statistics

     VALUE NAME                                SID
---------- ----------------------------------- ----------
        15 CPU used by this session                  142
      2048 CPU used when call started               142
         1 DB time                                   142
         8 DBWR checkpoint buffers written          142
         1 DBWR thread checkpoint buffers writ      142
    125847 consistent gets                           142
      4521 consistent gets from cache               142
         1 cursor authentications                    142
         8 db block changes                          142
      1247 db block gets                             142
      1247 db block gets from cache                  142
         3 execute count                             142
      1952 logical read bytes from cache            142
         2 opened cursors cumulative                 142
         1 parse count (hard)                       142
         3 parse count (total)                      142
         2 physical read IO requests                 142
         2 physical read bytes                       142
         4 physical read total IO requests          142
         4 physical read total bytes                142
         1 recursive calls                          142
         8 redo entries                             142
      2048 redo size                                142
         1 session logical reads                    142
         3 sorts (memory)                           142
         1 user calls                               142

Key Statistics Categories

CPU and Time

• CPU used by this session: Total CPU centiseconds consumed
• DB time: Database time spent on the session
• CPU used when call started: CPU at start of current call

Memory and Buffer Activity

• consistent gets: Logical reads from buffer cache
• db block gets: Current mode block reads
• session logical reads: Total logical I/O operations
• physical read bytes: Bytes read from disk

Parse and Execution

• parse count (total): Total SQL parse operations
• parse count (hard): Hard parse operations requiring optimization
• execute count: SQL statement executions
• opened cursors cumulative: Total cursors opened

I/O Operations

• physical read IO requests: Physical read requests to disk
• physical read total bytes: Total bytes read physically
• redo size: Amount of redo generated
• redo entries: Number of redo entries created

Sorting and Processing

• sorts (memory): In-memory sort operations
• sorts (disk): Disk-based sort operations
• recursive calls: Internal Oracle recursive operations

Performance Analysis

Efficiency Indicators

-- Calculate hit ratios and efficiency metrics
Buffer Cache Hit Ratio = (consistent gets - physical reads) / consistent gets * 100
Parse Ratio = hard parses / total parses * 100

Resource Consumption Patterns

• High CPU: Indicates compute-intensive operations
• High Logical Reads: May suggest inefficient SQL or missing indexes
• High Physical Reads: Could indicate inadequate buffer cache
• High Hard Parses: Suggests cursor sharing issues

Common Use Cases

1. Session Performance Tuning

   • Analyze current session resource consumption
   • Identify performance bottlenecks

2. Before/After Testing

   • Run before executing SQL statements
   • Compare statistics after execution
   • Measure operation impact

3. Resource Monitoring

   • Monitor session-level resource usage
   • Track I/O patterns and CPU consumption

4. Troubleshooting

   • Understand what your session is doing
   • Identify unexpected resource consumption

Analysis Examples

High Logical I/O Investigation

-- If consistent gets is very high:
-- Check for:
-- - Missing indexes
-- - Inefficient SQL statements
-- - Large table scans

Parse Analysis

-- If parse count is high:
-- Check for:
-- - Lack of bind variables
-- - Cursor sharing issues
-- - Hard parsing problems

Memory Usage

-- If sorts (disk) > 0:
-- Consider increasing PGA memory
-- Review sort-heavy operations

Integration with Performance Tuning

Baseline Establishment

1. Run Before Operation

   @session_stats.sql
   -- Save output as baseline

2. Execute Operation

   -- Run your SQL statements

3. Compare Results

   @session_stats.sql
   -- Compare with baseline

Key Metrics to Watch

• Consistent Gets: Should be minimized
• Physical Reads: Minimize for better performance
• CPU Usage: Track for resource-intensive operations
• Parse Counts: Optimize to reduce hard parsing

Troubleshooting

No Output

• Ensure your session has executed some operations
• Zero values are filtered out by default

Too Much Output

• Normal - Oracle tracks many statistics
• Focus on relevant metrics for your analysis

Unexpected Values

• Statistics are cumulative since session start
• Consider resetting session or starting fresh

Related Scripts

• session-wait.sql - Current session wait events
• vsesstat.sql - Enhanced session statistics
• gvsess.sql - Active session analysis
• pga.sql - PGA memory analysis