-
-- 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
-
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)
-
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!!
-
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 ..)
