System Time Analysis (systime.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 a comprehensive time-based analysis of database instance activity, breaking down how the Oracle instance spends its time between CPU processing and various wait events. It calculates percentages of total time and non-idle time spent on different activities, helping DBAs identify performance bottlenecks and understand workload characteristics.

Script

/**********************************************************************
 * File:  systime.sql
 * Type:  SQL*Plus script
 * Author:  Tim Gorman (SageLogix, Inc)
 * Date:  25-Mar-02
 *
 * Description:
 *  SQL*Plus script to display total time spent waiting info
 *  (from V$SYSTEM_EVENT) along with total time spent processing
 *  info (from V$SYSSTAT for "CPU used by this session" statistic),
 *  along with a calculation of the percentage of time the instance
 *  spent doing each thing...
 *
 * Note:
 *  Due to use of "analytic" reporting function, this query can only
 *  be used with v8.1.6 and above...
 *
 * Modifications:
 *********************************************************************/
break on report on username on sid skip 1
set pagesize 100 lines 80 trimspool on trimout on verify off

undef usr

col type format a5 heading "Svc,|Idle,|Wait"
col name format a35 heading "Name" truncate
col tot_secs_spent format 999,999,999,990.00 heading "Total|Seconds|Spent"
col pct_total format 990.00 heading "%|Total"
col nonidle_total format 990.00 heading "%|NonIdle"

select  type,
  name,
  tot_secs_spent,
  (tot_secs_spent / (sum(tot_secs_spent) over ()))*100 pct_total,
  (nonidle_secs_spent / (sum(nonidle_secs_spent) over ()))*100 nonidle_total
from  (select  decode(event,
      'rdbms ipc message', 'Idle',
      'rdbms ipc reply', 'Idle',
      'SQL*Net message from client', 'Idle',
      'SQL*Net break/reset to client', 'Idle',
      'pipe get', 'Idle',
      'pmon timer', 'Idle',
      'smon timer', 'Idle',
      'dispatcher timer', 'Idle',
      'virtual circuit status', 'Idle',
      'PX Idle Wait', 'Idle',
      'PX Deq: Execute Reply', 'Idle',
      'PX Deq: Execution Msg', 'Idle',
      'PX Deq: Table Q Normal', 'Idle',
      'PX Deq Credit: send blkd', 'Idle',
      'PX Deq Credit: need buffer', 'Idle',
      'PX Deq: Parse Reply', 'Idle',
      'PX Deq: Signal ACK', 'Idle',
      'PX Deq: Join ACK', 'Idle',
      'PX qref latch', 'Idle',
      'PX Deq: Msg Fragment', 'Idle',
      'PL/SQL lock timer', 'Idle',
      'inactive session', 'Idle',
        'Wait') type,
    event name,
    time_waited/100 tot_secs_spent,
    decode(event,
      'rdbms ipc message', 0,
      'rdbms ipc reply', 0,
      'SQL*Net message from client', 0,
      'SQL*Net break/reset to client', 0,
      'pipe get', 0,
      'pmon timer', 0,
      'smon timer', 0,
      'dispatcher timer', 0,
      'virtual circuit status', 0,
      'PX Idle Wait', 0,
      'PX Deq: Execute Reply', 0,
      'PX Deq: Execution Msg', 0,
      'PX Deq: Table Q Normal', 0,
      'PX Deq Credit: send blkd', 0,
      'PX Deq Credit: need buffer', 0,
      'PX Deq: Parse Reply', 0,
      'PX Deq: Signal ACK', 0,
      'PX Deq: Join ACK', 0,
      'PX qref latch', 0,
      'PX Deq: Msg Fragment', 0,
      'PL/SQL lock timer', 0,
      'inactive session', 0,
        time_waited/100) nonidle_secs_spent
   from  v$system_event
   where  time_waited > 0
   union all
   select  'Svc' type,
    'other cpu usage' name,
    (t.value - (p.value + r.value))/100 tot_secs_spent,
    (t.value - (p.value + r.value))/100 nonidle_secs_spent
   from  v$sysstat t,
    v$sysstat p,
    v$sysstat r
   where  t.name = 'CPU used by this session'
   and  p.name = 'recursive cpu usage'
   and  r.name = 'parse time cpu'
   union all
   select  'Svc' type,
    name,
    value/100 tot_secs_spent,
    value/100 nonidle_secs_spent
   from  v$sysstat
   where  name = 'recursive cpu usage'
   and  value > 0
   union all
   select  'Svc' type,
    name,
    value/100 tot_secs_spent,
    value/100 nonidle_secs_spent
   from  v$sysstat
   where  name = 'parse time cpu'
   and  value > 0)
order by 5 desc, 4 desc, 3 desc, 2

spool systime
/
spool off

Usage

-- Run the script in SQL*Plus or SQLcl
@systime.sql

-- No parameters required
-- Output is automatically spooled to 'systime.lst'

Required Privileges

• SELECT on V$SYSTEM_EVENT
• SELECT on V$SYSSTAT
• Typically requires SELECT_CATALOG_ROLE or DBA

Sample Output

Svc,
Idle,                                   Total              %      %
Wait Name                               Seconds         Total NonIdle
----- --------------------------------- ----------- --------- -------
Svc   other cpu usage                    12,345.67     45.23   67.89
Wait  db file sequential read             5,678.90     20.81   31.24
Wait  log file parallel write             3,456.78     12.67   19.02
Svc   recursive cpu usage                 2,345.67      8.60   12.91
Svc   parse time cpu                      1,234.56      4.53    6.79
Wait  db file scattered read              1,123.45      4.12    6.18
Idle  SQL*Net message from client       45,678.90     167.45    0.00
Idle  pmon timer                         23,456.78      86.04    0.00
Idle  smon timer                         12,345.67      45.29    0.00

Key Output Columns

• Type - Activity category:
  • Svc - CPU service time (actual processing)
  • Wait - Non-idle wait events
  • Idle - Idle wait events
• Name - Specific event or CPU usage type
• Total Seconds Spent - Cumulative time since instance startup
• % Total - Percentage of total instance uptime
• % NonIdle - Percentage of non-idle time (excludes idle waits)

Understanding the Analysis

Service Time (Svc)

• other cpu usage - General CPU processing
• recursive cpu usage - CPU for recursive SQL
• parse time cpu - CPU spent parsing SQL

Wait Events Classification

• User I/O - Application-driven waits (db file reads)
• System I/O - Database maintenance I/O (log writes)
• Concurrency - Lock and latch waits
• Idle - Sessions waiting for work

Performance Indicators

• High CPU % - CPU-bound workload
• High I/O Wait % - I/O bottleneck
• High Concurrency % - Locking/latching issues

Performance Analysis

CPU vs I/O Bound Analysis

-- Determine if system is CPU or I/O bound
WITH time_analysis AS (
  SELECT
    CASE WHEN type = 'Svc' THEN 'CPU' ELSE 'WAIT' END category,
    SUM(tot_secs_spent) total_time
  FROM systime_results
  GROUP BY CASE WHEN type = 'Svc' THEN 'CPU' ELSE 'WAIT' END
)
SELECT category,
       total_time,
       ROUND(total_time / SUM(total_time) OVER () * 100, 2) pct
FROM time_analysis;

Top Wait Events

-- Focus on top non-idle waits
SELECT name, tot_secs_spent, nonidle_total
FROM systime_results
WHERE type = 'Wait'
AND nonidle_total > 5  -- > 5% of non-idle time
ORDER BY nonidle_total DESC;

Interpretation Guidelines

Healthy System Indicators

• CPU usage 40-70% of non-idle time
• No single wait event > 30% of total
• Idle events dominating total time
• Reasonable I/O wait distribution

Performance Issues

• CPU Starvation - CPU < 20% of non-idle time
• CPU Saturation - CPU > 90% of non-idle time
• I/O Bottleneck - Single I/O event > 50%
• Contention - High latch/lock wait percentages

Common Scenarios

OLTP Workload Pattern

Typical OLTP characteristics:
- High "db file sequential read"
- Moderate CPU usage
- Low "db file scattered read"
- Network waits prominent in total

Data Warehouse Pattern

Typical DW characteristics:
- High "db file scattered read"
- High CPU usage
- Long-running operations
- PX (parallel) waits

Batch Processing Pattern

Typical batch characteristics:
- High CPU usage during processing
- Significant log file waits
- Periodic I/O spikes
- Lower network waits

Troubleshooting Guidance

High I/O Waits

1. Check storage performance
2. Review SQL execution plans
3. Consider index optimization
4. Evaluate partitioning strategies

High CPU Usage

1. Identify CPU-intensive SQL
2. Review execution plans
3. Check for missing indexes
4. Consider query optimization

Contention Issues

1. Identify blocking sessions
2. Review locking patterns
3. Optimize transaction design
4. Consider partitioning

Historical Analysis

Baseline Comparison

-- Create baseline during normal operations
CREATE TABLE systime_baseline AS
SELECT * FROM systime_current_view;

-- Compare current vs baseline
SELECT b.name baseline_name,
       c.name current_name,
       c.nonidle_total - b.nonidle_total change_pct
FROM systime_baseline b, systime_current c
WHERE b.name = c.name
ORDER BY ABS(c.nonidle_total - b.nonidle_total) DESC;

Best Practices

Regular Monitoring

1. Run during different workload periods
2. Establish baselines for comparison
3. Track trends over time
4. Correlate with application activity

Performance Tuning

• Address top wait events first
• Balance CPU and I/O optimization
• Monitor impact of changes
• Document normal patterns

Related Scripts

• AWR System Wait Event Analysis (dhsysev.md) - Historical wait analysis
• High Wait Time Analysis (high_wait_times.md) - Current waits
• System CPU Metrics (sysmetric_cpu.md) - CPU utilization

Technical Notes

• Requires Oracle 8.1.6+ for analytic functions
• Values are cumulative since instance startup
• Time measurements in centiseconds (divided by 100)
• Idle events excluded from non-idle calculations
• Results automatically spooled to file