hey -- if you use them, you won't lose them -- if you scan them lots, you know where they will be? in the buffer cache.

you can use the v$ segment statistics view to see what segments you are doing lots of physical IO against, you would see if they make sense for special consideration.

Thanks -- appreciate the input always...

@connect /

drop table t;

create table t pctfree 90 pctused 10 as select * from all_users where 1=0;
insert into t select * from all_users;
select distinct dbms_rowid.rowid_block_number(rowid) from t;

begin
for i in 1 .. 10
loop
for x in ( select * from t )
loop
null;
end loop;
end loop;
end;
/
set autotrace traceonly statistics
exec dbms_lock.sleep(3)
select * from t;
exec dbms_lock.sleep(3)
select * from t;
exec dbms_lock.sleep(3)
select * from t;
exec dbms_lock.sleep(3)
select * from t;

column data_object_id new_val d
select data_object_id from user_objects where object_name = 'T';
@connect "/ as sysdba"
select file#, dbablk, tch from x$bh where obj = &D;
@connect /



I get this:

old 1: select file#, dbablk, tch from x$bh where obj = &D
new 1: select file#, dbablk, tch from x$bh where obj = 36320

FILE# DBABLK TCH
---------- ---------- ----------
12 9 4
12 10 1
12 11 1


(9206)

table fetch by rowid typically comes from an index range scan. We get a rowid and read a single row from the table


table fetch continued row is a row that was fetched (be it by rowid or scanning) that either didn't fit on a block or in the case of a rowid fetch - has migrated. It does not MEAN the row was migrated, it could be that the row is just too large to fit on a single block.

table scan blocks/rows gotten - self defining, just counters of rows/blocks retrieved during full scans


direct read scans are full scans that bypass the buffer cache (much like parallel query always used to to - but in 11g parallel query might not do anymore - it depends). We read directly from disk into your pga to process the data, the buffer cache is deemed far too inefficient.

table scans long tables - big tables that were full scanned and placed into the buffer cache in a manner so as to not fill up the buffer cache. Typically as you are scanning the table - you are using a very very small set of blocks in the buffer cache over and over - so the table basically overwrites itself in the cache. This prevents a 5gb full table scan from obliterating a 4gb db cache size. That 5gb scan would only use a very small section of the buffer cache.

table scans rowid ranges - self describing, scanning a portion of a table based on rowid ranges.


table scans short tables - table is deemed small enough to fit comfortably in the buffer cache so unlike the long table full table scan - it does not overwrite itself in the cache.


the method for determining a small table is constantly being revised - so the process is a little different from the 7.x days but the net result is the same. Consider any table greater than 5% the size of the cache to be a large table an anything less is probably small.