Striving for Optimal Performance

Oracle Database, as of 10g Release 2, uses bloom filters in various situations. Unfortunately, no information about their usage is available in Oracle documentation. For this reason, I decided to write a paper to explain not only what bloom filters are, but also, and foremost, to describe how Oracle Database makes use of them. Specifically, to explain how the database engine uses bloom filters to reduce data communication between slave processes in parallel joins and to implement join-filter pruning.

Originally, I wrote this paper for the IOUG Select Journal. Even if I wrote it last June, I wanted to receive the printed copies before putting it online. Today a packet with five copies of the Q4/2008 issue and a polo shirt arrived… Hence, it is now available online as well.

If you don’t have access to Select Journal, you can download it from this page.

I already pointed out in a previous post that Tanel Poder will be speaking in Vienna, Düsseldorf, Munich and Zürich next November. The news is that an additional presentation (followed by an aperitif) will take place at the same locations and dates as Tanel’s seminar. At 5pm on the first day, I will give a short presentation on the key methods and techniques covered in my book . Then, from 6pm onwards, participants are invited to continue the discussion with presenters and other participants as we meet over drinks.

It is important to emphasize two things about this additional event:

• It is open to everyone, not just those already attending Tanel’s seminar.
• It is free of charge. If you want to come, you can register by sending an email to training@trivadis.com . (The registration is only necessary to ensure that there is enough space and drinks for everyone.)

So, if you are in one of the following cities at the specified dates, feel free to join us:

• Vienna, November 3, 2008
• Düsseldorf, November 6, 2008
• Munich, November 10, 2008
• Zürich, November 13, 2008

More information about my presentation and Tanel’s seminar is available in the flyer.

During some tests with patchset 11.1.0.7 (which is presently available for Linux 32-bit and 64-bit only) I noticed something new in the SQL trace files.

Let’s see what is the difference if I run the following statements with and without the patchset:

execute dbms_monitor.session_trace_enable(waits=>FALSE)
SELECT 'Hello world!' FROM dual;
execute dbms_monitor.session_trace_disable

The relevant part of the trace file is the following:

• 11.1.0.6.0

PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=1222018499653282 hv=1643260990 ad='6e1e6ac8' sqlid='7a45d99hz4b1y'
SELECT 'Hello world!' FROM dual
END OF STMT
PARSE #1:c=1000,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1222018499653279
EXEC #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1222018499653403
FETCH #1:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1222018499653472
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1222018499653788

• 11.1.0.7.0

PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=1222018585374765 hv=1643260990 ad='6f5af5e0' sqlid='7a45d99hz4b1y'
SELECT 'Hello world!' FROM dual
END OF STMT
PARSE #1:c=999,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1222018585374765
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1222018585374765
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1222018585374765
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #1:c=1000,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=1222018585396826
CLOSE #1:c=0,e=0,dep=0,type=0,tim=1222018585396826

You spotted the difference?

Yes! There are two fetches. But, what I wanted to point out with this post, is the new line beginning with CLOSE.

Over the coming months, I’ll be speaking at the following events.

The abstracts of the presentations are the following (at the events in DACH I’ll give the presentations in German; hence the first abstract is in German…):

• Query Optimizer 11g: Mit jedem neuen Release wird der Query Optimizer verbessert. 11g ist da keine Ausnahme. Diese Präsentation zeigt Ihnen die zentralen Verbesserungen in den folgenden Bereichen: Object Statistics, Optimization Techniques, SQL Plan Baselines und Bind Variable Peeking.
  Jede Verbesserung wird anhand einer Demo live gezeigt und gibt Aufschluss wie das Feature in der Praxis zum Einsatz kommt und welchen Applikationsvorteil es bringt.
• Bloom Filters: A bloom filter is a data structure used to support membership queries. Simply put, a bloom filter is used to test whether an element is a member of a given set or not. Since Oracle Database 10g Release 2 bloom filters are used in various situations. Unfortunately, no information about their usage is available in Oracle documentation. The aim of this presentation is to explain not only what bloom filters are, but also, and foremost, to describe how the database engine makes use of them. Specifically, it explains how the database engine uses bloom filters to reduce data communication between slave processes in parallel joins and to implement join-filter pruning.
• Execution Plans: An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency. The aim of this session is to describe in detail how you should perform these three actions.

Few days ago, in the post entitled Long Parses, I pointed out that stored outlines can be used to shorten the parse time by restricting the number of execution plans considered by the query optimizer.

As of Oracle Database 11g, stored outlines are deprecated in favor of SQL plan baselines. Therefore, someone might think that SQL plan baselines can also be used to workaround long parses.

Actually, this is not the case :-(

The figure on the right (which is taken from page 292 of TOP) shows why…

Do you spot the problem?

The essential thing to notice is that the query optimizer generates execution plans before considering SQL plan baselines. And, therefore, a long parse would be performed even if a matching SQL plan baseline exists.

Also note that SQL plan baselines are only available in Enterprise Edition. Therefore, when stored outlines will be desupported, in Standard Edition a similar feature will no longer be available. Or do you thing that Oracle will change the licensing policy?