Thursday, August 11, 2011

INSERT with PARALLEL hint + force direct-path INSERT

I posted What wrong? (INSERT) PARALLEL hint - It not compress. I know because it's not use "direct path write".
How Hybrid Columnar Compression Works When Rows are Inserted/Updated. [ID 1332853.1]
The hybrid columnar compression (HCC) works with Direct path loads only like ALTER TABLE MOVE, IMPDP with DIRECT option or DIRECT path inserts. HCC is meant for those type of data which do not get updated or not updated very often. If you update rows in HCC table, The entire compression unit is locked. The rows that are updated may be moved to a lower compression level such as No compression/ OLTP compression

But many friends told me, it should work with compression. I tested by using "ALTER SESSION force PARALLEL DML", To make sure it uses parallel with DML(direct-path INSERT).

Case 1:
SQL> insert /*+ parallel */ into tb_b t select * from dba_objects where rownum <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select sum(g.bytes / 1024) sum,
g.segment_name,
g.tablespace_name
from dba_segments g
where g.segment_name = 'TB_B'
group by tablespace_name, segment_name; 2 3 4 5 6

SUM SEGMENT_NAME TABLESPACE_NAME
---------- --------------------------------------------------------------------------------- ------------------------------
1024 TB_B TBS_TEST_EXA
Case 2: use "ALTER SESSION { ENABLE | FORCE } PARALLEL DML" before insert
SQL> ALTER SESSION force PARALLEL DML;

Session altered.

SQL> insert /*+ parallel */ into tb_b t select * from dba_objects where rownum <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select sum(g.bytes / 1024) sum,
g.segment_name,
g.tablespace_name
from dba_segments g
where g.segment_name = 'TB_B'
group by tablespace_name, segment_name;

SUM SEGMENT_NAME TABLESPACE_NAME
---------- --------------------------------------------------------------------------------- ------------------------------
64 TB_B TBS_TEST_EXA
trace 10046 and review trace file:

PARSING IN CURSOR #47620816211296 len=198 dep=1 uid=0 oct=3 lid=0 tim=1313054941154387 hv=2190895844 ad='c3af40060' sqlid='b91n32219cur4'
select ilevel, bestsortcol, tinsize, toutsize, analyzer from (select * from compression$ where obj#=:1 and ulevel=:2 order by mtime desc) where rownum < 2
END OF STMT

WAIT #47620816212296: nam='direct path write' ela= 312 file number=530 first dba=556 block cnt=8 obj#=19 tim=1313054941193775
WAIT #47620816212296: nam='enq: FB - contention' ela= 134 name|mode=1178730502 tablespace #=536 dba=2222981680 obj#=-1 tim=1313054941210382
WAIT #47620816212296: nam='gc current multi block request' ela= 138 file#=530 block#=567 id#=33554433 obj#=55937 tim=1313054941210600
WAIT #47620816212296: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=203 new aio limit=201 obj#=19 tim=1313054941211131
EXEC #47620816212296:c=266959,e=3487089,p=0,cr=810,cu=118,mis=1,r=10000,dep=0,og=1,plh=1346303798,tim=1313054941211293
STAT #47620816212296 id=1 cnt=1 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=803 pr=0 pw=8 time=3478516 us)'

It used "direct path write" with parallel and DATA was Compressed!!!

No comments: