Re: table does appeared in v$bh when explain show blocks was read from cache ?

tedchyn_at_yahoo.com (Ted Chyn) wrote in message news:<44a19320.0405260612.2b96b0a9_at_posting.google.com>...
> 1. ran sql with trace on, table is in cache buffer because logical read is
> around 30k and physcal read = 0 (see below).
> 2. when I look into v$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 ###===

Ted,

Try joining bh.objd with dba_objects.data_object_id. If your table is MOVE'd or TRUNCATE'd, then its data_object_id will be incremented while object_id remains the same.

Yong Huang Received on Wed May 26 2004 - 13:23:35 CDT