Friday Nov 06, 2009

New Blog location

Since I will likely have to transition blogging systems after the Oracle acquisition, I decided to avoid the trouble and just begin using WordPress now. I intend to use this site for future posts regarding Oracle and Sun performance... and I will likely repost material as well.

My new Oracle/Sun performance blog is:

I already made a post about the value of predictable IO latency with Exadata V2. I hope you enjoy it and this new site.

Take care,
Glenn

Monday Oct 19, 2009

Exadata V2... Oracle grid consolidation in a box

I spent some time last week at OOW talking with Oracle customers regarding the technology in the Exadata V2 database machine. There were certainly a lot of customers excited to use this for their data warehouses - 21GB/sec disk throughput, 50GB/sec flash cache, and Hybrid Columnar Compression really accelerate this machine past the competition. The viability of Exadata V2 for DW/BI was really a given, but what impressed me the most was the number of customers looking to consolidate applications in this environment.

Ever since I was first brought onto this project, I thought Exadata V2 would be an excellent platform for consolidation. In my experience working on the largest of Sun's servers, I have seen customers with dozens of instances on a single machine. Using M9000 series machines, you can create domains in order to support multiple environments - this very much mirrors what Exadata V2 can provide. Exadata V2 allows DBAs to deploy multiple instances across a grid of RAC nodes available in the DB machine – and since you are using RAC, availability is a given. Also, the addition of Flash allows for up to 1 million IOPs to support your ERP/OLTP environments. Consider the picture below.

With this environment, your production data warehouse can share the same infrastructure as the ERP, test, and development environments. This model allows the flexibility to add/subtract nodes from a particular database as needed. But, the operational efficiency is not the biggest benefit to consolidation. The savings in terms of power, space, and cooling are substantial.

Consider for a moment the number of drives necessary to match the 1 million IOPs available in the database machine. Assuming you are using the best 15,000 rpm drive, you would be able to do 250 IOPs/drive. So, to get to 1 million IOPs, you would need 4,000 drives! A highly dense 42U storage rack can house any where from 300-400 drives. So, you would need 10 racks, just for the storage and at least one rack for servers.

With Exadata V2, you get more than 10:1 savings in floor space and all the power an cooling benefits as well. It is no wonder people are excited about Exadata V2 as a platform to consolidate compute and storage resources.

Friday Sep 25, 2009

Jumbo Frames with Oracle RAC really does Rock!!

I have been involved in a customer situation on and off for at least 6 months now. The customer had been seeing performance issues with their application running on Oracle 10g RAC. We looked through the mounds of data initially and noticed that they were indeed waiting quite a bit on Global cache buffer waits. This was during times of fairly heavy load and we could see the CPU was fairly busy with interrupts as well. After looking at the MTU size for the cluster interconnect, we noticed that it was incorrectly set to the default (1500). Thus started the odyssey to implement Jumbo Frames.

The default MTU is 1500 for Solaris, but this is not ideal when Oracle is using an 8K block size. Simple math tells us that you will require 6 transfers to transmit just one block of data across the cluster interconnect. This just creates additional overhead on the server and additional latency waiting for global blocks to be transferred. Changing the MTU to be a "jumbo frame" of 8K or greater is fairly simple from a technical point of view, but it can quickly turn into a political issue.

The cluster interconnect is often relegated to be the responsibility of the networking group. No problem right? While this is a network component, it is really part of the server - no different really from a PCI bus or processor back plane. The networking groups will often apply their tried and true methods for LANs around the company, but this doesn't translate to RAC. Modern network switches can easily handle this configuration change as well, but policy often wins. The networking group assures everyone their switch can handle the traffic with the default MTU and everyone goes on their merry way.

So, what happened?

After months looking at "other things", they finally were convinced to try this "Best Practice" with Jumbo Frames. Immediately, they saw:

  • 50% reduction in CPU overhead
  • 75% reduction in Global cache buffer waits
  • IP Reassemblies dropped by 10x

