Thursday, August 11, 2011

What wrong? (INSERT) PARALLEL hint - It not compress

I saw some post about (INSERT) parallel hint not work with "compress for query high " on communities.oracle.com, So I tested it...
*** Oracle 11.2.0.2 and Image version: 11.2.2.2.0.101206.2 ***
On table no compress:
SQL> create table tb_a tablespace TBS_TEST_EXA as select * from dba_objects where 1=0;

Table created.

SQL> insert /*+ parallel */ into tb_a t select * from dba_objects;

17808 rows created.

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

SUM SEGMENT_NAME TABLESPACE_NAME
---------- --------------------------------------------------------------------------------- ------------------------------
2048 TB_A TBS_TEST_EXA
On compress for query high:
SQL> create table tb_b tablespace TBS_TEST_EXA compress for query high as select * from dba_objects where 1=0;

Table created.
Case A: Use PARALLEL hint
SQL> ALTER session SET EVENTS '10046 trace name context forever, level 12';
SQL> insert /*+ parallel(t) */ into tb_b t select * from dba_objects;
SQL> ALTER session SET EVENTS '10046 trace name context off';
In trace file: I don't see about compression. just see:

WAIT #47502448530384: nam='asynch descriptor resize' ela= 2 outstanding #aio=0 current aio limit=219 new aio limit=154 obj#=19 tim=1313001654410274
EXEC #47502448530384:c=101984,e=113787,p=0,cr=1579,cu=2514,mis=0,r=17807,dep=0,og=1,plh=3173891493,tim=1313001654410582
STAT #47502448530384 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1579 pr=0 pw=0 time=113756 us)'


Check segment size:
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
---------- --------------------------------------------------------------------------------- ------------------------------
2048 TB_B TBS_TEST_EXA
Case B: Use APPEND hint
SQL> ALTER session SET EVENTS '10046 trace name context forever, level 12';
SQL> insert /*+ append */ into tb_b t select * from dba_objects;
SQL> ALTER session SET EVENTS '10046 trace name context off';
In trace file:

PARSING IN CURSOR #47502447102008 len=198 dep=1 uid=0 oct=3 lid=0 tim=1313001730563910 hv=2190895844 ad='da3536290' 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 #47502448530384: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=223 new aio limit=158 obj#=19 tim=1313001730671372
WAIT #47502448530384: nam='direct path write' ela= 380 file number=530 first dba=642 block cnt=15 obj#=19 tim=1313001730683122
EXEC #47502448530384:c=118983,e=121768,p=0,cr=781,cu=55,mis=0,r=17807,dep=0,og=1,plh=332911567,tim=1313001730683230
STAT #47502448530384 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD AS SELECT (cr=781 pr=0 pw=15 time=121705 us)'

Check segment size:
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
---------- --------------------------------------------------------------------------------- ------------------------------
192 TB_B TBS_TEST_EXA
Oops! What wrong?

3 comments:

Surachart Opun said...

Thank You someone in communities.oracle.com for
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

Surachart Opun said...

Test: alter table move

ALTER session SET EVENTS '10046 trace name context forever, level 12';
alter table tb_b move;
ALTER session SET EVENTS '10046 trace name context off';

trace file:

PARSING IN CURSOR #47620816211296 len=198 dep=1 uid=0 oct=3 lid=0 tim=1313054321509642 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= 39670 file number=530 first dba=545 block cnt=5 obj#=55937 tim=1313054321700715
WAIT #47620816212296: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=201 new aio limit=199 obj#=55937 tim=1313054321700917

Surachart Opun said...

After I tested more ...
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;

SUM SEGMENT_NAME TABLESPACE_NAME
---------- --------------------------------------------------------------------------------- ------------------------------
1024 TB_B TBS_TEST_EXA


Case 2: enable parallel DML

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


in trace file (Case 2) :

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)'


*** ALTER SESSION force PARALLEL DML - Oracle use direct path and compress data ***