- ran sql with trace on, table is in cache buffer because logical read is
around 30k and physcal read = 0 (see below).
- when I look into v_at_bh view, I can not find the table in cache ?
can you shed light on this. I am the only user in the database.
thanks a lot
ted chyn
SQL> set autotrace on
SQL> select * from rdidev.patient
where (PATIENT_ACCT like '%194325095%')
2 3
SQL> /
PATIENT_ACCT FACIL PATIENT_LST_NME PATIENT_FS PATIENT_MI
------------------- ----- -------------------- ---------- ----------
ADDRESS_1
ADDRESS_2
ADDRESS_3
CITY ST ZIP CTRY_NME
---------------------------------------- -- ----------- --------------------
LAST_ACT_DA DFN DATE_LOADED
----------- ---------------- -----------
691194325095EVANS 691 EVANS FRANK H
430 E SAN JOSE AVE
BURBANK CA 91501
28-FEB-2004 29-FEB-2004
1 row selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2105 Card=119100 Byt
es=10957200)
1 0 TABLE ACCESS (FULL) OF 'PATIENT' (Cost=2105 Card=119100 By
tes=10957200)
Statistics
0 recursive calls
0 db block gets
34296 consistent gets
0 physical reads ===#####note physical read is 0 #####
0 redo size
1499 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> column object_name format a40
column number_of_blocks format 999,999,999,999
select o.object_name,count(1) number_of_blocks
from dba_objects o, v$bh bh
where o.object_id=bh.objd
and o.owner !='SYS'
group by o.object_name
order by count(1)
7 /
OBJECT_NAME NUMBER_OF_BLOCKS
---------------------------------------- ----------------
REPCAT$_REPPROP 1
AQ$_QUEUES 2
PW_EXPIRE 14
3 rows selected. ===#### note no patient blocks are selected here ###===
Received on Wed May 26 2004 - 09:12:18 CDT