DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Individual Segment Statistics

  1. #1
    Join Date
    Sep 2006
    Posts
    114

    Individual Segment Statistics

    Dear All,
    i have executed the following script to know about invidual segment causing problem

    select * from
    (
    select
    DECODE
    (GROUPING (a.object_name), 1, 'All Objects', a.object_name)
    AS "Object",
    sum (case when a.statistic_name = 'ITL waits'
    then a.value else null end) "ITL Waits",
    sum(case when a.statistic_name = 'buffer busy waits'
    then a.value else null end) "Buffer Busy Waits",
    sum(case when a.statistic_name = 'row lock waits'
    then a.value else null end) "Row Lock Waits",
    sum(case when a.statistic_name = 'physical reads'
    then a.value else null end) "Physical Reads",
    sum(case when a.statistic_name = 'logical reads'
    then a.value else null end) "Logical Reads"
    from v$segment_statistics a
    where a.owner like upper('&owner')
    group by rollup(a.object_name)) b
    where (b."ITL Waits">0 or b."Buffer Busy Waits">0)

    Object ITLWaits BufferWaits Physical Reads Logical Reads
    ----------------- ------ ----------------- -------------- -------
    HR_EMP_ATTENDANCE 0 1910 12678637 19801744
    HR_EMP_CHECKIN 0 5 12788 96368
    All Objects 0 1915 12947951 22760320

    HR_EMP_ATTENDANCE= 512MB in size
    it looks like that HR_EMP_ATTENDANCE is causing 99% of the total
    buffer busy waits and also most of the physical reads belong to
    HR_EMP_ATTENTDANCE
    Now what i should do now?
    because i am having 57% buffer cache hit percentage
    can u help me in this regard?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you need to learn the different between buffer busy waits and a buffer cache hit ratio first as they arent related as simply as you would like them to be.

    search google for buffer busy waits and you can see what it means, as what you have posted there means nothing

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I suggest you query v$segstat instead of v$segment_statistics to reduce overhead.

    Is buffer busy waits causing you any problems at all? Normally buffer busy waits means hot blocks, you need to query v$session_wait and get p1 values, if it's 130 the most probably it is hotspots.

    If you get 130 and in your case I assume it is a table if that is the case I would check the sql statements accesing the table, see if there are Full Table Scan running at same time by several sessions causing buffer busy waits.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width