Monthly archives for September, 2008

Bloom Filters

Sep26
2008 11 Comments Written by Christian Antognini

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.

Posted in 10gR2, 11gR1, Parallel Processing, Partitioning, Query Optimizer

Book Presentation

Sep23
2008 Leave a Comment Written by Christian Antognini

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.

Posted in Speaking, TOP

New Information in SQL Trace Files

Sep21
2008 3 Comments Written by Christian Antognini

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.

Posted in 11gR1, SQL Trace
← Older Entries