Memory Structures - Expert Oracle Database Architecture - page 130

■ Before using this script (or any script, for that matter), make sure you understand what the script does. This
script drops and re-creates a table called SESS_STATS . If your schema already has such a table, you'll probably want to
use a different name!

Note

The other script is called watch_stat.sql , and for this case study, it uses the MERGE SQL statement so we can
initially INSERT the statistic values for a session and then later come back and update them—without needing a
separate INSERT / UPDATE script:

merge into sess_stats
using
(
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = :sid
and (a.name like '%ga %'
or a.name like '%direct temp%')
) curr_stats
on (sess_stats.name = curr_stats.name)
when matched then
update set diff = curr_stats.value - sess_stats.value,
value = curr_stats.value
when not matched then
insert ( name, value, diff )
values
( curr_stats.name, curr_stats.value, null )
/

select name,
case when name like '%ga %'
then round(value/1024,0)
else value
end kbytes_writes,
case when name like '%ga %'
then round(diff /1024,0)
else value
end diff_kbytes_writes
from sess_stats
order by name;

I emphasize the phrase “for this case study” because of the lines in bold—the names of the statistics we're
interested in looking at change from example to example. In this particular case, we're interested in anything with ga
in it ( pga and uga ), or anything with direct temp , which in Oracle 10 g and above will show us the direct reads and
writes against temporary space (how much I/O we did reading and writing to temp).

■ In Oracle9 i , direct I/O to the temporary tablespace was not labeled as such, so we used a WHERE clause that
included ( and a.name like '%ga %' or a.name like '%physical % direct%') in it.

Note