Cool 11g New Features

I’m preparing to build our new 11g environment and as I “study up” on all that is new in 11g, there are a few features that stand out over the rest, (at least in my book!)

I/O Calibration

I’m am challenging the upper most limits of my hardware with how much I/O I can produce. I have spent much of my time since I started with my new company correcting the I/O issues manually, (yes, ASM failed me, but I’m sure it will live up to my expectations someday!:))

The CALIBRATE_IO procedure that comes as part of the DBMS_RESOURCE_MANAGER packge is a dream come true for someone like me. This will return a report to me, after passing in the number of disks and maximum latency that is tolerable, the maximum I/O requests per second, the maximum MB per second and even the actual latency.

Reference Partitioning

Somedays I look at the choices in partitioning in my environments and shake my head. I was taught that you either “partition right or you don’t partition at all”, so when I see partitioning that causes more performance issues than gains, it leaves me a bit frustrated.

This new partitioning option allows you, when a child table with a referencing foreign key, but without the partitioned key column from the parent table existing, to still partition through the actual referencing foreign key! I have numerous tables where columns have been duplicated simply for partitioning reasons and this option will relieve this challenge.

The Pivot Operator

I don’t know how often we’ve experienced performance challenges from complex decodes. Often our data is simply not presented in the format we require for reporting and the PIVOT operator gives us the option to present the data in a crosstab format.

oracle.com has a great example of this feature:

Here is the output:
. TIMES_PURCHASED ‘NY’ ‘CT’ ‘NJ’ ‘FL’ ‘MO’
————— ———- ———- ———- ———- ———-
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
… and so on …