Oracle Cloud Infrastructure
Coming soon ...
Apps R12.2
  • R12.2 Architecture
  • Cloning Error (RC-50208)
  • Apps R12.1
  • Major changes from 11i
  • R12:HTTP Debug Logging
  • Compile Apps Schema invalid objects in R12
  • Apps 11i
  • Apps Components and Architecture
  • Concurrent managers
  • Patching
  • Using AD Patch
  • Using AD Control
  • FNDCPASS utility
  • Single-node Installation
  • Multi-node Installation
  • Find Apps Version
  • Cloning
  • Upgrade 11.5.9 to 11.5.10.2
  • Upgrade from 11.5.10.2 to R12
  • Upgrading 9i to 10g with 11i
  • 11i/R12 General Topics
  • AppsDBA System Management Utilities Guide
  • Identifying Long Idle Sessions
  • Identifying High Active Sessions
  • Change hostname for EBS
  • Oracle 12c Database
  • Oracle12c PSU Apply
  • Oracle12c Datafile moved Online
  • Oracle 11g Database
  • Upgrade 10g to 11g R1
  • Upgrade 11.2.0.2 to 11.2.0.3
  • Database 9i-10g
  • Top 99 Responsibilities of a DBA
  • General Info
  • Database Patching
  • 10g:ASM
  • 10g:Data Pump
  • 10g:Data Guard Installing
  • 10g:Rollback Monitoring
  • 10g:Flashback Table
  • Tablespace Management
  • Materialized Views
  • 10g:Enterprise Manager
  • 10g:Upgrade
  • Error:Ora-01631
  • DBA Scripts
  • Disk I/O,Events,Waits
  • Tablespace Information
  • Session Statistics
  • Hit/Miss Ratios
  • User Information
  • Rollback Segments
  • Full Table Scans
  • Contention/Locking
  • Redo Log Buffer
  • Data Dictionary Info
  • Oracle10g Application Server
  • Oracle10g Application Installation
  • (Re)Securing OAS Control
  • Oracle AS10g null protocol issue
  • Oracle Backup & Recovery
  • RMAN
  • RMAN Setup
  • Backup Recovery
  • Flash Recovery Area
  • Oracle10g Discoverer with Apps
    Coming soon ..............
    Troubleshooting
  • Discoverer Troubleshooting
  • Access EBS in mozile
  • Linux and Unix Platforms
  • How To configure NFS
  • Unix/Linux Command
  • Change hostname in Linux
  • SENDMAIL configuration
  • This Oracle Application DBA Portal is the biggest knowledge gateway for the people in the world of Oracle...
    Monday, June 22, 2009
    Hit/Miss Ratios
    Hit/Miss Ratios
    Here are some scripts related to Hit/Miss Ratios .

    Buffer Hit Ratio
    BUFFER HIT RATIO NOTES:


    Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
    DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
    Physical Reads - The cumulative number of blocks read from disk.

    Logical reads are the sum of consistent gets and db block gets.
    The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
    Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

    select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
    sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
    sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
    round((sum(decode(name, 'consistent gets',value, 0)) +
    sum(decode(name, 'db block gets',value, 0)) -
    sum(decode(name, 'physical reads',value, 0))) /
    (sum(decode(name, 'consistent gets',value, 0)) +
    sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
    from v$sysstat

    Data Dict Hit Ratio
    DATA DICTIONARY HIT RATIO NOTES:


    Gets - Total number of requests for information on the data object.
    Cache Misses - Number of data requests resulting in cache misses

    Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

    select sum(GETS),
    sum(GETMISSES),
    round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
    from v$rowcache

    SQL Cache Hit Ratio
    SQL CACHE HIT RATIO NOTES:


    Pins - The number of times a pin was requested for objects of this namespace.
    Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

    Hit Ratio should be > 85%

    select sum(PINS) Pins,
    sum(RELOADS) Reloads,
    round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
    from v$librarycache

    Library Cache Miss Ratio
    LIBRARY CACHE MISS RATIO NOTES:


    Executions - The number of times a pin was requested for objects of this namespace.
    Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

    Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

    select sum(PINS) Executions,
    sum(RELOADS) cache_misses,
    sum(RELOADS) / sum(PINS) miss_ratio
    from v$librarycache

    Labels:

    posted by Srinivasan .R @ 1:12 AM  
    0 Comments:
    Post a Comment
    << Home
     
    About Me

    Name: Srinivasan .R
    Home: Chennai, India

    About Me:
    I am working as an Oracle Applications DBA specializing in EBS 11i/R12 with Over 14+ years of experience, mainly in different versions of Oracle Database & Application administration on various platforms like HP-UX, SOLARIS, AIX, Red hat Linux & Windows
    See my complete profile
    High Availability
  • Oracle10g RAC Installation
  • A Quick Reference for Oracle Database 10g RAC on Linux and Unix Platforms
  • Implementing Oracle 10g RAC with ASM on AIX
  • Locked objects for whole RAC
  • Monitor Memory RAC
  • Sessions RAC
  • Install Oracle 11g RAC On Linux
  • Migrating Oracle10g DB to ASM
  • Helpful Links
  • Good Metalink Notes
  • Discoverer:Metalink Notes
  • Logs Scripts:Metalink Notes
  • Support:Metalink Notes
  • Previous Post
  • Session Statistics
  • Tablespace Information
  • Disk I/O, Events, Waits
  • General Info
  • Migrating Oracle10g DB to ASM
  • Install Oracle 11g RAC On Linux
  • Sessions RAC
  • Monitor Memory RAC
  • Lists all locked objects for whole RAC
  • Concurrent Managers
  • Archives
    Download Software
  • Oracle 11g
  • Oracle 10g
  • 10g Express Edition
  • Oracle 9i
  • Oracle Apps
  • Oracle Linux
  • Oracle VM
  • App Server
  • Solaris
  • Fedora
  • Fedora
  • OpenSUSE
  • Ubuntu
  • Advertisement Links
    INTUIT Technology

    MACHS DATA

    Add Ons
    Locations of visitors to this page

    Add to Google Reader or Homepage

    Template by
    Sreene



    Oracle Application DBA Portal