Hotsos 2008, Day one

Hotsos 2008, first day of the symposium

Today I had to make some difficult choices: There are too many presentation to see or just not enough

me to go around.

Finally I choose to attend:

• The Keynote by Cary Millsap
• Semantic Query Optimization by Toon Koppelaars
• Leveraging Oracle’s Extended SQL Trace Data to Expedite Software Development by Andy Zitelli
• Advanced Oracle Troubleshooting: No Magic is Needed, Systematic Approach Will Do by Tanel Poder
• Cost-Based Subquery Transformations: Concepts and Analysis Using 10053 Trace by Riyaj Shamsudeen
• Making RMAN Perform by Jared Still

First there was some time to socialize and have some breakfast or coffee.

The keynote contained a few references to Cary’s other presentations so I’m inclined to attend them. He explained the difference between ‘tuning’ and optimizing.

Tuning is a method based on trail and error, and Optimizing is maximizing a systems economic value by taking informed actions of which the results are forecastable. It was too bad that there was a defective projector on my side

of the room, because that spoiled the keynote a bit for me.

The presentation by Toon Koppelaars was very good in my opinion. I liked his demo’s in which he

showed that the Oracle Optimizer did some ‘strange’ things. At least at a first glance. Some explain

plans showed “filter (NULL is NOT NULL)” which ofcourse is allways false so there is no need to go

one step further and retrieve data for the result set.

This happens for example in the query:

explain plan for
select *
from emp
where empno <= 0;

On an emp table with an extra (check) constraint that looks like “check empno > 0”.

In that case the

query is semantically optimized by incorperating the existing constraint in the original query. The

result is offcourse that no rows will be retrieved. And that manifests itself in the explain plan by

the filter (NULL is NOT NULL).

The mathematical/logical ‘keywords’/concepts here are:

• Identity
• Commutativity
• Associativity
• Distributivity
• Absorption
• Transitivity
• De Morgan
• Idempotence
• Involution 

These are apparently explained in the book he has written with Lex de Haan: Applied Mathematics for

Database Professionals since he showed a picture of that book.

He then went on to explain those terms and demonstrate which of those are programmed into the Oracle

Optimizer and which are not. He also told us that there apparently has been no change in the

Optimizer between 10G versus 11G for this. The 11G join elimination was demonstrated and a nice wish list for the future was mentioned.

A question from the audience was if this SQO will also work for queries that use bind variables and Toon said that he hadn’t looked into that yet so couldn’t give an answer right now.

Andy Zitelli showed us that the 10046 traces could also be used for a completely different use than Method R uses them for. He has developed several perl scripts to mine these trace files for different things. Amongst others:

• expose the lack of use of bind variables
• infer erroneous client/mid-tier behaviour from durations between database calls
• identify query logic across simultaneous database sessions which may lead to deadlocks etc.

He calls this the Early Detection Approach (EDA) and uses this on development systems to find issues before they become a real problem. As his perl scripts run quite efficiently they cost him almost no time.

This is something that probably should become standard practice. Especially with all those developers out there that are not required to know how to write SQL statements anymore but do expect the database to perform whatever they throw at it.

That’s just like driving a Ferrari only using first and second gear, and wonder why it won’t go faster than 80 km/hour…

The presentation of Tanel Poder was loaded with live demo’s and information about Advanced Oracle Troubleshooting. He showed us situations in which Oracle is saying the process is using CPU but the OS is telling us that the process is not using CPU. He demonstrated his tool snapper that can make a snapshot of v$ views and show the difference between two snapshots which is usefull (for example) in situations where the Oracle Wait Interface hasn’t gotten an instruction to write something to your 10046 tracefile. Thus that tracefile is not very usefull up to that point. He mentioned that his scripts will become available on his site in a few days.

It is all about knowing or understanding the flow of the server process and knowing how and when to measure it step by step using the tools available. Which he demonstrated in several demo’s.

It is getting a bit late and I do not want to miss anything tomorrow so I’ll stop for now and hope to update this later. BTW: tonight it was snowing

And that is something I didn’t expect!

Patrick