Influence on conventional load vs direct path insert
Preparation
Create two tables to test the influence of the
append hint:
create table tq84_src (id number, val varchar2(100));
create table tq84_dest(id number, val varchar2(100));
Serial execution
explain plan for
insert into tq84_dest
select * from tq84_src;
… and display the plan:
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ----------------------------------------------
-- | Id | Operation | Name |
-- ----------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | LOAD TABLE CONVENTIONAL | TQ84_DEST |
-- | 2 | TABLE ACCESS FULL | TQ84_SRC |
-- ----------------------------------------------
Same thing, but this time using the append hint:
explain plan for
insert into tq84_dest
select /*+ append */ * from tq84_src;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ------------------------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | LOAD AS SELECT | TQ84_DEST |
-- | 2 | OPTIMIZER STATISTICS GATHERING | |
-- | 3 | TABLE ACCESS FULL | TQ84_SRC |
-- ------------------------------------------------------
Parallel execution
explain plan for
insert into tq84_dest
select /*+ parallel(4) enable_parallel_dml */ * from tq84_src;
select * from table(dbms_xplan.display(format => 'basic +note'));
--
-- --------------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | PX COORDINATOR | |
-- | 2 | PX SEND QC (RANDOM) | :TQ10000 |
-- | 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TQ84_DEST |
-- | 4 | OPTIMIZER STATISTICS GATHERING | |
-- | 5 | PX BLOCK ITERATOR | |
-- | 6 | TABLE ACCESS FULL | TQ84_SRC |
-- --------------------------------------------------------
--
-- Note
-- -----
-- - dynamic statistics used: dynamic sampling (level=2)
-- - Degree of Parallelism is 4 because of hint
Same thing, but using the noappend hint. This hint disables direct path («direct load», see Note of dbms_xplan.display below):
explain plan for
insert into tq84_dest
select /*+ parallel(4) noappend enable_parallel_dml */ * from tq84_src;
select * from table(dbms_xplan.display(format => 'basic +note'));
--
-- ------------------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------------
-- | 0 | INSERT STATEMENT | |
-- | 1 | PX COORDINATOR | |
-- | 2 | PX SEND QC (RANDOM) | :TQ10000 |
-- | 3 | LOAD TABLE CONVENTIONAL | TQ84_DEST |
-- | 4 | PX BLOCK ITERATOR | |
-- | 5 | TABLE ACCESS FULL | TQ84_SRC |
-- ------------------------------------------------
--
-- Note
-- -----
-- - dynamic statistics used: dynamic sampling (level=2)
-- - Degree of Parallelism is 4 because of hint
-- - Direct Load disabled because noappend hint used
Cleaning up:
drop table tq84_dest;
drop table tq84_src ;