Moral of the story: Implement Jumbo Frames for Oracle RAC interconnects... It is a best practice after all :)

Thursday Sep 17, 2009

Breaking the silence... Exadata V2 performance details at OOW.

UPDATE: Unfortunately our OOW session was canceled. I will still be at the show participating in various performance related activities.

Now that Exadata V2 has been announced, I can begin to talk about what I have been working on lately. Kevin Closson and I have been working to characterize the performance of the Exadata V2 product. The performance of this machine is just stunning. This is the fastest machine I have ever used and I have been benchmarking big iron SMP with Oracle for the better part of 20 years now.

Exadata V2 brings together the best of Brawny Hardware and Brainy Software. The storage cells utilize Sun's new FlashFire technology along with the latest Nehalem based servers. Software advances with Storage Indexes and Hybrid Columnar compression push the performance to amazing speeds. If you are at Oracle Open World, please stop by our session to learn more about the performance of this exciting new product. I will post the session details once they are available.

Monday Sep 14, 2009

Sun Oracle Database machine arrives!

Let the games begin... http://www.oracle.com/features/larry-ellison-webcast.html

I will post some informational links as they are available. Pretty exciting stuff!

Wednesday Jun 17, 2009

Chronology of Sun technology for Oracle performance

With the pending buyout of Sun by Oracle still fresh in our minds, a recent email thread emerged wanting to put together a time-line of technologies created to help Sun servers run Oracle better. This seemed like a great way to start off the Oracle Performance portion of the Performance Technologies group new wiki. I hope you enjoy this overview of the key technologies used to optimize Oracle performance on Sun servers.

Monday Apr 20, 2009

Oracle buys Sun! Dtrace probes for Oracle?

As a Sun engineer who specializes on Oracle performance, I am pretty excited for the future. Sun and Oracle have been working together closely for years, but this will allow for even closer integration and innovation.

Could you imagine... "Dtrace probes for Oracle?" How cool would that be?

Time will tell innovations will come out of this acquisition, but I for one am pretty excited... Certainly, much more so than last week :) Heck, I now get to work with my old buddy Kevin Closson, the Exadata performance guru. Should be a fun ride ahead.

Tuesday Dec 09, 2008

Oracle analysis 101 : Begining analysis techniques

Recently, I was asked to present beginning Oracle analysis techniques to an internal audience of Sun engineers. This presentation was a lot of fun to put together and was well received. After cleaning it up a bit and taking out the boring internal Sun stuff, I thought the presentation might be useful to a larger audience. This presentation focuses on problem statement, environmental, and basic AWR/Statspack analysis.

If you find this useful or have suggestions, drop me a note.

Saturday Nov 08, 2008

Virtual CPUs effect on Oracle SGA allocations.

Several years ago, I wrote about how Oracle views multi-threaded processors. At the time we were just introducing a dual-core processor. This doubling of the number of cores was presented by Solaris as virtual CPUs and Oracle would automatically size the CPU_COUNT accordingly. But what happens when you introduce a 1RU server that has 128 virtual CPUs?

The UltraSPARC T1/T2/T2+ servers have many threads or virtual CPUs. The CPU_COUNT on these systems is sized no different than before. So, the newly introduced T5540 with 4xUltraSPARC T2+ processors would have 256 threads and CPU_COUNT would be set to 256.

So, what does CPU_COUNT have to do with memory?

Thanks to my friends in the Oracle Real World Performance group, I was made aware that Oracle uses CPU_COUNT to size the minimum amount of SGA allowed. In one particular case, the DBA was trying to allocate 70 database instances on a T5140 with 64GB of memory and 128 virtual CPUs. Needless to say, the SGA_TARGET would have to be set fairly low in-order to accomplish this task. A SGA_TARGET was set to 256MB, but the following error was encountered.

    ORA-00821: Specified value of sga_target 256M is too small
After experimentation, they were able to start Oracle with a target of 900MB, but with 70 instances this would not fly. Manually lowering the CPU_COUNT allowed the DBA to use an SGA_TARGET of 256MB. Obviously, this is an extreme case and changing CPU_COUNT was reasonable.

