shows the users who are consuming more machine

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

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

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

Note: Requires timed_statistics be set to TRUE.

-------------------------------------------------------
-- 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;
/

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

Thats one of the init.ora parameter.

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

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.

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.

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

Hi,

Check your init.ora file to see if it has any include files. Sometimes you define parameters in the include file and when ORACLE starts it will take the first occurance of the parameter and take that value.

Baliga

Are you sure you are modifying the correct init.ora file. For example, it is pretty common on Unix systems to create a link from $ORACLE_HOME/dbs/initSID.ora to the $ORACLE_BASE/admin/SID/pfile/initSID.ora file. However, I was once at a site where they kept the source in $ORACLE_BASE/admin/SID/pfile but then copied the file to $ORACLE_HOME/dbs. When I instinctively modified the file in admin/SID/pfile, I assumed that the database would read via the link. However, since they were two seperate files, the old values were still read in.

Thanks everyone.
Set timed_statistics true worked when I did
alter system
set timed_statistics...
But not when I changed it in init.ora file.
It did show a change in init.ora but when I did a query it said that timed statistics is false.

Change in number of processes worked only after I restarted my database even when the change was shown in init.ora file,
it wasn't showing up in v$parameter table.
How I tested it:
1st I wanted to increase the number, but when it did not show up in v$parameter I was doubtful so I changed it back to a vary small number 15. When nuumber of processes crossed that number, it did not blow up !

I was useing correct init.ora file (thanks for the tip)

I should have told you TIMED_STATISTICS is a dynamic init.ora paramter. SORRY !

Timed statistics enables and disables the collection of timed statistics, such as CPU and elapsed times, by the SQL trace facility, as well as the collection of various statistics in the dynamic performance tables. The default value of FALSE disables timing. A value of TRUE enables timing. Enabling timing causes extra timing calls for low-level operations. This is a session parameter.