FAQ
What is the range of values for buffer_gets column in stats$sql_summary
table. Our tables have -ve values in few cases.

Thanks
Vijay

Search Discussions

  • John Kanagaraj at Nov 28, 2006 at 8:10 pm
    Vijay,

    -ive values are generally due to wrap-around. Since STATSPACK data is just
    snapshots of V$ views, which in turn are cumulative, you will see this issue
    when your database has been up for a long time and the counter wraps around.
    In the case of STATS$SQL_SUMMARY, the BUFFER_GETS is derived from
    V$SQL.BUFFER_GETS. Negative values in buffer_gets may be seen in SQL that
    results in a lot of buffer gets *AND* is 'kept alive' in the lib cache via
    constant executions and pins. (I have one particular SQL that has this
    "issue" on an Oracle Apps 11i instance)
    What is the range of values for buffer_gets column in stats$sql_summary
    table. Our tables have -ve values in few cases.
    --
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Disappointment is always inevitable; Discouragement is invariably optional

    ** The opinions and facts contained in this message are entirely mine and do
    not reflect those of my employer or customers **

    --
    http://www.freelists.org/webpage/oracle-l
  • Fairlie rego at Nov 28, 2006 at 11:18 pm
    Vijay,


    As John mentioned the value is derived from v$sql which derives the value from x$kglob and you have not mentioned the Oracle version.


    Hence


    if [ version -lt 10.1 ];
    then bug=2758126
    fi


    Rediscovery information:
    Query of v$sql or v$kglob could show negative values for disk_reads and buffer_gets.


    -Fairlie

    John Kanagaraj wrote:

    Vijay,


    -ive values are generally due to wrap-around. Since STATSPACK data is just snapshots of V$ views, which in turn are cumulative, you will see this issue when your database has been up for a long time and the counter wraps around. In the case of STATS$SQL_SUMMARY, the BUFFER_GETS is derived from V$SQL.BUFFER_GETS. Negative values in buffer_gets may be seen in SQL that results in a lot of buffer gets *AND* is 'kept alive' in the lib cache via constant executions and pins. (I have one particular SQL that has this "issue" on an Oracle Apps 11i instance)


    What is the range of values for buffer_gets column in stats$sql_summary table. Our tables have -ve values in few cases.

    --
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Disappointment is always inevitable; Discouragement is invariably optional

    ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

    Fairlie Rego
    Senior Oracle Consultant
    http://www.linkedin.com/in/fairlierego

    http://el-caro.blogspot.com/
    M: +61 402 792 405

    ---------------------------------
    Want to start your own business? Learn how on Yahoo! Small Business.
    --
    http://www.freelists.org/webpage/oracle-l
  • Vijay Vardhineni at Nov 28, 2006 at 11:25 pm
    Thanks Fairlie and John.

    I am using Oracle 9.2.0.7 on HP Tru64.

    Thanks
    Vijay
    On 11/28/06, fairlie rego wrote:

    Vijay,

    As John mentioned the value is derived from v$sql which derives the value
    from x$kglob and you have not mentioned the Oracle version.

    Hence

    if [ version -lt 10.1 ];
    then bug=2758126
    fi

    Rediscovery information:
    Query of v$sql or v$kglob could show negative values for disk_reads and buffer_gets.


    -Fairlie

    *John Kanagaraj * wrote:

    Vijay,

    -ive values are generally due to wrap-around. Since STATSPACK data is just
    snapshots of V$ views, which in turn are cumulative, you will see this issue
    when your database has been up for a long time and the counter wraps around.
    In the case of STATS$SQL_SUMMARY, the BUFFER_GETS is derived from
    V$SQL.BUFFER_GETS. Negative values in buffer_gets may be seen in SQL that
    results in a lot of buffer gets *AND* is 'kept alive' in the lib cache via
    constant executions and pins. (I have one particular SQL that has this
    "issue" on an Oracle Apps 11i instance)

    What is the range of values for buffer_gets column in stats$sql_summary
    table. Our tables have -ve values in few cases.
    --
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Disappointment is always inevitable; Discouragement is invariably optional

    ** The opinions and facts contained in this message are entirely mine and
    do not reflect those of my employer or customers **




    *Fairlie Rego
    *Senior Oracle Consultant
    http://www.linkedin.com/in/fairlierego
    <http://www.optus.com.au/>
    http://el-caro.blogspot.com/
    M: +61 402 792 405


    ------------------------------
    Want to start your own business? Learn how on Yahoo! Small Business.<http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index>
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 28, '06 at 7:51p
activeNov 28, '06 at 11:25p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase