FULL TABLE SCAN FOR SMALL TABLES MORE EXPENSIVE IN ASSM TABLESPACE (Doc ID 296913.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.

Goal

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.

For example,

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.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms