The V$SESS_IO view records the cumulative logical reads and physical reads
performed for each user??™s session. You can query V$SESS_IO for each session??™s hit ratio, as shown
in the following listing:
select SESS.Username,
SESS_IO.Block_Gets,
SESS_IO.Consistent_Gets,
SESS_IO.Physical_Reads,
round(100*(SESS_IO.Consistent_Gets
+SESS_IO.Block_Gets-SESS_IO.Physical_Reads)/
(decode(SESS_IO.Consistent_Gets,0,1,
SESS_IO.Consistent_Gets+SESS_IO.Block_Gets)),2)
session_hit_ratio
from V$SESS_IO sess_io, V$SESSION sess
where SESS.Sid = SESS_IO.Sid
and SESS.Username is not null
order by Username;
To see the objects whose blocks are currently in the data block buffer cache, query the X$BH
table in SYS??™s schema, as shown in the following query (note that the SYS and SYSTEM objects are
excluded from the output so the DBA can focus on the application tables and indexes present in
the SGA):
select Object_Name,
Object_Type ,
count(*) Num_Buff
from X$BH a, SYS.DBA_OBJECTS b
where A.Obj = B.Object_Id
and Owner not in ('SYS','SYSTEM')
group by Object_Name, Object_Type;
NOTE
You can query the Name and Kind columns from V$CACHE to see
similar data if you are not connected as the SYS user.
254 Oracle Database 11g DBA Handbook
There are multiple cache areas within the data block buffer cache:
The DEFAULT cache This is the standard cache for objects that use the default database
block size for the database.
The KEEP cache This is dedicated to objects you wish to keep in memory at all times.
Pages:
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427