Saturday, February 26, 2011

Parallel DML - Conventional (non-direct-path) Inserts As Select

In a recent discussion I've mentioned that I thought to remember that the DML part of conventional load as select inserts will always be executed serially, even with parallel DML enabled and requesting parallel DML execution. It's important to understand in this context that this is not the same as the parallel query execution of the SELECT part, which is possible independently from the parallel DML part.

After that discussion I realized that it was quite some time ago that I tested this scenario, probably it was back then with some 10.2 version.

So I quickly put together a small test case that I ran on 11g versions and the results were quite surprising which motivated me to take a closer look.

To set the scene here's a quote from the Oracle's Database Administrator's guide 10.2. You can find exactly the same paragraph in the most recent version of the guide (at the time of writing this 11.2.0.2) here, so according to the documentation nothing has changed in this regard:

"When you are inserting in parallel DML mode, direct-path INSERT is the default. In order to run in parallel DML mode, the following requirements must be met:

* You must have Oracle Enterprise Edition installed.
* You must enable parallel DML in your session. To do this, run the following statement:

ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

* You must specify the parallel attribute for the target table, either at create time or subsequently, or you must specify the PARALLEL hint for each

insert operation.

To disable direct-path INSERT, specify the NOAPPEND hint in each INSERT statement. Doing so overrides parallel DML mode."

I've highlighted the parts that are important to my test case. The first highlighted part seems to suggest that there is a "non-default" mode for parallel DML inserts, but the second highlighted part seems to tell then if you disable direct-path mode by using the NOAPPEND hint, no parallel DML will be used.

The first part also seems to suggest that when performing parallel DML the direct-path insert mode will be used as default, so without using the explicit usage of the APPEND hint. Note that this has the consequence that a statement that doesn't use the APPEND hint will use conventional inserts when parallel DML is not used, something that people are probably not that clear about.

So this:


insert /*+ parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;


is not the same as this:


insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;


when parallel DML is not enabled/possible.

I would always recommend to use both, the APPEND and PARALLEL hint for the INSERT part to make clear that direct-path mode and parallel DML are intended. In case parallel DML is not enabled/possible then you get at least direct-path inserts if possible.

Back to the main topic: So here is the test case that will be used across the different versions with slight variations:


set echo on timing on serveroutput off linesize 130 pagesize 999 tab off trimspool on

-- drop index t1_idx;

drop table t1;

drop table t2;

create table t1
as
select * from dba_objects;

exec dbms_stats.gather_table_stats(null, 't1')

-- create index t1_idx on t1(object_id);

create table t2
as
select * from dba_objects;

exec dbms_stats.gather_table_stats(null, 't2')

delete from t1;

commit;

-- After initial creation and delete
select
blocks
from
user_tables
where
table_name = 'T1';

alter session enable parallel dml;

variable n_parallel_dml_start number

begin
select
value
into :n_parallel_dml_start
from
v$mystat s
, v$statname n
where
n.name = 'DML statements parallelized'
and s.statistic# = n.statistic#;
end;
/

insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

select * from table(dbms_xplan.display_cursor);

set serveroutput on

declare
n_parallel_dml_end number;
begin
select
value
into n_parallel_dml_end
from
v$mystat s
, v$statname n
where
n.name = 'DML statements parallelized'
and s.statistic# = n.statistic#;
dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
end;
/

-- If it was direct-path and/or parallel DML you can't access the object within the same transaction
-- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
select count(*) from t1;

-- Active transaction(s)
select
used_ublk
, used_urec
, ptx
from
v$transaction
start with
xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
connect by
case when xid = ptx_xid then null else ptx_xid end = prior xid;

commit;

exec dbms_stats.gather_table_stats(null, 't1')

-- After insert
select
blocks
from
user_tables
where
table_name = 'T1';


And this is the result that I get from 10.2.0.5:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.19
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.60
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.65
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL>
SQL> delete from t1;

50756 rows deleted.

Elapsed: 00:00:01.18
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.23
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.01
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

50757 rows created.

Elapsed: 00:00:00.57
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID ah9bvdhtcd38n, child number 0
-------------------------------------
insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2

Plan hash value: 1251029007

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 22 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN| :TQ10000 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | P->P | RND-ROBIN |
| 6 | PX BLOCK ITERATOR | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| T2 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

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

7 - access(:Z>=:Z AND :Z<=:Z)


24 rows selected.

Elapsed: 00:00:00.09
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.03
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.04
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.44
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1791

Elapsed: 00:00:00.01
SQL>


If you examine the execution plan above you'll notice two important things:

- LOAD AS SELECT denotes a direct-path insert operation
- The LOAD AS SELECT is marked as PCWP (Parallel Combined With Parent) which means that it is executed in parallel

Here is a short recap of direct-path parallel DML inserts:

- A lot of restrictions apply to Direct-Path / Parallel DML inserts - so they are only allowed if certain conditions are met

- The whole segment will be exclusively locked by this operation, which means that other sessions can only read, but not modify the segment at the same time. Note that you can restrict it to particular (sub-)partitions if used with the explicit (SUB)PARTITION syntax (insert into table_x partition (y)...), so it is possible to perform them concurrently into different partitions of the same segment

- Direct-path inserts (serial or parallel) write directly to the datafiles bypassing the buffer cache, so the blocks are written by the user process(es) and not by the DBWR process(es).

- By the default every parallel slave of an insert into a non-partitioned segment will use its own temporary segment for loading which will be merged afterwards into one. Note that this can lead to issues when loading smaller amounts of data with large UNIFORM extent sizes (no final extent trimming possible and therefore potentially much larger resulting segments than necessary) or system-managed extent sizes when extent trimming leads to extent sizes that won't be re-used by subsequent loads. Therefore you can optionally use the "brokered load" feature but only with clearance from Oracle Support (see e.g. fix control 6941515 ("use high watermark brokering for insert into single segment") in 11.2)

You can see from above output that the increase in segment size is more than 100% compared to the original segment size - a side-effect of the temp segment merge operation requiring more space for the same amount of data (see comment below by Greg Rahn, exaggerated by my low data volume and extent settings, by the way, you won't see such a significant "wastage" under more realistic conditions).

- As you can see from above output of the test case, direct-path (serial or parallel) loads do not re-use any free space in existing blocks. It will always "append" above the current high-water mark, making the segment grow.

- Another side-effect of this is that it doesn't have to write UNDO for rollback and read-consistency purposes, as for rollback it only needs to release the newly allocated blocks and for read-consistency nothing needs to be done since the high-water mark has not been moved yet and therefore the new blocks are not visible to other sessions. You can see that from the USED_UREC / USED_UBLK output above - no undo has been generated by the parallel DML direct-path insert

- Yet another side-effect of this is that this operation can optionally be a NOLOGGING operation, writing only minimal amount of redo, if the segment has been marked so. Of course you need to be aware of the implications of a NOLOGGING operation.

- Note that if there are usable indexes on the segment defined then the insert will maintain them. This index maintenance will generate undo and redo even if the data segment has been marked as and loaded using NOLOGGING. This holds true, by the way,even if the indexes are also marked as NOLOGGING, since that will only be applicable to index creation and rebuilds, but not index maintenance. If the amount of data loaded into the segment is significant it can be more efficient to set the index segments to unusable before loading the data and rebuild the index segments afterwards.

- You can not access/modify the segment within the same transaction after a serial direct-path or any parallel DML operation, you'll get "ORA-12838: cannot read/modify an object after modifying it in parallel"

- You can not perform parallel DML on an object within the same transaction if serial DML has already been applied to it. You'll get an error "ORA-12839: cannot modify an object in parallel after modifying it". This effectively means within a single transaction a single object can only be modified at most once if it is supposed to be a parallel DML operation

- The session/system statistic "DML statements parallelized" will be increased whenever a DML statement has been run in parallel

If I change now the APPEND hint in the INSERT statement to NOAPPEND I get the following result from 10.2.0.5:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.66
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.66
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL>
SQL> delete from t1;

50756 rows deleted.

Elapsed: 00:00:01.06
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.03
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.03
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

50757 rows created.

Elapsed: 00:00:00.34
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 22 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T2 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------

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

4 - access(:Z>=:Z AND :Z<=:Z)


21 rows selected.

Elapsed: 00:00:00.29
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;

COUNT(*)
----------
50757

Elapsed: 00:00:00.01
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
24 1326 NO

Elapsed: 00:00:00.00
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.00
SQL>


So indeed, the description quoted above seems to be correct: The usage of the NOAPPEND hint turned this into a serial, conventional insert as select.

- No LOAD AS SELECT operation, so no direct-path insert

- The query part of the select ran in parallel, but the insert was performed serially.

- Undo has been generated

- The parallel DML statistic hasn't been increased

- The free blocks of the table have been re-used by the insert (no segment growth after the insert)

So, let's repeat the same exercise on 11.1.0.7. The APPEND version will look the same (except for the slightly larger copy of DBA_OBJECTS) and is therefore not shown here, but what about the NOAPPEND version:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.35
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.59
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.59
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.06
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50
SQL>
SQL> delete from t1;

69441 rows deleted.

Elapsed: 00:00:01.99
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.04
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1020

Elapsed: 00:00:00.01
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.79
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

5 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.03
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 264 YES
7 275 YES
7 293 YES
6 247 YES
6 215 YES
6 253 YES
6 240 YES
6 262 YES

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.05
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1020

Elapsed: 00:00:00.01
SQL>


Now that is a surprising output. Let's see, we have:

- A new operation in the execution plan, called "LOAD TABLE CONVENTIONAL" (which will also be used for serial conventional insert as selects from 11.1 on)

- A parallel DML operation according to the plan, because the LOAD TABLE CONVENTIONAL is marked as PCWP

- Also the statistics tell us that the DML statement has been run in parallel

- We also get the "ORA-12838: cannot read/modify an object after modifying it in parallel" error message when attempting to access the object after modification indicating a parallel DML operation

- We also see the transactions of the parallel slaves

- But this time these have actually generated UNDO

- And the free space has been re-used

So this is an interesting mixture that obviously has been added in 11.1 - it seems not to be possible to get the same output in pre-11g versions.

Obviously the documentation is not up to date with changes introduced in 11.1 - the NOAPPEND hint now will no longer override the "parallel" DML hint of the insert - that part of the documentation seems to be wrong/outdated.

I got the same result from 11.2.0.1 and 11.2.0.2, by the way.

Update March 2011: Christian Antognini mentioned that this new feature is controlled by the parameter "_disable_parallel_conventional_load".

However, I also found an oddity with this new feature while testing. Look closely at the output of the following script:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:01.05
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.02
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.85
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.98
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.15
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
SQL>
SQL> delete from t1;

69441 rows deleted.

Elapsed: 00:00:00.75
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.19
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.29
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

5 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

Elapsed: 00:00:00.34
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
119 119

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.01
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 234 YES
6 259 YES
6 259 YES
6 251 YES
6 227 YES
6 251 YES
7 258 YES
6 268 YES

9 rows selected.

Elapsed: 00:00:00.06
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.80
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

5 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
141 119 2 1 1
135 119 2 1 2
127 119 2 1 3
138 119 2 1 4
116 119 2 1 5
109 119 2 1 6
146 119 2 1 7
144 119 2 1 8
119 119

17 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 289 YES
6 234 YES
7 284 YES
6 259 YES
6 287 YES
7 280 YES
6 259 YES
6 251 YES
6 227 YES
4 117 YES
3 20 YES
7 308 YES
7 341 YES
6 251 YES
7 258 YES
6 268 YES

17 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.55
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

5 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
141 119 2 1 1
135 119 2 1 2
127 119 2 1 3
138 119 2 1 4
116 119 2 1 5
109 119 2 1 6
146 119 2 1 7
144 119 2 1 8
145 119 3 1 1
133 119 3 1 2
121 119 3 1 3
110 119 3 1 4
139 119 3 1 5
131 119 3 1 6
115 119 3 1 7
120 119 3 1 8
119 119

25 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 289 YES
6 234 YES
6 243 YES
6 211 YES
6 244 YES
7 284 YES
6 259 YES
6 287 YES
7 280 YES
6 259 YES
6 251 YES
6 241 YES
7 265 YES
6 251 YES
5 226 YES
6 250 YES
6 227 YES
4 117 YES
3 20 YES
7 308 YES
7 341 YES
6 251 YES
7 258 YES
6 268 YES

25 rows selected.

Elapsed: 00:00:00.02
SQL>


All I do is an attempt to repeat the same insert as select statement three times within the same transaction, additionally I show the parallel slave information from V$PX_SESSION.

The expected result was to get simply the "ORA-12838: cannot read/modify an object after modifying it in parallel" error that is indeed thrown when attempting to read from the object after the first parallel DML operation.

Instead, something odd happens: The subsequent insert operations succeed but they don't get marked as "parallel DML" according to the session statistics, and what is even more puzzling: Each execution adds another group of parallel slaves to my parallel execution coordinator.

Note that Oracle also allows other insert operations, like single row insert or other conventional insert as select executions into the same table within the same transaction.

Clearly this doesn't look like intended behaviour, and indeed, when running the same script but with the CREATE INDEX T1_IDX ON T1(OBJECT_ID) command uncommented it behaves as expected and doesn't allow the subsequent manipulation of the object and instead errors out with "ORA-12838: cannot read/modify an object after modifying it in parallel".

I get the same result from 11.2.0.1 and 11.2.0.2, by the way, so this problem is not fixed yet. The only difference in 11.2 is that creating an index on the segment adds another set of parallel slaves that take care of the "INDEX MAINTENANCE" operation that 11.2 shows in the execution plan. In 11.1.0.7 the additional operation does not appear and the same slaves that write in to the segment also take care of the index maintenance.

Summary

Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel. I haven't spent too much time yet searching for any official documentation of that new feature, so I can only speculate why it has been added.

Wednesday, February 23, 2011

Concurrent Index Creation

When I read the recent post by the optimizer group about the new concurrent gather stats feature added in 11.2.0.2 it reminded me of the fact that I intended to publish something based on the same idea already some time ago.

The Problem

It was motivated by a client's regular need during a transition phase from non-Exadata to Exadata to create literally thousands of indexes with potentially a multitude of (sub-)partitions as fast as possible - as part of a full datapump import job of a multi-terabyte database running 11.1.0.7 and 11.2.0.1 (Exadata V2).

There are actually two issues regarding the index creation part of a large database import:

1. The datapump import performs the index creation only by a single worker thread even when using the PARALLEL worker thread import feature. Although an index could be created in parallel if you have thousands of smaller index objects this single worker thread potentially does not make efficient use of the available hardware resources with high-end configurations, including and in particular Exadata.

2. There is a nasty bug 8604502 that has been introduced with 11.1.0.7 that affects also 11.2.0.1 (fixed in 11.2.0.2 and a generic one-off patch is available on My Oracle Support for 11.1.0.7 and 11.2.0.1): The IMPDP creates all indexes serially, even those supposed to be created in parallel, and only after the creation ALTERs them to the defined PARALLEL degree. Note that the fix actually only fixes the problem at actual execution time, even with the fix installed (and in 11.2.0.2) the SQLFILE option of IMPDP still generates CREATE INDEX DDLs that will always have the parallel degree set to PARALLEL 1 (see MOS document 1289032.1 and bug 10408313 - INDEXES ARE CREATED WITH PARALLEL DEGREE 1 DURING IMPORT which has been closed as not being a bug). This "not-being-a-bug" also affects all other versions that support the datapump utility - the SQLFILE option always generates CREATE INDEX scripts with the parallel degree set to 1 no matter what the actual degree of the index is supposed to be. It's only the ALTER INDEX DDL command following the CREATE INDEX command that sets the parallel degree correctly.

These two issues in combination meant to them that a full database import job took ages to complete the index creation step after loading quite quickly the vast amount of table data in parallel.

In case of partitioned indexes there is another complication independently from the mentioned issues: Oracle uses only one parallel slave per partition for creation - in case of large and/or few partitions this again doesn't make efficient use of the available resources.

Oracle therefore provides several means to speed up index creation and rebuild tasks, in particular the documented DBMS_PCLXUTIL package that is around since the Oracle 8 days to overcome the above mentioned limitation of partitioned index creation by spawning multiple jobs each rebuilding an index partition in parallel.

Another, undocumented feature is the DBMS_INDEX_UTL package that is obviously used internally as part of several maintenance operations, for example those DDLs that include the "UPDATE INDEXES" clause. According to the spec it allows to rebuild multiple indexes concurrently by spawning multiple jobs - however since it is undocumented it might not be safe to use in production-like configurations - furthermore it might be changed in future releases without further notice and therefore is potentially unreliable.

A Solution

Since the client wanted a quick solution that ideally addressed all of the above issues I came up with a simple implementation that uses Advanced Queueing and background jobs to create as many indexes as desired concurrently.

The solution is targeted towards the client's scenario, so the following is assumed:

- There is a SQL file that contains the CREATE INDEX statements. This can easily be generated via IMPDP based on the dump files using the SQLFILE option.

- To address the CREATE INDEX (not-being-a-)bug (the bugfix for the bug 8604502 still generates incorrect CREATE INDEX DDLs with the SQLFILE option of IMPDP as mentioned above) I've created a combination of "sed" and "awk" unix scripts that take the IMPDP SQLFILE potentially including all DDLs commands as input and create a output file that consists solely of the CREATE INDEX commands with correct PARALLEL clauses based on the ALTER INDEX command following the CREATE INDEX in the script

- To address the lengthy index creation process I've created a small PL/SQL package that sets up the required AQ infrastructure, takes the CREATE INDEX DDL file as input, populates a queue with the index creation commands and spawns as many worker threads as specified that will take care of the actual index creation (that in turn might be a parallel index creation)

As a side note it is interesting that Oracle actually allows to build several indexes concurrently on the same segment (which makes totally sense but does probably not happen too often in practice).

Note that in principle this code could be used as a general template to execute arbitrary DDLs concurrently (of course with corresponding modifications).

The following link allows to download an archive that contains the following subdirectories:

- correct_parallel_clause: This directory contains the Unix scripts mentioned above that allow to process a SQLFILE generated by IMPDP and output a DDL file that solely consists of the CREATE INDEX commands contained in the SQLFILE. The generated CREATE INDEX statements also use a correct PARALLEL clause - the degree is taken from the ALTER INDEX DDL command following the CREATE INDEX in the SQLFILE. For further details refer to the README.txt in that directory. Note that the script at present does not handle Domain Indexes, only conventional and bitmap.

- source: Contains the package source for the concurrent index creation, furthermore a package that is required by the provided automated unit testing (see below for more details) and a script that prompts for the required details to initiate a concurrent index creation. The README.txt in that directory provides a quick start guide how to use the concurrent index creation.

- test: Contains two flavours of test harnesses for automated unit testing of the package. One based on the unit testing feature implemented in SQLDeveloper 2.1.1, and another one based on "dbunit", an open-source unit testing framework based on jUnit. The README.txt in the respective subdirectories explain how to use these unit tests.

How to use it

The usage is split into two parts: The first part deals with preparing a suitable text file that consists of the CREATE INDEX commands, the second part is about processing this text file with as many worker threads as desired.

Preparing the file is straightforward: You can use the "transform_all_sql.sh" script to generate the required CREATE INDEX script from a DDL script created via IMPDP SQLFILE.

The script has been tested primarily with bash, sed and awk under Cygwin 1.7.1 and OEL5, different Unix flavors might have different versions of the shell, awk or sed and therefore might behave differently.

Simply put all four Unix scripts in the "correct_parallel_clause" directory into the same directory, mark them as executable and run the "transform_all_sql.sh" like that:


./transform_all_sql.sh < input_file > output_file


where "input_file" is the file generated via IMPDP SQLFILE option and "output_file" will be the result.

In order to perform the parallel index creation, you need an account that has suitable privileges granted. Since it is assumed that the indexes will have to be created in different schemas this account will have to have extended privileges granted. The package is implemented using invoker's rights so granting these privileges via roles is sufficient. A quick and dirty solution could be creating a temporary account and granting simply the DBA role to it (this is what I used to do to test it). Note that the account also requires EXECUTE privileges on the DBMS_AQ and DBMS_AQADM packages for the AQ stuff. It also needs a simple logging table where errors and progress will be written to as well as a type that is used as payload of the queue. Obviously the account also needs to be able to create jobs - in this version of the package this is done via DBMS_SCHEDULER. At execution time the package is going to create a queue plus queue table that also needs to be stored in a tablespace - so you should make sure that the account (or at least the database) that executes the index creation has an appropriate default tablespace defined.

You can simply run the "pk_create_index_concurrent.sql" script (located in the "source" directory) in such a suitable account which will deinstall/install all required objects.

The execution of the index creation is then straightforward (taken from the package specification):


/**
* The main entry point to create indexes via parallel threads / AQ
* @param p_directory_name The directory where the file resides that contains the CREATE INDEX DDLs
* @param p_file_name The file name in the directory above
* @param p_parallel_degree_set_1 The number threads to start for the worker thread 1 which usually
represents the SERIAL_INDEX threads - G_AUTO_PARALLEL_DEGREE means use the CPU_COUNT and
CLUSTER_DATABASE_INSTANCES parameter to determine number of threads automatically
* @param p_parallel_degree_set_2 The number threads to start for the worker thread 2 which usually
represents the PARALLEL_INDEX threads - G_AUTO_PARALLEL_DEGREE means get the CPU_COUNT and
CLUSTER_DATABASE_INSTANCES parameter to determine number of threads automatically,
however 1 is the default here since we assume that these indexes use parallel DDL
* @param p_job_submit_delay The number of seconds each job will be delayed to allow Oracle
proper load balancing in a cluster, default 30 seconds (commented out at present due to
odd locking issues on the queue table in RAC environments)
* @param p_sleep_seconds The number of seconds to wait for the threads to startup
before attempting to teardown the AQ infrastructure again
* @param p_optional_init Optionally a SQL can be passed usually used to initialize the session
for example forcing a particular parallel degree
* @param p_worker_set_id_1
The character identifier used to identify the indexes to process by the first worker thread set
Default value is "SERIAL_INDEX"
* @param p_worker_set_id_2
The character identifier used to identify the indexes to process by the second worker thread set
Default value is "PARALLEL_INDEX"
**/
procedure create_index_concurrent(
p_directory_name in varchar2
, p_file_name in varchar2
, p_parallel_degree_set_1 in integer default G_AUTO_PARALLEL_DEGREE
, p_parallel_degree_set_2 in integer default 1
, p_job_submit_delay in integer default 30
, p_sleep_seconds in integer default 10
, p_optional_init in varchar2 default null
, p_worker_set_id_1 in varchar2 default G_WORKER_SET_ID_1
, p_worker_set_id_2 in varchar2 default G_WORKER_SET_ID_2
);


