Re: How to analyze a session?

"utkanbir" <hopehope_123_at_yahoo.com> wrote in message news:f6c90ebe.0401260018.159b59c1_at_posting.google.com...
> Dear Gurus ,
> I have session which shows some waits in v$session_wait view, most of
> the time the session waits on "db buffer sequental read" . Does this
> mean the session reads data from disk? If so , how can i find how much
> data it reads?
>
> This is the output of :
> select * from v$session_wait where sid=115
>
> 115 4625 db file sequential
> read file# 59 000000000000003B block# 175664 000000000002AE30 blocks 1
0000000000000001 0 0 WAITING
>
> 115 21369 free buffer
> waits file# 188 00000000000000BC block# 186055 000000000002D6C7 set-id# 5
0000000000000005 0 0 WAITING
>
> 115 47842 null event 59 000000000000003B 180807 000000000002C247 1
0000000000000001 -1 0 WAITED
> KNOWN TIME
>
> 115 64068 global cache open
> x file# 189 00000000000000BD block# 117097 000000000001C969 le
4398113597472 0000040003FFA820 0 0 WAITING
>
> I run this query various times , db_file_sequential_read is the
> dominant wait event that the session is waiting .
>
> This is the sql statement:
> update "TANIDWH"."MLOG$_ALISVERIS_ANA" set snaptime$$ = :1 where rowid
> in (select rowid from "TANIDWH"."MLOG$_ALISVERIS_ANA" AS OF SNAPSHOT
> (:2) log$ where snaptime$$ >
> to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'))
>
>
> This sql is an update for materialized view , i dont know why it runs
> such a query.
>
> This is the output of select * from v$session_longops where sid=115
>
>
> 115 881 Table Scan TANIDWH.MLOG$_ALISVERIS_ANA 340158 340158 Blocks
1/26/2004
> 7:45:27 AM 1/26/2004 7:59:04 AM 0 817 0 Table Scan:
> TANIDWH.MLOG$_ALISVERIS_ANA: 340158 out of 340158 Blocks
> done TANIDWH 000004004B8AF510 4125076020 0
> 115 881 Table Scan TANIDWH.MLOG$_ALISVERIS_ANA 340158 340158 Blocks
1/26/2004
> 7:59:16 AM 1/26/2004 8:13:35 AM 0 859 0 Table Scan:
> TANIDWH.MLOG$_ALISVERIS_ANA: 340158 out of 340158 Blocks
> done TANIDWH 000004004B8AF510 4125076020 0
> 115 881 Hash Join 19376 37037 Blocks 1/26/2004 8:13:35 AM 1/26/2004
> 9:18:48 AM 3567 3913 0 Hash Join: : 19376 out of 37037 Blocks
> done TANIDWH 000004004B8AF510 4125076020 0
>
> This view has a sofar column which i guess explains how far the
> session completes , i want to know the detail info such as how many
> disk reads dows the session do , memory reads ,consumed memory , etc.
>
> I will be appreciated if anyone can comment about this.
>

Hi hope,

I'm not a guru so excuse me for my little humble suggestion.

If you're interested in what disk reads your session is performing and details on any associated waits, I would recommend tracing your session with event 10046, level 12 (eg. alter session set events '10046 trace name context forever, level 12).

The associated trace file will supply you with much info of interest (there are various notes on how to interpret the file on Metalink).

Cheers

Richard Received on Tue Jan 27 2004 - 06:00:42 CST