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 & OMF.
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!?!
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
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
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.