11g compression 新特性(1)

11g引入了大量compress相关的特性,其中之一便是dbms_compression包;GET_COMPRESSION_RATIO函数可以帮助我们了解压缩某个表后各种可能的影响。换而言之,这个函数可以让我们在具体实施表压缩技术或者测试前,对于压缩后的效果能有一个基本的印象。该包在11gr2中被首次引入,故而使用之前版本的包括11gr1都无缘得用。其次除OLTP压缩模式之外的柱形混合压缩只能在基于Exdata存储的表空间上实现。使用DBMS_COMPRESSION包获取的相关压缩信息是十分准确的,因为在评估过程中Oracle通过实际采样并建立模型表以尽可能还原逼真的数据。 我们可以通过trace来分析其评估过程中的具体操作,可以分成2步:

1. 建立原表的样本表,其采样值基于原表的大小:

SQL> create table samp_dss_nation tablespace SCRATCH as select * from dss_nation sample block (50);

Table created.

2. 基于采用表建立对应压缩类型的模型表:

SQL> create table model_dss_nation tablespace SCRATCH compress for query high as select * from samp_dss_nation;
create table model_dss_nation tablespace SCRATCH compress for query high as select * from samp_dss_nation
*
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

可以看到在实际建立过程中Oracle将拒绝在非Exdata存储的表空间上建立该类柱形混合压缩(包括:COMP_FOR_QUERY_HIGH,COMP_FOR_QUERY_LOW,COMP_FOR_ARCHIVE_HIGH,CO
MP_FOR_ARCHIVE_LOW)。但DBMS_COMPRESSION在进行评估时可以绕过Oracle对于该类操作的LOCK.

要在没有Exdata存储设备的情况下使用dbms_compression包评测OLTP压缩模式外的柱状混合压缩模式时
(hybrid columnar compression is only supported in tablespaces residing on Exadata storage),首先需要打上patch 8896202:

[oracle@rh2 admin]$ /s01/dbhome_1/OPatch/opatch lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/dbhome_1
Central Inventory : /s01/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /s01/dbhome_1/oui
Log file location : /s01/dbhome_1/cfgtoollogs/opatch/opatch2010-06-02_23-08-33PM.log

Patch history file: /s01/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /s01/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2010-06-02_23-08-33PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  8896202      : applied on Wed Jun 02 21:55:44 CST 2010
Unique Patch ID:  11909460
Created on 29 Oct 2009, 15:21:45 hrs US/Pacific
Bugs fixed:
8896202

该patch用以:ENABLE COMPRESSION ADVISOR TO ESTIMATE EXADATA HCC COMPRESSION RATIOS

接着我们还需要运行被修改后的DBMSCOMP包创建SQL,具体操作为:

SQL> @?/rdbms/admin/prvtcmpr.plb

Package created.

Grant succeeded.

Package body created.

No errors.

Package body created.

No errors.

Type body created.

No errors.
SQL> @?/rdbms/admin/dbmscomp.sql

Package created.

Synonym created.

Grant succeeded.

No errors.

DBMS_COMPRESSION包在对表压缩进行评估时,默认表最少数据为1000000行,可能在你的测试库中没有这么多数据,我们可以修改这个下限;

通过将COMP_RATIO_MINROWS常数修改为1后,就可以分析最小为1行的表了:

SQL>create or replace package sys.dbms_compression authid current_user is

  COMP_NOCOMPRESS       CONSTANT NUMBER := 1;
  COMP_FOR_OLTP         CONSTANT NUMBER := 2;
  COMP_FOR_QUERY_HIGH   CONSTANT NUMBER := 4;
  COMP_FOR_QUERY_LOW    CONSTANT NUMBER := 8;
  COMP_FOR_ARCHIVE_HIGH CONSTANT NUMBER := 16;
  COMP_FOR_ARCHIVE_LOW  CONSTANT NUMBER := 32;

  COMP_RATIO_MINROWS CONSTANT NUMBER := 10;
  COMP_RATIO_ALLROWS CONSTANT NUMBER := -1;

  PROCEDURE get_compression_ratio(scratchtbsname IN varchar2,
                                  ownname        IN varchar2,
                                  tabname        IN varchar2,
                                  partname       IN varchar2,
                                  comptype       IN number,
                                  blkcnt_cmp     OUT PLS_INTEGER,
                                  blkcnt_uncmp   OUT PLS_INTEGER,
                                  row_cmp        OUT PLS_INTEGER,
                                  row_uncmp      OUT PLS_INTEGER,
                                  cmp_ratio      OUT NUMBER,
                                  comptype_str   OUT varchar2,
                                  subset_numrows IN number DEFAULT COMP_RATIO_MINROWS);

  function get_compression_type(ownname IN varchar2,
                                tabname IN varchar2,
                                row_id  IN rowid) return number;

  PROCEDURE incremental_compress(ownname         IN dba_objects.owner%type,
                                 tabname         IN dba_objects.object_name%type,
                                 partname        IN dba_objects.subobject_name%type,
                                 colname         IN varchar2,
                                 dump_on         IN number default 0,
                                 autocompress_on IN number default 0,
                                 where_clause    IN varchar2 default '');

end dbms_compression;

Package created.

SQL> alter package dbms_compression compile body;

Package body altered.

接下来我们通过建立一个基于TPC-D的测试的Schema,保证各表上有较多的数据,并且数据有一定的拟真度:

SQL> select table_name,num_rows,blocks from user_tables ;

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
DSS_SUPPLIER                        20000        496
DSS_PART                           400000       7552
DSS_REGION                              5          5
DSS_PARTSUPP                      1600000      29349
DSS_LINEITEM                     12000000     221376
DSS_ORDER                         3000000      48601
DSS_CUSTOMER                       300000       6922
DSS_NATION                             25          5

现在可以进行压缩评估了,我们针对测试模型Schema编辑以下匿名块并运行

SQL> set serveroutput on;
SQL> declare
  cmp_blk_cnt   binary_integer;
  uncmp_blk_cnt binary_integer;
  cmp_rows      binary_integer;
  uncmp_rows    binary_integer;
  cmp_ratio     number;
  cmp_typ       varchar2(100);
BEGIN
  for i in (SELECT TABLE_NAME
              from dba_tables
             where compression = 'DISABLED'
               and owner = 'MACLEAN' and num_rows>1000000) loop
    for j in 1 .. 5 loop
      dbms_compression.get_compression_ratio(scratchtbsname => 'SCRATCH',
                                             ownname        => 'MACLEAN',
                                             tabname        => i.table_name,
                                             partname       => NULL,
                                             comptype       => power(2, j),
                                             blkcnt_cmp     => cmp_blk_cnt,
                                             blkcnt_uncmp   => uncmp_blk_cnt,
                                             row_cmp        => cmp_rows,
                                             row_uncmp      => uncmp_rows,
                                             cmp_ratio      => cmp_ratio,
                                             comptype_str   => cmp_typ);
      dbms_output.put_line(i.table_name || '--' || 'compress_type is ' ||
                           cmp_typ || ' ratio :' ||
                           to_char(cmp_ratio, '99.9') || '%');

    end loop;
  end loop;
end;
/
DSS_ORDER--compress_type is "Compress For OLTP" ratio :  1.1%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_ORDER--compress_type is "Compress For Query High" ratio :  2.7%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_ORDER--compress_type is "Compress For Query Low" ratio :  1.7%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_ORDER--compress_type is "Compress For Archive High" ratio :  2.9%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_ORDER--compress_type is "Compress For Archive Low" ratio :  2.7%
DSS_PARTSUPP--compress_type is "Compress For OLTP" ratio :   .9%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_PARTSUPP--compress_type is "Compress For Query High" ratio :  1.8%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_PARTSUPP--compress_type is "Compress For Query Low" ratio :  1.2%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_PARTSUPP--compress_type is "Compress For Archive High" ratio :  1.9%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_PARTSUPP--compress_type is "Compress For Archive Low" ratio :  1.8%
DSS_LINEITEM--compress_type is "Compress For OLTP" ratio :  1.4%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_LINEITEM--compress_type is "Compress For Query High" ratio :  3.5%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_LINEITEM--compress_type is "Compress For Query Low" ratio :  2.3%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_LINEITEM--compress_type is "Compress For Archive High" ratio :  4.3%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
DSS_LINEITEM--compress_type is "Compress For Archive Low" ratio :  3.7%

PL/SQL procedure successfully completed.

可以从上述测试看到,”Compress For Archive High”压缩率最高,该类型最适合于数据归档存储,但其算法复杂度高于”Compress For Archive Low”,压缩耗时亦随之上升。
总体压缩率都较低,这同TPC-D测试的数据建模有一定关联,我们再使用一组TPC-H的测试数据来模拟压缩:

SQL> conn liu/liu;
Connected.

SQL> select num_rows,blocks,table_name from user_tables;

  NUM_ROWS     BLOCKS TABLE_NAME
---------- ---------- ------------------------------
   3000000      46817 H_ORDER
    300000       6040 H_CUSTOMER
  12000000     221376 H_LINEITEM
        25          5 H_NATION
    400000       7552 H_PART
         5          5 H_REGION
   1600000      17491 H_PARTSUPP
     20000        496 H_SUPPLIER

8 rows selected.

SQL> set serveroutput on;
SQL> declare
  cmp_blk_cnt   binary_integer;
  uncmp_blk_cnt binary_integer;
  cmp_rows      binary_integer;
  uncmp_rows    binary_integer;
  cmp_ratio     number;
  cmp_typ       varchar2(100);
BEGIN
  for i in (SELECT TABLE_NAME
              from dba_tables
             where compression = 'DISABLED'
               and owner = 'LIU' and num_rows>1000000) loop
    for j in 1 .. 5 loop
      dbms_compression.get_compression_ratio(scratchtbsname => 'SCRATCH',
                                             ownname        => 'LIU',
                                             tabname        => i.table_name,
                                             partname       => NULL,
                                             comptype       => power(2, j),
                                             blkcnt_cmp     => cmp_blk_cnt,
                                             blkcnt_uncmp   => uncmp_blk_cnt,
                                             row_cmp        => cmp_rows,
                                             row_uncmp      => uncmp_rows,
                                             cmp_ratio      => cmp_ratio,
                                             comptype_str   => cmp_typ);
      dbms_output.put_line(i.table_name || '--' || 'compress_type is ' ||
                           cmp_typ || ' ratio :' ||
                           to_char(cmp_ratio, '99.9') || '%');

    end loop;
  end loop;
end;
/
H_ORDER--compress_type is "Compress For OLTP" ratio :  1.1%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_ORDER--compress_type is "Compress For Query High" ratio :  5.2%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_ORDER--compress_type is "Compress For Query Low" ratio :  2.9%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_ORDER--compress_type is "Compress For Archive High" ratio :  7.2%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_ORDER--compress_type is "Compress For Archive Low" ratio :  5.5%
H_PARTSUPP--compress_type is "Compress For OLTP" ratio :   .9%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_PARTSUPP--compress_type is "Compress For Query High" ratio :  5.1%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_PARTSUPP--compress_type is "Compress For Query Low" ratio :  2.7%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_PARTSUPP--compress_type is "Compress For Archive High" ratio :  7.2%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_PARTSUPP--compress_type is "Compress For Archive Low" ratio :  5.3%
H_LINEITEM--compress_type is "Compress For OLTP" ratio :  1.4%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_LINEITEM--compress_type is "Compress For Query High" ratio :  5.2%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_LINEITEM--compress_type is "Compress For Query Low" ratio :  3.0%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_LINEITEM--compress_type is "Compress For Archive High" ratio :  7.4%
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
H_LINEITEM--compress_type is "Compress For Archive Low" ratio :  5.6%

PL/SQL procedure successfully completed.

可以看到相比TPC-D的测试用数据,TPC-H建立的数据更具可压缩性。

PS:
TPC-D represents a broad range of decision support (DS) applications that require complex, long running queries against large complex data structures. Real-world business questions were written against this model, resulting in 17 complex queries.
The TPC Benchmark™H (TPC-H) is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.
The performance metric reported by TPC-H is called the TPC-H Composite Query-per-Hour Performance Metric (QphH@Size), and reflects multiple aspects of the capability of the system to process queries. These aspects include the selected database size against which the queries are executed, the query processing power when queries are submitted by a single stream, and the query throughput when queries are submitted by multiple concurrent users. The TPC-H Price/Performance metric is expressed as $/QphH@Size.

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. admin says:

    Applies to:
    Oracle Server – Enterprise Edition – Version: 11.2.0.1 to 11.2.0.1.0 – Release: 11.2 to 11.2
    Information in this document applies to any platform.
    Symptoms
    When using DBMS_COMPRESSION.GET_COMPRESSION_RATIO

    SQL> set serveroutput on
    SQL> DECLARE
    2 blkcnt_cmp pls_integer;
    3 blkcnt_uncmp pls_integer;
    4 row_cmp pls_integer;
    5 row_uncmp pls_integer;
    6 cmp_ratio pls_integer;
    7 comptype_str varchar2(100);
    8 BEGIN
    9 DBMS_COMPRESSION.GET_COMPRESSION_RATIO (‘USERS’, ‘SCOTT’, ‘EMP’, ”,
    10 DBMS_COMPRESSION.COMP_FOR_OLTP,
    11 blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
    12
    13 DBMS_OUTPUT.PUT_LINE(‘Block count compressed = ‘ || blkcnt_cmp);
    14 DBMS_OUTPUT.PUT_LINE(‘Block count uncompressed = ‘ || blkcnt_uncmp);
    15 DBMS_OUTPUT.PUT_LINE(‘Row count per block compressed = ‘ || row_cmp);
    16 DBMS_OUTPUT.PUT_LINE(‘Row count per block uncompressed = ‘ || row_uncmp);
    17 DBMS_OUTPUT.PUT_LINE(‘Compression type = ‘ || comptype_str);
    18 DBMS_OUTPUT.PUT_LINE(‘Compression ratio = ‘||blkcnt_uncmp/blkcnt_cmp||’ to 1′);
    19 DBMS_OUTPUT.PUT_LINE(‘Compression ratio org= ‘||cmp_ratio);
    20 END;
    21 /

    the following errors are coming:

    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at “SYS.PRVT_COMPRESSION”, line 459
    ORA-01536: space quota exceeded for tablespace ‘USERS’
    ORA-06512: at “SYS.DBMS_COMPRESSION”, line 214
    ORA-06512: at line 9

    Cause
    There is a privilege missing for the user specified as the second parameter in the DBMS_COMPRESSION.GET_COMPRESSION_RATIO.
    Solution
    To get rid of the errors, the user specified as the second parameter in the DBMS_COMPRESSION.GET_COMPRESSION_RATIO need to have UNLIMITED TABLESPACE privilege or at least some QUOTA over the scratch tablespace specified by the first parameter.

    When using DBMS_COMPRESSION.GET_COMPRESSION_RATIO to analyze the compression ratio of a table, and to get information about compressibility of a table it is important to take care at the privileges the user has over the scratch tablespace.
    It is needed to have UNLIMITED TABLESPACE granted over the scratch tablespace or at least some quota over this tablespace as the procedure creates different tables in the scratch tablespace and runs analysis on these objects.

  2. admin says:

    11g new features:Advanced Compression overview and advantages
    Applies to:
    Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.2.0.1.0
    Information in this document applies to any platform.
    Oracle Server Enterprise Edition – Version: 11.1.0.6 to 11.2.0.1.0
    Purpose

    The aim of this article is to discuss benefits of Advanced Compression.
    Scope and Application

    DBA’s interested in understanding and using Advanced Compression.
    11g new features:Advanced Compression overview and advantages
    OVERVIEW

    The Oracle Database 11g Advanced Compression Option introduces a comprehensive set of compression capabilities to help customers maximize resource utilization and reduce costs.

    Oracle 11g introduces supports regular structured data (numbers, characters), unstructured data (documents, spreadsheets, XML and other files), and other backup data.
    Note: The Oracle 11g Advanced Compression Option is licensed separately from the RDBMS Enterprise Edition. Please see the licensing documentation referenced at the end of this note.

    The Oracle Advanced Compression option contains the following features:
    OLTP TABLE COMPRESSION

    Oracle Database 9i introduced Table Compression several years ago to allow data to be compressed during bulk load operations such as direct path load, CREATE TABLE AS SELECT…. (CTAS), etc.. This form of compression was ideally suited for data warehousing environments where most data is loaded in the database using batch processes. Oracle Database 11g Table Compression allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE.

    These operations include:

    1) Direct path SQL*Loader

    2) CREATE TABLE and AS SELECT statements

    3) Parallel INSERT (or serial INSERT with an APPEND hint) statements

    4) Single-row or array inserts

    5) Single-row or array updates

    Existing data in the database can also be compressed by moving it into compressed form through ALTER TABLE and MOVE statements. This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes.

    Compressed tables or partitions can be modified the same as other Oracle Database tables or partitions. Deleting compressed data is as fast as deleting uncompressed data. Inserting new data is also as fast. Updating compressed data can be slower in some cases, because Oracle Database supports all DML operations (insert, update, delete) on compressed tables.

    This new feature significantly improves performance by reducing the overhead of write operations making it suitable for transactional or OLTP environments as well.
    SECURE FILE COMPRESSION AND DEDUPLICATION

    SecureFiles Deduplication is an intelligent technology that eliminates duplicate copies of SecureFiles data. Oracle stores one image of the SecureFiles data and replaces the duplicate copies with references to this image.

    EXAMPLE: 10 users receive an email with the same 1MB attachment. Without SecureFiles Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in our example had used SecureFiles with Deduplication, it would have stored the 1MB attachment just once. That’s a 90% savings in storage requirements.
    DATA PUMP COMPRESSION

    With Data Pump compression,table data can be compressed on export.There is no need to uncompress a dump file before importing it.The compressed dump file sets are automatically decompressed during an import operation without any additional steps by the Database Administrator.
    RMAN COMPRESSION

    With RMAN Compression backup data is compressed before it is written to disk or tape and doesn’t need to be uncompressed before recovery – providing an enormous reduction in storage costs.
    DATA GUARD NETWORK COMPRESSION

    Oracle Advanced Compression introduces the capability to compress redo data as it is sent over the network during redo gap resolution. Through this compression network bandwidth is maximized to increase the gap resolution throughput. Gap resolution can be up to two times faster with compression – ensuring that the standby database is quickly synchronized and High Availability is achieved.

    For more information, please refer to these documents on the Oracle Technology Network (OTN): http://www.oracle.com/technology/index.html

Trackbacks

  1. […] columnar compression可以参见这里。 包括软件许可证(license)和第一年维护支持,总费用达到了$7,240,240; […]

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569