| |
 |
|
Oracle reactive monitoring tools
Oracle Database Tips by Donald Burleson |
There is an important
distinction between reactive (problem happening, take a look), and
proactive Oracle tuning (monitor continuously, locate trends and alert
before problem).
While proactive monitoring has show great success for predictive analysis and
anticipation of upcoming problems, there are always cases where Oracle
performance degrades and the DBA must intercede to locate the source of the
bottleneck.
There are several ways to
perform reactive Oracle monitoring:
- Reactive Monitoring
Software -Tools such as Quest Spotlight and Confio Ignite provide great
real-time information about Oracle performance. Downside, they are
expensive and require in-dept knowledge of Oracle internal metrics.
- Reactive Monitoring
Scripts - Many Oracle professionals use the
Ault
Oracle tuning script collection, 670 ready-to-run scripts for monitoring
real-time performance.
The problem with reactive
tuning scripts is that many of the fixed tables (the x$ tables) are accumulated
values, increasing since database startup time. Hence, it's important to
compare values between two periods and take the delta values to see what is
changing.
In the IOUG 2006 paper titled ?BACK
PORTING ADDM, AWR, ASH AND METRICS TO ORACLE 9I & 8I?, John Kanagaraj has an
interesting script that takes two time-series samples to monitor current
performance metrics, allowing the Oracle DBA to see changes in real time.
Note the references to the undocumented fixed x$ tables x$ksusgsta, x$kslei and
x$ksled.
rem Notes
rem Has to be run by
SYS to create the package; Requires STATSPACK$IDLE_EVENT
rem
rem Usage:
rem set
serveroutput on size 1000000 format wrapped
rem set linesize
120
rem set
trimspool on
rem
rem For system
level snapshots
rem execute
xxdba_pack.start_sys_snap;
rem -- let the
database do something
rem execute
xxdba_pack.end_sys_snap;
rem
create or replace
package sys.xxdba_pack as
procedure
start_sys_snap;
procedure
end_sys_snap;
end;
/
create or replace
package body sys.xxdba_pack as
target_sid integer;
cursor sys_stat is
select
indx,ksusdnam, ksusgstv
from x$ksusgsta
where ksusgstv !=
0;
cursor sys_evt is
select
s.indx
indx,
d.kslednam event,
s.ksleswts total_waits,
s.kslestmo total_timeouts,
s.kslestim
time_waited,
s.kslesmxt max_wait
from
x$kslei
s, x$ksled d
where s.indx =
d.indx
and
s.ksleswts != 0
and
d.kslednam not in (
select
event from perfstat.stats$idle_event
)
order by
d.indx
;
type sys_stat_type
is table of sys_stat%rowtype index by binary_integer;
sys_stat_list sys_stat_type;
type sys_evt_type is table of
sys_evt%rowtype index by binary_integer;
sys_evt_list
sys_evt_type;
m_sys_start_time
date;
m_sys_start_flag
char(1);
m_sys_end_time
date;
procedure start_sys_snap
is
begin
m_sys_start_time
:= sysdate;
for r in sys_stat
loop
sys_stat_list(r.indx).ksusgstv :=
r.ksusgstv;
end loop;
for i in sys_evt
loop
sys_evt_list(i.indx).event := i.event;
sys_evt_list(i.indx).total_waits := i.total_waits;
sys_evt_list(i.indx).total_timeouts := i.total_timeouts;
sys_evt_list(i.indx).time_waited := i.time_waited;
sys_evt_list(i.indx).max_wait := i.max_wait;
See IOUG 2006 proceedings
CD for full script.
John Kanagaraj
also shows his script in-action, with the DBA specifying the time between
snapshots, a truly amazing script:
03:46:01
SQL> @snap_sys
Please
enter number of seconds between snapshots : 30
PL/SQL
procedure successfully completed.
PL/SQL
procedure successfully completed.
---------------------------------
System
stats - 01-Dec 03:50:21
Interval:- 31 seconds
---------------------------------
Name
Value
----
-----
logons
cumulative 34
logons
current -1
opened
cursors cumulative 2,516
opened
cursors current -67
user
commits
211
user
rollbacks
4
user
calls
4,318
recursive calls
283,055
recursive cpu usage
7,102
session
logical reads 7,523,432
CPU
used when call started
6,781
CPU
used by this session
8,824
<snip - cut out irrelevant data>
db
block gets
254,833
consistent gets
7,270,588
physical reads
85,521
db
block changes
41,170
consistent
changes 18,416
physical
writes 1,078
physical
writes non checkpoint 1,065
<snip>
redo
entries
20,645
redo
size
4,630,604
redo
wastage
71,012
redo
writes
283
redo
blocks written
9,664
redo write
time 210
data
blocks consistent reads - undo records applied
18,198
no work
- consistent read gets 1,972,837
<snip>
table
scans (short tables) 55,748
table
scans (long tables) 7
table
scan rows gotten 2,356,966
table
scan blocks gotten 143,332
table
fetch by rowid 3,825,394
table
fetch continued row 208,480
<snip>
parse
time cpu 235
parse time
elapsed 271
parse
count (total) 3,420
parse
count (hard) 19
execute
count 102,984
bytes sent
via SQL*Net to client 897,927
bytes
received via SQL*Net from client 844,937
SQL*Net
roundtrips to/from client 4,723
sorts
(memory) 1,990
sorts
(rows) 20,111
session
cursor cache hits 1,435
session
cursor cache count -229
cursor
authentications 2
buffer is
pinned count 11,928,248
buffer is
not pinned count 5,904,527
---------------------------------
Event TotalWaits TotTmOuts TimeWaitd
MaxWait
------ ----------- ----------- -----------
-----------
latch
free 1,345 1,293 2,435 0
control
file parallel write 10 0 9 0
buffer
busy waits 589 0 508 0
log file
parallel write 284 0 209 0
LGWR wait
for redo copy 3 0 0 0
log file
sync 107 0 85 0
db file
sequential read 21,885 0 5,560 0
db file
scattered read 9,926 0 9,532 0
db file
parallel write 58 0 0 0
file
open 173 0 5
0
SQL*Net
more data to client 274 0 5 0
SQL*Net
more data from client 30 0 34 0
SQL*Net
break/reset to client 6 0 0 0
PL/SQL
procedure successfully completed.
For a full collection of Oracle
monitoring scripts, we recommend the Mike Ault Oracle script collection, 681
scripts for under $80. You can
instantly download his scripts here.
 |
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. |
|
and include the URL for the page.
