OOW: Day 4

Automatic Storage Management – Frits Hoogland

Fantastic crowd for 09:00am the day after the big party. The room is jammed packed.

Frits comes across as a very confident speaker. This was one of the most technical in depth presentations I have seen the whole week at Openworld. It feels quite strange after all the generic overviews.

not all that many people using ASM in the hall, of those that are, 10.2 is the vast majority.

Run through of ASM basics.

Describing redundandcy, claiming vast majority of people using ASM are using it in external redundancy.

jumped right into disk headers and showed diagram of what happens when you increase the number of disks to the disk headers. he really needed to explain Allocation units first.

I don’t think he has explained extents.

Explained the concept of not being able to allocate space even when one disk in a diskgroup still has free space.

Explains about how ASM tunes I/O – it’s just allocation policy only!


ASM sees each device as an individual entity and stripes over all devices it sees.

ASMLIB: support library for ASM. It is an API for storage & O/S vendors to add functionality

Device name labels, persistent device names. ASMLIB creates a meta device and sorts out the correct permissions


ASMLIB adds a kernel dependency, adds dependency to asmlib.

ASM Advantages & Disadvantages

Using ASM pushes to the DBA more responsibility for volume management  & filesystem management. RMAN backups are compulsory – this is a good thing. ASM is relatively young. There is no black magic in terms of allocating storage space.

ASM does SAME – Stripe And Mirror Everything

Online storage migration and configuration changes can be a real manageability win.

Frits did not take questions which was a real, real shame as there was an absolutely enormous crowd around him at the end asking questions, it would have been interesting to hear some the questions – and answers!

Real World Performance – Andrew Holdsworth

There is a guy sitting next me eating his lunch & talking on his freaking bluetooth headset. w/hat the hell is the matter with these people!?!

Optimizer Expose

issues he hears at time:  never using correct index, optimizer scans table when i want to use index access, why are nested loops so bad sometimes

Problems occur sometimes upon upgrading.

DBMS_STATS auto gathering has impacted production systems throughout the world, unpredictable performance when execution plans unpredictably change.

auto gather when 10% of the rows have changed

new histograms may be created

bind peeking may become an issue because of new histograms

It’s all about the statistics that are generated.

contention between letting stats evolve but risk changing good plans or keep them static and predictable and potentially not get the in many cases log file sync was seen as impacting scalability

optimal plan

To keep consistency of plans do not gather histograms, accurate high/low values are crucial:

use tools like SQL profiles, outlines
manually hint every statement – bad idea
this approach will not give the best plans but does give predictability

Plan efficiency important where I/O is not just memory access

six challenges: data skew  – a non uniform distribution of data generally on a per column basis

histograms can help with data skew, or determine if uniform plans are ok

Bind peeking: different plans are even possible on different instances in a cursor.

high/low cardinality: impossible for optimizer to get the correct # of rows when the high/low values are incorrect

correlations can throw the optimizer

cardinality approximation

the debugging process is all about making sure the optimizer has correct cardinality

running with gather_plan_statistics is how to get what the  cardinality estimate the optimizer is making.

Managing statistics on partitioned tables

different possibilities for building stats with partitions

when to apply the knife to your data/workloads/databases

a little discussion on sharding and in memory db on the middleware need to make sure you can route transactions from the middleware to the correct shard.

detecting and avoiding hiccups in your  system

in many oracle systems log file sync is the dominant wait.

output from v$event_histogram for logfile sync

graph showing count x elapsed time shwing peak at 16ms but another peak at around the timeout of 1sec.

statistical averages can be misleading

cursor invalidations can lead to massive re parsing.


root cause analysis is vital

Well that is a wrap from me, my OpenWorld is over for this year, really hope to make it back next year.


One thought on “OOW: Day 4

  1. Thanks jason for attending my session!

    The purpose of showing the header spreading was (also) to show what adding a disk means. But the point’s taken.

    Also I really forgot to add a questions and answers screen at the end. Because of that, the end seemed kind of sudden, and made people come to me instead of publical questions and answers.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s