Note that the "p_job_submit_delay" parameter is currently not used - there were some odd locking issues on the AQ table in case of a RAC environment when using that option so I have commented out its usage at present - I haven't had a chance yet to investigate further what the problem actually was.

So the only required input to the CREATE_INDEX_CONCURRENT procedure is the name of the directory object that points to the directory where the file to process resides and the name of the file itself.

You probably want to specify the number of worker threads for the two sets: The idea here is to distinguish between the creation of serial and parallel indexes. The first parameter specifies the number of worker threads used for serial indexes, the second one the number of concurrent threads for parallel indexes.

The default is CPU_COUNT * INSTANCES threads for serial indexes and a single thread for parallel indexes.

If you don't want/need this separation of serial and parallel indexes simple use the same "worker_set_id" for both parameters "p_worker_set_id_1" and "p_worker_set_id_2" and specify the desired total parallel degree in one of the degree parameters and set the other one to 0 (the 0 is required otherwise one of the DBMS_SCHEDULER.CREATE_JOB calls will fail with a "duplicate job name/job name already exists").

The "p_sleep_seconds" parameter is only used to allow the jobs spawned to put a lock on the queue table - the teardown is then going to wait until all locks have been removed and therefore all queue processing has ended. The default of 10 seconds was sufficient in all cases I've encountered.

