set pagesize 200
col EVENT format a40
SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*),
SUM (time_waited) time_waited
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state ORDER BY time_waited DESC;
EVENT SESSION COUNT(*) TIME_WAITED
---------------------------------------- ------- ---------- -----------
reliable message WAITING 20 18927347
gcs log flush sync WAITING 1 29291
control file sequential read WAITING 5 18952
control file parallel write WAITING 2 10017
log file sync WAITING 1 7060
log file parallel write WAITING 1 6561
gc cr block 2-way WAITING 2 1340
ON CPU 122 0
null event WAITING 1 0
9 rows selected.
Top CPU consuming Session in last 5 minutes?
Select session_id,
count(*)
from v$active_session_history
where session_state= 'ON CPU' and
SAMPLE_TIME > sysdate - (5/(24*60))
group by session_id
order by count(*) desc;
SESSION_ID COUNT(*)
---------- ----------
2670 12
2755 1
2701 1
Top Waiting Session in last 5 minutes
Select session_id,
count(*)
from v$active_session_history
where session_state='WAITING' and
SAMPLE_TIME > SYSDATE - (5/(24*60))
group by session_id
order by count(*) desc;
SESSION_ID COUNT(*)
---------- ----------
2738 1
Investigate one session running that module
SQL> select distinct module from v$active_session_history ;
MODULE
------------------------------------------------
sqlplus@********** (TNS V1-V3)
backup incr datafile
backup full datafile
DBMS_SCHEDULER
w4wp.exe
SEVERITY EVALUATION
racgimon@********** (TNS V1-V3)
restore full datafile
backup archivelog
Oracle Enterprise Manager.rollup
rman@********** (TNS V1-V3)
MMON_SLAVE
racgimon@****** (TNS V1-V3)
15 rows selected.
col EVENT format a40
SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*) counter ,
SUM (time_waited) time_waited
FROM v$active_session_history
WHERE 1=1
-- and module = 'w4wp.exe'
AND sample_time > SYSDATE - 1/24
--AND session_id = 276
GROUP BY DECODE (session_state, 'WAITING', event, NULL), session_state
ORDER BY time_waited DESC;
EVENT SESSION COUNTER TIME_WAITED
---------------------------------------- ------- ---------- -----------
reliable message WAITING 20 18927347
gcs log flush sync WAITING 1 29291
control file sequential read WAITING 5 18952
control file parallel write WAITING 2 10017
log file sync WAITING 1 7060
log file parallel write WAITING 1 6561
gc cr block 2-way WAITING 2 1340
ON CPU 124 0
null event WAITING 1 0
9 rows selected.
Join with v$sqlarea and include sql_stms for that session
col sqls format a50
col EVENT_NAME format a25
SELECT substr(b.sql_text,1,50) sqls, b.sql_id, DECODE (a.session_state, 'WAITING', a.event, NULL) "EVENT_NAME", a.session_state, COUNT(*) counter ,
SUM (a.time_waited) time_waited
FROM v$active_session_history a , V$SQLAREA b
WHERE a.sample_time > SYSDATE - 1/24
AND a.SQL_ID = b.SQL_ID
-- AND a.module = ' '
-- AND a.session_id = 276
GROUP BY b.sql_text, b.sql_id,
DECODE (a.session_state, 'WAITING', a.event, NULL), a.session_state
ORDER BY time_waited DESC;
SQLS SQL_ID EVENT_NAME SESSION COUNTER TIME_WAITED
-------------------------------------------------- ------------- ------------------------- ------- ---------- -----------
SELECT COUNT(*) FROM MGMT_METRIC_DEPENDENCY_DETAIL dj1q81rxhr7w3 ON CPU 15 0
SELECT overtime3.*, LU1.title_txt as ratetitle, LU dp14mt0pcd9qz ON CPU 1 0
SELECT DECODE (session_state, :"SYS_B_0", event, N a7srcjcxc5ndx ON CPU 2 0
SELECT substr(b.sql_text,:"SYS_B_0",:"SYS_B_1") s apzsyc42mzacp ON CPU 1 0
SELECT b.sql_text, DECODE (a.session_state, :"SYS dwjwa5969hfjc ON CPU 1 0
select decode(nvl(to_char(s.sid),-:"SYS_B_00"),-:" dzhnbx1xydp8x ON CPU 5 0
select ash.session_id, ash.session_seria fuusjf8tkp10y ON CPU 3 0
SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILI 661tvbvnac891 ON CPU 10 0
SELECT OP.*******_NUM FROM *******_POSITION OP WHE gxmzbfrhsxt65 ON CPU 24 0
Select session_id, co 712dz9xh6rx44 ON CPU 1 0
SELECT substr(b.sql_text,:"SYS_B_0",:"SYS_B_1"), 02fw2pw2718yk ON CPU 1 0
SELECT DECODE (session_state, :"SYS_B_0", event, N b218g0qq3hwm0 ON CPU 1 0
select ash.SQL_ID , sum(decode(ash.session_state,: 41asd2tscsmcd ON CPU 1 0
select decode(nvl(to_char(s.sid),-:"SYS_B_00"),-: fps86p3ur4qu2 ON CPU 3 0
DECLARE job BINARY_INTEGER := :job; next_date DATE 6gvch1xu9ca3g ON CPU 7 0
select ash.session_id, ash.session_seria 5g2qccc23aun2 ON CPU 9 0
SELECT DECODE (session_state, :"SYS_B_0", event, N 42cgt5qz22z0a ON CPU 1 0
17 rows selected.
What SQL is currently using the most resources?
col sqls format a50
col USERNAME format a20
SELECT active_session_history.user_id,dba_users.username, substr(sqlarea.sql_text,1,50) sqls ,
-- sqlarea.sql_text,
active_session_history.SQL_ID,
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username,active_session_history.SQL_ID
ORDER BY 4;
USER_ID USERNAME SQLS SQL_ID TOTAL_WAIT_TIME
---------- -------------------- -------------------------------------------------- ------------- ---------------
0 SYS SELECT substr(b.sql_text,:"SYS_B_0",:"SYS_B_1"), 02fw2pw2718yk 3
29 SYSMAN SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILI 661tvbvnac891 596
29 SYSMAN DECLARE job BINARY_INTEGER := :job; next_date DATE 6gvch1xu9ca3g 264
0 SYS SELECT substr(b.sql_text,:"SYS_B_0",:"SYS_B_1") s apzsyc42mzacp 3
0 SYS SELECT DECODE (session_state, :"SYS_B_0", event, N b218g0qq3hwm0 1
29 SYSMAN SELECT COUNT(*) FROM MGMT_METRIC_DEPENDENCY_DETAIL dj1q81rxhr7w3 812
6548 ***** SELECT overtime3.*, LU1.title_txt as ratetitle, LU dp14mt0pcd9qz 27
0 SYS SELECT b.sql_text, DECODE (a.session_state, :"SYS dwjwa5969hfjc 4
6548 CAR SELECT OP.*******_NUM FROM *******_POSITION OP WHE gxmzbfrhsxt65 8428
9 rows selected.
What object is currently causing the highest resource waits?
set linesize 150
col OBJECT_NAME format a28
col EVENT format a20
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 - 60/2880 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 ttl_wait_time DESC;
OBJECT_NAME OBJECT_TYPE EVENT TTL_WAIT_TIME
---------------------------- ------------------- -------------------- -------------
SCHEDULER$_GLOBAL_ATTRIBUTE TABLE reliable message 7812784
SCHEDULER$_GLOBAL_ATTRIBUTE TABLE 5878998
SCHEDULER$_JOB TABLE 4892625
SCHEDULER$_JOB TABLE enq: JS - queue lock 4212
SCHEDULER$_JOB TABLE gc cr block 2-way 729
I_LINK1 INDEX 6
I_PROFILE INDEX 4
7 rows selected.
Top SQL by CPU usage, wait time and IO time
select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
4gd6b1r53yt88 0 1284 0 1284
8szmwam7fysa3 0 16 81 97
fp6yq2gqh1x55 0 10 0 10
8p8yrmpfknn3f 0 2 8 10
apaj3xcy1arks 0 5 0 5
2wxbajru4agjp 0 5 0 5
b6usrg82hwsa3 0 5 0 5
89qyn4bbt03jq 0 0 3 3
4y1y43113gv8f 0 2 1 3
4ph7h2jrsquwh 0 2 0 2
33y4yxzq1ms0d 0 2 0 2
Top SESSION by CPU usage, wait time and IO time !!!!!!!!!!!
col PROGRAM format a30
select
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc
SESSION_ID SESSION_SERIAL# USER_ID PROGRAM CPU WAITING IO TOTAL
---------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
2676 28638 0 rman@********** (TNS V1-V3) 0 85895 0 85895
2670 8769 0 rman@********** (TNS V1-V3) 0 18599 0 18599
2738 1 0 oracle@********** (MMON) 0 1284 0 1284
2676 47346 0 rman@********** (TNS V1-V3) 0 834 0 834
2752 1 0 oracle@********** (LMON) 0 272 0 272
2743 1 0 oracle@********** (LGWR) 0 269 0 269
2742 1 0 oracle@********** (CKPT) 0 123 0 123
Top SQL with Username and connection status Top SQL with Username and connection status
col NAME format a20
col PROGRAM format a30
col "STATUS" format a15
select decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id "SESSION_ID",
u.name "NAME",
topsession.program "PROGRAM",
max(topsession.CPU) "CPU",
max(topsession.WAITING) "WAITING",
max(topsession.IO) "IO",
max(topsession.TOTAL) "TOTAL"
from ( select
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id
and
-- /* outer join to v$session because the session might be disconnected */
topsession.session_id = s.sid (+)
and topsession.session_serial# = s.serial# (+)
and u.name !='SYS'
group by topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc
STATUS SESSION_ID NAME PROGRAM CPU WAITING IO TOTAL
--------------- ---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------
DISCONNECTED 2687 SYSMAN oracle@********** (J000) 0 0 3 3
DISCONNECTED 2687 ***** oracle@********** (J000) 0 1 0 1
DISCONNECTED 2670 SYSMAN oracle@********** (J000) 0 1 0 1
DISCONNECTED 2687 ***** oracle@********** (J000) 0 1 0 1
DISCONNECTED 2703 ***** oracle@********** (J000) 0 1 0 1
DISCONNECTED 2687 SYSMAN oracle@********** (J000) 0 1 0 1
DISCONNECTED 2683 SYSMAN oracle@********** (J000) 0 0 1 1
7 rows selected.
The Power of ASH (Active Session History)
Oracle 10g, brings many new features through which one can easily tune the bad sqls or also can diagnose the database performance issues.Using database metrics, active session history and time model views.Following query fetchs top sqls spent more on cpu/wait/io.
select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by ash.SQL_ID
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
gb4qjzp9su4h4 0 1 0 1
18naypzfmabd6 0 0 1 1
5qkswwgj544dx 0 1 0 1
8szmwam7fysa3 0 16 81 97
bj8hx2dgty2pp 0 0 1 1
7xgrmvd2nnjn6 0 1 0 1
1un0npcp105qq 0 1 0 1
Get the sql_text for above sql_id.
SELECT sql_text FROM v$sqlarea WHERE sql_id = '8szmwam7fysa3';
dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.
SELECT * FROM table(dbms_xplan.display_awr('8szmwam7fysa3'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8szmwam7fysa3
--------------------
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL,
'FALSE', :5, 'FALSE'))
Plan hash value: 1947143079
-------------------------------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 24 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| OBJECT_GROWTH_TREND | |
-------------------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
20 rows selected.
set pagesize 32000
set linesize 200
column sid format a10
column username format a10
column terminal format a10
column sql_text format a90 wrap
column OPERATION format a30
column OBJECT format a30
break on SID
Select ''''||s.sid||','||s.serial#||'''' SID,
CASE
WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT
else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.')
END SQL_TEXT
--,E.OBJECT_NODE
--,E.OPTIMIZER
--,E.ID
--,E.PARENT_ID
--,E.DEPTH
--,E.COST
--,E.CARDINALITY
--,E.BYTES
--,E.OTHER_TAG
--,E.PARTITION_START
--,E.PARTITION_STOP
--,E.PARTITION_ID
--,E.OTHER
--,E.DISTRIBUTION
,E.CPU_COST
,E.IO_COST
From v$sql_plan E, v$sql Q, V$SESSION S
Where E.address=Q.address(+)
and E.hash_value=Q.hash_value(+)
and E.child_number=Q.child_number(+)
and E.address=S.sql_address
order by s.sid,e.ID
/
SID SQL COMMAND CPU_COST IO_COST
---------- ------------------------------------------------------------------------------------------ ---------- ----------
'2701,1097 Select :"SYS_B_00"||s.sid||:"SYS_B_01"||s.serial#||:"SYS_B_02" SID, CASE WHEN (E.OPERATI
3' ON like :"SYS_B_03") then Q.SQL_TEXT else LPAD(:"SYS_B_04",E.DEPTH*:"SYS_B_05",:"SYS_B_0
6")||RTRIM(REPLACE(E.OPERATION||:"SYS_B_07"||E.OPTIONS||:"SYS_B_08"||E.OBJECT_OWNER||:"SYS
_B_09"||E.OBJECT_NAME,:"SYS_B_10",:"SYS_B_11")||:"SYS_B_12"||to_char(E.PARTITION_START)||:
"SYS_B_13"||to_char(E.PARTITION_STOP),:"SYS_B_14") END SQL_TEXT --,E.OBJECT_NODE --,E.OPTI
MIZER --,E.ID --,E.PARENT_ID --,E.DEPTH --,E.COST --,E.CARDINALITY --,E.BYTES --,E.OTHER_T
AG --,E.PARTITION_START --,E.PARTITION_STOP --,E.PARTITION_ID --,E.OTHER --,E.DISTRIBUTION
,E.CPU_COST ,E.IO_COST From v$sql_plan E, v$sql Q, V$SESSION S Where E.address=Q.address(
+) and E.hash_value=Q.hash_value(+) and E.child_number=Q.child_number(+) and E.address=S.s
ql_address order by s.sid,e.ID
SORT/ORDER BY 16549005 0
NESTED LOOPS/OUTER 391415 0
NESTED LOOPS 387615 0
NESTED LOOPS 383815 0
FIXED TABLE/FULL/SYS.X$KSUSE 380315 0
FIXED TABLE/FIXED INDEX/SYS.X$KSLED (ind:2) 3500 0
FIXED TABLE/FIXED INDEX/SYS.X$KQLFXPL (ind:2) 3800 0
FIXED TABLE/FIXED INDEX/SYS.X$KGLCURSOR_CHILD (ind:1) 3800 0
#CPU Time and OS process
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'DB User' JUSTIFY right
COLUMN os_username FORMAT a12 HEADING 'OS User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program'
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_cpu FORMAT 9,999,999,999 HEADING 'CPU(ms/s)'
COLUMN sql_text FORMAT a40 HEADING 'SQL COMMAND' JUSTIFY center
SET pagesize 200
SELECT s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value/1000 from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic# and
sn.name = 'CPU used by this session') session_cpu,sql_text
FROM v$sqlarea sqlarea, v$session s
WHERE s.sql_address = sqlarea.address
ORDER BY session_cpu desc;
SID DB User OS User Session Program Machine CPU(ms/s) SQL COMMAND
------ ------------ ------------ ------------------ -------- -------------- ----------------------------------------
2701 SYS oracle sqlplus@***001**db ***001m* 93 SELECT s.sid sid , lpad(s.usern
(TNS V1-V3) ame,:"SYS_B_0") oracle_username
, lpad(s.osuser,:"SYS_B_1") os_usern
ame , s.program sessio
n_program , lpad(s.machine,:"SYS_
B_2") session_machine , (selec
t ss.value/:"SYS_B_3" from v$sesstat ss,
v$statname sn where ss.sid = s.sid an
d sn.statistic# = ss.statistic# and
sn.name = :"SYS_B_4") session_cpu,s
ql_text FROM v$sqlarea sqlarea, v$sess
ion s WHERE s.sql_address = sqlarea.
address ORDER BY session_cpu desc
set linesize 200
set pagesize 32000
column sid format a13
column username format a10
column terminal format a10
column sql_text format a90 wrap
column OPERATION format a30
column OBJECT format a30
break on SID
Select ''''||s.sid||','||s.serial#||'''' SID,
CASE
WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT
else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.')
END SQL_TEXT
,E.CPU_COST
,E.IO_COST
From v$sql_plan E, v$sql Q, V$SESSION S
Where E.address=Q.address(+)
and E.hash_value=Q.hash_value(+)
and E.child_number=Q.child_number(+)
and E.address=S.sql_address
and sid=:X_SID --------- <<<<<<<<<<<
order by s.sid,e.ID
/
SID SQL COMMAND CPU_COST IO_COST
------------- ------------------------------------------------------------------------------------------ ---------- ----------
'2701,10973' Select :"SYS_B_00"||s.sid||:"SYS_B_01"||s.serial#||:"SYS_B_02" SID, CASE WHEN (E.OPERATI
ON like :"SYS_B_03") then Q.SQL_TEXT else LPAD(:"SYS_B_04",E.DEPTH*:"SYS_B_05",:"SYS_B_0
6")||RTRIM(REPLACE(E.OPERATION||:"SYS_B_07"||E.OPTIONS||:"SYS_B_08"||E.OBJECT_OWNER||:"SYS
_B_09"||E.OBJECT_NAME,:"SYS_B_10",:"SYS_B_11")||:"SYS_B_12"||to_char(E.PARTITION_START)||:
"SYS_B_13"||to_char(E.PARTITION_STOP),:"SYS_B_14") END SQL_TEXT ,E.CPU_COST ,E.IO_COST Fro
m v$sql_plan E, v$sql Q, V$SESSION S Where E.address=Q.address(+) and E.hash_value=Q.hash_
value(+) and E.child_number=Q.child_number(+) and E.address=S.sql_address and sid=:"SYS_B_
15" order by s.sid,e.ID
SORT/ORDER BY 16172243 0
NESTED LOOPS/OUTER 14653 0
NESTED LOOPS 10853 0
NESTED LOOPS 7053 0
FIXED TABLE/FIXED INDEX/SYS.X$KSUSE (ind:1) 3553 0
FIXED TABLE/FIXED INDEX/SYS.X$KSLED (ind:2) 3500 0
FIXED TABLE/FIXED INDEX/SYS.X$KQLFXPL (ind:2) 3800 0
FIXED TABLE/FIXED INDEX/SYS.X$KGLCURSOR_CHILD (ind:1) 3800 0
9 rows selected.
For Sql Text ....
SELECT sql_text FROM v$sqlarea WHERE sql_id = '8szmwam7fysa3’'
For explain Plan
SELECT * FROM table(dbms_xplan.display_awr('8szmwam7fysa3'));
set linesize 200
set pagesize 32000
column sid format a10
column username format a10
column terminal format a10
column sql_text format a40 wrap
Select ''''||s.sid||','||s.serial#||'''' SID
,s.status
,s.username
,s.terminal
,q.SQL_TEXT
,q.command_type
from v$session s
,(select distinct
address
,sql_text
,command_type
from v$sql) q
where q.address=s.sql_address
and s.sid=:X_SID
order by s.sid
/
Oracle Query sql_text in sql area,session,process,pid,cpu time,users Under Creative Commons License: Attribution
set linesize 200
col "Snap Day" format a15
col "Sql" format a40
SELECT *
FROM (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"
+ "Disk Reads"
+ "Buffer Gets"
+ "Writes"
+ "Sorts"
+ "Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day",
shs.sql_id "Sql id",
-- REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40) ),
-- CHR (10), '') "Sql",
SUM (shs.executions_delta) "Execs",
ROUND ( (SUM (shs.elapsed_time_delta) / 1000000) / SUM (shs.executions_delta),1) "Time Ea Sec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1) "CPU Ea Sec",
-- ROUND ( (SUM (shs.iowait_delta) / 1000000)/ SUM (shs.executions_delta),1) "IO/Wait Ea Sec",
SUM (shs.cpu_time_delta) "CPU Time",
SUM (shs.disk_reads_delta) "Disk Reads",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.direct_writes_delta) "Writes",
SUM (shs.parse_calls_delta) "Parses",
SUM (shs.sorts_delta) "Sorts",
SUM (shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,
TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'),
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 40) AS VARCHAR (40)
)
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20
AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');
Rank Snap Day Sql id Execs Time Ea Sec CPU Ea Sec CPU Time Disk Reads Buffer Gets Writes Parses Sorts Elapsed
---------- --------------- ------------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1 05/28/12 6gvch1xu9ca3g 1560 1.5 1.5 2313611092 2928 273553140 0 1560 0 2328252252
2 05/28/12 dj1q81rxhr7w3 24793080 0 0 901691280 0 223137720 0 1560 0 901691280
3 05/28/12 brcgd2t9fv9j4 312 2 2 614230556 0 5401968 0 312 0 614230556
4 05/28/12 gxmzbfrhsxt65 312 2 2 614102356 0 5401968 0 312 624 614102356
5 05/28/12 661tvbvnac891 24799320 0 0 304841546 0 49598640 0 3120 0 304841546
6 05/28/12 bunssq950snhf 52 1.4 1.4 74053802 8 578 0 52 52 74110932
7 05/28/12 d2w5sd5tug7xf 22 10.1 2.5 54123262 110656 11337998 586 22 194 221471924
8 05/28/12 84qubbrsr0kfn 52 .6 .6 31550544 494 7960 0
10g or higher: Find Sessions with the Highest DB Time
set lines 300
col module format a50
col event format a40
col username format a15
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, se.event, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
where s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited desc
SID SERIAL# OS PID USERNAME MODULE DB Time
(sec) CPU Time (sec) % CPU
---------- ---------- ------------ --------------- --------------- -------------------- -------------- ----------
2284 33483 27025 SMRCA w3wp.exe 6.04 4.21 69.7
2117 21985 26847 SYS sqlplus@***001m .03 1.18 3933.33
tdb (TNS V1-V3)
2074 33314 26839 SMRCA w3wp.exe 6.72 4.22 62.8
2653 33104 26816 SMRCA .33 .04 12.12
2132 49296 26812 SMRCA w3wp.exe .01 0 0
1963 587 13537 SMRCA .68 .4 58.82
2210 10798 26354 SMRCA 7.37 4.05 54.95
2290 59823 26352 SMRCA w3wp.exe .01 0 0
1900 22304 26287 SMRCA .31 .22 70.97
2654 33047 26281 SMRCA
1 comment:
Great Link Anuj, do you have an example of a ASH query which will identify queries that ran over x # of minutes in the last 30 minutes?
Post a Comment