HYBRID COLUMNAR COMPRESSION (EXADATA)

Standard oracle block format and row format:

Rows are stored in no specific order,but columns are generally stored in the order in which they were defined in table.

For each row in a block there will be a row header and column data for each column.

These rows are stored in standard oracle block format called row piece, rows data may be stored in more than one chunk.A pointer will be pointed towards the next row piece

Compression TypesDescriptionExpected Compression
Query lowLZO Algorithm,consume least CPU,Warehouse low4x
Query highZLIB(gzip),Warehouse high6x
Archive lowZLIB(gzip),Query high7x
Archive highBZIP2,Archive low12x
Types of HCC compression types

CompressionTypemin_target_sizemax_target_sizealignment_target_size
QueryLow3200032000128000
QueryHigh3200064000128000
ArchiveLow32000256000128000
ArchiveHigh256000256000128000
Compression unit sizes for each algorithm

ARCHIVE HIGH:

Check the segment size

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segmen                                      t_name='SALES';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------                                      
      .092041016 SALES

Compress the table using archive high clause

kish@exdbx<>alter table sales compress for archive high;

Table altered.

Compress the existing data in table using archive high

kish@exdbx<>alter table sales move compress for query low parallel 2;

Table altered.

Elapsed: 00:00:11.85

Check the reduced size now

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segmen                                    t_name='SALES';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------                                      
      .037597656 SALES

QUERY HIGH:

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES3';

BYTES/1073741824 SEGMENT_NAME
---------------- --------------------------------------------------------------------------
          .09375 SALES3

Compress the table using query high clause

kish@exdbx<>alter session set tracefile_identifier='HCC_TEST';

Session altered.

kish@exdbx<>alter session set events '10046 trace name context forever,level 12';

Session altered.

Compress the existing data in table using query high clause

kish@exdbx<>alter table sales3 move compress for query high parallel 4;

Table altered.

Elapsed: 00:00:13.43

Check the final size

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES3';

BYTES/1073741824 SEGMENT_NAME
---------------- ------------------------------------------------------------------------
      .018432617 SALES3

ARCHIVE LOW

kish@exdbx<>alter table sales2 compress for archive low;

Table altered.

Elapsed: 00:00:00.03
kish@exdbx<>alter table sales2 move compress for archive low parallel 2;

Table altered.

Elapsed: 00:00:14.89

Check the presize of table

kish@exdbx<>select bytes/1073741824,segment_name from user_segments where segment_name='SALES2';

BYTES/1073741824 SEGMENT_NAME
---------------- ---------------------------------------------------------------------------------
      .081420898 SALES2

Elapsed: 00:00:00.01

Now we can identify the tables and their compression type.As you can see the different tables compressed with different types of compression.

kish@exdbx<>select owner,table_name,compress_for from dba_tables where compression='ENABLED';

OWNER                          TABLE_NAME                     COMPRESS_FOR
------------------------------ ------------------------------ ------------
SYS                            SALES                          ARCHIVE HIGH
TEST                           SALES2                         ARCHIVE HIGH
SYS                            T                              ARCHIVE HIGH
SYS                            T1                             ARCHIVE LOW
SYS                            T2                             QUERY HIGH
SYS                            T3                             QUERY LOW
SYS                            T11                            QUERY HIGH
SYS                            T12                            QUERY LOW
SYS                            T13                            ARCHIVE HIGH
SYS                            T14                            ARCHIVE LOW
SYS                            T33                            ARCHIVE HIGH
SYS                            T31                            ARCHIVE HIGH
SYS                            T30                            ARCHIVE HIGH
SYSMAN                         MGMT_POLICIES                  OLTP
SYSMAN                         MGMT_METRICS                   OLTP
TEST                           SALES3                         ARCHIVE HIGH
SYS                            SALES2                         ARCHIVE LOW
TEST                           SALES                          ARCHIVE HIGH

lets test with sys.sales table which is currently having archive high compression

There are two ways to compress the data:

  • Compress existing data
  • Compress future incoming data