Since the package requires as prerequisite a directory where the file to process resides, I've prepared the script "create_index_concurrent.sql" that guides through the required inputs and takes care of that step as well.

It takes the full O/S path to the file and the file name as input, creates a directory CREATE_INDEX_CONCURRENT_DIR pointing to that directory and prompts then for the two degrees as input and the names of the two worker thread sets before calling the CREATE_INDEX_CONCURRENT stored procedure.

Caveats

Please note that you should double-check not to pass a non-transformed SQLFILE generated via IMPDP to the procedure - the results may be dire since the generated SQLFILE always contains much more than the bare CREATE INDEX commands, no matter what options you use for IMPDP. Always use the provided Unix scripts to post-process the SQLFILE before initiating the index creation.

Furthermore you need to be aware of the current limitation of the package that it does not attempt to tokenize the file contents. It simply uses a semicolon as delimiter to separate the DDL commands. This should be sufficient for most cases, but in case you have a function-based index using a string expression containing a semicolon as part of the index definition this will not work as expected. Also if you plan to use this package for other DDL execution activities like CTAS statements you might again hit this limitation if the DDL text contains semicolons.

Note that creating indexes using this tool results potentially in different index statistics than creating the indexes using IMPDP since IMPDP by default also imports the index statistics whereas the indexes created using this tool will end up with the current index statistics automatically generated during index creation (from 10g onwards, and the code requires at least 10.2). If you want to have the index statistics imported you can run IMPDP after the index creation using the INCLUDE=INDEX_STATISTICS option. This should complete fairly quickly and will import the index statistics only.

