Practice 32 - Using Table Compression P a g e |1
Practice 32
Using Table Compression
Practice Target
In this practice, you will perform the following:
• Examine when basic compression and advanced compression take effect
• Use basic compression and direct path loading in enhancing query performance
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |2
Preparing the Practice Environment
In the following steps, you will create the script files that will be used in this practice.
1. Open Putty, login as oracle to srv1.
2. Run the following code to create a script file named as display_table_stats.sql
The script displays the size statistics about the tables that will be created in this practice.
cat > display_table_stats.sql <<EOL
col TABLE_NAME format a17
SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE
FROM USER_TABLES
WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');
EOL
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |3
Examining the Way Compression Types Work
In this section of the practice, you will create three tables of different compression types and examine
their effect on saving disk space.
3. Invoke SQL*Plus, and login as soe to ORADB.
sqlplus soe/soe
4. Run the following code to create three tables. One is not compressed (CUST_SOURCE), one is basic
compressed (CUST_BCOMPRESSED), and the last one is advanced compressed
(CUST_ACOMPRESSED).
The PCTFREE is set to zero in the advanced compressed table because by default it is set to 10. It
is not set in the basic compressed table because it is by default set to 0 in the basic compressed
tables.
Note: Using advance compression requires a separate license.
CREATE TABLE CUST_SOURCE
( CUSTOMER_NO NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
NOTE1 VARCHAR2(100),
NOTE2 VARCHAR2(100)
) NOLOGGING PCTFREE 0 TABLESPACE SOETBS;
CREATE TABLE CUST_BCOMPRESSED
( CUSTOMER_NO NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
NOTE1 VARCHAR2(100),
NOTE2 VARCHAR2(100)
) ROW STORE COMPRESS BASIC NOLOGGING TABLESPACE SOETBS;
CREATE TABLE CUST_ACOMPRESSED
( CUSTOMER_NO NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
NOTE1 VARCHAR2(100),
NOTE2 VARCHAR2(100)
) ROW STORE COMPRESS ADVANCED NOLOGGING PCTFREE 0 TABLESPACE SOETBS;
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |4
5. Run the following code to populate CUST_SOURCE with random data, copy the data from
CUST_SOURCE to CUST_BCOMPRESSED, and then gather their optimizer statistics.
Because the data was populated randomly, there is hardly any chance of having repeated data in
the columns.
INSERT INTO CUST_SOURCE
SELECT LEVEL AS CUSTOMER_NO,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) FIRST_NAME,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) LAST_NAME,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2
FROM DUAL
CONNECT BY LEVEL <= 100000;
COMMIT;
INSERT INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;
COMMIT;
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_SOURCE')
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED')
6. Display the table statistics.
Observe that the compressed table is of the same size as the non-compressed table.
Conclusion: Basic compression does not compress data inserted by normal INSERT statements.
@ display_table_stats.sql
7. Run the following code to repopulate the compressed table with the same data but using direct
path loading this time.
TRUNCATE TABLE CUST_BCOMPRESSED;
INSERT /*+ APPEND */ INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;
COMMIT;
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED')
8. Display the table statistics.
Observe that the compressed table is slightly smaller than the non-compressed table. The
compression had slight effect because there is hardly any repeated in the columns.
Conclusion: Basic compression does compress data inserted by direct path loading methods.
@ display_table_stats.sql
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |5
9. Run the following code to repopulate the non-compressed and compressed tables.
This time the data has a lot of repeated data in its columns NOTE1 and NOTE2.
TRUNCATE TABLE CUST_SOURCE;
TRUNCATE TABLE CUST_BCOMPRESSED;
INSERT INTO CUST_SOURCE
SELECT LEVEL AS CUSTOMER_NO,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) FIRST_NAME,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) LAST_NAME,
'XXXXXX' NOTE1,
'YYYYYY' NOTE2
FROM DUAL
CONNECT BY LEVEL <= 100000;
COMMIT;
INSERT /*+ APPEND */ INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;
COMMIT;
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_SOURCE')
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED')
10. Display the table statistics.
Observe that the compressed table is much smaller than the non-compressed file.
Conclusion: Basic compression ratio depends on the repeated data in the table columns.
@ display_table_stats.sql
11. Populate the advanced compressed table with the same data using the normal INSERT statement.
INSERT INTO CUST_ACOMPRESSED SELECT * FROM CUST_SOURCE;
COMMIT;
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_ACOMPRESSED')
12. Display the table statistics.
Observe that the advanced compressed table is a little bit smaller than the non-compressed file
but larger than the basic compressed table.
Conclusion: Advanced compression works with normal INSERT statements.
@ display_table_stats.sql
13. Repopulate the advanced compressed table with the same data using the direct path loading.
TRUNCATE TABLE CUST_ACOMPRESSED;
INSERT /*+ APPEND */ INTO CUST_ACOMPRESSED SELECT * FROM CUST_SOURCE;
COMMIT;
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_ACOMPRESSED')
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |6
14. Display the table statistics.
Observe that the advanced compressed table is as small in size as the basic compressed table
size.
Conclusion: With direct path loading, advanced compression has the same effect as the basic
compression.
@ display_table_stats.sql
15. To clean up, drop the tables and the file that you created.
DROP TABLE CUST_SOURCE;
DROP TABLE CUST_BCOMPRESSED;
DROP TABLE CUST_ACOMPRESSED;
host rm display_table_stats.sql
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |7
Using Basic Compression and Direct Path Loading in
Enhancing Query Performance
In the following steps, you will study the effect of using compression and direct path loading in
enhancing query performance.
16. Create the following three testing tables, T1, T2 and T3.
T1 does not have the compression enabled. T2 and T3 do have their basic compression enabled.
-- just in case the tables were already there:
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
DROP TABLE T3 PURGE;
CREATE TABLE T1 AS SELECT * FROM USER_OBJECTS WHERE 1=2;
CREATE TABLE T2 ROW STORE COMPRESS BASIC AS SELECT * FROM USER_OBJECTS WHERE
1=2;
CREATE TABLE T3 ROW STORE COMPRESS BASIC AS SELECT * FROM USER_OBJECTS WHERE
1=2;
17. Populate the tables with rows as follows. Compare between the time taken by each INSERT
statement.
Populating T3 took much less time than the time needed to populate T1 and T2 with the same
data (in my testing case %70 less).
Conclusion: Using direct path loading on a compressed table provides performance gain in
loading the table.
set timing on
INSERT INTO T1 SELECT A.* FROM USER_OBJECTS A,USER_OBJECTS B,USER_OBJECTS C,
(SELECT 1 FROM DUAL CONNECT BY LEVEL <= 20) D;
set timing off
COMMIT;
set timing on
INSERT INTO T2 SELECT A.* FROM USER_OBJECTS A,USER_OBJECTS B,USER_OBJECTS C,
(SELECT 1 FROM DUAL CONNECT BY LEVEL <= 20) D;
set timing off
COMMIT;
set timing on
INSERT /*+ APPEND */ INTO T3 SELECT A.* FROM USER_OBJECTS A,USER_OBJECTS
B,USER_OBJECTS C, (SELECT 1 FROM DUAL CONNECT BY LEVEL <= 20) D;
set timing off
COMMIT;
18. Gather statistics for all the tables.
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'T1')
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'T2')
exec DBMS_STATS.GATHER_TABLE_STATS(USER,'T3')
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |8
19. In the following commands, you will run three code blocks. Each code block retrieves the same
data from each table. After running each code block, take a copy of the output statistics.
When comparing between the statistics of each code block, the data retrieved from T3 with much
less time than the other tables, fewer "consistent gets" and fewer "physical reads".
Retrieving from T2 was a little bit better than retrieving the data from T1.
Conclusion: Compressed table populated with direct path loading method provides performance
gain in retrieving the data.
-- code block 1
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
set timing on
set autot trace stat
SELECT COUNT(*) FROM T1;
set autot off
set timing off
-- code block 2
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
set timing on
set autot trace stat
SELECT COUNT(*) FROM T2;
set autot off
set timing off
-- code block 3
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
set timing on
set autot trace stat
SELECT COUNT(*) FROM T3;
set autot off
set timing off
Further practice: repeat the same steps with the advanced compression tables and observe the
difference.
You will observe that the regular INSERT statement runs faster with the advanced compression tables.
Queries run faster with table loaded by the regular INSERT statement.
20. Clean up
DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
DROP TABLE T3 PURGE;
Oracle Database Performance Tuning, a course by Ahmed Baraka
Practice 32 - Using Table Compression P a g e |9
Summary
• Basic compression is linked to the data duplication in the table columns. The more duplicate
data, the more efficient is the compression.
• Basic compression does not compress data inserted by normal INSERT statements. Basic
compression compresses data inserted by direct path loading methods.
• Using direct path loading on a compressed table provides performance gain in loading the
table.
• Basic compressed table populated with direct path loading method provides performance gain
in retrieving the data.
• Advanced compression works with normal INSERT statements as well as with direct path
loading methods.
• Advanced compression could allow loading data with regular INSERT statement more
efficiently.
• Advanced compression could make queries run more efficiently.
Oracle Database Performance Tuning, a course by Ahmed Baraka