Full Table Scan in Oracle 11g - Part 1

Common things which might strike your mind the moment you hear Full Table Scan (FTS) are

• Why my index is not used
• How to avoid the full table scan
• Why Oracle optimizer uses full table scan
• selecting more data hence full table scan's are good.
• Full table scan means "db file scattered read" (db file scattered read - Means that oracle reads the blocks using multiblock read count indicator, which looks in a scattered fashion)

          But today we will see a new approach taken by Full table scan's in Oracle 11g.

Lets start with very simple tables like below,

Big Table
create table tbl_fts_pr (id number,id_ch char(100),id_vh varchar2(100),id_char1 char(1000));
insert into tbl_fts_pr
Select Rownum, 'fts','fts','fts' From Dual Connect By Level < 100000;
insert into tbl_fts_pr
Select Rownum, 'fts','fts','fts' From Dual Connect By Level < 100000;
commit;

Small Table
create table tbl_fts_lr (id number,id_ch char(100),id_vh varchar2(100));
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
Insert Into Tbl_Fts_lr
Select Rownum, 'fts','fts' From Dual Connect By Level < 100000;
commit;

exec dbms_stats.gather_table_stats('HARI','TBL_FTS_PR');
exec dbms_stats.gather_table_stats('HARI','TBL_FTS_LR');

Now a simple query leading to full table scan like below on the above 2 tables,

Query

    

          This is just the execution plan from autotrace. Things look quite normal here at this stage, lets get into the second part of the autotrace- Statistics for both the queries

Autotrace- Statistics

Note the physical reads between them. Now you can see a clear differentiation in the FTS - strategy for data retrivial. Lets see further to check the actual volume of data in units of blocks to be processed to perform this FTS,                                                                                                                                  

Block Details

 *_PR table's average row length is ten times that of *_LR table and blocks are also seven times bigger.
                                   





                       
                                  Lets dig more to see the actual methodology imposed to perform block reads and the time consumed in retrieving them.

Tkprof Details- TBL_FTS_LR





                                                                                                                                                                                
_*LR behaviour is expected and the blocks are read in "db file scattered read" fashion which is what we have seen so far for FTS.  Lets see the other query's tkprof output                                                                                                                                                                                                     
                      

                                                                                                                                                               







Tkprof Details- TBL_FTS_PR


_*PR has more physical reads and elapsed time is relatively less in comparision with _*LR's stats.

Now take a look at the wait events. Where is my "db file scattered read", a full table scan without a db file scattered read is not seen so far. Oracle has chosen to go with "direct path read" and yes has proven to be faster in this case where we have more (Will see what is more in the coming post) number of blocks to be processed.  

Conclusion

            On account of FTS, Oracle 11g does its calculation on the number of blocks impacted in satisfying a query and decides the access method which can be traditional one sequential read plus n number of scattered read OR direct path read. Now with the above inference can we conclude like this 

• FTS on big tables are faster
• FTS on big tables with LESS MEMEORY are FASTER
• FTS can bypass logical reads

 Or can we also conclude like below

•  FTS on big tables with MORE MEMORY are SLOWER

Is this a bug or a feature, can this make my queries run faster in Test Env with less memory  and run slower in Production with more Memory, Yes metalink says that too "Full Table Scan May be Slower In Active Production Environment than Test [ID 1457693.1]"