If you have SERVEROUTPUT enabled by default then you will very likely see some errors that will be printed by the initial attempt to tear down the AQ infrastructure. These errors are expected if the previous run was completed successfully or in case of the initial run and can be ignored (and will be catched/ignored by the default implementation).

Note also that all provided scripts except for the Unix shell scripts use DOS file format - under OEL this isn't a problem but it might be on your platform.

Finally the inevitable disclaimer: Although this has been tested thoroughly it comes with absolutely no warranty. Use it at your own risk and test it in your environment before attempting any runs against anything important.

Monitoring the execution

The code logs errors and progress into the table CREATE_INDEX_CONCURRENT_LOG. At present the code logs every attempt to execute DDL into the table as well as any errors that are raised during that DDL execution.

So the table can be used for both, monitoring the progress as well as checking for errors. The code currently continues the execution in case of errors encountered using the dreaded WHEN OTHERS THEN NULL construct, but the code is already prepared for a more granular error handling if required - see the defined exceptions and commented out exception handler.

You can view the queue contents in the corresponding queue view created by the AQ setup (AQ$CREATE_INDEX_QUEUE) in order to see the data to process. Note that due to the fact that all worker threads do not commit the queue transaction you won't be able to see the progress in the queue table until all worker threads committed. If you don't like that you can remove the wait and "teardown_aq" call at the end of the main procedure "create_index_concurrent" and uncomment the dequeue option "visibility=immediate" in the "create_index_thread" procedure. You would need then to call "teardown_aq" in a separate step as desired. With this modification you can monitor the progress by monitoring the queue, but the provided automated unit testing won't work with that variant since it relies on the main call to wait for all worker threads to complete before validating the results.

