 |
|
Checking Server Trends by Hour
Oracle Tips by Burleson Consulting
|
Returning to our basic script, we can easily modify it
to capture CPU usage, averaged by hour of the day. Note that all we
need to do is to change the date format mask from ’yyyy-mm-dd’ to
’HH24.’
(3)rpt_vmstat_hr.sql
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column my_date heading 'date' format a20
column c2 heading runq format 999
column c3 heading pg_in format 999
column c4 heading pg_ot format 999
column c5 heading usr format 999
column c6 heading sys format 999
column c7 heading idl format 999
column c8 heading wt format 999
select
to_char(start_date,'HH24') my_date,
-- avg(runque_waits) c2
-- avg(page_in) c3
-- avg(page_out) c4,
avg(user_cpu + system_cpu) c5
-- avg(system_cpu) c6,
-- avg(idle_cpu) c7,
-- avg(wait_cpu) c8
from
stats$vmstat
group BY
to_char(start_date,'HH24')
order by
to_char(start_date,'HH24')
;
Now, when we run this script, we get the average CPU
usage data:
date usr
-------------------- ----
00 15
01 13
02 13
03 16
04 15
05 14
06 13
07 14
08 17
09 20
10 21
11 21
12 31
13 34
14 37
15 34
16 21
17 21
18 22
19 21
20 19
21 18
22 16
23 15
When we plot this data,
we clearly see the server gets very busy each day at noon and
continues until 3:00 p.m.
(see Figure 15-11).
Figure 15-124: A plot of CPU use by hour of the
day
Next, let's modify the script and see trends by
day of the week.
Plotting Server Statistics by Day of the Week
Next, we change the dat string from ’HH24’ to ’day’.
This will aggregate the statistics by day of the week. When we run the
script, we see the average CPU by day of the week.
date usr
-------------------- ----
friday 18
monday 53
saturday 14
sunday 14
thursday 20
tuesday 55
wednesday 20
When we plot this data,
we see that Monday and Tuesday are the busiest processing days of the
week (see Figure 15-12).
Figure 15-125: A plot of CPU % by day of week
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
