Hotsos Symposium 2009 - Final Day

For 8:30 on the Wednesday morning after the big party night, Chen Shapira drew a nice crowd (see below) who wanted to learn about the "Seven Sins of Concurrency":





"Oracle Performance for Developers (and the DBAs Who Have to Work with Them)" was presented by Kerry Osborne:



Tanel Põder's "Zero Slides: The Scripts and Tools which Will Make Your Life Easier and Allow You to Troubleshoot Better" was definitely a big "thumbs-up".



Dan Norris tells us how to "Avoid Common RAC Issues":



The lovely Robyn Sands presented an informative "Variance as a Tool: Measuring for Robust Performance".



Captain Morgan? Uh, I mean Ric Van Dyke tells us about "Intelligent Cursor Sharing".



Jonathan Lewis begins the first part of his presentation, "Hints on Hints".



Kyle Towle teaches us how to "Predict Oracle Performance" in his presentation:



Jonathan Lewis finishes up with the second part of his "Hints on Hints".



Last, but definitely not least, Monty Orme presents "Give 'Em What They Need!":



OK, either Gary really secretly liked the earring from his pirate costume, or someone bet him a lot of money that he wouldn't wear the earring during the Closing Ceremony. What do you think?




Enrapt crowd waiting for the Silent Auction Winner announcements:



Bah-bye! We hope to see you all next year!



Thanks to everyone (presenters, attendees, Omni Mandalay hotel staff and Hotsos teammates) who made this Sym one of the best yet!

Hotsos Symposium 2009 Day 2

Bright and early on a Tuesday morning, attendees gathered for the first presentation of the day:



Cary Millsap delivers an entertaining presentation on "Lessons Learned v2009.03":



In this photo, Dan Tow convinces attendees to just say "no" to the safe use of views:



Margaret Norman teaches attendees how to control their environments with the Resource Manager:



Tanel Põder tells us how Oracle SQL Plan Execution really works in this session.



Amit Poddar's presentation was entitled "One-Pass Distinct Sampling".



Toon Koppelaars presents "The Helsinki Declaration, Part I" before lunch:



Mark Bobak and Jan van Mourik have their picture taken with Flat Larry during break:



He's ba-a-a-ack! Toon Koppelaars gives Part II of "The Helsinki Declaration" after lunch:



Below, Dave Abercrombie gives us a captivating tour of the AWR tables:



I'm sure that Bryn Llewellyn gave a very interesting talk on "Using the PL/SQL Hierarchical Performance Profiler". Such a character, he is...



Mark Williams taught us that "Instrumented Code is Better Code":



Oh! And Kerry Osborne and Stephan Haisley pulled the old switch-a-roo on the attendees just to keep them guessing. Stephan spoke about "Streams Optimization and Diagnostics".



Christian Antognini presented "Bloom Filters":



Hotsos' own, Monty Orme, facilitated the speaker panel after the last session:



And finally...the speaker panel:



What a great day it was! Stay tuned for Day 3...

Hotsos Symposium 2009 Day 1

Welcome back to Hotsos Symposium! Gary Goodman opens the proceedings:



Andy Zitelli gives a warm welcome to our keynote speaker, Chris Date:



Chris Date delivers the keynote and a bonus two technical sessions:



Wolfgang Breitling was gracious to fill-in for Joze Senegacnik. Wolfgang's presentation was entitled, Tuning by Cardinality Feedback. Thank you, Wolfgang!



Oh, no! It's Cary Millsap. Cary's presentation is called "Making Friends with the Oracle Database".



Andy Zitelli gives a great presentation entitled "Using Oracle's Extended SQL Trace Data to Quell Disputes".



