Oracle User Cache Hit Ratio Report
Displays cache hit ratio statistics per user session, including consistent gets, block gets, physical reads, and calculated hit ratio percentage. Useful for analyzing user-level buffer cache efficiency and workload behavior.
oraclesqlperformance-tuningv1.0.0
0 stars0 downloads16 views0 comments
By OracleDba • Created
Code
(22 lines)12345678910111213141516171819202122
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/user_hit_ratio.sql
-- Author : Tim Hall
-- Description : Displays the Cache Hit Ratio per user.
-- Requirements : Access to the V$ views.
-- Call Syntax : @user_hit_ratio
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
COLUMN "Hit Ratio %" FORMAT 999.99
SELECT a.username "Username",
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;