1. select
sid,s.statistic#,value,name from v$sesstat s,v$statname n where sid in (select
sid from v$session where username='MANUMGR') and s.statistic#=n.statistic# and
value>0 order by value desc;
2. SQL>
select SID,EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT,MAX_WAIT
from v$session_event where sid in (select sid from v$session where
username='MANUMGR');
=========================================================================
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
Top Wait Events Since Instance Startup
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
===========select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
Top Wait Events Since Instance Startup
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
Oracle DBA scripts: Active Session History Queries
-- TOP events
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2
-- Top sessions
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc
--Top queries
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC
-- Top segments
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC
-- Most IO
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));
-- Top 10 CPU consumers in last 60 minutes
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
-- Top 10 waiting sessions in last 60 minutes
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
-- Find session detail of top sid by passing sid
select serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid=&sid;
-- Find different sql_ids of queries executed in above top session by-passing sid
select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '60' minute
and session_id=&sid
--Find full sqltext (CLOB) of above sql
select sql_fulltext from v$sql where sql_id='&sql_id'
--find session wait history of above found top sessionselect * from v$session_wait_history where sid=&sid
--find all wait events for above top session
select event, total_waits, time_waited/100/60 time_waited_minutes,
average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc
--session statistics for above particular top session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2
-- Top sessions
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc
--Top queries
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC
-- Top segments
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC
-- Most IO
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));
-- Top 10 CPU consumers in last 60 minutes
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
-- Top 10 waiting sessions in last 60 minutes
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
-- Find session detail of top sid by passing sid
select serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid=&sid;
-- Find different sql_ids of queries executed in above top session by-passing sid
select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '60' minute
and session_id=&sid
--Find full sqltext (CLOB) of above sql
select sql_fulltext from v$sql where sql_id='&sql_id'
--find session wait history of above found top sessionselect * from v$session_wait_history where sid=&sid
--find all wait events for above top session
select event, total_waits, time_waited/100/60 time_waited_minutes,
average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc
--session statistics for above particular top session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc
|
Session - Show sessions using
resources
|
|
-- Sessionresources.sql
-- Sesssion - Show sessions using resources
-- Session stats
-------------------------------------------------------------------------------------------------------
-- v$sess_io: select session io stats
select osuser,username,b.*,a.program
from v$session a, v$sess_io b
where a.sid = b.sid;
-- show worse phys reads sessions
select osuser,substr(username,1,15),b.*,a.program
from v$session a, v$sess_io b
where a.sid = b.sid
and b.physical_reads > 1000
order by physical_reads desc;
-- show worse phys reads sessions
select osuser,substr(username,1,15),b.*,a.program
from v$session a, v$sess_io b
where a.sid = b.sid
and b.physical_reads > 1000
order by physical_reads desc;
-- SQL statements that consumed the most
resources in terms of CPU and/or memory
select substr(osuser,1,20) as osuser,
rpad(substr(username,1,12),12,'
') as username,
substr(terminal,1,25)
as terminal,
substr(s.machine,1,30) as machine,
substr(name,1,22),
value
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
-- and s.osuser = 'user123'
-- and n.name = 'session pga memory'
and t.value > &try_more_than_50000
order by value DESC
/
--
Select a.username, b.Executions,
((b.Disk_Reads + b.Buffer_Gets)
/ Decode(b.Executions, 0, 1, b.Executions)) Avg_Buffers,
b.Disk_Reads, b.Buffer_Gets,
b.first_load_time Load_Time, b.SQL_Text
From dba_users a, V$SQLArea b
Where a.user_id = b.parsing_user_id
And((Disk_Reads + Buffer_Gets) /
Decode(Executions, 0, 1, Executions)) > 10000
Or (Executions > 2000
And ((Disk_Reads +
Buffer_Gets) / Decode(Executions, 0, 1, Executions)) > 500)
Order By ((Disk_Reads + Buffer_Gets) /
Decode(Executions, 0, 1, Executions)) Desc
;
-- Display session stats for a specific sid
select substr(osuser,1,20) as osuser,
rpad(substr(username,1,12),12,' ') as username,
substr(terminal,1,25)
as terminal,
substr(s.machine,1,30) as machine,
substr(name,1,40),
value
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.sid = 9
and s.type = 'USER'
-- and s.osuser = 'user123'
-- and n.name = 'session pga memory'
-- and t.value > &try_more_than_50000
and n.name like '%cursor%'
order by value DESC
/
You can also pick out the user that has the most
disk I/Os and trace what statements they are running using the command:
SELECT ses.sid, ses.serial#, ses.osuser,
ses.process
FROM v$session ses, v$sess_io sio
WHERE ses.sid = sio.sid
AND nvl(ses.username,'SYS')
not in ('SYS', 'SYSTEM')
AND sio.physical_reads =
(SELECT MAX(physical_reads)
FROM v$session ses2, v$sess_io sio2
WHERE ses2.sid = sio2.sid
AND ses2.username
NOT
IN ('SYSTEM', 'SYS'));
SID
SERIAL#
OSUSER PROCESS
----------------------------------------------------------------
41
46
corriganp 12818
List all users and disk i/o:
set lines 200
SELECT ses.sid, ses.serial#,
ses.osuser,substr(ses.username,1,15) as username,
sio.physical_reads, sio.block_gets, ses.process
FROM v$session ses, v$sess_io sio
WHERE
ses.sid = sio.sid
AND nvl(ses.username,'SYS')
not in ('SYS', 'SYSTEM','DBSNMP')
ORDER
BY sio.physical_reads desc
;
SID
SERIAL# OSUSER
USERNAME PHYSICAL_READS BLOCK_GETS
PROCESS
--------- --------- ---------------
--------------- -------------- ---------- ---------
10 21
bergeroner
ADMIN
10185 13799 429453625
14 121
willhaukdo
ADMIN
86 1394 429411523
9
47 alamama
ADMIN
10 44 429486258
16 46
bergeroner
ADMIN
6 35 429460271
18 44
taillefede
ADMIN
6 69 429080223
19 63 novickmi
ADMIN
0 42 429453523
|
No comments:
Post a Comment