How to analyze a session?

Giganews Newsgroups
Subject: How to analyze a session?
Posted by:  hopehope_1…@yahoo.com
Date: 26 Jan 2004

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

Replies