System Statistics by Name (vsysname.sql)

What This Script Does

This script provides flexible access to Oracle system statistics:

Allows filtering by statistic name pattern
Shows statistic ID, current value, and descriptive name
Useful for targeted performance analysis
Provides foundation for custom monitoring queries

The Script

rem  vsysname.sql
rem
ttitle 'System Statistics - By Name'
rem
set lines 132
rem
col statistic# format 990 heading 'ID'
col value format b999,999,999,999,999,999 heading 'VALUE'
col name format a58 heading 'NAME'
rem
select statistic#, value, name
  from v$sysstat
  where name like nvl('&name','%')
 order by name;

Usage

-- View all statistics
@vsysname.sql
-- Enter: Press Enter (shows all)

-- Filter by pattern
@vsysname.sql
-- Enter: CPU% (shows CPU-related stats)

-- Specific statistic
@vsysname.sql
-- Enter: user commits (shows commit statistics)

Parameters

The script prompts for:

&name - Statistic name pattern (% wildcards supported, default shows all)

Required Privileges

SELECT ON V$SYSSTAT

Sample Output

System Statistics - By Name

  ID                    VALUE NAME
---- ------------------------ ----------------------------------------------------------
   1                   45,678 CPU used by this session
   2                1,234,567 CPU used when call started
   3                   12,345 CR blocks created
   4                  234,567 Cached Commit SCN referenced
   5                   56,789 DB time
  ...

Key Output Columns

ID - Internal statistic number
VALUE - Current accumulated value
NAME - Descriptive statistic name

Common Filtering Examples

CPU Statistics

@vsysname.sql
-- Enter: %CPU%
-- Shows all CPU-related metrics

I/O Statistics

@vsysname.sql
-- Enter: %read%
-- Shows read-related I/O metrics

Session Statistics

@vsysname.sql
-- Enter: %session%
-- Shows session-related statistics

Commit/Transaction Stats

@vsysname.sql
-- Enter: %commit%
-- Shows transaction commit statistics

Parse Statistics

@vsysname.sql
-- Enter: %parse%
-- Shows SQL parsing metrics

Important Statistics Categories

Performance Metrics:

CPU used by this session - Total CPU time
DB time - Database time spent
physical reads - Disk I/O operations
session logical reads - Buffer cache reads

Transaction Metrics:

user commits - Successful transactions
user rollbacks - Rolled back transactions
transaction rollbacks - All rollbacks

Parse Metrics:

parse count (total) - Total SQL parses
parse count (hard) - Hard parses
parse time cpu - CPU time for parsing

I/O Metrics:

physical reads - Disk reads
physical writes - Disk writes
redo size - Redo log bytes generated

Performance Analysis Tips

Identify Bottlenecks:

-- Look for high values in:
-- - CPU used by this session
-- - physical reads
-- - parse count (hard)
-- - user rollbacks

Calculate Ratios:

-- Buffer cache hit ratio:
-- (session logical reads - physical reads) / session logical reads

-- Parse ratio:
-- parse count (hard) / parse count (total)

Trend Analysis:

-- Run script periodically
-- Compare values over time
-- Look for increasing trends

Common Use Cases

Quick Performance Check

@vsysname.sql
-- Enter: %CPU%
-- Check CPU utilization

I/O Analysis

@vsysname.sql
-- Enter: %read%
-- Analyze read patterns

Application Monitoring

@vsysname.sql
-- Enter: %commit%
-- Monitor transaction activity

Tuning Validation

@vsysname.sql
-- Enter: %parse%
-- Verify parse reduction efforts

System Metrics Summary - Comprehensive system metrics
CPU Statistics - Detailed CPU analysis
Session Statistics - Session-level statistics
I/O Statistics - I/O performance metrics