Question: What do
I do to find the Oracle session that is generating high redo
log activity? I have a session that is performing lots
of DML and it is clobbering my redo logs, and I want to find
the session ID. How do I find the session that is
generating high redo?
Answer: To see the session that is
generating high redo, try this query that shows session data
and statistics for "redo size":
select
ss.sid,
'redo size:'||ss.value,
s.program,
s.module
from
v$statname
sn,v$sesstat
ss,v$session s
where
ss.statistic#=sn.statistic#
and
sn.name='redo size'
and
s.sid=ss.sid
and
ss.value>0
order by
ss.value;
You can also see redo usage (indirectly) by querying the
v$transaction view. Here is another script example
using v$transaction which is extremely valuable for Oracle
administrators who need to very quickly find out what is
going on within their Oracle system.
select
substr(s.username,1,18) username,
substr(s.program,1,15) program,
decode(s.command,
0,'No
Command',
1,'Create
Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create
Index',
15,'Alter
Table',
21,'Create View',
23,'Validate Index',
35,'Alter
Database',
39,'Create Tablespace',
41,'Drop
Tablespace',
40,'Alter
Tablespace',
53,'Drop
User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
from
v$session s,
v$process
p,
v$transaction t,
v$rollstat
r,
v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by 1;
You can also use the
v$sess_io view. This will show the session that
has the most I/O activity, but it does not find high-DML
sessions that cause high REDO activity:
set
lines 120
col osuser format a10
col username format 10
select
osuser,
username,
process pid,
ses.sid sid,
serial#,
physical_reads,
block_changes
from
v$session ses,
v$sess_io sio
where
ses.sid = sio.sid
order
by physical_reads desc;
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|