Just for completeness, I thought I’d share my experiences of the final day of the 2008 UKOUG.
Interpreting Execution Plans – Christian Antognini
Packed in like sardines for Christian Antognini’s talk on reading explain plans, I’ve already reviewed his book, and this talk was covering chapter 6.
First bit is a run through of getting plans. I think this is easy bit, the hard bit is actually understanding the plan you’ve found and then actually doing something about it.
Ways of getting a plan are:
select * from table(DBMS_XPLAN.DISPLAY_AWR (sql_id, hash_value)
@?/rdbms/admin/awrsqrpt.sql – realy, really, useful
Already used this script and it gives you a plan and the resources consumed by this bit of sql – looks excellent, just plug in a time period when you know the sql was running and plug in a sql_id. Could not be easier to get historical explain plans.
execution plan is a tree with every node being an operation.
parent has 1 or multiple child
a child has a single parent
only node in tree without a parent is the root
child to indented to the right of parent
parent id is always < child id
about 200 types of operations
3 different types:
has at most 1 child
generally children executed before parent (though not always)
child executed at most
every child feeds it’s parent.
operations having multiple children that are independently executed
every child executed only once
all operations having multiple children where one of the children controls the execution of all other children are related-combine operations.
child with smallest id controls the exectuon. children are not executed sequentially but a kind of interleaving is performed.
only the first child is executed at most once. All others may be exected several times or not at all.
not every child feeds it’s parents.
Chris showed examples of each type and had quite a large example to follow. He showed quite nicely how to split the larger plan into chunks of the type he covered, but when you just see the big plan, it is really hard to know where to start,.
At the end Chris mentioned the extended explain plan information were you compare what the optimiser estimates to be the number of rows returned by a query, and the actual amount it really got when it ran. When the estimate and the actual vary by a lot, this is often where a plan is going awry.
Another way of trying to optimise plans it to spot how many rows are returned and if there are steps that are throwing away large numbers of rows then try to get these steps down as early as possible – so there is less throwaway work being done – which is back to what Dan Fink was saying on Thursday.
Advanced Oracle Troubleshooting – Tanel Poder
For my money, this was the best presentation of the whole week.
I’ve been avidly following Tanel’s blog for a very long time and he was basically showing his systematic procedure for obtaining the cause of various issues. This was mostly demos which he handled with aplomb.
Most interesting for me was using pstack to see what a process was up to.
UKOUG In Summary
So that is UKOUG over for another year. 5 days is probably a bit too much for me, I’ll be happy when it’s down to 3. I think I learned this year that you must rehearse before you present, rather than just rely on the fact you have presented the same material 3 months earlier. If I get to present next year, maybe I’ll wait to write it to closer to the conference – I’ve been presenting on ASM for the best part of a year now, but I think it’s time to get back to dataguard!
I also learned this year that the after conference beer is probably more interesting than the actual presentations. Though it seems like I missed out on of the best presentations this year, “Drive Heads Revisited”.