Cache Hit Ratios Per User
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Cache Hit Ratios Per User

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    -- Displays Cache Hit Ratios Per User
    SELECT substr(a.username,1,10) "Username",a.osuser,
    b.consistent_gets "Consistent Gets",
    b.block_gets "DB Block Gets",
    b.physical_reads "Physical Reads",
    Round(100* (b.consistent_gets + b.block_gets - b.physical_reads) /
    (b.consistent_gets + b.block_gets),2) "Hit Ratio %"
    FROM v$session a,
    v$sess_io b
    WHERE a.sid = b.sid
    AND (b.consistent_gets + b.block_gets) > 0
    AND a.username IS NOT NULL;

    Can anyone explain what is Consistent Gets and DB Block gets ? I tried looking around, but did not get precise information.. The script is supposed run twice at certain intervals to determine the result based on delta..

    Thanks, ST2000

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    db block gets --> number of times when oracle read block (for update as a rule)
    +
    when segment header blok was read

    consistent gets --> # of times when a consistent read was requested for a block. (or more exactly --> # of block accesses that involved SCN checks)

  3. #3
    Join Date
    Jun 2001
    Posts
    316
    readin the docs...i came down to this conclusion!!

    consistent gets!!!

    when a query is executed...its given a SCN....n suppose just after that.. another update on the record has been done..with a SCN greater than the SCN of the query.

    Now when the query is executed,headers of each block is read...n the 1es with SCN less than the current 1(of the query) is read...n the blocks with SCN greater is omitted.

    so consistent gets....is when none of the read blocks for the query have SCN greater than the SNC of the query!!!

    Pls correct me if i am wrong!!

    thanx!!

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    YES this is correct.
    When oracle try to found buffer in cache then:
    if data block address exists in cache and
    if block is dirty and
    if buffer SCN > then requesting SCN
    THEN oracle have to allocate new db block in cache
    and in this case oracle increase statistics CONSISTENT GETS or DB BLOCK GETS
    (depend from sql operation --> select, select ... for update, update ..)



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