BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 900M

SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
sort_area_retained_size              integer     200000000
sort_area_size                       integer     200000000

SQL> ALTER SESSION SET EVENTS '10032 trace name context forever, level 4';

Session altered.

SQL> drop index i_t2;

Index dropped.

SQL> CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort;

Index created.

SQL> select bytes from dba_segments where segment_name='I_T2';

     BYTES
----------
 100663296

------######################################
soropn: opened (new) sort, sordef ffffffff7a3b5108, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-
opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start        End of abridged call stack trace.

*** 2012-03-05 17:02:01.119
smbalo: sort ffffffff7a3b5108 out of memory, smbtab is 104879104, smbbre is 3078990

soreod: sorp ffffffff7a3b5108
---- Sort Parameters ------------------------------
sort_area_size                    1245184
sort_area_retained_size           1245184
sort_multiblock_read_count        7
max intermediate merge width      2

sorcls: sorp ffffffff7a3b5108
---- Sort Statistics ------------------------------
Initial runs                              3
Number of merges                          1
Input records                             5000000
Output records                            5000000
Disk blocks 1st pass                      14754
Total disk blocks used                    14752
Total number of comparisons performed     64985620
  Comparisons performed by in-memory sort 58093351
  Comparisons performed during merge      6892267
  Comparisons while searching for key in-memory 2
Temp segments allocated                   1
Extents allocated                         116
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  4
Block pins (for run directory)            1
Block repins (for run directory)          3
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   576
Num blocks written (with direct write)    14750
Block pins (for sort records)             14750
Waits for async writes                    6
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               119
Number of blocks read synchronously       823
Number of direct async reads              2072
Number of blocks read asynchronously      13927
Waits for async reads                     36
---- End of Sort Statistics -----------------------

sho parameter workarea_size_policy

sho parameter workarea_size_policy

SQL> sho parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

*** 2012-03-05 17:34:56.371
soreod: sorp ffffffff7a30e1c8
---- Sort Parameters ------------------------------
sort_area_size                    500000000
sort_area_retained_size           500000000
sort_multiblock_read_count        2
max intermediate merge width      13754

*** 2012-03-05 17:35:05.425
sorcls: sorp ffffffff7a30e1c8
---- Sort Statistics ------------------------------
Input records                             5000000
Output records                            5000000
Total number of comparisons performed     58083117
  Comparisons performed by in-memory sort 58083117
Total amount of memory used               170552320
Uses version 2 sort
---- End of Sort Statistics -----------------------

NAME                 VALUE           DEFLT     TYPE                                     DESCRIPTION
-------------------- --------------- --------- ---------------------------------------- ---------------------------------------------
_smm_isort_cap       102400          TRUE      number                                   maximum work area for insertion sort(v1)
_smm_max_size        102400          TRUE      number                                   maximum work area size in auto mode (serial)
_smm_px_max_size     460800          TRUE      number                                   maximum work area size in auto mode (global)

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1600M

NAME                           VALUE      DEFLT     TYPE                                     DESCRIPTION
------------------------------ ---------- --------- ---------------------------------------- --------------------------------------------------
_pga_max_size                  335544320  TRUE      6                                        Maximum size of the PGA memory for one process

--##############################
*** 2012-03-05 21:51:53.961
soropn: opened (new) sort, sordef ffffffff7a1bbd28, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start
   End of abridged call stack trace.

*** 2012-03-05 21:51:58.013
smbalo: sort ffffffff7a1bbd28 out of memory, smbtab is 167778304, smbbre is 4926746

soreod: sorp ffffffff7a1bbd28
---- Sort Parameters ------------------------------
sort_area_size                    1245184
sort_area_retained_size           1245184
sort_multiblock_read_count        7
max intermediate merge width      2
*** 2012-03-05 21:52:16.688
sorcls: sorp ffffffff7a1bbd28
---- Sort Statistics ------------------------------
Initial runs                              2
Number of merges                          1
Input records                             5000000
Output records                            5000000
Disk blocks 1st pass                      14761
Total disk blocks used                    14753
Total number of comparisons performed     63014759
  Comparisons performed by in-memory sort 58016757
  Comparisons performed during merge      4998001
  Comparisons while searching for key in-memory 1
Temp segments allocated                   1
Extents allocated                         116
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  3
Block pins (for run directory)            1
Block repins (for run directory)          2
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   577
Num blocks written (with direct write)    14751
Block pins (for sort records)             14751
Waits for async writes                    2
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               118
Number of blocks read synchronously       820
Number of direct async reads              2072
Number of blocks read asynchronously      13931
Waits for async reads                     142
---- End of Sort Statistics -----------------------

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1650M

NAME                 VALUE                DEFLT     TYPE                                     DESCRIPTION
-------------------- -------------------- --------- ---------------------------------------- --------------------------------------------------
_pga_max_size        346030080            TRUE      6                                        Maximum size of the PGA memory for one process

--###########
*** 2012-03-05 22:05:08.100
soropn: opened (new) sort, sordef ffffffff7dfd5e38, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start
   End of abridged call stack trace.

*** 2012-03-05 22:05:16.928
soreod: sorp ffffffff7dfd5e38
---- Sort Parameters ------------------------------
sort_area_size                    170552320
sort_area_retained_size           170552320
sort_multiblock_read_count        1
max intermediate merge width      10408

*** 2012-03-05 22:05:26.127
sorcls: sorp ffffffff7dfd5e38
---- Sort Statistics ------------------------------
Input records                             5000000
Output records                            5000000
Total number of comparisons performed     58083117
  Comparisons performed by in-memory sort 58083117
Total amount of memory used               170552320
Uses version 2 sort
---- End of Sort Statistics -----------------------

alter session set "_smm_min_size" = 1048576;
alter session set "_smm_max_size" = 52428800;
alter session set "_smm_px_max_size" = 157286400;
alter session set "_smm_auto_min_io_size" =  256;
alter session set "_smm_auto_max_io_size" = 1024;
alter system set "_pga_max_size" = 17179852800 scope=memory;

SQL> create table my_table(n number);
 
Table created

SQL> explain plan for create index my_index on my_table(n);
 
Explained

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2778074136
--------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |    82 |  1066 |     3   (0)| 00:00:0
|   1 |  INDEX BUILD NON UNIQUE| MY_INDEX |       |       |            |
|   2 |   SORT CREATE INDEX    |          |    82 |  1066 |            |
|   3 |    TABLE ACCESS FULL   | MY_TABLE |    82 |  1066 |     2   (0)| 00:00:0
--------------------------------------------------------------------------------
Note
-----
   - estimated index size: 65536  bytes
 
14 rows selected

SQL> explain plan for create index my_index on my_table(n) parallel 32;
 
Explained

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 535124730
--------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |    82 |  1066 |     2   (0)| 00:00
|   1 |  PX COORDINATOR          |          |       |       |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |    82 |  1066 |            |
|   3 |    INDEX BUILD NON UNIQUE| MY_INDEX |       |       |            |
|   4 |     SORT CREATE INDEX    |          |    82 |  1066 |            |
|   5 |      PX RECEIVE          |          |    82 |  1066 |     2   (0)| 00:00
|   6 |       PX SEND RANGE      | :TQ10000 |    82 |  1066 |     2   (0)| 00:00
|   7 |        PX BLOCK ITERATOR |          |    82 |  1066 |     2   (0)| 00:00
|   8 |         TABLE ACCESS FULL| MY_TABLE |    82 |  1066 |     2   (0)| 00:00
--------------------------------------------------------------------------------
Note
-----
   - estimated index size: 65536  bytes
 
19 rows selected