AUTOEXTEND ON Next Size

UPDATE 28-Feb-11 : Apparently, in 11gR1, Bug 8318050 affects the behaviour of Autoextend On datafiles such that the NEXT size specified may not be honoured. See Oracle Support Article#8318050.8

In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component.

In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace).

But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater.

Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occassion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.

In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get's created with both the initial and increment at 100MB and AutoExtend ON. However, for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile. If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not specify the incremental size, Oracle defaults it to 1 Oracle Block.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME                                               FILE_SIZE_MB AUT INCREMENT_BY
--------------- ------------------------------------------------------- ------------ --- ------------
SYSTEM          /oracle_fs/Databases/ORT24FS/system01.dbf                        590 YES        1,280
UNDOTBS1        /oracle_fs/Databases/ORT24FS/undotbs01.dbf                       155 YES          640
SYSAUX          /oracle_fs/Databases/ORT24FS/sysaux01.dbf                        270 YES        1,280
USERS           /oracle_fs/Databases/ORT24FS/users01.dbf                          85 YES          160
EXAMPLE         /oracle_fs/Databases/ORT24FS/example01.dbf                       100 YES           80
TEST_A_TBS      /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf                      100 NO             0

6 rows selected.

SQL> alter system set db_create_file_dest='/var/tmp';

System altered.

SQL> create tablespace an_omf_tbs;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME                                               FILE_SIZE_MB AUT INCREMENT_BY
--------------- ------------------------------------------------------- ------------ --- ------------
SYSTEM          /oracle_fs/Databases/ORT24FS/system01.dbf                        590 YES        1,280
UNDOTBS1        /oracle_fs/Databases/ORT24FS/undotbs01.dbf                       155 YES          640
SYSAUX          /oracle_fs/Databases/ORT24FS/sysaux01.dbf                        270 YES        1,280
USERS           /oracle_fs/Databases/ORT24FS/users01.dbf                          85 YES          160
EXAMPLE         /oracle_fs/Databases/ORT24FS/example01.dbf                       100 YES           80
TEST_A_TBS      /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf                      100 NO             0
AN_OMF_TBS      /var/tmp/ORT24FS/datafile/o1_mf_an_omf_t_541wgb0c_.dbf           100 YES       12,800

7 rows selected.

SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf';
create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'
ORA-17610: file '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' does not exist and no size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' size 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME                                               FILE_SIZE_MB AUT INCREMENT_BY
--------------- ------------------------------------------------------- ------------ --- ------------
SYSTEM          /oracle_fs/Databases/ORT24FS/system01.dbf                        590 YES        1,280
UNDOTBS1        /oracle_fs/Databases/ORT24FS/undotbs01.dbf                       155 YES          640
SYSAUX          /oracle_fs/Databases/ORT24FS/sysaux01.dbf                        270 YES        1,280
USERS           /oracle_fs/Databases/ORT24FS/users01.dbf                          85 YES          160
EXAMPLE         /oracle_fs/Databases/ORT24FS/example01.dbf                       100 YES           80
TEST_A_TBS      /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf                      100 NO             0
AN_OMF_TBS      /var/tmp/ORT24FS/datafile/o1_mf_an_omf_t_541wgb0c_.dbf           100 YES       12,800
REGULAR_TBS     /oracle_fs/Databases/ORT24FS/regular_tbs.dbf                     100 NO             0

8 rows selected.

SQL> create tablespace ANOTHER_TBS datafile '/oracle_fs/Databases/ORT24FS/another_tbs.dbf' size 100M autoextend on ;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME                                               FILE_SIZE_MB AUT INCREMENT_BY
--------------- ------------------------------------------------------- ------------ --- ------------
SYSTEM          /oracle_fs/Databases/ORT24FS/system01.dbf                        590 YES        1,280
UNDOTBS1        /oracle_fs/Databases/ORT24FS/undotbs01.dbf                       155 YES          640
SYSAUX          /oracle_fs/Databases/ORT24FS/sysaux01.dbf                        270 YES        1,280
USERS           /oracle_fs/Databases/ORT24FS/users01.dbf                          85 YES          160
EXAMPLE         /oracle_fs/Databases/ORT24FS/example01.dbf                       100 YES           80
TEST_A_TBS      /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf                      100 NO             0
AN_OMF_TBS      /var/tmp/ORT24FS/datafile/o1_mf_an_omf_t_541wgb0c_.dbf           100 YES       12,800
REGULAR_TBS     /oracle_fs/Databases/ORT24FS/regular_tbs.dbf                     100 NO             0
ANOTHER_TBS     /oracle_fs/Databases/ORT24FS/another_tbs.dbf                     100 YES            1

9 rows selected.

SQL>  

You would be well-adviced to remember that when you create or add a non-OMF datafile, you should specify the Increment size with the AutoExtend ON. Else, you might suffer the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)

