May 22 2010
In one month I will be in Berlin presenting a two-day seminar based on the chapters 1, 2, 8, 9, 10 and 11 of my book. The event is organized by DOAG. You can read the full description of the seminar (incl. agenda) here. Just be careful that the spoken language will be German (slides will be in English, though). Since I was just informed that there are less than ten free seats, do not wait to long if you want to join us…
May 20 2010
According to the documentation the GET_COMPRESSION_RATIO procedure of the DBMS_COMPRESSION package can be used to assess the impact of different compression options for a given table. In other words, it allows us to find out the expected compression ratio for a given set of data without having to really create a compressed table. The question is: how good are the values it returns?
Before answering this question it is essential to point out two things. First, this package is available as of 11.2 only. Second, it can be used to find out (and compare) the expected compression ratio of OLTP compression as well as Exadata Hybrid Columnar Compression (EHCC). Note that for getting values about EHCC it is not required to actually have access to an Exadata Storage Server. How is that possible?
To answer this second question, let’s describe how such an analysis is performed…
The following PL/SQL block shows how to start an analysis for all uncompressed tables of the current user.
DECLARE
l_blkcnt_cmp BINARY_INTEGER;
l_blkcnt_uncmp BINARY_INTEGER;
l_row_cmp BINARY_INTEGER;
l_row_uncmp BINARY_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(100);
BEGIN
FOR i IN (SELECT table_name
FROM user_tables
WHERE compression = 'DISABLED'
ORDER BY table_name)
LOOP
FOR j IN 1..5
LOOP
dbms_compression.get_compression_ratio(
-- input parameters
scratchtbsname => 'SCRATCH', -- scratch tablespace
ownname => user, -- owner of the table
tabname => i.table_name, -- table name
partname => NULL, -- partition name
comptype => power(2,j), -- compression algorithm
-- output parameters
blkcnt_cmp => l_blkcnt_cmp, -- number of compressed blocks
blkcnt_uncmp => l_blkcnt_uncmp, -- number of uncompressed blocks
row_cmp => l_row_cmp, -- number of rows in a compressed block
row_uncmp => l_row_uncmp, -- number of rows in an uncompressed block
cmp_ratio => l_cmp_ratio, -- compression ratio
comptype_str => l_comptype_str -- compression type
);
dbms_output.put_line(i.table_name||' - '||'type: '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999'));
END LOOP;
END LOOP;
END;
As you can see the idea is that we provide to the package a table and, based on its data, the package estimates the compression ratio that can be achieved with the specified algorithm. Notice that as input parameter a scratch tablespace is also specified. This is necessary because the package, to estimate the output parameters, actually creates a compressed table. If you trace an execution you would see two CREATE TABLE statements.
CREATE TABLE dbms_tabcomp_temp_uncmp TABLESPACE <tablespace name> AS SELECT * FROM <table name> SAMPLE BLOCK( <sampling percentage> )
CREATE TABLE dbms_tabcomp_temp_uncmp ORGANIZATION HEAP TABLESPACE <tablespace name> COMPRESS FOR QUERY HIGH AS SELECT * FROM dbms_tabcomp_temp_uncmp
In other words the estimations are based on data that is actually compressed. Not on some heuristics…
The essential thing to note is that the second CREATE TABLE statement cannot be directly executed without an Exadata Storage Server. In fact, if you try to run it, the database engine raises an ORA-64307 (hybrid columnar compression is only supported in tablespaces residing on Exadata storage). Based on that observation it is sensible to say that in the current implementation there is a software lock that prevents us from using EHCC without an Exadata Storage Server. But, as just described, that lock can be overridden by the DBMS_COMPRESSION package.
Now, back to the initial question: how good are the values it returns?
To answer this question I installed a TPC-H schema (scale factor 10) and compared the estimations of the package with the actual values. For that purpose I created one table with each type of compression.
SQL> SELECT comp.table_name, round(uncomp.blocks/comp.blocks,3) AS ratio 2 FROM user_tables comp, user_tables uncomp 3 WHERE comp.compression = 'ENABLED' 4 AND uncomp.compression = 'DISABLED' 5 AND comp.table_name LIKE uncomp.table_name||'/_%' ESCAPE '/' 6 ORDER BY uncomp.table_name, nullif(comp.compress_for,'OLTP') DESC; TABLE_NAME RATIO ------------------------------ ---------- CUSTOMER_OLTP 1.041 CUSTOMER_QUERY_LOW 2.220 CUSTOMER_QUERY_HIGH 3.708 CUSTOMER_ARCHIVE_LOW 3.837 CUSTOMER_ARCHIVE_HIGH 4.432 LINEITEM_OLTP 1.487 LINEITEM_QUERY_LOW 3.034 LINEITEM_QUERY_HIGH 4.912 LINEITEM_ARCHIVE_LOW 5.144 LINEITEM_ARCHIVE_HIGH 6.704 ORDERS_OLTP 1.149 ORDERS_QUERY_LOW 2.887 ORDERS_QUERY_HIGH 5.038 ORDERS_ARCHIVE_LOW 5.305 ORDERS_ARCHIVE_HIGH 6.704 PART_OLTP 1.328 PART_QUERY_LOW 3.307 PART_QUERY_HIGH 6.718 PART_ARCHIVE_LOW 7.296 PART_ARCHIVE_HIGH 11.049 PARTSUPP_OLTP 0.991 PARTSUPP_QUERY_LOW 2.892 PARTSUPP_QUERY_HIGH 5.428 PARTSUPP_ARCHIVE_LOW 5.659 PARTSUPP_ARCHIVE_HIGH 8.071
CUSTOMER - type: "Compress For OLTP" ratio: 1.041 CUSTOMER - type: "Compress For Query High" ratio: 3.742 CUSTOMER - type: "Compress For Query Low" ratio: 2.228 CUSTOMER - type: "Compress For Archive High" ratio: 4.460 CUSTOMER - type: "Compress For Archive Low" ratio: 3.894 ORDERS - type: "Compress For OLTP" ratio: 1.149 ORDERS - type: "Compress For Query High" ratio: 5.048 ORDERS - type: "Compress For Query Low" ratio: 2.886 ORDERS - type: "Compress For Archive High" ratio: 6.651 ORDERS - type: "Compress For Archive Low" ratio: 5.325 PART - type: "Compress For OLTP" ratio: 1.328 PART - type: "Compress For Query High" ratio: 6.778 PART - type: "Compress For Query Low" ratio: 3.338 PART - type: "Compress For Archive High" ratio: 11.126 PART - type: "Compress For Archive Low" ratio: 7.343 PARTSUPP - type: "Compress For OLTP" ratio: .990 PARTSUPP - type: "Compress For Query High" ratio: 5.451 PARTSUPP - type: "Compress For Query Low" ratio: 2.893 PARTSUPP - type: "Compress For Archive High" ratio: 8.051 PARTSUPP - type: "Compress For Archive Low" ratio: 5.657
By comparing the estimations with the actual values it is possible to say that the value returned by the DBMS_COMPRESSION package are very accurate. Having so accurate estimations is a good thing because you probably want to know how much space you can save before doing a major reorganization of large tables as well as before buying an Exadata Storage Server just to test how effective the compression of EHCC is.
You might ask why the second output does not provide information about the LINEITEM table. The problem is that the package was not able to process it. In fact, the following error was raised:
ORA-00942: table or view does not exist ORA-06512: at "SYS.PRVT_COMPRESSION", line 459 ORA-30562: SAMPLE percentage must be in the range [0.000001,100) ORA-06512: at "SYS.DBMS_COMPRESSION", line 214 ORA-06512: at line 16
The problem is (probably) due to a rounding performed during the selection of the sampling percentage. In fact, for the LINEITEM table (which is the bigger one), the following CREATE TABLE statement was generated (notice that the sampling percentage is set to 0):
CREATE TABLE dbms_tabcomp_temp_uncmp TABLESPACE scratch AS SELECT * FROM lineitem SAMPLE BLOCK( 0)
I was not able to find a bug in MOS, but it is definitely one.
May 10 2010
This is a short note to point out that I just added to the Public Appearances page the next conference organized by the AOUG in Vienna. It will take place on June 15. My talk will be about edition-based redefinition. The full agenda (incl. abstracts) is available here. It is interesting to point out that the two key-note speakers are Tom DeMarco and Tom Kyte.
May 05 2010
In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering.
Join filtering is not something specific to the Exadata Storage Server. In fact, it is an Enterprise Edition feature available since Oracle Database 10g Release 2. Simply put, it is used to reduce data communication between slave processes in parallel joins. For more information about it I suggest you to read a paper I published in June 2008 entitled Bloom Filters. In it I describe not only what bloom filters are, but also how Oracle Database uses them. And, one of the use cases is join filtering.
What I want to show here is how Exadata Storage Server is able to take advantage of join filtering. For that purpose let’s have a look to the following execution plan:
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 |
|* 3 | HASH JOIN BUFFERED | |
| 4 | JOIN FILTER CREATE | :BF0000 |
| 5 | PX RECEIVE | |
| 6 | PX SEND HASH | :TQ10000 |
| 7 | PX BLOCK ITERATOR | |
|* 8 | TABLE ACCESS STORAGE FULL| T1 |
| 9 | PX RECEIVE | |
| 10 | PX SEND HASH | :TQ10001 |
| 11 | JOIN FILTER USE | :BF0000 |
| 12 | PX BLOCK ITERATOR | |
|* 13 | TABLE ACCESS STORAGE FULL| T2 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ID"="T2"."ID")
8 - storage("T1"."MOD"=42)
filter("T1"."MOD"=42)
13 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."ID"))
As you can see, join filtering is used. In fact, the operation 4 (JOIN FILTER CREATE) builds a bloom filter that, later on, is used by operation 11 (JOIN FILTER USE) to filter out the data that does not fulfill the join condition. However, the most important thing to notice in this execution plan is the STORAGE predicate applied by the operation 13. According to it the bloom filter is applied not only by the operation 11, but also by the operation 13. And, since the operation 13 is a smart scan operation, the STORAGE predicate is evaluated by the cells. This means that the reduction of data communication does not only take place between slave processes, but also between the cells and the database instances. Remarkable!
May 04 2010
Today I installed for the first time the patchset 10.2.0.5. While reading the README file, I noticed the following piece of information.
To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:
_optimizer_native_full_outer_join =force
You can set this parameter for the system or for a specific session.
Besides dramatically improving the performance of a full outer join, the new implementation fixes a variety of issues, for examples a variety of ORA-942 (table or view doesn’t exists) and ORA-4331 (unable to allocate string bytes of shared memory) errors.
This issue is tracked with Oracle bug 6322672.
Great! At last we can officially take advantage of native full outer join also in 10.2 (the feature was officially introduced in 11.1, but was already “available” in 10.2.0.3).
Here is an example:
SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 2291915024
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | UNION-ALL | |
|* 3 | HASH JOIN OUTER | |
| 4 | TABLE ACCESS FULL| EMP |
| 5 | TABLE ACCESS FULL| DEPT |
| 6 | NESTED LOOPS ANTI | |
| 7 | TABLE ACCESS FULL| DEPT |
|* 8 | INDEX RANGE SCAN | EMP_DEPTNO_I |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
8 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL> ALTER SESSION SET "_optimizer_native_full_outer_join" = force;
SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 51889263
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | VW_FOJ_0 |
|* 2 | HASH JOIN FULL OUTER| |
| 3 | TABLE ACCESS FULL | DEPT |
| 4 | TABLE ACCESS FULL | EMP |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")