search
HomeDatabaseMysql TutorialOracle压缩功能小结2—预估表压缩的效果

在使用压缩之前,我们可以估算一下使用压缩能够拥有多大的效果。 11gr2以前可以使用dbms_comp_advisor,具体代码已经在附件中给出。只需要执行两个文件dbmscomp.sql和prvtcomp.plb,然后使用DBMS_COMP_ADVISOR.getratio存储过程即可。不再详细描述。 SQL set

在使用压缩之前,我们可以估算一下使用压缩能够拥有多大的效果。

11gr2以前可以使用dbms_comp_advisor,具体代码已经在附件中给出。只需要执行两个文件dbmscomp.sql和prvtcomp.plb,然后使用DBMS_COMP_ADVISOR.getratio存储过程即可。不再详细描述。


SQL> set serveroutput on
SQL> execdbms_comp_advisor.getratio('SH','SALES',10)
Sampling table: SH.SALES
Sampling percentage: 10%
Estimated compression ratio for the advancedcompression option is : 2.96

11gr2以后系统会自带一个dbms_compression的包,用来代替dbms_comp_advisor提供服务。

 

_sys@FAKE> desc dbms_compression
PROCEDURE GET_COMPRESSION_RATIO
 ArgumentName                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 SCRATCHTBSNAME                 VARCHAR2                IN
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN
 COMPTYPE                       NUMBER                  IN
 BLKCNT_CMP                     BINARY_INTEGER          OUT
 BLKCNT_UNCMP                   BINARY_INTEGER          OUT
 ROW_CMP                        BINARY_INTEGER          OUT
 ROW_UNCMP                      BINARY_INTEGER          OUT
 CMP_RATIO                      NUMBER                  OUT
 COMPTYPE_STR                   VARCHAR2                OUT
 SUBSET_NUMROWS                 NUMBER                  IN     DEFAULT
FUNCTION GET_COMPRESSION_TYPE RETURNS NUMBER
 ArgumentName                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 ROW_ID                         ROWID                   IN
PROCEDURE INCREMENTAL_COMPRESS
 ArgumentName                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 OWNNAME                        VARCHAR2(30)            IN
 TABNAME                        VARCHAR2(128)           IN
 PARTNAME                       VARCHAR2(30)            IN
 COLNAME                        VARCHAR2                IN
 DUMP_ON                        NUMBER                  IN     DEFAULT
 AUTOCOMPRESS_ON                NUMBER                  IN     DEFAULT
 WHERE_CLAUSE                   VARCHAR2                IN     DEFAULT
 


重点看GET_COMPRESSION_RATIO这个存储过程,它可以预估表的压缩比例。
可以使用以下的匿名块执行。

DECLARE
 blkcnt_comp PLS_INTEGER;
 blkcnt_uncm PLS_INTEGER;
 row_comp    PLS_INTEGER;
 row_uncm    PLS_INTEGER;
 comp_ratio  number;
 comp_type   VARCHAR2(30);
 username varchar2(30) := '&USER';
 tablename varchar2(30) := '&TB' ;
BEGIN
 dbms_compression.get_compression_ratio('&Usedtbs',
                                         username,
                                        tablename,
                                         NULL,
                                        dbms_compression.COMP_FOR_OLTP,
                                        blkcnt_comp,
                                         blkcnt_uncm,
                                        row_comp,
                                        row_uncm,
                                        comp_ratio,
                                        comp_type);
 dbms_output.put_line('Sampling table: '||username||'.'||tablename);
 dbms_output.put_line('Estimated compression ratio: ' ||TO_CHAR(comp_ratio));
 dbms_output.put_line('Compression Type: ' || comp_type);
END;
/

执行效果:

 

 /
Enter value for user: DEXTER
old  8:   username varchar2(30) :='&USER';
new  8:   username varchar2(30) :='DEXTER';
Enter value for tb: ACCOUNT
old  9:   tablename varchar2(30) :='&TB' ;
new  9:   tablename varchar2(30) :='ACCOUNT' ;
Enter value for usedtbs: USERS
old 11:   dbms_compression.get_compression_ratio('&Usedtbs',
new 11:  dbms_compression.get_compression_ratio('USERS',
Sampling table: DEXTER.ACCOUNT
Estimated compression ratio: 1
Compression Type: "Compress For OLTP"
 
PL/SQL procedure successfully completed.


因为表中的重复值非常少,上文中Estimated compression ratio: 1,表示没有任何压缩效果。
高级压缩,基于块内的压缩。所以就算有重复值,但是没有在一个块中,那么高级压缩还是无法起作用。

这里重点介绍一个参数 COMPTYPE,它一共有6个选项,分别是


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;

Query high 以下都是HCC(HybridColumnar Compression)的内容,因为与Exadata的存储节点相关,所以在非Exadata一体机环境无法使用。不过有意思的是,你可以在普通环境下使用get_compression_ratio来预估压缩的比例。




11gr2以前compression-advisor存储过程下载地址:

http://download.csdn.net/detail/renfengjun/7514723


 

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL handle concurrency compared to other RDBMS?How does MySQL handle concurrency compared to other RDBMS?Apr 29, 2025 am 12:44 AM

MySQLhandlesconcurrencyusingamixofrow-levelandtable-levellocking,primarilythroughInnoDB'srow-levellocking.ComparedtootherRDBMS,MySQL'sapproachisefficientformanyusecasesbutmayfacechallengeswithdeadlocksandlacksadvancedfeatureslikePostgreSQL'sSerializa

How does MySQL handle transactions compared to other relational databases?How does MySQL handle transactions compared to other relational databases?Apr 29, 2025 am 12:37 AM

MySQLhandlestransactionseffectivelyusingtheInnoDBengine,supportingACIDpropertiessimilartoPostgreSQLandOracle.1)MySQLusesREPEATABLEREADasthedefaultisolationlevel,whichcanbeadjustedtoREADCOMMITTEDforhigh-trafficscenarios.2)Itoptimizesperformancewithabu

What are some best practices for writing efficient SQL queries in MySQL?What are some best practices for writing efficient SQL queries in MySQL?Apr 29, 2025 am 12:24 AM

Best practices include: 1) Understanding the data structure and MySQL processing methods, 2) Appropriate indexing, 3) Avoid SELECT*, 4) Using appropriate JOIN types, 5) Use subqueries with caution, 6) Analyzing queries with EXPLAIN, 7) Consider the impact of queries on server resources, 8) Maintain the database regularly. These practices can make MySQL queries not only fast, but also maintainability, scalability and resource efficiency.

How does MySQL differ from PostgreSQL?How does MySQL differ from PostgreSQL?Apr 29, 2025 am 12:23 AM

MySQLisbetterforspeedandsimplicity,suitableforwebapplications;PostgreSQLexcelsincomplexdatascenarioswithrobustfeatures.MySQLisidealforquickprojectsandread-heavytasks,whilePostgreSQLispreferredforapplicationsrequiringstrictdataintegrityandadvancedSQLf

How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software