SQL> create tablespace LAST_TBS datafile '/oracle_fs/Databases/ORT24FS/last_tbs.dbf' size 100M autoextend on next 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME                                               FILE_SIZE_MB AUT INCREMENT_BY
--------------- ------------------------------------------------------- ------------ --- ------------
SYSTEM          /oracle_fs/Databases/ORT24FS/system01.dbf                        590 YES        1,280
UNDOTBS1        /oracle_fs/Databases/ORT24FS/undotbs01.dbf                       155 YES          640
SYSAUX          /oracle_fs/Databases/ORT24FS/sysaux01.dbf                        270 YES        1,280
USERS           /oracle_fs/Databases/ORT24FS/users01.dbf                          85 YES          160
EXAMPLE         /oracle_fs/Databases/ORT24FS/example01.dbf                       100 YES           80
TEST_A_TBS      /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf                      100 NO             0
AN_OMF_TBS      /var/tmp/ORT24FS/datafile/o1_mf_an_omf_t_541wgb0c_.dbf           100 YES       12,800
REGULAR_TBS     /oracle_fs/Databases/ORT24FS/regular_tbs.dbf                     100 NO             0
ANOTHER_TBS     /oracle_fs/Databases/ORT24FS/another_tbs.dbf                     100 YES            1
LAST_TBS        /oracle_fs/Databases/ORT24FS/last_tbs.dbf                        100 YES       12,800

10 rows selected.

SQL>  

Tablespace LAST_TBS's datafile is created with a meaningful Increment for AutoExtend.

.
.
.

Why EXPLAIN PLAN should not be used with Bind Variables

Oracle's EXPLAIN PLAN feature is very (far too) frequently used. It is available in three ways :
1. With "SET AUTOTRACE" in SQLPlus
2. With the command-line option "EXPLAIN=username/password" when running tkprof against a Trace File
3. Explicitly as "EXPLAIN PLAN FOR ...."

However, not enough people know (and, frankly I didn't till a few years ago !) that Explain Plan ignores Bind Variables. You can pass an SQL statement that uses Binds to an EXPLAIN but it the command will ignore the Bind -- it effectively ignores Data Skew. Since Bind Variable Peeking was introduced in 9i, *runtime* SQL does *not* ignore Bind Variables (that there are other issues with Bind Variable Peeking is, so to say, being minimalist ; but let's put aside those issues for the time being).
What this means is that while the Runtime Execution Plan may be plan 'A', EXPLAIN PLAN may "incorrectly" show you plan 'B'.

Here is a simple demonstration of the difference :

I first setup my test data :

SQL> create table demo_explain_bind
2  (owner  varchar2(30) not null , object_name varchar2(30) not null , created date)
3  /
SQL>
SQL> alter table demo_explain_bind nologging;
SQL>
SQL> insert /*+ APPEND */ into demo_explain_bind
2  select owner, object_name, created from dba_objects where object_id is not null
3  union all
4  select owner, object_name, created from dba_objects where object_id is not null
5  union all
6  select owner, object_name, created from dba_objects where object_id is not null
7  union all
8  select owner, object_name, created from dba_objects where object_id is not null
9  /
SQL>
SQL> create index demo_explain_bind_ndx on demo_explain_bind (owner) nologging;
SQL>
SQL> exec dbms_stats.gather_table_stats('','DEMO_EXPLAIN_BIND',-
> estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);
SQL>
SQL> select /*+ FULL (d) */ count(*) from demo_explain_bind d;

COUNT(*)
----------
202492
SQL> select count(*) from demo_explain_bind where owner = 'SYS';

COUNT(*)
----------
91752
SQL> select count(*) from demo_explain_bind where owner = 'HEMANT';

COUNT(*)
----------
72
SQL> select count(distinct(owner)) from demo_explain_bind;

COUNT(DISTINCT(OWNER))
----------------------
            26
SQL> select count(*)/count(distinct(owner)) Avg_per_Owner from demo_explain_bind;

AVG_PER_OWNER
-------------
7788.15385
SQL> select distinct(owner) from demo_explain_bind order by 1;

OWNER
------------------------------
BI
CTXSYS
DBSNMP
DMSYS
EXFSYS
HEMANT
HR
IX
MDSYS
OE
OLAPSYS
ORACLE_OCM
ORDPLUGINS
ORDSYS
OUTLN
PM
PUBLIC
SCOTT
SH
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
TSMSYS
WMSYS
XDB

SQL> select num_rows, sample_size, blocks from user_tables where table_name  = 'DEMO_EXPLAIN_BIND';

NUM_ROWS SAMPLE_SIZE     BLOCKS
---------- ----------- ----------
202492      202492       1227
SQL> select num_rows, sample_size, blevel, leaf_blocks from user_indexes where index_name  = 'DEMO_EXPLAIN_BIND_NDX';

NUM_ROWS SAMPLE_SIZE     BLEVEL LEAF_BLOCKS
---------- ----------- ---------- -----------
202492      202492          2         468
SQL> 

Thus, I have table with 202,492 rows. This contains a list of 26 distinct "owner"s, returning an arithmetic average of 7,788 rows per "owner". However, the data is really skewed, with "SYS" accounting for 91,752 rows and "HEMANT" accounting for 72 rows.

Let's start comparing Runtime with Explain Plan :

The first Test Set "A" is with Literals instead of Bind Variables :