Core and virtual CPU counts have been on the rise for some years now. Combine rising virtual CPU count with the current economic climate and I would suspect that consolidation will be more popular than ever. In general, I would not advocate changing CPU_COUNT manually. If you had one instance on this box, the default be just fine. CPU_COUNT automatically sizes so many other parameters that you should be very careful before making a change.

Monday Sep 22, 2008

Oracle Open World 2008 - Growing Green Databases with UltraSPARC CMT

The time has come present at Oracle Open World on UltraSPARC CMT performance. I decided to post the final version here in addition to the OOW site. I hope to see you there!
Session ID: S299785
     Title: Growing Green databases with Oracle on the UltraSPARC CMT processor
      Date: Monday Sept 22nd
      Time: 13:00 - 14:00
     Place: Moscone South Rm 236

Thursday May 29, 2008

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;
    
    

Wednesday May 21, 2008

Optimizing Oracle index create with CMT based servers

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.

Wednesday May 14, 2008

Parallelizing Oracle backup with RMAN on CMT based servers

A backup window is important to keep in check to ensure time for batch and on-line work. With Oracle RMAN there are several ways to keep backups flowing smoothly. This example shows how you can use multiple channels and parallelism to increase the throughput of backup to the maximum of your IO configuration.

Results

This graph shows scaling in MB/sec based on the # of channels in use. The term "channels" used by Oracle does not have any relation to actual "physical" channels. In Oracle RMAN terms, a channel is simply a "connection" to a database for which to backup data. Data files are assigned to "connections" in a round-robin fashion so as to utilize all connections as evenly as possible.



By configuring a parallelism of 20 with RMAN, I was able to increase throughput from 5->80 MB/sec. Single threaded performance was limited to 5MB/sec mainly due to the high CPU component that comes with using "COMPRESSED" backups. The way to maximize IO throughput with COMPRESSION is to simply add more streams.

RMAN commands to achieve parallelism

I used the following commands to create 20 backup "channels" for RMAN. Notice that they configured to use the same directory, just with different file formats.

RMAN> configure channel 1 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c1%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
RMAN> configure channel 2 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c2%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
...
...
RMAN> configure channel 20 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c20%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;

After creating these channels, you must tell RMAN how to connect to these channels:

RMAN> configure channel 1 DEVICE TYPE DISK CONNECT '/as sysdba';
RMAN> configure channel 2 DEVICE TYPE DISK CONNECT '/as sysdba';
...
...
RMAN> configure channel 20 DEVICE TYPE DISK CONNECT '/as sysdba';

Next, you need to tell RMAN to use disk parallelism of 20:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE 
      TO COMPRESSED BACKUPSET PARALLELISM 20;

Finally, let's issue the backup command:

RMAN> BACKUP TABLESPACE GLENNF_RMAN;

