-
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...
-
like this!
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.
<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>
-
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
Sonali
-
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
Sonali
-
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.
Sonali
-
There is possibility that your OS doesn't support. try making less currently what you have before or now.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|