How oracle desides if he should use full table scan:
It's all about the information stored in the header. All the information about the number of extents,block and highwater mark.
To see that info, you can do the dump of the block:
SYS@SODS1 SQL> alter system dump datafile 46 block 130;
System altered.
In the trace file, you can see the the section Extent Control Header. there you can see the number of extents,blocks and highwater mark:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1647 #blocks: 13176
last map 0x0b8029d8 #maps: 3 offset: 2716
Highwater:: 0x0b8033f5 ext#: 1646 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 13173
mapblk 0x0b8029d8 offset: 323
Unlocked
--------------------------------------------------------
to check the tracefile name, you can use:
SYS@SODS1 SQL> def trc
DEFINE TRC = "/oracle/oraspre/diag/rdbms/sods/SODS1/trace/SODS1_ora_109335.trc" (CHAR)
According to the value of the #blocks below: 13173, the size of buffer cache and allready buffered blocks of that table, oracle desides whether to use direct path read or buffer full table scan.
All the informations about the segments headers are stored in the seg$ table. The information about the seg$ itself is stored in the sys.bootstrap$ segment.
You can see similar tables in the sys.bootstrap$ table:
MNISCAK@SODS1 SQL> select * from sys.bootstrap$
CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
.
.
CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NULL,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LISTS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NOT NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK#)
.
.
If you wan to query the orders table, here is the way the oracele knows where it resides:
File#1 header block (bootstrap DBA#) -> bootstrap$ -> seg$ -> orders
To see that info, you can do the dump of the block:
SYS@SODS1 SQL> alter system dump datafile 46 block 130;
System altered.
In the trace file, you can see the the section Extent Control Header. there you can see the number of extents,blocks and highwater mark:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1647 #blocks: 13176
last map 0x0b8029d8 #maps: 3 offset: 2716
Highwater:: 0x0b8033f5 ext#: 1646 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 13173
mapblk 0x0b8029d8 offset: 323
Unlocked
--------------------------------------------------------
to check the tracefile name, you can use:
SYS@SODS1 SQL> def trc
DEFINE TRC = "/oracle/oraspre/diag/rdbms/sods/SODS1/trace/SODS1_ora_109335.trc" (CHAR)
According to the value of the #blocks below: 13173, the size of buffer cache and allready buffered blocks of that table, oracle desides whether to use direct path read or buffer full table scan.
All the informations about the segments headers are stored in the seg$ table. The information about the seg$ itself is stored in the sys.bootstrap$ segment.
You can see similar tables in the sys.bootstrap$ table:
MNISCAK@SODS1 SQL> select * from sys.bootstrap$
CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
.
.
CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NULL,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LISTS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NOT NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK#)
.
.
If you wan to query the orders table, here is the way the oracele knows where it resides:
File#1 header block (bootstrap DBA#) -> bootstrap$ -> seg$ -> orders