Starting backup at 09-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=966 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=952 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=940 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=938 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=939 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=969 devtype=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: sid=961 devtype=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: sid=963 devtype=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: sid=953 devtype=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: sid=970 devtype=DISK
allocated channel: ORA_DISK_11
channel ORA_DISK_11: sid=920 devtype=DISK
allocated channel: ORA_DISK_12
channel ORA_DISK_12: sid=943 devtype=DISK
allocated channel: ORA_DISK_13
channel ORA_DISK_13: sid=968 devtype=DISK
allocated channel: ORA_DISK_14
channel ORA_DISK_14: sid=929 devtype=DISK
allocated channel: ORA_DISK_15
channel ORA_DISK_15: sid=960 devtype=DISK
allocated channel: ORA_DISK_16
channel ORA_DISK_16: sid=931 devtype=DISK
allocated channel: ORA_DISK_17
channel ORA_DISK_17: sid=927 devtype=DISK
allocated channel: ORA_DISK_18
channel ORA_DISK_18: sid=957 devtype=DISK
allocated channel: ORA_DISK_19
channel ORA_DISK_19: sid=958 devtype=DISK
allocated channel: ORA_DISK_20
channel ORA_DISK_20: sid=964 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00068 name=/oracle/O6S/sapraw/glenn1
channel ORA_DISK_1: starting piece 1 at 09-MAY-08
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00069 name=/oracle/O6S/sapraw/glenn2
channel ORA_DISK_2: starting piece 1 at 09-MAY-08
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00070 name=/oracle/O6S/sapraw/glenn3
channel ORA_DISK_3: starting piece 1 at 09-MAY-08
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00071 name=/oracle/O6S/sapraw/glenn4
channel ORA_DISK_4: starting piece 1 at 09-MAY-08
channel ORA_DISK_5: starting compressed full datafile backupset
channel ORA_DISK_5: specifying datafile(s) in backupset
input datafile fno=00072 name=/oracle/O6S/sapraw/glenn5
channel ORA_DISK_5: starting piece 1 at 09-MAY-08
channel ORA_DISK_6: starting compressed full datafile backupset
channel ORA_DISK_6: specifying datafile(s) in backupset
input datafile fno=00073 name=/oracle/O6S/sapraw/glenn6
channel ORA_DISK_6: starting piece 1 at 09-MAY-08
channel ORA_DISK_7: starting compressed full datafile backupset
channel ORA_DISK_7: specifying datafile(s) in backupset
input datafile fno=00074 name=/oracle/O6S/sapraw/glenn7
channel ORA_DISK_7: starting piece 1 at 09-MAY-08
channel ORA_DISK_8: starting compressed full datafile backupset
channel ORA_DISK_8: specifying datafile(s) in backupset
input datafile fno=00075 name=/oracle/O6S/sapraw/glenn8
channel ORA_DISK_8: starting piece 1 at 09-MAY-08
channel ORA_DISK_9: starting compressed full datafile backupset
channel ORA_DISK_9: specifying datafile(s) in backupset
input datafile fno=00076 name=/oracle/O6S/sapraw/glenn9
channel ORA_DISK_9: starting piece 1 at 09-MAY-08
channel ORA_DISK_10: starting compressed full datafile backupset
channel ORA_DISK_10: specifying datafile(s) in backupset
input datafile fno=00077 name=/oracle/O6S/sapraw/glenn10
channel ORA_DISK_10: starting piece 1 at 09-MAY-08
channel ORA_DISK_11: starting compressed full datafile backupset
channel ORA_DISK_11: specifying datafile(s) in backupset
input datafile fno=00078 name=/oracle/O6S/sapraw/glenn11
channel ORA_DISK_11: starting piece 1 at 09-MAY-08
channel ORA_DISK_12: starting compressed full datafile backupset
channel ORA_DISK_12: specifying datafile(s) in backupset
input datafile fno=00079 name=/oracle/O6S/sapraw/glenn12
channel ORA_DISK_12: starting piece 1 at 09-MAY-08
channel ORA_DISK_13: starting compressed full datafile backupset
channel ORA_DISK_13: specifying datafile(s) in backupset
input datafile fno=00080 name=/oracle/O6S/sapraw/glenn13
channel ORA_DISK_13: starting piece 1 at 09-MAY-08
channel ORA_DISK_14: starting compressed full datafile backupset
channel ORA_DISK_14: specifying datafile(s) in backupset
input datafile fno=00081 name=/oracle/O6S/sapraw/glenn14
channel ORA_DISK_14: starting piece 1 at 09-MAY-08
channel ORA_DISK_15: starting compressed full datafile backupset
channel ORA_DISK_15: specifying datafile(s) in backupset
input datafile fno=00082 name=/oracle/O6S/sapraw/glenn15
channel ORA_DISK_15: starting piece 1 at 09-MAY-08
channel ORA_DISK_16: starting compressed full datafile backupset
channel ORA_DISK_16: specifying datafile(s) in backupset
input datafile fno=00083 name=/oracle/O6S/sapraw/glenn16
channel ORA_DISK_16: starting piece 1 at 09-MAY-08
channel ORA_DISK_17: starting compressed full datafile backupset
channel ORA_DISK_17: specifying datafile(s) in backupset
input datafile fno=00084 name=/oracle/O6S/sapraw/glenn17
channel ORA_DISK_17: starting piece 1 at 09-MAY-08
channel ORA_DISK_18: starting compressed full datafile backupset
channel ORA_DISK_18: specifying datafile(s) in backupset
input datafile fno=00085 name=/oracle/O6S/sapraw/glenn18
channel ORA_DISK_18: starting piece 1 at 09-MAY-08
channel ORA_DISK_19: starting compressed full datafile backupset
channel ORA_DISK_19: specifying datafile(s) in backupset
input datafile fno=00086 name=/oracle/O6S/sapraw/glenn19
channel ORA_DISK_19: starting piece 1 at 09-MAY-08
channel ORA_DISK_20: starting compressed full datafile backupset
channel ORA_DISK_20: specifying datafile(s) in backupset
input datafile fno=00087 name=/oracle/O6S/sapraw/glenn20
channel ORA_DISK_20: starting piece 1 at 09-MAY-08
channel ORA_DISK_2: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c2O6S_S_81_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_3: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c3O6S_S_82_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_4: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c4O6S_S_83_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_9: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c9O6S_S_88_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_9: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_11: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c11O6S_S_90_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_11: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_12: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c12O6S_S_91_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_12: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_13: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c13O6S_S_92_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_13: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_18: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c18O6S_S_97_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_18: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_20: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c20O6S_S_99_P_1_T_654270135 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_20: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_10: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c10O6S_S_89_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_10: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_16: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c16O6S_S_95_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_16: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c1O6S_S_80_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:00
channel ORA_DISK_5: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c5O6S_S_84_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:01:00
channel ORA_DISK_14: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c14O6S_S_93_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_14: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_7: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c7O6S_S_86_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_8: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c8O6S_S_87_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_6: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c6O6S_S_85_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:01:04
channel ORA_DISK_15: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c15O6S_S_94_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_15: backup set complete, elapsed time: 00:01:02
channel ORA_DISK_17: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c17O6S_S_96_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_17: backup set complete, elapsed time: 00:01:02
channel ORA_DISK_19: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c19O6S_S_98_P_1_T_654270135 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_19: backup set complete, elapsed time: 00:01:01
Finished backup at 09-MAY-08