SQL> REM ######################################################################
SQL> REM ######################################################################
SQL> REM  REM REM REM  : Test Set A
SQL> REM #############  Run with Histograms
SQL> REM  ######################################################
SQL> REM  First, let's compare Actual Blocks and Rows statistics and Explain with Literals
SQL>
SQL> -- First we use Autotrace to get both Actual Blocks and Rows statistics and Explain
SQL> set autotrace traceonly explain statistics
SQL> select object_name from demo_explain_bind where owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 249876175

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   | 91752 |  2777K|   337   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| DEMO_EXPLAIN_BIND | 91752 |  2777K|   337   (1)| 00:00:05 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
1223  consistent gets
   0  physical reads
   0  redo size
2333602  bytes sent via SQL*Net to client
 690  bytes received via SQL*Net from client
  20  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
91752  rows processed

SQL> select object_name from demo_explain_bind where owner='HEMANT';

Execution Plan
----------------------------------------------------------
Plan hash value: 2077489054

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |    72 |  2232 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO_EXPLAIN_BIND     |    72 |  2232 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEMO_EXPLAIN_BIND_NDX |    72 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
  14  consistent gets
   0  physical reads
   0  redo size
1901  bytes sent via SQL*Net to client
 492  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  72  rows processed

SQL> 

For 'SYS', EXPLAIN PLAN expects 91,752 rows. This matches the real count (which we see as "rows processed"). Again, for "HEMANT", the expected and actual cardinalities are the same -- 72.

Now, let's run those queries using Bind Variables :

SQL> REM  ######################################################
SQL> REM  Now, let's compare Actual Blocks and Rows statistics and Explain with a Bind Value
SQL>
SQL> variable b_owner_1 varchar2(30);
SQL> variable b_owner_2 varchar2(30);
SQL>
SQL> exec :b_owner_1 := 'SYS';
SQL> exec :b_owner_2 := 'HEMANT';
SQL>
SQL> -- First we use Autotrace to get both Actual Blocks and Rows statistics and Explain
SQL> set autotrace traceonly explain statistics
SQL> select object_name from demo_explain_bind where owner=:b_owner_1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2077489054

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |  7788 |   235K|   117   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO_EXPLAIN_BIND     |  7788 |   235K|   117   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | DEMO_EXPLAIN_BIND_NDX |  7788 |       |    20   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:B_OWNER_1)


Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
    1223  consistent gets
       0  physical reads
       0  redo size
 2333602  bytes sent via SQL*Net to client
     690  bytes received via SQL*Net from client
      20  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
   91752  rows processed

SQL> select object_name from demo_explain_bind where owner=:b_owner_2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2077489054

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |  7788 |   235K|   117   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO_EXPLAIN_BIND     |  7788 |   235K|   117   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | DEMO_EXPLAIN_BIND_NDX |  7788 |       |    20   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:B_OWNER_2)


Statistics
----------------------------------------------------------
       1  recursive calls
       0  db block gets
      14  consistent gets
       0  physical reads
       0  redo size
    1901  bytes sent via SQL*Net to client
     492  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
      72  rows processed

SQL> 

Do you see the significant difference ? For 'SYS', the EXPLAIN PLAN expected cardinality was 7.788 rows. AutoTrace tells me that the query for :b_owner_1 being 'SYS' was executed as an Index Range Scan because only 7,788 rows were expected. However, the *real* runtime execution was a FullTableScan and *NOT* an Index Range Scan. How do I know so ? Firstly, the 'consistent gets' count of 1,223 blocks indicates a FullTableScan. Secondly, I ran that again with tracing enabled. A tkprof (without EXPLAIN) shows the Row Source Operations to be :

Rows     Row Source Operation
-------  ---------------------------------------------------
91752  TABLE ACCESS FULL DEMO_EXPLAIN_BIND (cr=1223 pr=0 pw=0 time=275422 us) 

Therefore, it is clear that what AUTOTRACE shows me as Oracle using an Index Range Scan for the query "select object_name from demo_explain_bind where owner=:b_owner_1;" is a lie. AutoTrace shows me an EXPLAIN PLAN, and not the real Execution Plan. RowSourceOperations on the trace file show me the real Execution Plan.
For the second query, using a diffrent bind variable (:b_owner_2) for 'HEMANT', AutoTrace shows me exactly the same plan ! Why ? Because EXPLAIN PLAN is "blind to binds". The actual plan for this was an IndexRangeScan but had to do only 14 consistent gets to fetch the 72 rows.

How did the EXPLAIN PLAN portion of SET AUTOTRACE come up with the figure of 7,788 rows for both :b_owner_1 and :b_owner_2 ? (hint : I've shown that figure earlier). EXPLAIN PLAN merely assumed equal distribution of rows across the 26 "owner"s !
.
This was Test Case "A". In my testing I also included EXPLAIN PLAN calls to confirm that SET AUTOTRACE was showing me EXPLAIN PLAN output. I've excluded those additional calls from the presentation above.
.
I have other Test Cases "B", "C" and "D". Where I go without Histograms, with Dynamic Sampling and without Dynamic Sampling.
.
I may present those other Test Cases over the next few days.
.
Happy EXPLAINing !
.
.
.