Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Don’t Knows

One of the biggest problems in learning is that you don’t know how much you don’t know, and this reises two questions: how do you find out that there are huge gaps in your knowledge that need to be filled ? how do you know whether or not the material you’re learning from is any [...]

Dropping and creating tables in read only tablespaces?!

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER   SEGMENT_NAME                    SEGMENT_TYPE
------- ------------------------------- ------------------
TANEL   BIN$ix7rAUXZfB3gQKjAgS4LXg==$0  TABLE

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER    SEGMENT_NAME                   SEGMENT_TYPE
-------- ------------------------------ ------------------
TANEL    9.130                          TEMPORARY

Wow, Oracle has converted the table segment into a temporary segment instead (see segment_type)! Bur our tablespace is read only, how can it do that?! The answer is that neither the regular DROP nor DROP PURGE need to write anything into the tablespace where the segment resides! The initial DROP operation just updated data dictionary, like renaming the table to BIN$… in OBJ$ and so on. The second DROP PURGE operation just ran a bunch of deletes against data dictionary to indicate that the table object is gone. But why is the TEMPORARY segment left behind? This has to do with locally managed tablespaces. Before LMT days, when you dropped a segment, then the segment space was released and acquired back to tablespace through inserts/updates to UET$/FET$ (used/free extents) base tables, which resided in system tablespace like all other data dictionary base tables. But with LMTs, the free space information is kept in bitmaps in the tablespace files themselves! Thus, if you drop a table in a read only LMT tablespace, the table will be gone, but the space will not be physically released (as you can’t update the LMT bitmaps in read only tablespace files). However, Oracle doesn’t want to lose that space should someone make the tablespace read write later on, so the table segment is updated to be a TEMPORARY segment instead of completely deleting it from data dictionary. That’s how the SMON can clean it up later on should that tablespace become read-write again. The 9.130 in SEGMENT_NAME column means relative file# 9 and starting block# 130, that’s a segment’s unique identifier in a tablespace. Let’s move on. This example is executed on Oracle 11.2, while logged on to a non-SYS/SYSTEM user:

SQL> select status from dba_tablespaces where tablespace_name = 'RONLY';

STATUS
---------
READ ONLY

The tablespace is still in read only status. Let’s try to CREATE a table into that tablespace:

SQL> create table test(a int) tablespace ronly;

Table created.

What? I can also CREATE a table into a read only tablespace?! Well, this is the behavior you get starting from 11gR2 onwards, it’s called deferred segment creation, Oracle doesn’t need to create any segments for a table until you actually insert rows into it! So, Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well. There’s new syntax which controls the deferred segment creation:

SQL> drop table test purge;

Table dropped.

SQL> create table test (a int) segment creation IMMEDIATE tablespace ronly;
create table test (a int) segment creation IMMEDIATE tablespace ronly
*
ERROR at line 1:
ORA-01647: tablespace 'RONLY' is read-only, cannot allocate space in it

In the above case I disabled the deferred segment creation and got an error immediately as Oracle tried to allocate space from the read only tablespace. Let’s try the other way:

SQL> create table test (a int) segment creation DEFERRED tablespace ronly;

Table created.

Now, with deferred segment creation enabled, Oracle didn’t try to allocate space from the read only tablespace. Let’s look into the segments in that tablespace again:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER        SEGMENT_NAME    SEGMENT_TYPE
------------ --------------- ---------------
TANEL        9.130           TEMPORARY

We still have that old segment from an earlier DROP TABLE operation waiting to be cleaned up (when the tablespace goes into read-write mode again), but no segment for our latest test table created. Let’s make the tablespace read write and check again:

SQL> alter tablespace ronly read write;

Tablespace altered.

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

no rows selected

Apparently SMON has kicked in already and cleaned up the table segment which had been marked temporary earlier when I dropped the table in a read only tablespace.

Note that tables in SYS and SYSTEM schema can not use deferred segment creation:

SQL> create table sys.test (a int) segment creation deferred;
create table sys.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

SQL> create table system.test (a int) segment creation deferred;
create table system.test (a int) segment creation deferred
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported for this table

SQL> create table tanel.test (a int) segment creation deferred;

Table created.

There’s also a parameter, deferred_segment_creation which controls the default behavior:

SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

SQL>

Share/Bookmark

Compression Restrictions

When using table or index compression certain restrictions apply. Since I find it always hard to gather that information from the manuals and also some of the restrictions are not documented properly or not at all, I'll attempt to summarize the restrictions that I'm aware of here:

1. Compression attribute on subpartitions
Note that you can't define the COMPRESSION on subpartition level - the subpartitions inherit this attribute from the parent partition.

It is however perfectly valid and legal to exchange a subpartition with a compressed table and that way introduce individual compression on subpartition level. It is unclear why this is only possible using this indirect method - it simply looks like a implementation restriction. Since compressed data is most suitable for data warehouse environments and these ought to use EXCHANGE [SUB]PARTITION technology anyway this restriction is probably not that crucial.

2. Number of columns
Basic heap table compression and the new OLTP (in 11.1 called "for all operations") compression are both limited to 255 columns. If you attempt to compress a table with more than 255 columns then no compression will be performed although you don't get an error message. Interestingly the COMPRESSION column in the dictionary still shows "ENABLED" which is certainly misleading.

3. DDL Restrictions - modification of table structure
As soon as a segment contains compressed data or has been marked for compression (does not apply in all cases, see below for more information), the following restrictions regarding the modification of the table structure apply:

a. Basic heap table compression will not prevent the addition of more than 255 columns via ADD COLUMN - but if you update the segment the affected blocks will be silently decompressed during the update operation. In 9.2 no schema evolution is possible with compressed segments, see the next paragraph.

b. In Oracle 9.2 a lot of modifications to the table are no longer possible with compression enabled (and this applies to partitioned tables as well if at least one partition is compressed):

- You cannot add or drop any columns, and you even can't set any column unused. The error messages are to say at least confusing, since it tells you something about virtual and object columns, but in fact it is about the enabled compression. These are serious limitations in terms of schema evolution. I'll address below what can be done to overcome this issue.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table t_part
2 compress
3 partition by list (user_id) (
4 partition p_default values (default)
5 )
6 as
7 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);
alter table t_part set unused (created)
*
ERROR at line 1:
ORA-12996: cannot drop system-generated virtual column

SQL> alter table t_part drop unused columns;

Table altered.

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-12996: cannot drop system-generated virtual column

SQL> alter table t_part add test_col varchar2(10);
alter table t_part add test_col varchar2(10)
*
ERROR at line 1:
ORA-22856: cannot add columns to object tables

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-22856: cannot add columns to object tables

SQL>

c. In Oracle 10.2 some restrictions have been lifted but there are still a couple of limitations:

- You can now add columns and you can set columns unused but you can't drop columns nor can you add columns with default values

This allows schema evolution but has interesting side effects if you attempt to use for example a Create Table As Select (CTAS) statement to dynamically create a copy of a table and attempt to perform an EXCHANGE PARTITION operation with that newly created table: If there are unused columns in the source table then these won't be copied over to the table created via CTAS and therefore the EXCHANGE PARTITION operation will fail with column mismatch error messages. Since you can't drop the unused columns as long as there is compression enabled on the table (more on that later since it is a bit more complex with partitioned tables), you effectively can't use that approach. In order to perform EXCHANGE PARTITION with compressed tables and evolved schemas that include unused columns you have basically three choices:

- Maintain the "EXCHANGE" table along with the target table which means apply the same DDL in the same order to both tables in order to keep them synchronized

- Extract the hidden columns from the dictionary and create a exchange table with the correct hidden/unused columns which can turn into a complex operation if you need to cover all possible column data types and features including LOBs etc.

- Perform some kind of rebuild operation on the target table to get rid of the unused columns. Some ideas regarding this point are going to follow further below

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_part
2 compress
3 partition by list (user_id) (
4 partition p_default values (default)
5 )
6 as
7 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part add test_col varchar2(10);

Table altered.

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL>

d. Oracle 11 introduces Advanced Compression which is COMPRESS FOR ALL OPERATIONS in 11.1 or OLTP compression in 11.2 (don't confuse this with HCC compression levels)

Basic compression in 11.1 has the same limitations as 10.2, but with advanced compression (called COMPRESS FOR ALL OPERATIONS in 11.1) all mentioned schema evolution restrictions have been lifted as it can be seen below.

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_part
2 --compress
3 compress for all operations
4 partition by list (user_id) (
5 partition p_default values (default)
6 )
7 as
8 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);

Table altered.

SQL> alter table t_part drop unused columns;

Table altered.

SQL> alter table t_part drop column username;

Table altered.

SQL> alter table t_part add test_col varchar2(10);

Table altered.

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL>

This is more of theoretical nature since you usually won't mix basic with advanced compression, bit things become more interesting when mixing basic and advanced compression in different partitions of the same table:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_part
2 compress
3 --compress for all operations
4 partition by list (user_id) (
5 partition p_default values (default)
6 )
7 as
8 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part add test_col varchar2(10);

Table altered.

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part split partition p_default values (123)
2 into (partition p_123 compress for all operations, partition p_default);

Table altered.

SQL> select
2 partition_name
3 , compression
4 , compress_for
5 from
6 user_tab_partitions
7 where
8 table_name = 'T_PART';

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
P_DEFAULT ENABLED DIRECT LOAD ONLY
P_123 ENABLED FOR ALL OPERATIONS

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL>

Notice the inconsistent behaviour: The first DDL command after having added a partition with advanced compression succeeds, but the following DDLs fail with an error message.

If the order of these DDLs is changed, then it becomes obvious that always the first DDL is successful no matter which of those failing otherwise comes first.

Furthermore in 11.1.0.7 if you have a mixture of Advanced Compression (COMPRESS FOR ALL OPERATIONS) and NOCOMPRESS partitions still above restrictions apply - only if you have set all partitions to COMPRESS FOR ALL OPERATIONS then the restrictions are lifted which looks more like a bug than a feature.

I'll go into more details below which settings influence this behaviour.

e. Oracle 11.2 introduces Hybrid Columnar Compression (HCC) which is only enabled in conjunction with ExaData

In 11.2 basic compression still has the same restrictions as already mentioned above, and if at least one partition of a partitioned table has advanced compression enabled then some of restrictions are lifted - even with a mixture of basic and advanced compression or a mixture of no compression and advanced compression, however adding a column with a default value is still only supported if all partitions are compressed with Advanced Compression. I'm not sure if this is really a feature since at least mixing Advanced Compression with no compression adding a column with default value should be possible.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_part
2 compress
3 --compress for all operations
4 partition by list (user_id) (
5 partition p_default values (default)
6 )
7 as
8 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part add test_col varchar2(10);

Table altered.

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part split partition p_default values (123)
2 into (partition p_123 compress for all operations, partition p_default);

Table altered.

SQL> select
2 partition_name
3 , compression
4 , compress_for
5 from
6 user_tab_partitions
7 where
8 table_name = 'T_PART';

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P_123 ENABLED OLTP
P_DEFAULT ENABLED BASIC

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop unused columns;

Table altered.

SQL> alter table t_part drop column username;

Table altered.

SQL> alter table t_part move partition p_default compress for all operations;

Table altered.

SQL> select
2 partition_name
3 , compression
4 , compress_for
5 from
6 user_tab_partitions
7 where
8 table_name = 'T_PART';

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P_123 ENABLED OLTP
P_DEFAULT ENABLED OLTP

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL>

When using HCC the most important difference is that it is not restricted to 255 columns - it successfully compresses segments with any number of columns (up to the hard limit of 1,000 columns).

HCC can only be used with direct-path operations, any conventional DML will decompress the affected blocks and use OLTP compression instead - which means that in case the segment has more than 255 columns the compression will effectively be disabled since any non-HCC compression supports only up to 255 columns.

HCC has only a few restrictions regarding schema evolution - the most noticeable one is that DROP COLUMN is always turned into a SET COLUMN UNUSED, so with HCC enabled you can't drop columns since DROP UNUSED COLUMNS is not supported and raises an error. This means that dropping / adding columns works towards the hard limit of 1,000 columns with HCC enabled.

Again this has the same side effect as described above - a simple CTAS operation to create a table to be exchanged with won't work any longer with unused columns.

SQL> create table t_part
2 compress for query high
3 --compress for all operations
4 partition by list (user_id) (
5 partition p_default values (default)
6 )
7 as
8 select * from all_users;

Table created.

SQL> alter table t_part set unused (created);

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;

Table altered.

SQL> alter table t_part add test_col varchar2(10);

Table altered.

SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL> alter table t_part split partition p_default values (123)
2 into (partition p_123 compress for all operations, partition p_default);

Table altered.

SQL> select
2 partition_name
3 , compression
4 , compress_for
5 from
6 user_tab_partitions
7 where
8 table_name = 'T_PART';

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P_123 ENABLED OLTP
P_DEFAULT ENABLED QUERY HIGH

SQL> alter table t_part add test_col3 varchar2(10) default 'BLA';

Table altered.

SQL> alter table t_part drop unused columns;
alter table t_part drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table t_part drop column username;
alter table t_part drop column username
*
ERROR at line 1:
ORA-00904: "USERNAME": invalid identifier

SQL> alter table t_part move partition p_default compress for all operations;

Table altered.

SQL> select
2 partition_name
3 , compression
4 , compress_for
5 from
6 user_tab_partitions
7 where
8 table_name = 'T_PART';

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P_123 ENABLED OLTP
P_DEFAULT ENABLED OLTP

SQL> alter table t_part add test_col4 varchar2(10) default 'BLA';

Table altered.

SQL>

And here is what happens if you're already hitting the 1,000 columns limit and attempt to use DROP [UNUSED] COLUMNS with HCC enabled:

SQL> alter table many_x_cols drop (col256);

Table altered.

SQL> alter table many_x_cols drop unused columns;
alter table many_x_cols drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table many_x_cols add (col256 varchar2(10));
alter table many_x_cols add (col256 varchar2(10))
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000

SQL> alter table many_x_cols move pctfree 0 nocompress;

Table altered.

SQL> alter table many_x_cols drop unused columns;

Table altered.

SQL> alter table many_x_cols add (col256 varchar2(10));

Table altered.

SQL>

As you can see dropping columns is only supported with HCC disabled which allows in this particular case to add another column after dropping the hidden/unused one.

- Index restrictions

* You cannot compress a table or a partition of table (or exchange a compressed partition into it) if there are usable bitmap indexes defined on the table due to the different Hakan factor that is reset when using by the compression - which means that with compressed segments more rows will fit into a single block leading to a different Hakan factor used for the bitmap indexes. The bitmap indexes need to be set unused (all partitions in case of a partitioned table/index) or dropped before enabling the compression and rebuild / recreated after compressing the heap segment. This is also officially documented. It is however interesting to note that this restriction is only enforced when switching from uncompressed to compressed segments / partitions. Changing the compression for example from basic compression to HCC compression is possible without hitting this restriction but due to the different compression levels in my opinion this potentially leads again to significantly different Hakan factors - so actively resetting the Hakan Factor using "ALTER TABLE ... MINIMIZE RECORDS_PER_BLOCK" and rebuilding the bitmap indexes might be in order when moving between different compression levels.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter table t_part move partition p_default nocompress;

Table altered.

SQL> alter table t_part move partition p_123 nocompress;

Table altered.

SQL> alter table t_part modify default attributes nocompress;

Table altered.

SQL> alter table t_part nocompress;

Table altered.

SQL> create bitmap index t_part_idx_bitmap on t_part (test_col) local;

Index created.

SQL> alter table t_part move partition p_default compress basic;
alter table t_part move partition p_default compress basic
*
ERROR at line 1:
ORA-14646: Specified alter table operation involving compression cannot be
performed in the presence of usable bitmap indexes

SQL> alter table t_part minimize records_per_block;
alter table t_part minimize records_per_block
*
ERROR at line 1:
ORA-28602: statement not permitted on tables containing bitmap indexes

SQL> alter index t_part_idx_bitmap modify partition p_default unusable;

Index altered.

SQL> alter table t_part move partition p_default compress basic;
alter table t_part move partition p_default compress basic
*
ERROR at line 1:
ORA-14646: Specified alter table operation involving compression cannot be
performed in the presence of usable bitmap indexes

SQL> alter table t_part minimize records_per_block;
alter table t_part minimize records_per_block
*
ERROR at line 1:
ORA-28602: statement not permitted on tables containing bitmap indexes

SQL> alter index t_part_idx_bitmap unusable;

Index altered.

SQL> alter table t_part move partition p_default compress basic;

Table altered.

SQL> alter table t_part minimize records_per_block;

Table altered.

SQL> alter index t_part_idx_bitmap rebuild partition p_default;

Index altered.

SQL> alter index t_part_idx_bitmap rebuild partition p_123;

Index altered.

SQL> alter table t_part move partition p_default nocompress update indexes;

Table altered.

As you can see switching on the compression is only allowed if all partitions of the bitmap index(es) are unusable - the opposite is not true: You can uncompress afterwards without any restrictions.

* This is related to B*Tree index compression and again more of theoretical nature but might become relevant when trying to introduce B*Tree index compression partition-wise via EXCHANGE PARTITION: You can't compress a single partition of a partitioned B*Tree index (or exchange a partition with a compressed index into the table) if the whole index has been not initially created with compression enabled. If you drop the index and recreate the whole index with compression enabled then you can turn the index compression individually on and off per index partition.

Very likely this is related to the restriction that the B*Tree index compression prefix length needs to be the same across all index partitions - this can only be defined on global level and I assume this is the reason why the index needs to be created compressed first. Since you usually want to have all partitions of an partitioned index to be compressed the same way this is probably a rather irrelevant restriction (except for the mentioned case regarding EXCHANGE PARTITION)

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create index t_part_idx on t_part (user_id, test_col) nocompress local;

Index created.

SQL> alter index t_part_idx rebuild partition p_default compress;
alter index t_part_idx rebuild partition p_default compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first

SQL> select
2 partition_name
3 , compression
4 from
5 user_ind_partitions
6 where
7 index_name = 'T_PART_IDX';

PARTITION_NAME COMPRESS
------------------------------ --------
P_123 DISABLED
P_DEFAULT DISABLED

SQL> drop index t_part_idx;

Index dropped.

SQL> create index t_part_idx on t_part (user_id, test_col) compress local;

Index created.

SQL> alter index t_part_idx rebuild partition p_default nocompress;

Index altered.

SQL> select
2 partition_name
3 , compression
4 from
5 user_ind_partitions
6 where
7 index_name = 'T_PART_IDX';

PARTITION_NAME COMPRESS
------------------------------ --------
P_123 ENABLED
P_DEFAULT DISABLED

SQL> drop index t_part_idx;

Index dropped.

SQL> create index t_part_idx on t_part (user_id, test_col) nocompress global
2 partition by range(user_id) (
3 partition p_default values less than (maxvalue)
4 );

Index created.

SQL> alter index t_part_idx rebuild partition p_default compress;
alter index t_part_idx rebuild partition p_default compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first

SQL> drop index t_part_idx;

Index dropped.

SQL> create index t_part_idx on t_part (user_id, test_col) compress global
2 partition by range(user_id) (
3 partition p_default values less than (maxvalue)
4 );

Index created.

SQL> alter index t_part_idx rebuild partition p_default nocompress;

Index altered.

SQL> select
2 partition_name
3 , compression
4 from
5 user_ind_partitions
6 where
7 index_name = 'T_PART_IDX';

PARTITION_NAME COMPRESS
------------------------------ --------
P_DEFAULT DISABLED

SQL>

- Overcoming any of the schema evolution restrictions by temporarily uncompressing / disabling enabled compression

As already mentioned above there are different options how to overcome schema evolution restrictions. One obvious option, although very likely not applicable in many cases simply due to space and load constraints, is to temporarily uncompress the segments and to disable the compression. It is interesting to note that it is not as simple as it seems to undo compression with partitioned tables as I will demonstrate - I think I've even read somewhere that it is not possible at all to revert a table into a state where the mentioned restrictions no longer apply. This is definitely not true.

First of all it is important to note that in addition to the obvious decompression of each partition that contains compressed data the "COMPRESS" attribute that determines if a suitable operation will generate compressed data or not needs to be reset to NOCOMPRESS (but see below for "oddities"). So there is a significant difference between "ALTER ... [NO]COMPRESS" and "ALTER ... MOVE [NO]COMPRESS". The latter reorganizes the segment / partition and compresses/uncompresses the data while doing so, the former just "marks" the segment / partition as "enabled/disabled for compression" but doesn't touch the data and leaves it in whatever state it is at present. As as side note, the "COMPRESSION" column in the dictionary can not be used to distinguish between these two - you can have the column show "DISABLED" with data still compressed, and you can have the column show "ENABLED" with no compressed data and you can end up with a mixture of both - not every block of a segment is necessarily in a compressed state.

Furthermore the "COMPRESSION" attribute can be defined on multiple levels with partitioned tables:

- On the global TABLE level: ALTER TABLE ... [NO]COMPRESS

- On the partition level as default attributes: ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES [NO]COMPRESS

- On the partition level to mark for [NO]COMPRESSION: ALTER TABLE ... MODIFY PARTITION [NO]COMPRESS

- On the partition level with MOVE: ALTER TABLE ... MOVE PARTITION [NO]COMPRESS ... [UPDATE [GLOBAL] INDEXES]

Now in order to be able to overcome the schema evolution restrictions all levels except the partition default attribute level need to be addressed: Any compressed data needs to be uncompressed, but also any partition that has been marked for compression (but doesn't necessarily contain compressed data) needs to be modified as well - finally the global table level also needs to be reset.

The setting on global table level seems to be the crucial step. It looks like Oracle checks during this operation (ALTER TABLE ... NOCOMPRESS) if all subordinate (sub-)partitions have a flag set that marks them as uncompressed. Only if that is the case some internal flag on global table level can also be reset so that the restrictions no longer apply.

There are however some oddities that need to be considered when doing so:

- You need to "move" any partition (ALTER TABLE ... MOVE PARTITION NOCOMPRESS) that has marked for compression if any other partition actually held compressed data - simply "unmarking" it using "ALTER TABLE ... MODIFY PARTITION NOCOMPRESS" is not sufficient even it does not hold any compressed data (for example, has only be marked with "ALTER TABLE ... MODIFY PARTITION COMPRESS").

- If you have BITMAP INDEXES defined on the table with compressed partitions you need to either set the whole indexes unusable or drop them before the COMPRESSION can be effectively disabled on global table level using ALTER TABLE ... NOCOMPRESS

See the following test case for all steps to unwind this:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t_part
2 nocompress
3 partition by list (user_id) (
4 partition p_default values (default)
5 )
6 as
7 select * from all_users;

Table created.

SQL> alter table t_part split partition p_default values (123)
2 into (partition p_123, partition p_default);

Table altered.

SQL> -- Compress one partition
SQL> alter table t_part move partition p_default compress;

Table altered.

SQL> -- And uncompress it again
SQL> alter table t_part move partition p_default nocompress;

Table altered.

SQL> -- Doesn't work!
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> -- Compress one partition
SQL> alter table t_part move partition p_default compress;

Table altered.

SQL> -- And uncompress it again
SQL> alter table t_part move partition p_default nocompress;

Table altered.

SQL> -- The global table level was missing!
SQL> alter table t_part nocompress;

Table altered.

SQL> -- Now it works
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL> -- Start again
SQL> alter table t_part drop column test_col2;

Table altered.

SQL> -- Compress one partition
SQL> alter table t_part move partition p_default compress;

Table altered.

SQL> -- Mark in addition another partition for compression (note: no data gets compressed!)
SQL> alter table t_part modify partition p_123 compress;

Table altered.

SQL> -- And uncompress the compressed partition again
SQL> alter table t_part move partition p_default nocompress;

Table altered.

SQL> -- Unmark the other partition marked for compression (note: no data was compressed!)
SQL> alter table t_part modify partition p_123 nocompress;

Table altered.

SQL> -- The global table level
SQL> alter table t_part nocompress;

Table altered.

SQL> -- But: Doesn't work!
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> -- Oddity 1: Although it doesn't contain compressed data
SQL> -- this partition needs to be reorganized as well
SQL> alter table t_part move partition p_123 nocompress;

Table altered.

SQL> -- Don't forget the global table level, otherwise below doesn't work
SQL> alter table t_part nocompress;

Table altered.

SQL> -- Now it works
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL> -- Start again
SQL> alter table t_part drop column test_col2;

Table altered.

SQL> -- Compress one partition
SQL> alter table t_part move partition p_default compress;

Table altered.

SQL> -- Add a bitmap index
SQL> create bitmap index t_part_idx_bitmap on t_part (username) local;

Index created.

SQL> -- And uncompress the compressed partition again
SQL> alter table t_part move partition p_default nocompress update indexes;

Table altered.

SQL> -- The global table level
SQL> alter table t_part nocompress;

Table altered.

SQL> -- But: Doesn't work!
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> -- Oddity 2: Bitmap indexes on compressed tables need to be set unusable or dropped
SQL> -- Note it is not sufficient to set single partitions unusable
SQL> alter index t_part_idx_bitmap unusable;

Index altered.

SQL> -- But still: Doesn't work because the global level has not been touched again
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';
alter table t_part add test_col2 varchar2(10) default 'BLA'
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> -- The global table level
SQL> alter table t_part nocompress;

Table altered.

SQL> -- Now it works
SQL> alter table t_part add test_col2 varchar2(10) default 'BLA';

Table altered.

SQL>

Backward Compatability

Backward Compatability is a very big technological driver. Especially for software companies like Oracle that have customers with a large installed base of users who rely on expensive functionality that they have purchased. When companies invest in software it is usually for very good business reasons and they expect that software to carry on doing [...]

Fully Exploiting Exadata

As a member of the Real-World Performance Group at Oracle I have participated in quite a number of Exadata POCs over the past two years. Often times those POCs are constrained in a number of ways: time, schema/app modifications, etc., because the objective is a proof, not a full blown migration. As a result there is often significant performance that is left on the table just waiting to be fully exploited — the kind of performance that really makes a database performance engineer excited — mind blowing performance. This includes, but is not limited to, data model changes, SQL query modifications and re-engineering batch processes. The reason these types of modifications get me so excited is that design decisions are often influenced by the then current deployment platform and with the Exadata powered Oracle Database Machine those restrictions are frequently lifted. You see, with Exadata the rules change, and so should your design decisions. Sure, you could just pluck-and-plop an existing Oracle data warehouse database onto an Oracle Database Machine and it would likely run much faster than it does on your current system, and you will be wowed, but you very well may shouting four letter expletives describing how [...]

Exadata and Parallel Queuing

Over the years Oracle has added many enhancements in order to allow individual SQL statements to take full advantage of multiprocessor computers. A few months ago Cary Millsap did a talk where he recalled the presentation Larry Ellison did when Oracle first announced the Parallel Query feature. During Larry’s demo he had a multiprocessor computer all to himself. I don’t remember how many processors it had, but I remember he had some kind of graphic showing individual CPU utilization on one screen while he fired up a parallel query on another screen. The monitoring screen lit up like a Christmas tree. Every one of the CPU’s was pegged during his demo. When Cary was telling the story he said that he had wondered at the time what would have happened if there had been other users on the system during the demo. Their experience would probably not have been a good one. I remember having the exact same thought.

Oracle’s parallel capabilities have been a great gift but they have also been a curse because controlling the beast in an environment where there are multiple users trying to share the resources is pretty difficult. There have been many attempts at coming up with a reasonable way of throttling big parallel statements along the way. But to date, I think this technology has only been used effectively in batch processing environments and large data warehouses where consuming the whole machine’s resources is acceptable due to the relatively low degree of concurrency required by those environments.

So why did I mention Exadata in the title of this post? Well I believe that one of the most promising aspects of Exadata is it’s potential with regard to running mixed work loads (OLTP and DW) on the same database, without crippling one or the other. In order to do that, Oracle needs some mechanism to separate the workloads. Resource Manager is an option in this area, but it doesn’t go far enough in controlling throughput on parallel queries. This new queuing mechanism should be a great help in that regard. So let’s review the options:

Parallel Adaptive Multi User (the old way)
This ability to automatically downgrade the degree of parallelism based on what’s happening on the system when a query kicks off is actually a powerful mechanism and is the best approach we’ve had prior to 11g Release 2. The downside of this approach is that parallelized statements can have wildly varying execution times. As you can imagine, a statement that gets 32 slaves one time and then gets downgraded to serial execution the next time will probably not make the user very happy. The argument for this type of approach is that stuff is going to run slower if the system is busy regardless of what you do. And that users expect it to run slower when the system is busy. The first part of that statement may be true but I don’t believe the second part is (at least in most cases). The bigger problem with the downgrade mechanism though is that the the decision about how many slaves to use is based on a single point in time (the point when the parallel statement starts). And once the degree of parallelism (DOP) is set for a statement it can not be changed. That execution of the statement will run with the number of slaves it got to start with, even if additional resources become available. So consider the statement that takes a minute with 32 slaves that gets downgraded to serial due to a momentarily high load. And say that 10 seconds after it starts the system load drops back to more normal levels. Unfortunately, the serialized statement will continue to run for nearly 30 minutes with it’s single process, even though on average the system is not busier than usual.

Parallel Queuing (the new way)
Now let’s compare that with the new mechanism introduced in 11gR2 that allows parallel statements to be queued in a First In - First Out fashion. This mechanism separates (presumably) long running parallel queries from the rest of the workload. The mechanics are pretty simple. Turn the feature on. Set a target number of parallel slaves (parallel_servers_target). Run stuff. If a statement tries to start that requires exceeding the target, it will be queued until the required number of slaves become available.

The Parallel Queuing feature is controlled by a hidden parameter called “_parallel_statement_queuing”. A value of TRUE turns it on and FALSE turns it off. FALSE is the default by the way. This parameter is not documented but is set automatically when the PARALLEL_DEGREE_POLICY parameter is set to AUTO. Unfortunately, PARALLEL_DEGREE_POLICY is one of those parameters that controls more than one thing. When set to AUTO it also turns on Automatic DOP calculation. This feature calculates a DOP for each statement regardless of whether any objects have been decorated with a parallel setting. The result is that all kinds of statements are run in parallel, even if no objects have been specifically defined with a parallel degree setting. This is truly automatic parallel processing because the database decides what to run in parallel and with how many slaves. On top of that, by default, the slaves may be spread across multiple nodes in a RAC database (this can be disabled by setting PARALLEL_FORCE_LOCAL to TRUE). Finally, AUTO is supposed to enable “In Memory Parallel Query”. This poorly named feature refers to 11gR2’s ability to make use of the SGA for parallel query, as opposed to using direct reads exclusively. Note: I haven’t actually seen this kick in yet, which is probably good, since Exadata Offloading depends on direct reads. I haven’t seen it kick in on non-Exadata databases either though.

Unfortunately this combination of features is a little like the wild west with things running in parallel all over the place. But the ability to queue parallel statements does provide some semblance of order. And to be fair, there are a number of parameters that can be set to control how the calculations are performed. Anyway, here’s a brief synopsis of parameter changes caused by the various settings PARALLEL_DEGREE_POLICY.

    Parameters Affected by Parallel_Degree_Policy
    Parallel_Degree_Policy Parameter Value
    MANUAL _parallel_statement_queuing FALSE
    _parallel_cluster_cache_policy ADAPTIVE
    LIMITED _parallel_statement_queuing FALSE
    _parallel_cluster_cache_policy ADAPTIVE
    AUTO _parallel_statement_queuing TRUE
    _parallel_cluster_cache_policy CACHED

So let’s look at how it behaves:

-bash-3.2$ !sql
sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 5 13:10:26 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
EXADATA> @parms 
Enter value for parameter: parallel
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
fast_start_parallel_rollback                       LOW                                                                    TRUE     FALSE      FALSE
parallel_adaptive_multi_user                       TRUE                                                                   TRUE     FALSE      FALSE
parallel_automatic_tuning                          FALSE                                                                  TRUE     FALSE      FALSE
parallel_degree_limit                              CPU                                                                    TRUE     FALSE      FALSE
parallel_degree_policy                             AUTO                                                                   TRUE     TRUE       TRUE
parallel_execution_message_size                    16384                                                                  TRUE     FALSE      FALSE
parallel_force_local                               FALSE                                                                  TRUE     FALSE      FALSE
parallel_instance_group                                                                                                   TRUE     FALSE      FALSE
parallel_io_cap_enabled                            FALSE                                                                  TRUE     FALSE      FALSE
parallel_max_servers                               160                                                                    TRUE     FALSE      FALSE
parallel_min_percent                               0                                                                      TRUE     FALSE      FALSE
parallel_min_servers                               0                                                                      TRUE     FALSE      FALSE
parallel_min_time_threshold                        AUTO                                                                   TRUE     FALSE      FALSE
parallel_server                                    TRUE                                                                   TRUE     FALSE      FALSE
parallel_server_instances                          2                                                                      TRUE     FALSE      FALSE
parallel_servers_target                            64                                                                     TRUE     FALSE      FALSE
parallel_threads_per_cpu                           2                                                                      TRUE     FALSE      FALSE
recovery_parallelism                               0                                                                      TRUE     FALSE      FALSE
 
18 rows selected.
 
EXADATA> select owner, table_name, degree from dba_tables where table_name = 'SKEW';
 
OWNER      TABLE_NAME                     DEGREE
---------- ------------------------------ ------
KSO        SKEW                                1
 
EXADATA> set echo on
EXADATA> @avgskew
EXADATA> select avg(pk_col) from kso.skew a where col1 > 0
  2  /
 
AVG(PK_COL)
-----------
 16093749.3
 
EXADATA> @fs
Enter value for sql_text: select avg(pk_col) from kso.skew a where col1 > 0
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1         28.77      162,427 select avg(pk_col) from kso.skew a where col1 > 0
 
1 row selected.
 
EXADATA> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    32M|   732M| 28420   (2)| 00:05:42 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
 
19 rows selected.
 
EXADATA> alter system set parallel_degree_policy=auto;
 
System altered.
 
EXADATA> @avgskew
 
AVG(PK_COL)
-----------
 16093749.3
 
1 row selected.
 
EXADATA> @fs
Enter value for sql_text: 
Enter value for sql_id: 05cq2hb1r37tr
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1         28.77      162,427 select avg(pk_col) from kso.skew a where col1 > 0
05cq2hb1r37tr      2  578366071          1         40.33      162,437 select avg(pk_col) from kso.skew a where col1 > 0
 
2 rows selected.
 
EXADATA> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 2
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 2
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 578366071
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |  6308 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |    24 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    24 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |    24 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    32M|   732M|  6308   (1)| 00:01:16 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| SKEW     |    32M|   732M|  6308   (1)| 00:01:16 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1">0)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 5
 
 
28 rows selected.
 
EXADATA> alter system set parallel_degree_policy=auto;
 
System altered.
 
EXADATA> !ss.sh avgskew.sql 20 kso/kso
 
starting 20 copies of avgskew.sql
 
EXADATA> set echo on
EXADATA> @queued_sql
EXADATA> col sql_text for a60 trunc
EXADATA> SELECT sid, sql_id, sql_exec_id, sql_text
  2  from v$sql_monitor
  3  WHERE status='QUEUED'
  4  order by 3
  5  /
 
 SID SQL_ID        SQL_EXEC_ID SQL_TEXT
---- ------------- ----------- ------------------------------------------------------------
 293 05cq2hb1r37tr    16777265 select avg(pk_col) from kso.skew a where col1 > 0
 270 05cq2hb1r37tr    16777266 select avg(pk_col) from kso.skew a where col1 > 0
   7 05cq2hb1r37tr    16777267 select avg(pk_col) from kso.skew a where col1 > 0
  22 05cq2hb1r37tr    16777268 select avg(pk_col) from kso.skew a where col1 > 0
  42 05cq2hb1r37tr    16777269 select avg(pk_col) from kso.skew a where col1 > 0
 101 05cq2hb1r37tr    16777270 select avg(pk_col) from kso.skew a where col1 > 0
  78 05cq2hb1r37tr    16777271 select avg(pk_col) from kso.skew a where col1 > 0
  60 05cq2hb1r37tr    16777272 select avg(pk_col) from kso.skew a where col1 > 0
 118 05cq2hb1r37tr    16777273 select avg(pk_col) from kso.skew a where col1 > 0
 160 05cq2hb1r37tr    16777274 select avg(pk_col) from kso.skew a where col1 > 0
 137 05cq2hb1r37tr    16777275 select avg(pk_col) from kso.skew a where col1 > 0
 181 05cq2hb1r37tr    16777276 select avg(pk_col) from kso.skew a where col1 > 0
 199 05cq2hb1r37tr    16777277 select avg(pk_col) from kso.skew a where col1 > 0
 216 05cq2hb1r37tr    16777278 select avg(pk_col) from kso.skew a where col1 > 0
 
14 rows selected.
 
EXADATA> -- using Tanel Poder's snapper - 
EXADATA> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all
Sampling with interval 5 seconds, 1 times...
 
-- Session Snapper v3.11 by Tanel Poder @ E2SN ( http://tech.e2sn.com )
 
 
--------------------------------------------------------------
Active% |    SID | EVENT                     | WAIT_CLASS
--------------------------------------------------------------
   100% |    118 | enq: JX - SQL statement q | Scheduler
   100% |    216 | enq: JX - SQL statement q | Scheduler
   100% |     78 | enq: JX - SQL statement q | Scheduler
   100% |     63 | enq: JX - SQL statement q | Scheduler
   100% |    233 | enq: JX - SQL statement q | Scheduler
   100% |    199 | enq: JX - SQL statement q | Scheduler
   100% |    137 | enq: JX - SQL statement q | Scheduler
   100% |     96 | enq: JX - SQL statement q | Scheduler
   100% |    101 | enq: JX - SQL statement q | Scheduler
   100% |     27 | enq: JX - SQL statement q | Scheduler
 
---------------------------------------------------
Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
---------------------------------------------------
  2095% |            |            | 05cq2hb1r37tr
     3% |            |            |
 
--  End of ASH snap 1, end=2010-07-04 20:18:05, seconds=5, samples_taken=39
 
 
PL/SQL procedure successfully completed.

There are several things worth mentioning in this example. First we see that with PARALLEL_DEGREE_POLICY set to it’s default value of MANUAL, my avgskew.sql script runs a serial plan. Second we see that when we set PARALLEL_DEGREE_POLICY to AUTO, the statement is automatically parallelized. Notice the note at the bottom of the plan output and you see that Automatic DOP was set at 5. Magical! I then used a shell script (ss.sh) to fire off 20 copies of the avgskew.sql script in rapid succession. Querying V$SQL_MONITOR showed that the statements were indeed queuing. Finally, I ran Tanel Poder’s snapper to see what event the queued statements were waiting on. Turns out it was “enq: JX - SQL statement queue”. Note that there is also an event called “PX Queuing: statement queue” that we didn’t see in the snapper output. I’m not exactly sure why Oracle split these two events apart, but apparently “PX Queuing: statement queue” is the event that clocks time when a statement is next in line, while “enq: JX - SQL statement queue” is used when a statement is in the queue but not the next one up. Guy Harrison has a really good post on the parallel_degree_policy that covers these two events here.

Oracle has provided some control over the Automatic DOP calculations as well with the PARALLEL_DEGREE_LIMIT parameter. The default value for this parameter is CPU which comes up with an “Ideal DOP” based on the amount of data, but then caps it with a formula based on CPU_COUNT, THREADS_PER_CPU and ACTIVE_INSTANCE_COUNT. There is a good description in this an Oracle white paper Parallel Execution Fundamentals that covers this calculation.

Of course, automatic DOP calculations are still a little scary. So it’s nice that there is a way to turn on the Parallel Queuing feature without enabling the Automatic DOP. Here’s another quick example showing that Queuing can be turned on without the other features:

EXADATA> alter system set parallel_degree_policy=manual;
 
System altered.
 
EXADATA> @flush_pool
 
System altered.
 
EXADATA> @avgskew
 
AVG(PK_COL)
-----------
 16093749.3
 
EXADATA> @fs
Enter value for sql_text: %avg%kso.skew%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  568322376          1         10.92      162,496 select avg(pk_col) from kso.skew a where col1 > 0
 
1 row selected.
 
EXADATA> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    32M|   732M| 28420   (2)| 00:05:42 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COL1">0)
 
 
19 rows selected.
 
EXADATA> -- so with MANUAL - the DOP is not set automatically
EXADATA> -- let's set the table to have a parallel degree
EXADATA>
EXADATA> alter table kso.skew parallel (degree 6);
 
Table altered.
 
EXADATA> @avgskew
 
AVG(PK_COL)
-----------
 16093749.3
 
1 row selected.
 
EXADATA> @fs
Enter value for sql_text: 
Enter value for sql_id: 05cq2hb1r37tr
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr      0  578366071          1         13.93      142,437 select avg(pk_col) from kso.skew a where col1 > 0
 
1 row selected.
 
EXADATA> @dplan
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
 
Plan hash value: 578366071
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |  5256 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |    24 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    24 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |    24 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    32M|   732M|  5256   (1)| 00:01:04 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| SKEW     |    32M|   732M|  5256   (1)| 00:01:04 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("COL1">0)
 
 
24 rows selected.
 
EXADATA>  !ss.sh avgskew.sql 20 kso/kso
 
starting 20 copies of avgskew.sql
 
EXADATA> @queued_sql
 
no rows selected
 
EXADATA> -- so now it's parallel but only objects or statements that we define as parallel
EXADATA> -- but no queuing, let's turn on queuing
EXADATA>
EXADATA> alter system set "_parallel_statement_queuing"=true;
 
System altered.
 
EXADATA> !ss.sh avgskew.sql 20 kso/kso
 
starting 20 copies of avgskew.sql 
 
EXADATA> @queued_sql
 
 SID SQL_ID        SQL_EXEC_ID SQL_TEXT
---- ------------- ----------- ------------------------------------------------------------
 181 05cq2hb1r37tr    16777363 select avg(pk_col) from kso.skew a where col1 > 0
 216                  16777364
  44                  16777365
 273                  16777366
 234                  16777367
 160                  16777368
 100                  16777369
 233                  16777370
  30                  16777371
 138                  16777372
   7                  16777373
 293                  16777374
 137                  16777375
  83                  16777376
 251                  16777377
  66                  16777378
 123                  16777379
 195                  16777380
 
18 rows selected.
 
EXADATA> -- now we have control of which statements are parallelized and we have queuing

This example shows that you can have Parallel Queuing without turning on Automatic DOP. Of course it’s using a hidden parameter, so don’t do this without checking with your mother first (I mean Oracle support). Maybe they will make this a real parameter in a future release (one can only hope). Note that there are hints to turn this feature on and off on a statement level as well (STATEMENT_QUEUING and NO_STATEMENT_QUEUING).

So why did I mention Exadata again? Mixed workloads require a mechanism that forces parallel queries to play nicely with others. (kind of like kindergarten) Parallel Queuing provides a relatively simple mechanism for doing just that. I also mentioned Resource Manager (RM) in passing. That’s another option at our disposal. It does have the ability to curb parallel processing as well and it’s enabled by default in 11gR2. RM, along with it’s kissing cousin IORM, are key components in consolidation strategies on Exadata as well. But we’ll have to leave that for another post. Some much to do and so little time.

Pete Finnigan will be teaching Oracle Security in Tallinn, Estonia and speaking at UKOUG Unix SIG at TVP

I have just added another public training date to my upcoming Oracle security trainings calendar. This is for November 4th and 5th in Tallinn, Estonia which I am really looking forwards to. I have also just agreed to do two....[Read More]

Posted by Pete On 07/07/10 At 01:31 PM

dba_tab_modifications

In case you don’t follow the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading. (And I’ve just discovered the ‘gutter=”false”;’ option for the ‘sourcecode’ tag in one of the comments on Martin’s blog – and that’s also a  helpful feature.) Filed [...]

Oracle's SQL Tuning Pack - part 3

How does the STA work in 11gR2 with the query from "Oracle's SQL Tuning Pack - part 2" ?

Here is the view on the Top Activity Page:
The first spike is when I ran the query. I pass the gray window over the spike and selected the first query and submitted it to the tuner. The rest of the load, the next 30 minutes is the STA running trying to find a better plan. After 30 minutes the STA times out saying not better plan found.
On 10gR2 the STA finishes in less than 2 minutes and finds a profile though the profile puts a higher load on the database for this query. On 11gR2, the STA runs for 30 and times out without finding any suggestions.

Here is a video on Vimeo of the tuning with DB Optimizer which takes less than 60 seconds to find a faster plan:

Oracle's SQL Tuning Pack, part 2

Above its the load produced by the same load. Its a query being run by 10 users 20 times with a sleep of random interval between 1-2 seconds.
The graph measures the load on the database. Green is CPU, blue is IO and the redish brown is concurrency.
First, on the left is the load of the original default execution plan.
Second, in the middle is the load after applying a profile from the SQL Tuning Advisor (STA).
Third, on the right is the load after having been tuned by DB Optimizer.
All this goes to show that the STA's profile can be sub-optimal even when a better plan exists.
Here is the view of the same load from the "Statistics" tab of the SQL Details in OEM 10g.
First, dark blue, is load from the default path.
Second, light blue is the load from the STA suggested profile.
Third is the load after tuning the query with DB Optimizer.
Here is the query, actually a Peoplesoft query,
SELECT
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD,
SUM (C.AMOUNT_DIFF) SUM_AMOUNT
FROM
PS_PAY_CALENDAR A,
WB_JOB B,
WB_RETROPAY_EARNS C,
PS_RETROPAY_RQST D,
PS_RETROPAYPGM_TBL E
WHERE
A.RUN_ID = 'PD2' AND
A.PAY_CONFIRM_RUN = 'N' AND
B.COMPANY = A.COMPANY AND
B.PAYGROUP = A.PAYGROUP AND
E.OFF_CYCLE = A.PAY_OFF_CYCLE_CAL AND
B.EFFDT = (SELECT MAX (F.EFFDT)
FROM WB_JOB F
WHERE
F.EMPLID = B.EMPLID AND
F.EMPL_RCD# = B.EMPL_RCD# AND
F.EFFDT < = A.PAY_END_DT) AND
       B.EFFSEQ = (SELECT MAX (G.EFFSEQ)
FROM WB_JOB G
WHERE
G.EMPLID = B.EMPLID AND
G.EMPL_RCD# = B.EMPL_RCD# AND
G.EFFDT = B.EFFDT) AND
C.EMPLID = B.EMPLID AND
C.EMPL_RCD# = B.EMPL_RCD# AND
C.RETROPAY_PRCS_FLAG = 'C' AND
C.RETROPAY_LOAD_SW = 'Y' AND
D.RETROPAY_SEQ_NO = C.RETROPAY_SEQ_NO AND
E.RETROPAY_PGM_ID = D.RETROPAY_PGM_ID
GROUP BY
A.COMPANY,
A.PAYGROUP,
E.OFF_CYCLE,
E.SEPCHK_FLAG,
E.TAX_METHOD,
E.TAX_PERIODS,
C.RETROPAY_ERNCD;

For the courageous or bored with idle time, the dmp and SQL are available at

and for kicks, the query's VST diagram
Couple of thoughts questions in my mind.
First if the reason for picking sub-optimal path in the first place is due to a bug, then it would make some sense that the the STA would still have the same bug and make the same mistakes.
Second, I wonder if the STA is really trying to find better plans, or if it is trying to pull the statistics used by the optimizer more in line with the actual statistics that would happen when running the query along the lines of TCF or tuning by cardinality feedback. When I look at the hints in profile the are of the nature of skewing stats one way or another:
OPT_ESTIMATE(@"SEL$485D066A", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=12.28434396)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=3.049755426)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=15.63082791)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=5.133606627)
OPT_ESTIMATE(@"SEL$485D066A", JOIN, ("B"@"SEL$1", "A"@"SEL$1", "VW_SQ_2"@"SEL$09936D23"), SCALE_ROWS=37.50606343)
OPT_ESTIMATE(@"SEL$B186933D", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=3.896066538)
OPT_ESTIMATE(@"SEL$B186933D", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$B186933D", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=9.626410845)
OPT_ESTIMATE(@"SEL$6E19F182", TABLE, "C"@"SEL$1", SCALE_ROWS=4.412144743)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_FILTER, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WBBRETROPAY_EARNS, SCALE_ROWS=3.642257471e-005)
OPT_ESTIMATE(@"SEL$6E19F182", INDEX_SKIP_SCAN, "C"@"SEL$1", WB_RETROPAY_EARNS_IDX1, SCALE_ROWS=3.642257471e-005)
The above profile "hints" are mostly concerning scaling the expected rows.
Speaking of TCF it's interesting to not that in DB Optimizer the TCF values of estimate verses actual come out closer in the optimize query verses the original.
Below are the explain plans for the default plan, plan with profile and the DB Optimizer tuned plan. Notice the "Orders of Magnitude" column. This column shows the orders of magnitude that the estimated rows differed from the actual rows per line in the execution plan. The larger the difference the bigger the discrepancy between what the optimizer expected and what actually happened
Default Plan and TCF
Profile Explain and TCF
DB Optimized Plan and TCF
Ironically the plan using the profile has the worst TCF discrepancies