FULL TABLE SCAN FOR SMALL TABLES MORE EXPENSIVE IN ASSM TABLESPACE
Last updated on SEPTEMBER 15, 2016
Applies to:Oracle Server - Enterprise Edition - Version: 126.96.36.199
Information in this document applies to any platform.
A full table scan for a small table accesses more buffers when the table is built in an ASSM tablespace than when it is not.
Two tablespaces, one ASSM and another non-ASSM and each has a table :
create tablespace manual datafile 'd:\datafiles\manu.dbf' size 1m extent management local autoallocate segment space management manual ;
create tablespace auto datafile 'd:\datafiles\auto.dbf' size 1m extent management local autoallocate segment space management auto ;
create table manual (n number) tablespace manual;
create table auto(n number) tablespace auto;
Inserted few rows into the tables and then scanned .
Event 10046 trace file shows the output as :
Result of tests with increasing number of rows :
Rows Extents Blocks BuffersASSM BuffersNonASSM
1 1 8 7 3
100 1 8 7 4
1000 1 8 7 4
5000 2 16 16 15
Up to 1000 rows there is a very significant differences in the buffer gets per full table scan.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms