Database Reference
In-Depth Information
where a.statistic# = b.statistic#
and b.sid = (select sid from v$mystat where rownum=1)
and (a.name like '%ga %'
or a.name like '%direct temp%')
union all
select 'total: ' || a.name, sum(b.value)
from v$statname a, v$sesstat b, v$session c
where a.statistic# = b.statistic#
and (a.name like '%ga %'
or a.name like '%direct temp%')
and b.sid = c.sid
and c.username is not null
group by 'total: ' || a.name
);
The columns in this table we'll be using for the metrics represent:
NAME : the name of the statistic we are gathering ( PGA and UGA information from V$SESSTAT for
the current session, plus all of the memory information for the database instance as well as
temporary tablespace writes).
VALUE : the value of the given metric.
ACTIVE : the number of other sessions doing work in the instance. Before we start, we assume
an “idle” instance; we are the only user session right now, hence the value of zero.
I then ran the following SQL*Plus script (stored in a file named single_load.sql ) in an interactive session.
The table T had been created beforehand with about 70,000 rows in it.
connect eoda/foo
set echo on
declare
l_first_time boolean default true;
begin
for x in ( select * from t order by 1, 2, 3, 4 )
loop
if ( l_first_time )
then
insert into sess_stats
( name, value, active )
select name, value,
(select count(*)
from v$session
where status = 'ACTIVE'
and username is not null)
from
(
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = (select sid from v$mystat where rownum=1)
and (a.name like '%ga %'
or a.name like '%direct temp%')
union all
 
Search WWH ::




Custom Search