Re: Interpreting writes from v$sysstat

> 1) Should sampling on v$sesstat suffice to find which process/es is/are writing actively. I suppose yes,

I agree.

> 2) According to the documentation (see above) 'physical write total bytes ' contains
> 'physical write bytes' + RMAN (none active at the moment) + background process activity (not noticeable)
> + exp/imp (none) + expd/impd (none) + recursive (und SYS) SQL activity.
> If it so, which process is consuming the bandwidth of
> 379kb/sec - 26kB/sec = 353kb/sec ? The bandwidth and performance is not the problem, I just want
> to be sure that I am interpreting statistics correctly...

I think you're on the right track. Just check the stats during that period and get the delta. You'll see that only background or RMAN processes have 'physical write total bytes' different from and greater than 'physical write bytes'. You can use this to find that out (run as SYS; hardcoded statistic# is for 10g):

set serverout on
declare
 pwt number;
 pw number;
begin
 for i in (select sid, program from v$session) loop   begin
   with x as (select value from v$sesstat where statistic# = 42 and sid = i.sid),

        y as (select value from v$sesstat where statistic# = 66 and sid = i.sid)    select x.value, y.value into pwt, pw
   from x, y where x.value != y.value;
   dbms_output.put_line(i.sid || '(' || i.program || '): ' || pwt || ' ' || pw);   exception when others then
   null;
  end;
 end loop;
end;
/

> 3) How to interpret the sentence "disk writes from the database application activity" ? Are these
> only writes generated by INSERT/UPDATE/DELETE/TRUNCATE user sql statements ?

User processes rarely write. DML or DDL itself doesn't cause write; DBWn does it on your behalf. User processes write when you use temp segments (sorting, hashing, etc).

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l