To compress existing data,


kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

98MB of data should be compressed

kish@exdbx<>alter table sys.sales move compress for query high;

Table altered.

Elapsed: 00:00:21.51

It took 21 seconds for the data to get compressed for 98MB and 8MB of storage is saved with query high for instance

kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     90112

Elapsed: 00:00:00.24

To compress future incoming data not the existing data,we literally issue >alter table .. compress for < syntax

kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

Elapsed: 00:00:00.09
kish@exdbx<>alter table sys.SALES compress for query high;

Table altered.

Elapsed: 00:00:00.40
kish@exdbx<>select bytes/1024 from dba_segments where segment_name='SALES' and owner='SYS';

BYTES/1024
----------
     98304

Elapsed: 00:00:00.10
kish@exdbx<>select owner,table_name,compress_for from dba_tables where compression='ENABLED' and table_name='SALES' and owner='SYS';

OWNER                          TABLE_NAME                     COMPRESS_FOR
------------------------------ ------------------------------ ------------
SYS                            SALES                          QUERY HIGH

Elapsed: 00:00:00.25
kish@exdbx<>

In the above output,the compression factor has been changed from archive high to query high but the size of data remains same without any compressed size of segment

create a table with three partitions P1(uncompressed),P2(query high) and P3(archive low) compression

CREATE TABLE "TEST"."TABX"
( "TRANS_ID" VARCHAR2(30),
"UNIT_ID" NUMBER(30,0),
"DAY" NUMBER(30,0), 
"TRANS_SEQ" VARCHAR2(30),
"END_DATE" DATE,
"BEGIN_DATE" DATE,
"UNIT_TYPE" VARCHAR2(30),
"CUST_TYPE" VARCHAR2(1),
"LOAD_DATE" DATE,
"CURRENCY_TYPE" CHAR(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("BEGIN_DATE")
(PARTITION "P1" VALUES LESS THAN (TO_DATE
(' 2008-09-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P2" VALUES LESS THAN (TO_DATE
(' 2008-09-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR QUERY HIGH NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P3" VALUES LESS THAN (TO_DATE
(' 2008-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR ARCHIVE LOW NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) ;

Below is a compression advisor script by oracle provided by which can used to get compression ratio of objects with HCC

set serveroutput on
declare

    m_scratchtbsname    varchar2(32) := 'USERS';
    m_ownname       varchar2(32) := 'TEST';
    m_tabname       varchar2(32) := 'TABX';
	m_partname      varchar2(32) := 'P1';
    m_comptype      number(10,0) := dbms_compression.comp_for_archive_high;
--    m_comptype      number(10,0) := dbms_compression.comp_for_archive_low;
--    m_comptype      number(10,0) := dbms_compression.comp_for_query_high;
--    m_comptype      number(10,0) := dbms_compression.comp_for_query_low;
    m_blkcnt_cmp        pls_integer;
    m_blkcnt_uncmp      pls_integer;
    m_row_cmp       pls_integer;
    m_row_uncmp         pls_integer;
    m_cmp_ratio         number;
    m_comptype_str      varchar2(4000);

begin

    DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
        scratchtbsname  => m_scratchtbsname,
        ownname     => m_ownname,
        tabname     => m_tabname,
		partname    => m_partname,
        comptype    => m_comptype,
        blkcnt_cmp  => m_blkcnt_cmp,
        blkcnt_uncmp    => m_blkcnt_uncmp,
        row_cmp     => m_row_cmp,
        row_uncmp   => m_row_uncmp,
        cmp_ratio   => m_cmp_ratio,
        comptype_str    => m_comptype_str
    );

    dbms_output.put_line('Blocks compressed:           ' || m_blkcnt_cmp);
    dbms_output.put_line('Blocks uncompressed:         ' || m_blkcnt_uncmp);
    dbms_output.put_line('Rows per block compressed:   ' || m_row_cmp);
    dbms_output.put_line('Rows per block uncompressed: ' || m_row_uncmp);
    dbms_output.put_line('Compression Ratio:           ' || m_cmp_ratio);
    dbms_output.put_line('Comment:                     ' || m_comptype_str);

end;
/

Active partitions with mixed compression ratios

kish@exdbx<>set lines 155
compute sum of totalsize_megs on report
break on report on owner on segment_name
col owner for a20
col segment_name for a20
col part_name for a20
col segment_type for a10
col totalsize_megs for 999,999.9
select s.owner, segment_name, t.partition_name part_name,
sum(bytes/1024/1024) as totalsize_megs, compress_for
from dba_segments s, dba_tab_partitions t
where s.owner = t.table_owner
and t.table_name = s.segment_name
and s.owner like nvl('&owner',s.owner)
and segment_name like nvl('&table_name',segment_name)
group by s.owner, segment_name, t.partition_name, compress_for
order by 3;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6    7    8    9
Enter value for owner: TEST
old   6: and s.owner like nvl('&owner',s.owner)
new   6: and s.owner like nvl('TEST',s.owner)
Enter value for table_name: TABX
old   7: and segment_name like nvl('&table_name',segment_name)
new   7: and segment_name like nvl('TABX',segment_name)

OWNER                SEGMENT_NAME         PART_NAME            TOTALSIZE_MEGS COMPRESS_FOR
-------------------- -------------------- -------------------- -------------- ------------
TEST                 TABX                 P1                             24.0 <null>
                                          P2                             24.0 QUERY HIGH
                                          P3                             24.0 ARCHIVE LOW
******************** ********************                      --------------
sum                                                                      72.0

Elapsed: 00:00:00.96

TRACING HYBRID COLUMNAR COMPRESSION:

Lets trace EHCC using ADVCMP and examine the kernel stack behind the compression algorithms

kish@exdbx<>oradebug doc component ADVCMP


Components in library ADVCMP:
--------------------------
  ADVCMP_MAIN                  Archive Compression (kdz)
    ADVCMP_COMP                Archive Compression: Compression (kdzc, kdzh, kdza)
    ADVCMP_DECOMP              Archive Compression: Decompression (kdzd, kdzs)

There are different level of trace like low,lowest,high and highest used in ADVCMP utility

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter table test.sales_tab move compress for query high;

Table altered.

Elapsed: 00:01:47.62
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] off';

Session altered.

Elapsed: 00:00:00.00

Warning !! – Tracing HCC generate huge trace file size which you never expect .So keep a monitoring system to monitor your tracefile growth.

[oracle@exdbadm01 ~]$ du -sh /data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc
444M    /data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc

Take a look at kdzhDecideAlignment () routine in the trace output which shows the compression unit (CU) sizes with min and max target size .Interesting isnt it?

kdzcinit(): ctx: 0x2b650b5eff40  actx: (nil)  zca: (nil)  ulevel: 2  ncols: 14 totalcols: 14
kdzainit(): ctx: 2b650b5ef890 ulevel 2 amt 1048576 row 16384 min 5
kdzalcd(): objn: 88695 ulevel: 2
kdzalcd(): topalgo: -1 err: 100
kdza_init_eq(): objn: 88695  ulevel: 2  enqueue state:0


kdzhDecideAlignment(): pnum: 0 min_target_size: 32000 max_target_size: 64000 alignment_target_size: 128000 ksepec: 0 postallocmode: 0 hcc_flags: 0
 <===========================


kdzh_datasize(): freesz: 0 blkdtsz: 8168 flag: 1 initrans: 3 dbidl: 8050 dbhsz: 22 dbhszz: 14 drhsz: 9 maxmult: 1176
kdzh_datasize(): pnum: 0 ds: 8016 bs: 8192 ov: 20 alloc_num: 12 min_targetsz: 32000 max_targetsz: 64000 maxunitsz: 80000 delvec_size: 7904
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 0 kltbrld: 0 kltbnrp: 0 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 1 kltbrld: 1 kltbnrp: 1 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 2 kltbrld: 2 kltbnrp: 2 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 3 kltbrld: 3 kltbnrp: 3 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 4 kltbrld: 4 kltbnrp: 4 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 5 kltbrld: 5 kltbnrp: 5 rowcnt: 1 flags: 131
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0
kdblai(): klptrld: 6 kltbrld: 6 kltbnrp: 6 rowcnt: 1 flags: 131

kernel stack trace logs

data_block_dump,data header at 0x2b650bc8f07c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x2b650bc8f07c
     76543210
flag=-0------
ntab=1
nrow=1
frre=-1
fsbo=0x1c
fseo=0x1f
avsp=0x3
tosp=0x3
        r0_9ir2=0x0
        mec_kdbh9ir2=0x0
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R-----      Archive compression: Y
                fcls_9ir2[0]={ }
0x16:pti[0]     nrow=1  offs=0
0x1a:pri[0]     offs=0x1f
block_row_dump:
tab 0, row 0, @0x1f
tl: 8033 fb: ------PN lb: 0x0  cc: 1
nrid:  0x01005af0.0
col  0: [8021]
Compression level: 02 (Query High)
 Length of CU row: 8021

Decompression stack trace:

END_CU
bindmp: 03 00 01 01 00 5a f0 00 00 fe 55 1f 00 00 1f 55 10 b8 27 73 14 89 21 a4 1c db 82 28 7d 05 51 25 17 01 9c 6d 5c 57 ca 69 f2 07 0c 5a 8a 41 3a 56 4a 39 64 42 a6 4a 62 0f 53 f2 89 b6 f4 e8 66 a2 43 95 23 6d a6 a1 61 99 a7 6c 44 bb 61 b3 e3 2c 40 25 84

end_of_block_dump
kdzdinit(): initializing decompression context 0x2b650b726110 fco: 0x0 fcount: 0
kdzsInitialize(): context 2b650b726230 initialized
kdzdend(): freeing decompression context 0x2B650B726110
kdzsClose(): context 2b650b726230 destroyed
kdzhGetBlock():  pnum: 0 dba: 16800496 flags: 643 flag: 4 curblkidx_kdzh: 0 maxblkidx_kdzh: 2
kdzhailseb(): Normal new block avsp: 8036 fsbo: 28 fseo: 8064 nrow: 1 flags: 643 flag: 4 newed: 1 fscm: 8064 remsize: 8016
kdzhailseb(): pnum: 0 remsize : 8016 advcompress->remsize_kdzh: 6169 pdbh->kdbhavsp: 8036, cusize:23266 last_block: 0 first_block: 0
kdzhailseb(): pnum: 0 len_copied : 6169 crheader_len: 5 collen: 3, header_len: 3 curblk: 3 fscm: 8064 dba: 16800496
kdzh_copy_updptr(): numbufs:1 size: 6169 remlen: 6169 curoff: 17097 curidx: 0 culen[curidx]:23266 cusize: 23266 remsize: 6169
kdzhailseb(): Block loaded avsp: 1856 tosp: 1856 fsbo: 28 fseo: 1884 nrow: 1 flags: 643 flag: 4 fscm: 1884 remsize: 8016
bdba: 0x01005af0
data_block_dump,data header at 0x2b650bc9107c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x2b650bc9107c
     76543210
flag=-0------
ntab=1

Query high trace in log file which shows construction of compression unit

nrow=1
frre=-1
fsbo=0x1c
fseo=0x75c
avsp=0x740
tosp=0x740
        r0_9ir2=0x0
        mec_kdbh9ir2=0x0
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R-----      Archive compression: Y
                fcls_9ir2[0]={ }
0x16:pti[0]     nrow=1  offs=0
0x1a:pri[0]     offs=0x75c
block_row_dump:
tab 0, row 0, @0x75c
tl: 6180 fb: -----LP- lb: 0x0  cc: 1
col  0: [6174]
Compression level: 02 (Query High)
 Length of CU row: 6174
kdzhrh: --------START_CU:
 00 00 18 1e 10 24 31 6b 44 9d 50 55 6f 15 58 57 4f 7e 74 16 36 2f a6 9f 2a
 3f 51 73 ac 71 2c 72 c4 bd 57 39 e9 0d ea 07 9f 03 5c 30 32 c7 b2 34 11 31
 f7 36 dd 4c 12 4e ba 67 e0 0a 35 27 4c a1 2b 23 52 77 f9 4b c4 6a 31 c2 fc
 50 c5 ba 46 6e ee a1 46 aa ad 54 14 99 51 0d d1 68 99 33 be 62 59 b9 98 64
 ba ca 6f 99 d8 9b 3f 51 0b eb 6a ed 3d f8 f8 fb 23 81 61 4f 26 9d b9 0b 41

kdzcompress() algorithm for compression of objects

kdzcompress(): first CU, analyzed target input size: 152516
kdzcompress(): algo = 0, grouping = 2
kdzcompress(): ctx: 2b650b5eff40  CU: 0  Heap: 2b650b58e300  Heap size: 4535840  Allocated space: 4228984
Buffered length: 1048629  Rows: 13333  Columns: 14
kdzc_init_colgrps(): column-major
kdzc_finish_unit(): target output size: 32000  output size: 34683
kdzc_calc_new_target_input_size(): target size this unit: 140629, wtnew: 0.750000
 old target size: 152516, new target size: 143600
 input size: 152420, cu length: 34683
kdzc_finish_unit(): total units: 1 avg size: 34683
kdzc_finish_unit(): number of rows buffered: 13333  rows to unbuffer: 1934
 input size: 152420  buffered_size: 896209  new target_input_size: 143600
kdzhcl(): Invoking kdzhailseb klptpnum : 0 klptrld: 13332 kltbrld: 13332 kltbnrp: 13332 num_rows: 1934 flags: 131 *comp_state: 3
kdzhcl(): Invoking kdzhailseb pnum: 0 cusize:34683 num_bufs:1 num_rows:1934 expected_max_cusize: 0 max_delvec_size: 0 flags: 131 flag: 0
kdzhailseb(): Hybrid Columnar compression loading 1934 rows pnum: 0
kdzhailseb(): pnum: 0 context : 0 CUheader_sz: 268 CURHpmap_sz: 62 advcompress->remsize_kdzh: 34683 advcompress->cusize_kdzh: 34683 rstubsiz: 9 approx_blks: 6
kdzhailseb(): pnum: 0 flags: 131 kdobjflg: 0
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791691
kdzhailseb(): Got dba for pnum: 0 blk: 0 lens: 7954 remcusz: 26729
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791692
kdzhAUCrossed(): pnum: 0 computed alignsz: 1048576 divalign: 128
kdzhailseb(): Got dba for pnum: 0 blk: 1 lens: 8016 remcusz: 18713
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791693
kdzhailseb(): Got dba for pnum: 0 blk: 2 lens: 8016 remcusz: 10697
kdzhGetBlockDba(): kdblGet pnum: 0 dba: 16791694
kdzhailseb(): Got dba for pnum: 0 blk: 3 lens: 8016 remcusz: 2681

Check the CPU utilization while compression for all four algorithms

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP_ARC_LOW';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.04
kish@exdbx<>alter table test.sales_tab move compress for archive low;

Table altered.

Elapsed: 00:01:56.63



kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] off';

Session altered.


kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=8172;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_LOW.trc


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 
8172 oracle    25   0 2124m  72m  57m R 92.6  1.6   0:43.09 oracle

Archive low CPU usage()

kish@exdbx<>alter session set tracefile_identifier='HCC_CMP_ARC_HIGH';

Session altered.

Elapsed: 00:00:00.00

kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter table test.sales_tab move compress for archive high;

Table altered.

Elapsed: 00:01:59.35

kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=8172;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_8172_HCC_CMP_ARC_HIGH.trc

 
 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8172 oracle    25   0 2157m 113m  72m R 98.7  2.5   2:03.88 oracle

Compression Analyzer Context Dump End
-------------------------------------
kdzalcd(): topalgo: 0 err: 0
kdza_init_eq(): objn: 88695  ulevel: 4  state: 0  already analyzed
kdzhDecideAlignment(): pnum: 0 min_target_size: 256000 max_target_size: 256000 alignment_target_size: 128000 ksepec: 0 postallocmode: 0 hcc_flags: 0
kdzh_datasize(): freesz: 0 blkdtsz: 8168 flag: 1 initrans: 3 dbidl: 8050 dbhsz: 22 dbhszz: 14 drhsz: 9 maxmult: 1
kdzh_datasize(): pnum: 0 ds: 8016 bs: 8192 ov: 20 alloc_num: 42 min_targetsz: 256000 max_targetsz: 256000 maxunitsz: 320000 delvec_size: 7604
kdzhbirc(): pnum: 0 buffer 1 rows soff: 0

kdzhGetBlockDba(): FETCHDBAS pnum: 0 numdbas_kdzh: 13, curdbidx_kdzh: 10 dbas_kdzh[0]:16777867 dbas_kdzh[curdbidx]:16777877 dba_kdzh[0]:16777867
kdzhailseb(): Got dba for pnum: 0 blk: 10 lens: 8016 remcusz: 6230
kdzhGetBlockDba(): FETCHDBAS pnum: 0 numdbas_kdzh: 13, curdbidx_kdzh: 11 dbas_kdzh[0]:16777867 dbas_kdzh[curdbidx]:16777878 dba_kdzh[0]:16777867
kdzhailseb(): Got dba for pnum: 0 blk: 11 lens: 6230 remcusz: 0
kdzhailseb(): Start loading loop pnum: 0 nblks: 12 ctx_restored: 0 flags: 139 flag: 0 dbas_contiguous: 11
kdzhGetBlock():  pnum: 0 dba: 16777867 flags: 139 flag: 0 curblkidx_kdzh: 0 maxblkidx_kdzh: 14
kdzhailseb(): Normal new block avsp: 8036 fsbo: 28 fseo: 8064 nrow: 1 flags: 139 flag: 0 newed: 1 fscm: 8064 remsize: 8016
kdzhailseb(): pnum: 0 remsize : 8016 advcompress->remsize_kdzh: 94274 pdbh->kdbhavsp: 8036, cusize:94274 last_block: 0 first_block: 1
kdzhailseb(): pnum: 0 len_copied : 7884 crheader_len: 120 collen: 3, header_len: 9 curblk: 0 fscm: 8064 dba: 16777867
kdzh_copy_updptr(): numbufs:1 size: 7884 remlen: 94274 curoff: 0 curidx: 0 culen[curidx]:94274 cusize: 94274 remsize: 94274
kdzhailseb(): Block loaded avsp: 20 tosp: 20 fsbo: 28 fseo: 48 nrow: 1 flags: 139 flag: 4 fscm: 48 remsize: 8016
bdba: 0x0100028b
data_block_dump,data header at 0x2b58079f907c
===============


 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8172 oracle    25   0 2129m  88m  73m R 97.0  1.9   4:52.14 oracle

Query low CPU usage()

kish@exdbx<>select username,terminal,program,tracefile from v$process where spid=3276;

USERNAME        TERMINAL                       PROGRAM
--------------- ------------------------------ ------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle          UNKNOWN                        oracle@exdbadm01 (TNS V1-V3)
/data/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_ora_3276_HCC_QUERY_LOW.trc

kish@exdbx<>alter session set tracefile_identifier='HCC_QUERY_LOW';

Session altered.

Elapsed: 00:00:00.02
kish@exdbx<>alter session set events 'trace[ADVCMP_MAIN.*] disk=high';

Session altered.

Elapsed: 00:00:00.23
kish@exdbx<>alter table test.sales_tab move compress for query low;
Elapsed: 00:02:38.51


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3276 oracle    25   0 2116m  51m  43m R 96.2  1.1   0:17.97 oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s