Configuration

  • T5240 - "Maramba" CMT based server
    • 2 x UltraSPARC T2 Plus @ 1.4GHz
    • 128GB memory
    • 2 x 1GB fiber channel HBA
    • 1 x 6140 Storage array with 1 lun per channel.
  • Software
    • Solaris 10 Update 5
    • Oracle 10.2.0.3
    • CoolTools

42 -- the anwser to the ultimate question

I just finished reading an excellent blog entry by Andy Harrison that discusses the importance of a good problem statement. After working many problems with shady problem definitions, I will forward this to entry to all violators... So if we can't agree on the problem, we can all agree on the answer :)

Monday May 12, 2008

Optimizing Oracle Schema Analyze with CMT based servers

A common observation regarding Niagara based servers is that system maintenance or database administration tasks can run slower than previous generations of Sun servers. While single-threaded performance may be less, these maintenance tasks are often able to be parallelized, especially using a database engine as mature as Oracle. Take for instance the task of gathering schema statistics. Oracle offers many options on how to gather schema statistics, but there are a few ways to reduce overall gather statistics time:
  • Increased Parallelism
  • Reduced Sample Size
  • Concurrency
Oracle has written many articles in metalink which discuss sample size and the various virtues. There have also been many volumes written on optimizing the Oracle cost based optimizer (CBO). Jonathan Lewis of who is a member of the famous Oaktable network has written books and multiple white papers on the topic. You can read these for insight into the Oracle CBO. While a reasonable sample size or the use of the "DBMS_STATS.AUTO_SAMPLE_SIZE" can seriously reduce the gather statistics times, I will leave that up to you to choose the sample size the produces the best plans.

