HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Useful queries for running processes check
ORACLE STUFFS
14-Oct-2008
SQL tools like TOAD or PL/SQL developer contain enough functionality to check, anyway often you get into situation when it is impossible to use them. In that case I used to use a few useful queries to do despite no proper tools are present. Just a SQL+.

Following privileges are required for such tasks:

grant select on SYS.V_$SESSION to <user>;
grant select on SYS.V_$SQL to <user>;
grant select on SYS.V_$SQL_PLAN to <user>;
grant select on SYS.V_$TRANSACTION to <user>;
grant select on SYS.V_$SESSION_EVENT to <user>;
grant select on SYS.V_$SESSION_LONGOPS to <user>;


First step - enriched list of sessions:

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
order by s.sid
/

[Download]

The Result let you get brief summary about running processes:

SQL> @s SID STATUS USERNAME TERMINAL SQL_TEXT COMMAND_TYPE ---------- -------- ---------- ---------- ---------------------------------------- ------------ '79,7942' ACTIVE A2 BW001 Select ''''||s.sid||','||s.serial#||'''' 3 SID ,s.status ,s.username ,s.term inal ,q.SQL_TEXT ,q.command_type fro m v$session s ,(select distinct a ddress ,sql_text ,command_type f rom v$sql) q where q.address=s.sql_add ress order by s.sid '86,13' ACTIVE OWBSYS unknown BEGIN wb_rt_service_control.wait( :1 , 47 :2 , :3 ) ; END;


Second step - explain plan of running processes:

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
/

[Download]

And the result:

SQL> @e SID SQL_TEXT CPU_COST IO_COST ---------- ------------------------------------------------------------------------------------------ ---------- ---------- '79,7942' 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_valu e=Q.hash_value(+) and E.child_number=Q.child_number(+) and E.address=S.sql_address order b y s.sid,e.ID SORT/ORDER BY 22545828 0 NESTED LOOPS/OUTER 394915 0 NESTED LOOPS 391115 0 NESTED LOOPS 387315 0 NESTED LOOPS 383815 0 FIXED TABLE/FULL/SYS.X$KSUSE 380315 0 FIXED TABLE/FIXED INDEX/SYS.X$KSLWT (ind:1) 3500 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


Third step - long running processes:

set linesize 200
set pagesize 32000
column sid format 9999
column DONE_PCT format a15
column MINUTES_REM format 99999
select SID,to_char(ROUND((SOFAR*100)/TOTALWORK,2))||' %' DONE_PCT
 ,trunc(TIME_REMAINING/60) MINUTES_REM 
 ,trunc(ELAPSED_SECONDS/60) MINUTES_ELAPSED
 ,MESSAGE
from V$SESSION_LONGOPS
order by start_time
/

[Download]

And the result:

SQL> @l SID DONE_PCT MINUTES_REM MINUTES_ELAPSED MESSAGE ----- --------------- ----------- --------------- ------------------------------------------------------------------------------------------------------ 75 100 % 0 1 Advisor: : 9 out of 9 Sql Statements done 77 100 % 0 0 Gather Table's Index Statistics: Table KET$_AUTOTASK_STATUS : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table KET$_CLIENT_TASKS : 2 out of 2 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WRI$_SCH_CONTROL : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WRI$_SCH_VOTES : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WRM$_DATABASE_INSTANCE : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WRM$_WR_CONTROL : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WK$INSTANCE : 3 out of 3 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table WK$_JOB_INFO : 1 out of 1 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table MGMT_TARGETS : 3 out of 3 Indexes done 100 % 0 0 Gather Table's Index Statistics: Table MGMT_CURRENT_METRICS : 2 out of 2 Indexes done


Fourth step - transaction state (dynamics of USED_UREC can show direction of the process - decreasing means rollback in progress):

set linesize 200
set pagesize 32000
column SID format 9999
select s.SID,LOG_IO,PHY_IO,CR_GET,CR_CHANGE,USED_UREC from v$transaction t,v$session s where s.saddr=t.ses_addr
/

[Download]


I used to use following way to monitor only one certain session by mentioned scripts:
1. get SID value using list of session query
2. set up sqlplus variable using following script
3. use *x.sql mutations of scripts to show data about just one session

set pagesize 32000
set linesize 2000
set def on
VARIABLE X_SID NUMBER;
BEGIN
  :X_SID:=&sid;
END;
/
set def off

[Download]

The script works following way:

SQL> @x Put value for sid:

Remains to put the SID number of session and all following scripts will work just with the session.


Sessions:

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
/

[Download]


Explain plan:

set linesize 200
set pagesize 32000
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.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
/

[Download]


Long operations:

set linesize 500
set pagesize 32000
column sid format 9999
column DONE_PCT format a15
column MINUTES_REM format 99999
column MINUTES_ELAPSED format 99999
column MESSAGE format a300
select SID,to_char(ROUND((SOFAR*100)/TOTALWORK,2))||' %' DONE_PCT
 ,trunc(TIME_REMAINING/60) MINUTES_REM 
 ,trunc(ELAPSED_SECONDS/60) MINUTES_ELAPSED
 ,MESSAGE
from V$SESSION_LONGOPS
where SID=:X_SID
order by start_time
/

[Download]


Transactions:

set linesize 200
set pagesize 32000
column SID format 9999
select s.SID,LOG_IO,PHY_IO,CR_GET,CR_CHANGE,USED_UREC from v$transaction t,v$session s 
where s.saddr=t.ses_addr
and SID=:X_SID
/

[Download]


And wait events:

set linesize 200
set pagesize 32000
column SID format 99999
break on SID
select * from  v$session_event 
where SID=:X_SID
order by SID,TOTAL_TIMEOUTS desc,TOTAL_WAITS desc
/

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky