What is causing Redo / Writes/ Log Creation- Statspack 8i/9i

Posted By Sagar Patil

Physical WRITES from the time Instance Started

select * from (
select obj#,physical_writes,row_number() over (order by physical_writes desc) rn from STATS$SEG_STAT where
snap_id > (select min(snap_id)from stats$snapshot where  trunc(snap_time)=trunc(sysdate))  AND
snap_id < (select max(snap_id) +1 from stats$snapshot where  trunc(snap_time)=trunc(sysdate))
)
where rn < 10
order by physical_writes desc

OBJ# Writes Rownum
61038 75268973 1
61038 75193857 2
61038 75112659 3
61038 75026083 4
61038 74949280 5
61038 74890055 6
61038 74813232 7
61038 74731555 8
61038 74641167 9

Top N OBJECTS involved in WRITES

select obj#,sum(physical_writes) from (
select obj#,physical_writes,row_number() over (order by physical_writes desc) rn from STATS$SEG_STAT where
snap_id > (select min(snap_id)from stats$snapshot where  trunc(snap_time)=trunc(sysdate))  AND
snap_id < (select max(snap_id) +1 from stats$snapshot where  trunc(snap_time)=trunc(sysdate))
)
where rn < 100
group by obj#
order by 2 desc;
-----------------------------------------------------
OBJ# WRITES
-----------------------------------------------------
61038 1197483298
34239 103901495
31503 20008939
61010 18732518
61039 11588335
50742 6510015
50708 1190885

Find out Owner, Segment Contributing to HIGH WRITE IO 

select owner,object_name,object_id  from dba_objects where object_id in
(
select obj# from (

select obj#,sum(physical_writes)
from (
select obj#,physical_writes,row_number() over (order by physical_writes desc) rn from STATS$SEG_STAT where
snap_id > (select min(snap_id)from stats$snapshot where  trunc(snap_time)=trunc(sysdate))  AND
snap_id < (select max(snap_id) +1 from stats$snapshot where  trunc(snap_time)=trunc(sysdate))
)
where rn < 100
group by obj#
order by 2 desc
)
)

Locate WRITES Done between old and new SNAP_ID

&1 as well as &2 are input hours

Example
&1=16 , &2=18 will return any writes between sysdate-16 /sysdate-18 hours

SELECT   sn.obj# oject_no,
sn.physical_writes - so.physical_writes physical_writes
FROM stats$seg_stat sn,
stats$seg_stat so,
(SELECT MIN (snap_id) min_snap, MAX (snap_id) max_snap
FROM stats$snapshot
WHERE snap_time > sysdate -&1/24  and snap_time < sysdate - &2/24) v
WHERE sn.snap_id = v.max_snap
AND so.snap_id = v.min_snap
AND sn.dbid = so.dbid
AND sn.instance_number = so.instance_number
AND sn.dataobj# = so.dataobj#
AND sn.obj# = so.obj#
ORDER BY 2 DESC

Here are some examples:
Description   Date Expression
Now   SYSDATE
Tomorow/ next day   SYSDATE + 1
Seven days from now   SYSDATE + 7
One hour from now   SYSDATE + 1/24
Three hours from now   SYSDATE + 3/24
An half hour from now   SYSDATE + 1/48
10 minutes from now   SYSDATE + 10/1440
30 seconds from now   SYSDATE + 30/86400
Tomorrow at 12 midnight   TRUNC(SYSDATE + 1)
Tomorrow at 8 AM   TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon   NEXT_DAY(TRUNC(SYSDATE), ‘MONDAY’) + 12/24
First day of the month at 12 midnight   TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m   TRUNC(LEAST(NEXT_DAY(sysdate,”MONDAY’ ‘ ),NEXT_DAY(sysdate,”WEDNESDAY”), NEXT_DAY(sysdate,”FRIDAY” ))) + (9/24)

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu