Database Reference
In-Depth Information
cgs 294.73 161.94
osmcache 0 0
streams .03 4.02
diag 28846.1 103344.72
ksv 0 0
ping 5050.59 5050.58
internal 0 0
Of course, the output in Listing 12-3 shows the traffic breakdown from the start of the PROD1 instance. So,
output from x$ksxpclient can be misleading if we need to measure interconnect network traffic for a constrained time
window. AWR captures the data from x$ksxpclient into AWR tables and externalizes in
dba_hist_ic_client_stats
view. Listing 12-4 queries dba_hist_ic_client_stats and prints the network traffic for the PX workload. By adding
value from all three instances for bytes_received (or bytes_sent), we can calculate total bytes transmitted for the PX
workload. In this example, on 11/11/12 between 4:00 and 4:30 p.m., this database generated 82MB of network traffic
for the PX workload.
Listing 12-4.
dba_hist_ic_client_stats
SELECT TO_CHAR(snaps.begin_interval_time, 'DD-MON-YYYY HH24:MI') begin_time,
snaps.instance_number,
hist.name,
TRUNC((hist.bytes_received - lag (hist.bytes_received) OVER (
PARTITION BY hist.name, startup_time, hist.instance_number
ORDER BY begin_interval_time, startup_time,
hist.instance_number, hist.name))/1048576,2) Mbyte_rcvd,
TRUNC((hist.bytes_sent - lag (hist.bytes_sent) OVER (
PARTITION BY hist.name, startup_time, hist.instance_number
ORDER BY begin_interval_time, startup_time,
hist.instance_number, hist.name))/1048576,2) Mbyte_sent
FROM dba_hist_ic_client_stats hist,
DBA_HIST_SNAPSHOT snaps
WHERE snaps.snap_id = hist.snap_id
AND snaps.instance_number = hist.instance_number
AND snaps.begin_interval_time > sysdate-1
AND hist.name ='ipq'
ORDER BY snaps.snap_id, instance_number;
BEGIN_TIME Inst NAME MBYTE_RCVD MBYTE_SENT
----------------------- ---- --------- ---------- ----------
...
11-NOV-2012 16:00 1 ipq 31.23 33.7
2 ipq 28.31 35.01
3 ipq 42.9 33.72
11-NOV-2012 16:30 1 ipq 82.19 44.41
2 ipq 32.61 66.86
3 ipq 63.57 67.06
11-NOV-2012 17:00 1 ipq 32.89 32.33
2 ipq 14.28 36.99
3 ipq 55.45 33.32
This metric is also printed in an AWR report in the
interconnect throughput by client
section. You can see
that interconnect traffic for PX workload is very minimal in this database.
