Sequential Read Performance Analysis (random_io.sql)
What This Script Does
Section titled “What This Script Does”This script analyzes database file sequential read performance bottlenecks from Oracle Enterprise Manager, providing an hourly breakdown of I/O wait percentages. It queries OEM’s historical metrics to show when sequential read operations are causing performance issues, helping identify I/O patterns and peak contention periods throughout the day.
The script specifically focuses on the ‘db_file_sequential_read_pct’ metric from the ‘wait_bottlenecks’ metric group, which indicates what percentage of time is spent waiting for single-block reads.
The Script
Section titled “The Script”rem random_io.sql - displays maximum CPU utilization by hourttitle 'MAX CPU Utilization Report'set linesize 250set pagesize 200remcol host_name format a20col target_name format a30col column_label format a30col dt format a10 heading 'Date'col day format a4 heading 'Day'col total format 999 heading 'Total'col h0 format 999 heading '00'col h1 format 999 heading '01'col h2 format 999 heading '02'col h3 format 999 heading '03'col h4 format 999 heading '04'col h5 format 999 heading '05'col h6 format 999 heading '06'col h7 format 999 heading '07'col h8 format 999 heading '08'col h9 format 999 heading '09'col h10 format 999 heading '10'col h11 format 999 heading '11'col h12 format 999 heading '12'col h13 format 999 heading '13'col h14 format 999 heading '14'col h15 format 999 heading '15'col h16 format 999 heading '16'col h17 format 999 heading '17'col h18 format 999 heading '18'col h19 format 999 heading '19'col h20 format 999 heading '20'col h21 format 999 heading '21'col h22 format 999 heading '22'col h23 format 999 heading '23'
break on report on Dt skip 1compute sum label SUM of h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23 on report Dt
SELECT trunc(rollup_timestamp) Dt , to_char(rollup_timestamp, 'Dy') Day , host_name , target_name , SUM(decode(to_char(rollup_timestamp, 'hh24'),'00',value_maximum,0)) h0 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'01',value_maximum,0)) h1 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'02',value_maximum,0)) h2 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'03',value_maximum,0)) h3 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'04',value_maximum,0)) h4 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'05',value_maximum,0)) h5 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'06',value_maximum,0)) h6 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'07',value_maximum,0)) h7 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'08',value_maximum,0)) h8 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'09',value_maximum,0)) h9 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'10',value_maximum,0)) h10 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'11',value_maximum,0)) h11 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'12',value_maximum,0)) h12 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'13',value_maximum,0)) h13 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'14',value_maximum,0)) h14 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'15',value_maximum,0)) h15 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'16',value_maximum,0)) h16 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'17',value_maximum,0)) h17 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'18',value_maximum,0)) h18 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'19',value_maximum,0)) h19 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'20',value_maximum,0)) h20 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'21',value_maximum,0)) h21 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'22',value_maximum,0)) h22 , SUM(decode(to_char(rollup_timestamp, 'hh24'),'23',value_maximum,0)) h23 FROM mgmt_metrics_1hour cm JOIN mgmt_metrics m ON m.metric_guid = cm.metric_guid JOIN mgmt_targets t ON t.target_guid = cm.target_guid AND t.type_meta_ver = m.type_meta_ver AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ') where metric_name = 'wait_bottlenecks' and metric_column = 'db_file_sequential_read_pct' and nvl(t.host_name, 0) like nvl('&host_name', '%') and nvl(t.target_name, 0) like nvl('&target_name', '%') and ROLLUP_TIMESTAMP > sysdate - nvl('&nbr_of_days','2') and ROLLUP_TIMESTAMP < sysdategroup by trunc(rollup_timestamp), to_char(rollup_timestamp, 'Dy'), target_name, host_nameorder by 1 desc, 3 desc, 4 desc;
clear breaksclear computesParameters
Section titled “Parameters”The script prompts for:
- host_name: Hostname to filter results (optional, defaults to all hosts)
- target_name: Target database name to filter results (optional, defaults to all targets)
- nbr_of_days: Number of days back to analyze (optional, defaults to 2 days)
-- Basic usage (will prompt for parameters)@random_io.sql
-- Example values when prompted:-- host_name: db01.company.com-- target_name: PRODDB-- nbr_of_days: 3Sample Output
Section titled “Sample Output” MAX CPU Utilization Report
Date Day HOST_NAME TARGET_NAME 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23---------- ---- -------------------- -------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----2025-01-05 Sun db01.company.com PRODDB 12 8 6 5 4 6 15 25 35 45 50 55 48 52 47 42 38 32 28 22 18 16 14 132025-01-04 Sat db01.company.com PRODDB 10 7 5 4 3 5 12 22 32 42 48 52 45 49 44 39 35 29 25 19 15 13 11 10---------- ---- -------------------- -------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----SUM 22 15 11 9 7 11 27 47 67 87 98 107 93 101 91 81 73 61 53 41 33 29 25 23