- Ran the trend chart query in stage1
- Checked the v$active_session_history table for what is the event that the query is waiting on
- 'direct path read temp' --- This is the even that the query is waiting for
- Screenshot for total time waited on this event below
- What it means ??--- Query is pulling data from temp segment to PGA. (by passing SGA buffer cache). Most of the time query is waiting for pulling data from temp, which means i/o is not able to supply data to PGA at a good rate
- Solution – Check for i/o -- is the server using RAID (mostly yes) .So why is it not able to supply the data at the rate required from temp
select
sid,serial#,state,seconds_in_wait,event,event#,blocking_session,program
FROM v$SESSION
WHERE
OSUSER='ABC'
AND USERNAME
='ORAKK'
and program = 'SQL
Developer'
select
session_id,object_name, object_type,CURRENT_OBJ#, event, sum(time_waited) from
v$active_session_history, dba_objects
where session_id in
( select sid FROM v$SESSION
WHERE
OSUSER='ABC'
AND USERNAME
='ORAKK'
and
blocking_session is null )
and object_id =
CURRENT_OBJ#
group by
object_name, object_type,CURRENT_OBJ#, event,session_id
order by 6 desc
select * from
all_objects
where object_id
=87968
SELECT p1 file#, p2 block#, p3 class#,EVENT
FROM v$session_wait where sid in (
select
sid FROM v$SESSION
WHERE
OSUSER='ABC'
AND USERNAME
='ORAKK'
and
blocking_session is null)
and upper(event)
like '%DIRECT%READ%TEMP%'
order by event
Session wait is
important because it tells you what your session is blocked on
SELECT relative_fno, owner, segment_name,
segment_type
FROM dba_extents
WHERE file_id in
( SELECT p1
FROM v$session_wait where sid in (
select
sid FROM v$SESSION
WHERE
OSUSER='ABC'
AND USERNAME
='ORAKK'
and
blocking_session is null)
and upper(event)
like '%DIRECT%READ%TEMP%'
) and 1632256 between block_id and
block_id+1632256-1
select DISTINCT from dba_extents
WHERE SEGMENT_NAME = 'TEMP'
where UPPER(SEGMENT_NAME) LIKE '%TEMP%'
where file_id = 1509
select * from
V$TEMPSEG_USAGE-- gives which queries are using temp segemnt
select * from v$sort_usage
Number of records
that need to be pulled by Quality trend chart for doing calculation is
9,993153- ( approx 10 million). There is a condition in the query for pivoting
which is multiplying 10 million by 24 giving us 239 million.
To perform operation on 239 million query takes some
time.
Below query gives the space in
TEMP in GB being used by the query
select
a.username,
sum(srt.blocks * 8 / 1024) "Used Space
in MB",
sum(srt.blocks * 8 / 1024)/1024 "Used
Space in GB"
from
v$session
a,
v$sort_usage srt
where
a.saddr = srt.session_addr
and sid in ( select sid FROM v$SESSION WHERE OSUSER='ORAKK')
group by a.username
select * from
V$TEMPSEG_USAGE
Get the block id
from the tempseg_usage table and see who is using it
How to check on what event how much time is being
taken. Useful to check how much time oracle takes to write to database
select
se.event,
sum(se.total_waits),
sum(se.total_timeouts),
sum(se.time_waited/100) time_waited
from
v$session_event se,
v$session sess
where
sess.sid = se.sid
and sess.sid in ( select
sid FROM v$SESSION
WHERE
OSUSER='ABC'
AND USERNAME
='ORAKK'
and
blocking_session is null )
group by se.event
order by 4 desc
No comments:
Post a Comment