Oracle Database Blog: Experiments & Learnings

September 15, 2011

Order by clause and Cost

Filed under: Oracle RDBMS — Tags: , , , , — Saurabh Manroy @ 3:01 pm

This post is a result of tests done for the quiz series posted by Richard Foote on his blog.

Precisely, I create a table with one of its columns defined as not null. I index that column and then run a select query to select all columns and rows in table with an order by clause on indexed column. Optimizer performs a Index Full Scan rather than a Full Table Scan because:

a.) Orderby clause is used for a NOT Null Column. Normal B-Tree indexes don’t store nulls.

b.) Clustering factor is equal to number of blocks in the table.

and finally:

Cost of Full Table Scan (FTS) + Cost of Sorting (Order by clause)  >  (Cost of accessing already sorted data in index sequentially + Table access for each index leaf block access)

Then I tweak the “_smm_min_size” parameter to see what impact it has on query execution path.

SQL> create table bt_test as select rownum id, to_char('level_'||level) lv, sysdate dtime from dual connect by level <=500000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'bt_test');

PL/SQL procedure successfully completed.

SQL> alter table bt_test modify id not null;

Table altered.

SQL> create index bt_idx on bt_test(id);

Index created.

SQL> set autot traceonly explain
SQL> select * from bt_test order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838880622

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   500K|    11M|  3254   (1)| 00:00:40 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BT_TEST |   500K|    11M|  3254   (1)| 00:00:40 |
|   2 |   INDEX FULL SCAN           | BT_IDX  |   500K|       |  1120   (1)| 00:00:14 |
---------------------------------------------------------------------------------------
SQL> select clustering_factor from dba_indexes where index_name='BT_IDX';

CLUSTERING_FACTOR
-----------------
             2129
SQL> select blocks from dba_tables where table_name='BT_TEST';

    BLOCKS
----------
      2129

Note that Clustering factor is one of the major factors while considering Index path access in execution plan of any query.

Lets look at 10053 trace:

ORDER BY sort
    SORT ressource         Sort statistics
      Sort width:         531 Area size:      464896 Max Area size:    93113344
      Degree:               1
      Blocks to Sort: 2327 Row size:     38 Total Rows:         500000
      Initial runs:   2 Merge passes:  1 IO Cost / pass:       1262
      Total IO sort cost: 3589      Total CPU sort cost: 505932425
      Total Temp space used: 18138000

Area Size in the 10053 trace shown above corresponds to “_smm_min_size” for the session This parameter specifies minimum work area size in auto mode. Default value of this parameter is 454K in 11.1
Max Area Size in the trace corresponds to “_smm_max_size” for the session . This parameter specifies (maximum work area size in auto mode. Default value of this parameter is 90931 in 11.1

Optimizer thinks that sort will spill to disk (Temporary Tablespace) and it adds up cost of Disk Sorting as well to the overall cost and that makes (FTS+orderby) an expensive operation. Why have I used the word thinks because, “_smm_max_size” is already set to 90M (default) and for a small sized table (bt_test), 90M should be more than enough to perform the sort.

Lets pre-allocate some memory to work areas.

SQL> alter session set "_smm_min_size"=20000;

Session altered.

SQL> set autot traceonly explain
SQL> select * from bt_test order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2233975280

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 11M| 603 (5)| 00:00:08 |
| 1 | SORT ORDER BY | | 500K| 11M| 603 (5)| 00:00:08 |
| 2 | TABLE ACCESS FULL| BT_TEST | 500K| 11M| 583 (1)| 00:00:07 |
------------------------------------------------------------------------------
10053 Trace:
ORDER BY sort
 SORT ressource Sort statistics
 Sort width: 531 Area size: 20480000 Max Area size: 93113344
 Degree: 1
 Blocks to Sort: 2327 Row size: 38 Total Rows: 500000
 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
 Total IO sort cost: 0 Total CPU sort cost: 448627723
 Total Temp space used: 0

Trace file confirms that Area size is set to 20M. This makes optimizer think that complete sorting would finish in memory. Temp Space usage has changed to zero. I/O cost for sorting has reduced to zero which makes FTS + Sort a better option compared to Index full Scan.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: