Optimizing Oracle DSS operations with CMT based servers

Thursday May 29, 2008

This entry continues the Throughput Computing Series to show how a typical DSS operation can be optimized with CMT based servers. The "Create as Select" and "Insert into as Select" operations are quite common in DSS and OLTP environments as well. Unless parallelism is specified, Oracle will single thread these operations. To achieve optimal throughput, these operations can use parallel query and DML operations.

Results

I created a 20GB table on a T5240 server to serve as the source for the "Create as Select" (CAS) operations. The parallelism of the CAS operation was increased until the IO subsystem was maxed out. This resulted in a drop from 25 minutes with no parallelism to 2 minutes 40 seconds with 8 threads...thats nearly a 10x speedup by simply using parallelism built into Oracle!



This server was configured with just two HBAs, one for each the source and destination tables. This limited throughput of CAS operations to 127MB/sec, or one HBA. With this IO configuration, it took only 8 threads to reach maximum throughput. You should experiment to achieve maximum throughput of your IO configuration. If you suspect your IO configuration is not performing up to speed, look into doing some IO micro benchmarking to find the maximum throughput outside of Oracle. A topic for a later discussion

SQL syntax

The following shows how to use parallel DML and parallel query.

       ## Create as Select ##
       ##
       SQL> alter session enable parallel dml;
            
       SQL> create table abc
            parallel (degree 32)
            as
            select /*+ parallel(gtest, 32) */ * from gtest;


       ## Insert as Select ##
       ##
       SQL> alter session enable parallel dml;
        
       SQL> insert /*+ parallel(abc,32) */
            into abc
            select /*+ parallel(gtest,32) */ * from gtest;

Optimizing Oracle index create with CMT based servers

Wednesday May 21, 2008

One of the most common ways to improve SQL performance is the use of indexes. While Oracle does have a wide variety of indexes available, these tests focus on the most commonly used B-tree index. On large tables it is important to ensure indexes get created in a timely fashion, that is why Oracle introduced several features to decrease index creation time:

• "unrecoverable"
  
  This feature prevent the logging of intermediate steps of the index creation process. There is really no value to logging of intermediate steps. Index creation should be thought of as an atomic process - if it fails, you can always start over. If you create indexes as "unrecoverable" they won't be recoverable until a backup is performed on the target tablespace.
  
  
• "parallel"
  
  This simply uses parallel query/dml to speed the creation of indexes.
  

The following index create statement shows how to use the "parallel" and "unrecoverable" features for index creation.

create index gtest_c1 on gtest(idname)
pctfree 30  parallel 64 tablespace glennf_i unrecoverable;

Results

The following test created an non-unique index on varchar(32) column of a 20GB table. Parallelism was increased from 1->64 in order to use the available IO bandwidth. With parallelism of 1 index creation took 34 minutes, while with parallelism of 64 it took only 3 minutes and 45 seconds!



These tests use the same configuration as previous posts regarding Oracle in the Throughput Computing series.