Results

The following graph shows the total run time in seconds of a "GATHER_SCHEMA_STATS" operations at various levels of parallelism and sample size on a simple schema of 130GB. All tests were run on a Maramba T5240 with a 6140 array and two channels.

GATHER_SCHEMA_STATS parallelism and sample_size


Note that if higher levels of sampling are required, parallelism can help to significantly reduce the overall runtime of the GATHER_SCHEMA_STATS operation. Of course a smaller sample size can be employed as well.

GATHER_SCHEMA_STATS options

SQL> connect / as sysdba

-- Example with 10 percent with parallel degree 32
--
SQL> EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'GLENNF', 
     ESTIMATE_PERCENT=>10, 
     DEGREE=>32, 
     CASCADE=>TRUE);

-- Example with AUTO_SAMPLE_SIZE and parallel degree 32
--
SQL> EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'GLENNF', 
     ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, 
     DEGREE=>32, 
     CASCADE=>TRUE);

Note that you must have "parallel_max_servers" set to at least the level of parallelism desired for the GATHER_SCHEMA_STATS operation. I typically set it higher to allow for other parallel operations to get servers.

        SQL> alter system set parallel_max_servers = 128;

Finally, you can easily run a schema collect on multiple schema's concurrently and in parallel by issuing GATHER_SCHEMA_STATS from multiple sessions and ensuring the level of parallelism is set high enough to accommodate.

Configuration

  • T5240 - "Maramba" CMT based server
    • 2 x UltraSPARC T2 Plus @ 1.4GHz
    • 128GB memory
    • 2 x 1GB fiber channel HBA
    • 1 x 6140 Storage array with 1 lun per channel.
  • Software
    • Solaris 10 Update 5
    • Oracle 10.2.0.3
    • CoolTools
  • Schema
      SQL> Connected.
      SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  
      OWNER	 TABLE_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 B2	       239826150    38560
      GLENNF	 B1	       237390000    32110
      GLENNF	 S2		 4706245      750
      GLENNF	 S4		 4700995      750
      GLENNF	 S5		 4699955      750
      GLENNF	 S7		 4698450      750
      GLENNF	 S8		 4706435      750
      GLENNF	 S9		 4707445      750
      GLENNF	 S10		 4700905      750
      GLENNF	 S3		 4706375      750
      GLENNF	 GTEST		 4706170      750
      
      OWNER	 TABLE_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S6		 4700980      750
      GLENNF	 S1		 4705905      710
      HAYDEN	 HTEST		 4723031      750
      
      14 rows selected.
      
      SQL>   2    3    4  
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 B1_I2	       244841720    11623
      GLENNF	 B2_I2	       239784800    11451
      GLENNF	 B1_I1	       248169793     8926
      GLENNF	 B2_I1	       241690170     8589
      GLENNF	 S6_I2		 4790380      229
      GLENNF	 S3_I2		 4760090      227
      GLENNF	 S2_I2		 4693120      226
      GLENNF	 S5_I2		 4688230      224
      GLENNF	 S8_I2		 4665695      223
      GLENNF	 S4_I2		 4503180      216
      GLENNF	 S1_I2		 4524730      216
      
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S9_I2		 4389080      211
      GLENNF	 S10_I2 	 4364885      209
      GLENNF	 S7_I2		 4357240      208
      GLENNF	 S2_I1		 4972635      177
      GLENNF	 S3_I1		 4849660      174
      GLENNF	 S6_I1		 4830895      174
      GLENNF	 S9_I1		 4775830      171
      GLENNF	 S7_I1		 4772975      169
      GLENNF	 S5_I1		 4648410      168
      GLENNF	 GTEST_C1	 4686790      167
      GLENNF	 S1_I1		 4661605      166
      
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S4_I1		 4626965      166
      GLENNF	 S10_I1 	 4605100      164
      GLENNF	 S8_I1		 4590735      163
      
      25 rows selected.
      

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