http://www.oracle.com/technology/oramag/code/tips2005/010305.html
select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
count(*)
from v$open_cursor a, v$session b
where a.saddr=b.saddr
group by substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30)
order by 3 desc ;
Track and Monitor Resources
This tip comes from Luis Adalberto, DBA at Universidad Autonoma de Coahuila, in Saltillo, Coahuila, Mexico.
Running this query will produce a list of database statements and objects that are using the most resources.
Note: Execute as SYS user.
select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, V1.opname, to_char(V1.start_t
ime, ‘HH24:MI:SS’) AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS Pct_completed
FROM V$SESSION_LONGOPS V1, V$SESSION V2
WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#
AND (SOFAR/TOTALWORK)*100 < 100
AND TOTALWORK > 0
Track CPU Usage
This tip comes from Jony Safi, DBA at Videotron Ltee, in Montreal, Quebec,Canada.
This query monitors CPU usage by session, showing information about processes running. It can identify the whole proccess tree.
select s.username “Oracle User”,s.osuser “OS User”,i.consistent_gets “Consistent Gets”,
i.physical_reads “Physical Reads”,s.status “Status”,s.sid “SID”,s.serial# “Serial#”,
s.machine “Machine”,s.program “Program”,to_char(logon_time, ‘DD/MM/YYYY HH24:MI:SS’) “Logon Time”,
w.seconds_in_wait “Idle Time”, P.SPID “PROC”,
name “Stat CPU”, value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and ‘SQL*Net message from client’ = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like ‘%cpu%’
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc
Check Transactions Occupying Rollback Segments
This tip comes from Vishwamithran S, Manager, DB Support, in Chennai, Tamilnadu, India.
This script checks transactions that occupy rollback segments for more than a day.
select substr(v$session.sid,1,8) sid,to_char(spid) spid,
to_char(process) process,substr(r.usn,1,6) usn,
substr(nvl(v$session.program,machine),1,20) program,
to_char(used_ublk*8192/1024/1024) used,
substr(event,1,18) event,
round(seconds_in_wait/(60*60),2) hr,
r.status,start_time,sysdate from
v$session,v$transaction,v$rollstat r,
v$session_wait, v$process
where saddr=ses_addr and
xidusn=r.usn and
v$session.sid=v$session_wait.sid and
paddr=v$process.addr and
to_date(start_time,’mm/dd/yy hh24:mi:ss’)
Output of the query gives the process value (the program’s PID). You can then check for the existence of the process at the OS level, and if it is not found, you can then clean up the corresponding Oracle shado
Generate Horizontal-to-Vertical
This tip comes from Ilya Petrenko, a Sr.Oracle DBA with Open Distributed Solutions, Inc., in Jamison, Pennsylvania.
This query takes a long string of data and breaks it out into smaller, separate strings.
set ver off
def Del=’@’
def String=”@0@11@222@3333@44444@555555@6666666@77777777@888888888″
select
Rownum Row#,
SUBSTR(A.Str||’&Del’
, INSTR(A.Str||’&Del’ , ‘&Del’, 1, Rownum ) +1
, INSTR(A.Str||’&Del’ , ‘&Del’, 1, Rownum+1)
-INSTR(A.Str||’&Del’ , ‘&Del’, 1, Rownum ) -1
) Token
From
(
select
‘&String’ Str
from dual
)
A,
all_objects B
where
Rownum< length(A.Str)-length(REPLACE(A.Str,’&Del’))+1
;