shows the users who are consuming more machine

[hhbrazil]

somebody can pass me some script that it shows the users who are consuming more machine, and its percentage of consumption.

grateful
07:20 am

[aracnid]

Hi Brazil
...check the v$sess_io and join by sid with the v$session...

[hhbrazil]

set linesize 130 pages 76
col username format a20
col consistent_chages format 99999999999
col process format a20

spool c:\logs\verify.log
select to_char(sysdate,'dd-mon-rrrr - hh24:mi:ss') time_start from dual;


select a.username usuario, a.osuser,a.terminal,
b.block_gets, b.physical_reads,b.block_changes,b.consistent_changes,
((b.block_gets+b.physical_reads+b.block_changes+b.consistent_changes)/4) process
from
v$session a, v$sess_io b
where a.sid=b.sid
order by 8 desc;

spool off

[sreddy]

Note: Requires timed_statistics be set to TRUE.
<font face=courier>
-------------------------------------------------------
-- pl/sql CPU analysis
-- the follow script will measure CPU usage per session
-------------------------------------------------------
set echo off
-------------------------------------------------------
-- CPU usage by session
-------------------------------------------------------
SET SERVEROUTPUT ON;
DECLARE
v_str varchar2(30);
v_Out VARCHAR2(250);

cursor c1 is
select ss.username || '(' || se.sid || ')' user_process, value
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and ss.username is not null
order by user_process,value desc;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

DBMS_OUTPUT.PUT_LINE(' ');
select 'Snapshot Date: ' || to_char(sysdate,'MM/DD/YY HH:MI:SS')
into v_out from dual;
select name into v_str from v$database;
DBMS_OUTPUT.PUT_LINE( v_out || ' Database: ' || v_str );
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('CPU used by this session');
DBMS_OUTPUT.PUT_LINE('------------------------');

for c1_rec in c1 loop
DBMS_OUTPUT.PUT_LINE( rpad(c1_rec.user_process,15) ||
lpad(c1_rec.value,9));
end loop;
DBMS_OUTPUT.PUT_LINE(' ');

select 'Note: Requires timed_statistics be set to TRUE.' into
v_out
from dual
where 'FALSE' in (select value from v$parameter where
name='timed_statistics');
DBMS_OUTPUT.PUT_LINE( v_out );

exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE(' ');

end;
/

</font>

[sonaliak]

How do I set timed statistics to true ?
What happens when I do that ?
Do I have to set it back to false ( which is default I suppose) ?

Thanks

[sreddy]

Thats one of the init.ora parameter.

[sonaliak]

Thanks sreddy,
I have one more similar question about init.ora file.
I increased number of processes from 100 to 200 in init.ora file and saved that file.
I was expecting that when I restart my oracle service next time it will be set to new value. But looks like it has gone back to 100.
Why, do I have to do something else to set it ?
Now that I have set timed_statistics to true, will it be effective right away, will that decrease performance ?

thanks again

[sreddy]

processes should be showing 200 if you have modified and I don't think there is going to be performance problems to gather statistics.

It allows you to get statistics when you wanted betwwen a particular time interval, if you have that parameter set to true.

[sonaliak]

I tried it again, it now shows the change in init.ora.
Right now it does show the change in init.ora file but when I do select * from v$parameter it shows old values.

[sreddy]

There is possibility that your OS doesn't support. try making less currently what you have before or now.