Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the data in this view is cumulative, so you don’t what happened during a single time frame. To print the differences over time, I wrote what is below to be run as a SQL*Plus script.
set serveroutput on
set feedback off verify off
variable v1 number
exec :v1 := &sid
variable instance_id number
exec :instance_id := &instance_id
variable sleep_time number
exec :sleep_time := &sleep_time
declare
type t_vals1 is table of varchar2(100);
type t_vals2 is table of number;
type t_vals3 is table of varchar2(100);
type t_vals4 is table of number;
vals1 t_vals1 := t_vals1();
vals2 t_vals2 := t_vals2();
vals3 t_vals3 := t_vals3();
vals4 t_vals4 := t_vals4();
i number := 0;
j number := 0;
k number := 0;
l number := 0;
begin
for cur in (select name,value
from gv$sesstat s, v$statname n
where sid = :v1
and s.statistic# = n.statistic#
and inst_id = :instance_id
order by name) loop
vals1.extend(1);
vals2.extend(1);
i := i + 1;
vals1(i) := cur.name;
vals2(i) := cur.value;
end loop;
dbms_lock.sleep(:sleep_time);
for cur in (select name,value
from gv$sesstat s, v$statname n
where sid = :v1
and s.statistic# = n.statistic#
and inst_id = :instance_id
order by name) loop
vals3.extend(1);
vals4.extend(1);
j := j + 1;
vals3(j) := cur.name;
vals4(j) := cur.value;
end loop;
k := i;
l := 0;
dbms_output.put_line('========================================================');
while l < k loop
l := l + 1;
if vals1(l) = vals3(l) then
if vals4(l) > vals2(l) then
dbms_output.put_line(vals1(l) || ' ' || (vals4(l) - vals2(l)));
end if;
end if;
end loop;
dbms_output.put_line('========================================================');
end;
/
exit
All this script does is take three arguments:
The SID in which you are interested
The instance on which the session is running
The number of seconds you want to sleep between checks.
It queries gv$sesstat for the values for this SID, and then stores them in a dynamically defined type. After sleeping for the number fo seconds specified, it runs the same query again and stores the values in a second collection.
Lastly, it compares them and prints only those that have changed in the monitoring window.
I frequently run this with a time gap of as little as ten seconds. This prints output such as below:
14:28:55 oracle@oh1xpwcdb01 ~ >sp @diff_sid_stat
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 10 14:29:04 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enter value for sid: 2836
Enter value for instance_id: 1
Enter value for sleep_time: 10
========================================================
CPU used by this session 53
CPU used when call started 53
DB time 154
RowCR - row contention 5
RowCR attempts 85
RowCR hits 80
SQL*Net roundtrips to/from client 533
active txn count during cleanout 1
buffer is not pinned count 2414
buffer is pinned count 1264
bytes received via SQL*Net from client 207827
bytes sent via SQL*Net to client 180437
calls to get snapshot scn: kcmgss 5775
calls to kcmgas 83
calls to kcmgcs 1
cleanout - number of ktugct calls 7
cleanouts only - consistent read gets 6
cluster wait time 10
commit cleanout failures: block lost 2
commit cleanout failures: callback failure 33
commit cleanouts 342
commit cleanouts successfully completed 307
commit txn count during cleanout 7
concurrency wait time 6
consistent changes 2914
consistent gets 7214
consistent gets - examination 2748
consistent gets direct 31
consistent gets from cache 7183
db block changes 3934
db block gets 9774
db block gets direct 62
db block gets from cache 9712
deferred (CURRENT) block cleanout applications 102
dirty buffers inspected 75
enqueue releases 158
enqueue requests 158
execute count 541
free buffer inspected 671
free buffer requested 685
gc CPU used by this session 1
gc cr block receive time 1
gc cr blocks received 5
gc current block receive time 7
gc current blocks received 42
gc local grants 34
gc remote grants 72
gcs messages sent 151
ges messages sent 37
global enqueue get time 5
global enqueue gets sync 187
global enqueue releases 187
heap block compress 9
hot buffers moved to head of LRU 280
immediate (CR) block cleanout applications 6
immediate (CURRENT) block cleanout applications 1
index fetch by key 306
index scans kdiixs1 758
lob reads 496
lob writes 1271
lob writes unaligned 1271
messages sent 64
no work - consistent read gets 2799
opened cursors cumulative 225
parse count (total) 225
parse time cpu 1
physical read IO requests 122
physical read bytes 1318912
physical read total IO requests 122
physical read total bytes 1318912
physical reads 122
physical reads cache 91
physical reads direct 31
physical reads direct (lob) 31
physical reads direct temporary tablespace 31
physical write IO requests 62
physical write bytes 507904
physical write total IO requests 62
physical write total bytes 507904
physical writes 62
physical writes direct 62
physical writes direct temporary tablespace 62
physical writes non checkpoint 62
prefetched blocks aged out before use 5
recursive calls 971
recursive cpu usage 36
redo entries 599
redo ordering marks 20
redo size 357572
redo synch time 12
redo synch writes 62
rows fetched via callback 212
session cursor cache count 1
session cursor cache hits 131
session logical reads 16988
session pga memory 65536
session uga memory 65408
session uga memory max 65408
shared hash latch upgrades - no wait 104
sorts (memory) 31
sorts (rows) 567
table fetch by rowid 1571
undo change vector size 148428
user I/O wait time 73
user calls 663
user commits 62
workarea executions - optimal 124
========================================================
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
14:29:21 oracle@oh1xpwcdb01 ~ >