Joe Johnson gave an awesome presentation (of course, we'd think so, any way!) called "Using Hotsos Tuning Techniques to Find & Fix Performance Issues: 6 Real World Examples"...and no, Joe doesn't work for Hotsos. Yet...



Karen Morton worked her magic with her presentation entitled "Managing Statistics for Optimal Performance". Karen is shown here with Flat Larry. Karen's niece sent us a Flat Stanley, and we took pictures of him in all kinds of places. When Larry Klein couldn't make it to the Sym due to a go-live, we decided to make a Flat Larry.



Gerwin Hendriksen presented "Practical Use of Method GAPP to Find Performance Bottlenecks for Business Processes in Complex Architectures".



Henry Poras gave Part 2 of his presentation from last year, entitled "Queueing Theory Analysis of Statspack Reports Obtained from Database Load Tests".





Tanel Põder presented his extra session entitled "Latch, Lock and Mutex Contention Troubleshooting in Oracle".



Monday was a very good day!

The place to be this March

Have you signed up yet? You know you need to be there so sign up now.

Undocumented Feature 11g BULK In-BIND

Really this is a FEATURE, not a bug. Prior to 11g if you tried to refer to a field of a bulk bind in a FORALL statement you would get an error:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND

In 11g this works just fine. Consider this simple example:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DECLARE
2 TYPE little_rt IS RECORD (
3 empno emp.empno%TYPE,
4 sal emp.sal%TYPE );
5 TYPE empno_sal_list_tt IS TABLE OF little_rt INDEX BY PLS_INTEGER;
6 the_list empno_sal_list_tt;
7 BEGIN
8 SELECT empno, sal BULK COLLECT INTO the_list FROM emp;
9 FOR idx IN 1 .. the_list.COUNT LOOP
10 the_list (idx).sal := (the_list (idx).sal) * .15;
11 END LOOP;
12 FORALL idx IN 1 .. the_list.COUNT
13 UPDATE emp SET sal = the_list (idx).sal
14 WHERE empno = the_list (idx).empno;
15 dbms_output.put_line('Records updated: '||to_char(the_list.COUNT));
16 END;
17 /
Records updated: 14

PL/SQL procedure successfully completed.


Not sure why this is undocumented. Likely is just slipped thru the cracks. Maybe not as cool and market flashy as the Exadata is, but a nifty feature none the less.

A bug perhaps

OK in talking with Karen Morton and Jonathan Lewis it looked like a bug with the trace mechanism might be the root of the problem with the stat lines for a sort and not some new fancy way Oracle is doing the sorts. And I think that is true. Here is a look at getting the stats directly from v$sql_plan_statistics_all for my test SQL:

Select * from emp, dept where emp.deptno = dept.deptno
/










Now all the times look very nice. So I'm going with the BUG theory for now. (EDIT1 - Sorry I thought I had a way to keep the spacing but NO!) (EDIT 2 - Well let's try this little screen shot, a little small, but the numbers are easier to read.)

SORTs, TIMEs and 11g

A while back I noticed that the TIME values in the STAT lines for an 11g Database weren't adding up right if there was a sort operation in the plan. I talked with several folks and everyone agreed that this was going on but no one seemed to know why. I've just been working with Oracle support and I think I know what is happening.

This was an important issue to me for several reason not the least of which is I'm working on a paper for the upcoming UKOUG meeting about SORT MERGE joins, so knowing where the time was going was critical. Also this really makes the math look a bit odd when a SORT operation appears in a plan you are trying to optimize.

So here is what things would look like in 10, I've put the STAT line times and done the math to show how much time each step took for it self:

Plan and stats in 10g for a sort merge join (I know this is hard to read when all the spacing is mashed, I don't know how to retain the spacing in HTML):

Select /*+ use_merge(e,d) */ * from emp e, dept d
where e.deptno = d.deptno
/

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
STAT Time=821 self=201 (821-183-437)
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT |
STAT Time=183 self=102 (183-81)
| 3 | INDEX FULL SCAN | DEPT_DEPTNO_PK |
STAT Time=81 self=81
|* 4 | SORT JOIN | |
STAT Time=437 self=235 (437-202)
| 5 | TABLE ACCESS BY INDEX ROWID| EMP |
STAT Time=202 self=114 (202-88)
|* 6 | INDEX FULL SCAN | EMP_DEPT_IDX |
STAT Time=88 self=88
---------------------------------------------------------

Total of all selfs = 821us Top Line in STAT = 821us

OK that is all well and good. Now here is what that same plan looks like in 11g, notice in 11 I don't need a hint to get a SORT MERGE:

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
Stat Line=135us SELF=2us (135-35-24-74)
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT |
Stat Line=35us SELF=13us
| 3 | INDEX FULL SCAN | DEPT_DEPTNO_PK |
Stat Line=22us SELF=22us
|* 4 | SORT JOIN | |
Stat Line=24us SELF=24us
| 5 | TABLE ACCESS BY INDEX ROWID| EMP |
Stat Line=74us SELF=49us (74-25)
|* 6 | INDEX FULL SCAN | EMP_DEPT_IDX |
Stat Line=25us SELF=25us
--------------------------------------------------------

Total of all selfs = 135us Top Line in STAT = 135us

Notice that the SORT JOIN (#4) step shows a TIME of 24us, now in the past that time should have included it's children, which is #5 and #6, that would be impossible since 74us is much more then 24us.

What appears to be happening is that the SORT is now done "while" the child steps are accruing. I think the best way to describe this new behavior is that sorts now happen simultaneously with the child steps. I don't believe that this is a parallel action, it is an action that happens simultaneously. There is a subtle but distinct difference.

Well if you want to here more about this, you could come to my talk at the UKOUG meeting in December, or drop me a line and I'd be glad to chat about this more.