FAQ
Hi Friends,
In AWR under Instance Efficiency Percentages we see a Library Hit % and we
find it very low for one snapshot. I would like to know the trend for few
more days available in AWR snapshots
(dba_hist_snapshot,DBA_HIST_LIBRARYCACHE) using SQL Query.

Can some one please help with how this % is being calculated. What SQL can
I use to calculate this ? May be for a range of snapshot. Thanks in
Advance.

11.2.0.2.0 on Solairs 10 .

With Regards,
Sreejith

--
Sreejith S Nair
Associate Systems Architect | AOS DBA Team
IBS Software Services Private Ltd.
2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
India
((Direct) +91 471 661 4707 ) +91 808 648 5523
*sreejith.sreekantan@ibsplc.com
8 www.ibsplc.com





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."






--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Gaja Krishna Vaidyanatha at Apr 4, 2012 at 8:57 pm
    Hi Sreejith,
    Although I can't help you directly on the SQL you requested, but once you do start trend this phenomenon, here are a few things that will provide food for investigation and thought:

    1) What the are Top 5 Wait Events when this Instance Efficiency Percentage is low?
    2) Which SQL statements are being negatively affected by the Top 5 Wait Events? By how much?
    3) Is there a problem on your database for the period of the snapshot(s)?
    4) How does this snapshot's workload compare to a baseline snapshot?
    5) Is there evidence of some sort of abnormal application workload?
    6) Did you have more parsing than normal?

    7) What caused the increased parsing (if applicable)?
    8) Or was the increased parsing due to automatic memory management stealing from the shared pool to feed the db buffer cache in response to an increase I/O load?



    Cheers,

    Gaja

    Gaja Krishna Vaidyanatha,
    CEO & Founder, DBPerfMan LLC
    http://www.dbperfman.com
    http://www.dbcloudman.com

    Phone - +1-650-743-6060
    http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID14
    Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
    Enabling Cloud Deployment & Management for Oracle Databases


    ________________________________
    From: Sreejith S Nair <Sreejith.Sreekantan@ibsplc.com>
    To: oracle-l@freelists.org
    Sent: Wednesday, April 4, 2012 2:39 AM
    Subject: Instance Efficiency Percentages - Library Hit %: in AWR

    Hi Friends,
    In AWR under Instance Efficiency Percentages we see a Library Hit % and we
    find it very low for one snapshot. I would like to know the trend for few
    more days available in AWR snapshots
    (dba_hist_snapshot,DBA_HIST_LIBRARYCACHE) using SQL Query.

    Can some one please help with how this % is being calculated. What SQL can
    I use to calculate this ? May be for a range of snapshot. Thanks in
    Advance.

    11.2.0.2.0 on Solairs 10 .

    With Regards,
    Sreejith

    --
    Sreejith S Nair
    Associate Systems Architect | AOS DBA Team
    IBS Software Services Private Ltd.
    2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
    India
    ((Direct) +91 471 661 4707 ) +91 808 648 5523
    *sreejith.sreekantan@ibsplc.com
    8 www.ibsplc.com





    DISCLAIMER:

    "The information in this e-mail and any attachment is intended only for
    the person to whom it is addressed and may contain confidential and/or
    privileged material. If you have received this e-mail in error, kindly
    contact the sender and destroy all copies of the original communication.
    IBS makes no warranty, express or implied, nor guarantees the accuracy,
    adequacy or completeness of the information contained in this email or any
    attachment and is not liable for any errors, defects, omissions, viruses
    or for resultant loss or damage, if any, direct or indirect."






    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Sreejith S Nair at Apr 5, 2012 at 1:34 am
    Hi Gaja ,

    Many thanks for the hints.
    Yes, we have experienced severe system slowness during this period. I have checked DBA hist active session history and I can see that for the problem time window 'Concurrency' wait ever came all of a sudden. This is happening only in one instance. Top foreground timed events being 'Cursor pin wait on X' and 'Library Cache Mutex-X' waits. A severity 1 SR is raised with Oracle for this.

    There are bugs related to this 'mutex' , SQL high version count , which in fortunately I not available for our platform (Solaris x86 11.2.0.2)

    Other recommendations were to increase sag , increase interval between memory resize operations ( yes we use AMM) , gathering dictionary and fixed object statistics.

    The performance was really bad so that we see the Library Hit % being 1.7,0.8,-0.87 etc. I have analysed past trend which shows the % coming down.

    The ADDM for problem window recommends some session holding a mutex lock and some others waiting for it which contributes 30% of the activity.
    The SQLs reported were not so bad a these were critical ones which normally works not so bad.

    The activities that I see in this time on database are the statistics collection jobs , which were running. Not sure whether this has contributed a pressure in Library cache.

    After we increased sga it's getting to normal state. But due to a (possible) bug I feel this will come again.

    Regards,
    Sreejith
    -- Sent from my iPhone
    On 05-Apr-2012, at 2:26 AM, Gaja Krishna Vaidyanatha wrote:

    Hi Sreejith,
    Although I can't help you directly on the SQL you requested, but once you do start trend this phenomenon, here are a few things that will provide food for investigation and thought:

    1) What the are Top 5 Wait Events when this Instance Efficiency Percentage is low?�
    2) Which SQL statements are being negatively affected by the Top 5 Wait Events? By how much?�
    3) Is there a problem on your database for the period of the snapshot(s)?�
    4) How does this snapshot's workload compare to a baseline snapshot?
    5) Is there evidence of some sort of abnormal application workload?�
    6) Did you have more parsing than normal?�

    7) What caused the increased parsing (if applicable)?�
    8) Or was the increased parsing due to automatic memory management stealing from the shared pool to feed the db buffer cache in response to an increase I/O load?



    Cheers,

    Gaja

    Gaja Krishna Vaidyanatha,
    CEO & Founder, DBPerfMan LLC
    http://www.dbperfman.com
    http://www.dbcloudman.com

    Phone -�+1-650-743-6060
    http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table -�http://www.apress.com/book/bookDisplay.html?bID14
    Co-author:Oracle Performance Tuning 101 -�http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
    Enabling Cloud Deployment & Management for Oracle Databases


    ________________________________
    From: Sreejith S Nair <Sreejith.Sreekantan@ibsplc.com>
    To: oracle-l@freelists.org
    Sent: Wednesday, April 4, 2012 2:39 AM
    Subject: Instance Efficiency Percentages - Library Hit %: in AWR

    Hi Friends,
    In AWR under Instance Efficiency Percentages we see a Library Hit % and we
    find it very low for one snapshot. I would like to know the trend for few
    more days available in AWR snapshots
    (dba_hist_snapshot,DBA_HIST_LIBRARYCACHE) using SQL Query.

    Can some one please help with how this % is being calculated. What SQL can
    I use to calculate this ? May be for a range of snapshot. Thanks in
    Advance.

    11.2.0.2.0 on Solairs 10 .

    With Regards,
    Sreejith

    --
    Sreejith S Nair
    Associate Systems Architect | AOS DBA Team
    IBS Software Services Private Ltd.
    2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
    India
    ((Direct) +91 471 661 4707 ) +91 808 648 5523
    *sreejith.sreekantan@ibsplc.com
    8 www.ibsplc.com





    DISCLAIMER:

    "The information in this e-mail and any attachment is intended only for
    the person to whom it is addressed and may contain confidential and/or
    privileged material. If you have received this e-mail in error, kindly
    contact the sender and destroy all copies of the original communication.
    IBS makes no warranty, express or implied, nor guarantees the accuracy,
    adequacy or completeness of the information contained in this email or any
    attachment and is not liable for any errors, defects, omissions, viruses
    or for resultant loss or damage, if any, direct or indirect."






    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Gaja Krishna Vaidyanatha at Apr 5, 2012 at 6:17 am
    Hi Sreejith,
    Just as I suspected! Given the 2 wait events that I you have mentioned - 'Cursor pin wait on X' and 'Library Cache Mutex-X' and the fact that AMM is enabled, I am pretty certain that the issue that you are facing is caused due to memory stealing. Did you check out the number or resize operations during the problem period? Rather than increasing the SGA, try to find a steady state for all the memory structures. Analyze and determine the minimum sizing for each of the memory structures (various BUFFER CACHES (if relevant), SHARED_POOL, LARGE_POOL and JAVA_POOL if used).Â

    These are the times where I believe "too much automation" in database administration actually hurts. May be I am from the old school of Oracle Database Administration, but I still believe that SGA sizing  & PGA sizing needs to be a DBA's job and memory settings should be set manually (in most cases) by using the relevant "Memory Advisor Statistics". These statistics are clearly documented in every AWR report. I am a big fan of stability with the compromise of a little over-allocation of memory versus instability and inconsistent performance with automation. If you don't have the relevant bug fixes, I'd go the manual route, because the increase in SGA recommendation will be just a temporary Bandaid and it will be only a matter of time before it gives and falls apart.

    Moral of the Story - In most tuning exercises, start with the Wait Events. Instance Efficiency Percentages (erstwhile known as Cache Hit Ratios) don't lead you to the root cause. Wait Events do! And when Wait Events don't low-level OS traces do (i.e. dtrace, truss, pstack...etc). May it is time for me to reiterate something from 2000 - Let Compulsive Tuning Disorder (CTD) not get the better of you...:)))
    Â
    Cheers,

    Gaja

    Gaja Krishna Vaidyanatha,
    CEO & Founder, DBPerfMan LLC
    http://www.dbperfman.com
    http://www.dbcloudman.com

    Phone - +1-650-743-6060
    http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID14
    Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
    Enabling Cloud Deployment & Management for Oracle Databases


    ________________________________
    From: Sreejith S Nair <sreejithsna@gmail.com>
    To: "gajav@yahoo.com" <gajav@yahoo.com>
    Cc: "oracle-l@freelists.org" <oracle-l@freelists.org>
    Sent: Wednesday, April 4, 2012 6:33 PM
    Subject: Re: Instance Efficiency Percentages - Library Hit %: in AWR

    Hi Gaja ,

    Many thanks for the hints.
    Yes, we have experienced severe system slowness during this period.  I have checked DBA hist active session history and I can see that for the problem time window 'Concurrency' wait ever came all of a sudden. This is happening only in one instance. Top foreground timed events being 'Cursor pin wait on X' and 'Library Cache Mutex-X' waits. A severity 1 SR is raised with Oracle for this.

    There are bugs related to this 'mutex' , SQL high version count , which in fortunately I not available for our platform (Solaris x86 11.2.0.2)

    Other recommendations were to increase sag , increase interval between memory resize operations ( yes we use AMM) , gathering dictionary and fixed object statistics.

    The performance was really bad so that we see the Library Hit % being 1.7,0.8,-0.87 etc. I have analysed past trend which shows the % coming down.

    The ADDM for problem window recommends some session holding a mutex lock and some others waiting for it which contributes 30% of the activity.
    The SQLs reported were not so bad a these were critical ones which normally works not so bad.

    The activities that I see in this time on database are the statistics collection jobs , which were running. Not sure whether this has contributed a pressure in Library cache.

    After we increased sga it's getting to normal state. But due to a (possible) bug I feel this will come again.

    Regards,
    Sreejith
    -- Sent from my iPhone
    On 05-Apr-2012, at 2:26 AM, Gaja Krishna Vaidyanatha wrote:

    Hi Sreejith,
    Although I can't help you directly on the SQL you requested, but once you do start trend this phenomenon, here are a few things that will provide food for investigation and thought:

    1) What the are Top 5 Wait Events when this Instance Efficiency Percentage is low?�
    2) Which SQL statements are being negatively affected by the Top 5 Wait Events? By how much?�
    3) Is there a problem on your database for the period of the snapshot(s)?�
    4) How does this snapshot's workload compare to a baseline snapshot?
    5) Is there evidence of some sort of abnormal application workload?�
    6) Did you have more parsing than normal?�

    7) What caused the increased parsing (if applicable)?�
    8) Or was the increased parsing due to automatic memory management stealing from the shared pool to feed the db buffer cache in response to an increase I/O load?

    �

    Cheers,

    Gaja

    Gaja Krishna Vaidyanatha,
    CEO & Founder, DBPerfMan LLC
    http://www.dbperfman.com
    http://www.dbcloudman.com

    Phone -�+1-650-743-6060
    http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table -�http://www.apress.com/book/bookDisplay.html?bID14
    Co-author:Oracle Performance Tuning 101 -�http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
    Enabling Cloud Deployment & Management for Oracle Databases


    ________________________________
    From: Sreejith S Nair <Sreejith.Sreekantan@ibsplc.com>
    To: oracle-l@freelists.org
    Sent: Wednesday, April 4, 2012 2:39 AM
    Subject: Instance Efficiency Percentages - Library Hit %: in AWR

    Hi Friends,
    In AWR under Instance Efficiency Percentages we see a Library Hit % and we
    find it very low for one snapshot. I would like to know the trend for few
    more days available in AWR snapshots
    (dba_hist_snapshot,DBA_HIST_LIBRARYCACHE) using SQL Query.

    Can some one please help with how this % is being calculated. What SQL can
    I use to calculate this ? May be for a range of snapshot. Thanks in
    Advance.

    11.2.0.2.0 on Solairs 10 .

    With Regards,
    Sreejith

    --
    Sreejith S Nair
    Associate Systems Architect | AOS DBA Team
    IBS Software Services Private Ltd.
    2nd Floor, IBS Campus, Technopark Campus, Trivandrum - 695 581, Kerala,
    India
    ((Direct) +91 471 661 4707 ) +91 808 648 5523
    *sreejith.sreekantan@ibsplc.com
    8 www.ibsplc.com





    DISCLAIMER:

    "The information in this e-mail and any attachment is intended only for
    the person to whom it is addressed and may contain confidential and/or
    privileged material. If you have received this e-mail in error, kindly
    contact the sender and destroy all copies of the original communication.
    IBS makes no warranty, express or implied, nor guarantees the accuracy,
    adequacy or completeness of the information contained in this email or any
    attachment and is not liable for any errors, defects, omissions, viruses
    or for resultant loss or damage, if any, direct or indirect."






    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Lei Zeng at Apr 6, 2012 at 12:20 am
    Sreejith:

    For your original question, the following query can help you calculate the library cache hit ratio over the history AWR data:

    WITH x AS (
    SELECT DBID, INSTANCE_NUMBER, SNAP_ID, SUM(PINS) pins, SUM(RELOADS) reloads
    FROM DBA_HIST_LIBRARYCACHE
    WHERE DBID=? AND INSTANCE_NUMBER=? AND SNAP_ID BETWEEN ? AND ?
    GROUP BY DBID, INSTANCE_NUMBER, SNAP_ID
    ),
    y AS (
    SELECT DBID, INSTANCE_NUMBER,
    LAG(SNAP_ID, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) begin_snap_id, SNAP_ID end_snap_id,
    LAG(PINS, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) begin_pins, PINS end_pins,
    LAG(RELOADS, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) begin_reloads, RELOADS end_reloads
    FROM x
    )
    SELECT DBID, INSTANCE_NUMBER, begin_snap_id||'-'||end_snap_id snap_id,
    ROUND (((end_pins-begin_pins) - (begin_reloads-end_reloads))*100 /(end_pins-begin_pins)) library_hit_ratio
    FROM y
    WHERE y.begin_snap_id IS NOT NULL

    My opinion is that this metric itself doesn't tell much about database performance issue. Each database has its own characteristic and there is no absolute threshold value for this metrics.
    I have seen some databases with low library hit ratio but still can perform its job duty well.

    As shown in DBspeed database performance tuning tool, I would like check for those dimensions (variables which play roles in database performance issue) for more tuning strategies, such as top SQL, top wait event, top session, hot objects, hot files, etc. Some examples in http://www.dbspeed.com/case_study.html

    Regards,
    Lei
    DBspeed http://www.dbspeed.com/index.html
    data mining AWR & ASH data for database performance tuning

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 4, '12 at 9:40a
activeApr 6, '12 at 12:20a
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase