This is a continuation of my IOPs challenges.
This is a non DBA's cheat sheet for finding IO in a standard oracle database
Good to find out what queries are smashing the disk:
select
p.spid,
s.sid,
s.serial#,
s.process cli_process,
s.status,t.disk_reads,
s.last_call_et/3600 last_call_et_Hrs,
s.action,
s.program,
t.sql_fulltext
from
v$session s,
v$sqlarea t,
v$process p
where
s.sql_address = t.address
and
s.sql_hash_value = t.hash_value
and
p.addr = s.paddr
-- and
--t.disk_reads > 10
order by
t.disk_reads desc;
PID SID SERIAL# CLI_PROCESS STATUS DISK_READS LAST_CALL_ET_HRS ACTION PROGRAM SQL_FULLTEXT
------------------------ ---------- ---------- ------------------------ -------- ---------- ---------------- ---------------------------------------------------------------- ------------------------------------------------ --------------------------------------------------------------------------------
2924 3880 15998 1234 INACTIVE 48832423 4.4775 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
4939 7678 8934 1234 INACTIVE 48832423 4.57472222 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
4935 10191 19604 1234 INACTIVE 48832423 4.51472222 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
3679 10175 40187 1234 INACTIVE 20300027 10.71 JDBC Thin Client SELECT SDAN8,SDQTYT,SDPPDJ,SDUORG,SDDCT,SDFRGD,SDDELN,SDPA8,SDADTM,SDTHRP,SDSRP2
4931 19066 290 1234 INACTIVE 16277598 6.58472222 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
2181 1311 2983 1234 INACTIVE 7032445 41.3938889 JDBC Thin Client SELECT DISTINCT GLDOC,GLPOST,GLLT,GLDGJ,GLKCO,GLDCT,GLEXA,GLR1,GLRE,GLPN,GLICU,
9811 15283 13699 1234 INACTIVE 7032445 41.3938889 JDBC Thin Client SELECT DISTINCT GLDOC,GLPOST,GLLT,GLDGJ,GLKCO,GLDCT,GLEXA,GLR1,GLRE,GLPN,GLICU,
7281 15258 2380 1234 INACTIVE 3379248 37.2380556 JDBC Thin Client SELECT SDAN8,SDQTYT,SDPPDJ,SDUORG,SDDCT,SDFRGD,SDDELN,SDPA8,SDADTM,SDTHRP,SDSRP2
27197 41 1604 1234 INACTIVE 2911686 27.3166667 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
13675 16529 830 1234 INACTIVE 1207700 48.6297222 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
29908 2602 9964 1234 INACTIVE 1207700 48.6297222 JDBC Thin Client SELECT T1.GMANS,T0.GLODCT,T2.MCADDS,T2.MCRP20,T0.GLALT4,T2.MCCLNU,T0.GLPYID,T0.G
Remember that
If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
Handy to also look at longops to know how long they might take to complete, if they are listed there.
select
l.sid,
l.sofar,
l.totalwork,
l.start_time,
l.last_update_time,
s.sql_text
from
v$session_longops l
left outer join
v$sql s
on
s.hash_value = l.sql_hash_value
and
s.address = l.sql_address
and
s.child_number = 0
order by TOTALWORK desc;
The above is cool for seeing long operations, if you want to see active longops, add this where clause:
select
l.sid,
l.sofar,
l.totalwork,
l.start_time,
l.last_update_time,
s.sql_text
from
v$session_longops l
left outer join
v$sql s
on
s.hash_value = l.sql_hash_value
and
s.address = l.sql_address
and
s.child_number = 0
where sofar < totalwork
order by TOTALWORK desc;
select
l.sid,
l.sofar,
l.totalwork,
l.start_time,
l.last_update_time,
t.sql_fulltext
from
v$session_longops l
left outer join
v$sql s
on
s.hash_value = l.sql_hash_value
and
s.address = l.sql_address
and
s.child_number = 0
left outer join v$sqlarea t
on
l.sql_hash_value = t.hash_value
order by TOTALWORK desc;
Then kill it, remember RDS on AWS does not let you run this – no matter who you are connected as: Remember that if there are java processes in longops, basically you can kill them. I would not touch runbatch longops -that is legitimate. Quite often jdenet_k processes cannot really run long processes - you need to be a little bit careful here.
alter system kill session '13993,34274'
Error report -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
You need to run their prooceure.
begin
rdsadmin.rdsadmin_util.kill(
sid => 13993,
serial => 34274);
end;
