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:

select * from (
select
times_purchased, state_code
from customers
t
)
pivot <–How easy is that?! (
count(state_code)
for
state_code in (‘NY’,’CT’,’NJ’,’FL’,’MO’)
)
order by
times_purchased
/

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 …

I’m sure there are many others that I’ll come to admire as I start to work with 11g more, but these are topping my list so far!!

 

 

Print Friendly
January 11th, 2010 by

facebook comments:

  • Kellyn Pedersen

    I haven't had a chance to test any of these features yet, since I'm still preparing to build our 11g environment, but I will know very soon and will update the answer here. Sorry I don't know off the top of my head, but logically, you would think they would have built it in the way the examples are written so far! 🙂

  • Pingback: EM12c and Hardware - Oracle - Oracle - Toad World()

  • Facebook
  • Google+
  • LinkedIn
  • Twitter