However you can see the progress also in the log table using the following sample query:


select
to_char(log_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF') as log_timestamp
, sql_statement
, message
from
create_index_concurrent_log
order by
log_timestamp desc;


If you want to perform more sophisticated queries on the that table you might need to use some casts similar to the following, because the text columns are defined as CLOBs in order to be able to hold the complete DDLs and error messages in case of errors. The casts allow you to perform for example GROUP BYs etc.


select
to_char(log_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF') as log_timestamp
, cast(substr(sql_statement, 1, 30) as varchar2(30)) as index_name
, cast(substr(message, 1, 128) as varchar2(128)) as worker_set_id
from
create_index_concurrent_log
order by
log_timestamp desc;


The Unit Testing

Here we come to a completely different issue that is off-topic for this post, however in my experience so far it seems to be a very important one and I hopefully will have the time to cover it in the future with separate posts.

Generally speaking I've seen to many shops that don't follow best-practice when it comes to database deployment and development, therefore here is what you should know/do about it ideally - in a nutshell:

- Treat your database like source code, which means put everything related to the database under version control. This includes not only the obvious database source code but also DDL and DML scripts for schema evolution
- Use unit testing to test database code. Automate this unit testing
- Automate the deployment of your database related changes
- Install a continuous integration environment that runs the automated deployment and unit tests regularly, for example every night
- Automate deployment everywhere - starting from the development databases up to the production environment
- Follow your guidelines strictly - for example any hotfix-like adhoc change should still go through the established processes - code changes, testing, deployment etc.

I've helped several clients in the past to setup corresponding tools and processes for implementing above - if you are interested, get in touch with me.

So as a bonus, if you haven't spent too much time yet with above mentioned topics, in order to get you started at least with automated unit testing, I've included two different examples for this small source provided, one using the built-in unit test feature of SQLDeveloper and the other one using "dbunit". You can find both in the corresponding subdirectories of the "test" folder in the archive.

The unit testing is based on the "pk_create_index_concur_test.sql" package that is used to setup and teardown the environment for running the unit test. It assumes at present the existence of a directory "C:\app\oracle\admin\orcl112\dpdump" on O/S level. It will create a directory object for the path and attempt to create/write a file used for the unit test runs. You can pass any valid O/S directory path to the "pk_create_index_concur_test.setup" procedure if you want/need to use a different one.

All provided automated tests assume that both scripts, "pk_create_index_concurrent.sql" and "pk_create_index_concur_test.sql" have been run in the schema that should be used for test runs.

You can use the SQLDeveloper Unit Test feature to run the provided Unit Test. You can either use the GUI to import and run the test, or you can use a command line version that is actually using ANT to run the UTUTIL command line tool that comes with SQLDeveloper. You can read and follow the instructions in the "README.txt" in the test/SQLDeveloper directory how to do so. You'll need to setup a unit test repository initially if you want to use SQLDeveloper's unit testing feature either way (GUI or UTUTIL command line). See the SQLDeveloper's user's guide or online help how to do that (Hint: Menu item "Extras->Unit Testing" gets you started).

If you don't like the SQLDeveloper unit test approach or you are simply to lazy to install the tool, the unit test repository etc., you can alternatively try the automated unit testing using "dbunit". Follow the instructions in the "README.txt" in the test/dbunit directory how to run the unit tests using "dbunit".

This version of the package has successfully been tested using these unit tests on 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 (after all it's dead easy with automated unit testing :-).

Summary

The provided tool set should represent a solid foundation for the given task of concurrent index creation. In particular it has been designed with the following in mind:

- Efficient use of privileges granted via roles: The package uses invoker's rights and most operations use dynamic SQL to avoid compilation issues, therefore granting the required privileges to the account used via roles should be sufficient

- The Unix scripts should be able to deal with table-, schema- and database-level datapump formats from Oracle 10g and 11g (all these variants use slightly different texts to identify the relevant sections of the generated SQLFILE by IMPDP)

- Optional use of two separate worker thread sets: This allows the concurrent creation of a multitude of indexes, be it serial or parallel, with clear distinction between the handling of serial (possibly many worker threads) and parallel indexes (usually only a few worker threads)

- Support for arbitrarily sized SQL: The DDL commands for (sub-)partitioned indexes can become quite large due to the way the Oracle meta data API generates the SQL. Therefore these generated SQLs can easily exceed the usual 32KB limit for PL/SQL character strings. The implementation uses CLOBs for the processed SQLs (and DBMS_SQL in versions lower than 11 to handle these piecewise) to support these potentially very large SQLs

- RAC/Grid/Cluster support via DBMS_SCHEDULER: The usage of DBMS_SCHEDULER allows a fine grained control of the resource consumption by the optional use of job classes (not implemented yet but can easily be added - it is a simple additional parameter to the CREATE_JOB procedure) that allow to specify a resource consumer group and a specific service name for the spawned worker threads

- Automated Unit Testing support: The provided unit test harness allows for easy testing of modifications to the code

Saturday, February 5, 2011

Things worth to mention and remember (I) - Parallel Execution Control

Continue to Part II

Motivated by Arup Nanda's great "100 Things You Probably Didn't Know About Oracle Database" and inspired by Jonathan Lewis' "Philosophy" series I'll start a new series about things worth to mention and remember, but in contrast to my usual posts I'll furthermore set a new challenge for myself - trying to write as short and concise as Jonathan does in his series. It is probably no coincidence that Charles Hooper and myself contributed by far the longest chapter to "Expert Oracle Practices" :-)

So don't expect any lengthy descriptions and testcases in this series - also I don't intend to provide here ground-breaking material. All this has probably been published several times in the past, nevertheless I believe it's worth to mention these things again (and again...).

Here we go, the first one worth to mention and remember is important for those that have an Enterprise Edition license and make use of the Parallel Execution feature:

If you haven't enabled Parallel DML on session level (ALTER SESSION ENABLE/FORCE PARALLEL DML) you are not able to force parallel DML execution on statement level using explicit PARALLEL hints for the DML operation. (This one is probably known)


If you disable Parallel Query on session level (ALTER SESSION DISABLE PARALLEL QUERY) explicit PARALLEL hints on statement level for a query operation will overrule this and therefore still use Parallel Query. The session setting only overrules the object level parallel settings, not the PARALLEL hint (This is probably less known)


Although there is probably a rationale behind this it is still obvious I think that there is a major inconsistency here.

In particular the latter is unfortunate if you have existing code with embedded PARALLEL hints and want to ensure that the execution is done serially without any code changes in order to not waste any parallel slaves processes meant to be used by other concurrent executions.

To me it would be much more intuitive and sensible if the setting on session level overruled anything else and therefore allowed to control the code using that general setting, as it does with parallel DML.

By the way, a parallel DML degree forced via ALTER SESSION FORCE PARALLEL DML PARALLEL N will then still be overruled by explicit PARALLEL hints applied to the DML operation, it's just that PARALLEL DML is simply not possible if not at least enabled at session level.

Actually I'm not aware of any officially supported way (which means without fiddling with any undocumented parameters) of preventing the Parallel Query execution of statements with embedded PARALLEL hints on session level besides using the Resource Manager and limiting the Parallel Degree to 1.

Note that this approach has again its own quirks and drawbacks, since obviously the execution plan generated based on the optimizer assumption of Parallel Execution will be effectively downgraded to serial execution at runtime rather than being re-optimized for serial execution. In many cases not a good idea - an execution plan generated for serial execution might look quite different and might be much more efficient when being executed serially.

As a final note, if you think you can work around this by using the undocumented "_optimizer_ignore_hints" parameter, you will be in for a surprise that apparently the APPEND, PARALLEL and NO_PARALLEL hints do not get ignored and still apply and therefore parallelize query execution.

Addendum March 2011: One further oddity should not be left unmentioned: In above mentioned scenario of parallel query being disabled on SESSION level via ALTER SESSION DISABLE PARALLEL QUERY and the usage of PARALLEL hints the following applies:

- Potentially a plan based on parallel execution is generated by the optimizer (as outlined)
- But the costing of the operations is based on serial execution, so the costs of parallel full table scans or index scans are not scaled down based on the degree of parallelism as usually

This means that the resulting execution plans are possibly different to a session where parallel query is enabled - it is more likely to get a serial execution plan favoured by the optimizer in the scenario with parallel query disabled due to the costing based on serial costs.

Nevertheless at runtime parallel execution (with the computed parallel degree) will be used if a parallel plan has been generated by the optimizer.