FAQ
During a recent test on Oracle 9.2.0 3 just noticed some high logical reads generated through the v$segment_statsitics view, I can relate buffer busy waits and physical reads however, what does 'logical reads' at the segment level on a tablespace indicate?

SQL> COL VALUE FORMAT 99999999

SQL> SELECT TABLESPACE_NAME,STATISTIC_NAME,VALUE from V$SEGMENT_STATISTICS WHERE
2 OWNER NOT IN ('SYS','SYSTEM') AND VALUE > 100 ORDER BY VALUE DESC;

TABLESPACE_NAME STATISTIC_NAME VALUE
-------------------- ----------------------------------- ---------
MED_DATA_001 logical reads 21260912
MED_INDX_001 logical reads 13275744
MED_DATA_001 logical reads 13254432
MED_INDX_001 logical reads 10721792
LAR_DATA_001 logical reads 10616944
MED_DATA_001 physical reads 7169875
LAR_DATA_001 physical reads 5206083
SMA_INDX_001 logical reads 1915440
MED_DATA_001 logical reads 1214416
MED_INDX_001 logical reads 1123120
SMA_DATA_001 logical reads 1031616

TABLESPACE_NAME STATISTIC_NAME VALUE
-------------------- ----------------------------------- ---------
MED_DATA_001 physical reads 987820
SMA_DATA_001 logical reads 926608
MED_DATA_001 buffer busy waits 925822
MED_DATA_001 physical reads 831576
LAR_DATA_001 buffer busy waits 817668
MED_INDX_001 physical reads 610602
MED_INDX_001 logical reads 602368
SMA_INDX_001 logical reads 580128
SMA_INDX_001 logical reads 489632
MED_DATA_001 logical reads 485920
MED_INDX_001 physical reads 471294

Menon





Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Murali Menon
INET: mjgnmenon_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Arup Nanda at Apr 18, 2003 at 5:46 am
    All these stats from v$segment_statistics mean nothing at the tablespace
    level; only at the segment level. In your query, selectthe segment, too.

    Arup
    From: Murali Menon
    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Subject: Logical Reads from v$segment_statistics at the tablespace level!
    Date: Thu, 17 Apr 2003 18:46:59 -0800


    During a recent test on Oracle 9.2.0 3 just noticed some high logical reads
    generated through the v$segment_statsitics view, I can relate buffer busy
    waits and physical reads however, what does 'logical reads' at the segment
    level on a tablespace indicate?

    SQL> COL VALUE FORMAT 99999999
    SQL> SELECT TABLESPACE_NAME,STATISTIC_NAME,VALUE from V$SEGMENT_STATISTICS
    WHERE
    2 OWNER NOT IN ('SYS','SYSTEM') AND VALUE > 100 ORDER BY VALUE DESC;
    TABLESPACE_NAME STATISTIC_NAME VALUE
    -------------------- ----------------------------------- ---------
    MED_DATA_001 logical reads 21260912
    MED_INDX_001 logical reads 13275744
    MED_DATA_001 logical reads 13254432
    MED_INDX_001 logical reads 10721792
    LAR_DATA_001 logical reads 10616944
    MED_DATA_001 physical reads 7169875
    LAR_DATA_001 physical reads 5206083
    SMA_INDX_001 logical reads 1915440
    MED_DATA_001 logical reads 1214416
    MED_INDX_001 logical reads 1123120
    SMA_DATA_001 logical reads 1031616

    TABLESPACE_NAME STATISTIC_NAME VALUE
    -------------------- ----------------------------------- ---------
    MED_DATA_001 physical reads 987820
    SMA_DATA_001 logical reads 926608
    MED_DATA_001 buffer busy waits 925822
    MED_DATA_001 physical reads 831576
    LAR_DATA_001 buffer busy waits 817668
    MED_INDX_001 physical reads 610602
    MED_INDX_001 logical reads 602368
    SMA_INDX_001 logical reads 580128
    SMA_INDX_001 logical reads 489632
    MED_DATA_001 logical reads 485920
    MED_INDX_001 physical reads 471294


    Menon







    ---------------------------------
    Do you Yahoo!?
    The New Yahoo! Search - Faster. Easier. Bingo.
    Add photos to your messages with MSN 8. Get 2 months FREE*.
    http://join.msn.com/?page=features/featuredemail

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 18, '03 at 2:46a
activeApr 18, '03 at 5:46a
posts2
users2
websiteoracle.com

2 users in discussion

Murali Menon: 1 post Arup Nanda: 1 post

People

Translate

site design / logo © 2022 Grokbase