Re: How to analyze a session?

hopehope_123_at_yahoo.com (utkanbir) 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.
>
> Kind Regards,
> hope

v$session_wait specifies exactly which disk blocks it is reading. They are in your output, file 59, block 175664. A 'sequential read' always reads one block at a time. The update you see is a result of creating a materialized view. Materialized views are refreshed at specified intervals, so you may expect inserts and updates.

Sybrand Bakker
Senior Oracle DBA Received on Mon Jan 26 2004 - 09:08:47 CST