From STATSPACK, you can query the stats$system_event table to see the amount of time Oracle has waited
for network packets. As you recall, there are several system events
that can show us network activity:
SQL> select distinct event from
stats$system_event
2 where event like 'SQL%';
EVENT
----------------------------------------------------------------
SQL*Net break/reset to client
SQL*Net message from client
SQL*Net message from dblink
SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data from client
SQL*Net more data to client
From this STATSPACK table, we can select all
of the significant events, the number of waits, and the average wait
time in seconds. Remember, most networks such as TCP/IP send an
acknowledgement when a packet has been received, as shown in Figure
5-5.
The rpt_event.sql script below can be
run to see all Oracle system events that were captured in the
STATSPACK stats$system_event table.
Rpt_event.sql
set pages 999;
column mydate heading 'Yr. Mo Dy Hr'
format a13;
column event
format a30;
column waits
format 999,999;
column secs_waited
format 999,999,999;
column avg_wait_secs
format 99,999;
select
to_char(snap_time,'yyyy-mm-dd
HH24') mydate,
e.event,
e.total_waits - nvl(b.total_waits,0)
waits,
((e.time_waited -
nvl(b.time_waited,0))/100) /
nvl((e.total_waits -
nvl(b.total_waits,0)),.01) avg_wait_secs
from
stats$system_event b,
stats$system_event e,
stats$snapshot sn
where
e.snap_id = sn.snap_id
and
b.snap_id = e.snap_id-1
and
b.event = e.event
and
e.event like 'SQL*Net%'
and
e.total_waits - b.total_waits > 100
and
e.time_waited - b.time_waited > 100
;
Here is a sample of the output from
this report, showing the events and the wait times for each event.
This is a great report for showing specific times when the network
is overloaded with packet traffic.
Yr. Mo Dy Hr
EVENT WAITS AVG_WAIT_SECS
------------- ------------------------------
-------- -------------
2000-09-20 15 SQL*Net message from
client 1,277 1
2000-09-20 16 SQL*Net message from
client 133 64
2000-09-20 18 SQL*Net message from
client 325 1
2000-09-20 19 SQL*Net message from
client 410 0
2000-09-20 20 SQL*Net message from
client 438 22
2000-09-20 22 SQL*Net message from
client 306 8
2000-09-21 10 SQL*Net message from
client 253 4
2000-09-21 12 SQL*Net message from
client 208 0
2000-09-21 13 SQL*Net message from
client 230 6
2000-09-21 14 SQL*Net message from
client 311 6
2000-09-21 17 SQL*Net message from
client 269 21
2000-09-21 18 SQL*Net message from
client 222 29
2000-09-21 19 SQL*Net message from
client 362 22
2000-09-22 11 SQL*Net message from
client 111 32
2000-09-22 15 SQL*Net message from
client 353 10
2000-09-22 20 SQL*Net message from
client 184 18
2000-09-22 22 SQL*Net message from
client 642 104
2000-09-23 11 SQL*Net message from
client 125 22
2000-09-23 12 SQL*Net message from
client 329 11
2000-09-23 13 SQL*Net message from
client 329 172
2000-09-23 14 SQL*Net message from
client 310 4
2000-09-23 15 SQL*Net message from
client 501 17
2000-09-23 16 SQL*Net message from client
197 49
2000-09-23 19 SQL*Net message from
client 214 20
2000-09-24 16 SQL*Net message from
client 343 251
These STATSPACK reports can often give
the DBA an idea about potential network problems because Oracle
captures the number of seconds that have been waited for each
distributed event. Of course, Oracle can identify a latency problem,
but we need to go out to the network to find the exact cause of the
network problem.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
