Optimizing Oracle DSS operations with CMT based servers

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;
    
    
Comments:

Hi Glenn,

Can you please post a comparison with an UltraSparc IV+ or a UltraSparc VI series CPU too?

Thanks
Krishna

Posted by Krishna Manoharan on June 19, 2008 at 05:38 AM PDT #

It would be interesting to show all architectures. The curves should look very similar, but certainly would start at different points. I suspect the a USIV+ processor would show higher initial throughput assuming the IO setup is the same. If I get a chance to run something on a USIV/USIV+ machine I will post the results.

Posted by Glenn Fawcett on June 19, 2008 at 08:51 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder