Tag: Oracle

In my last two posts I talked a little bit about bitmap indexes, how they work and why they lock. I also mentioned that they should be used for low cardinality but that there is no cut-and-dried answer about what low cardinality is. I’m not starting here to talk about cardinality on bitmap indexes, actually Richard Foote did a much better job on that so if you interested in that check out the following blog posts from him:

• Myth: Bitmap Indexes with high distinct columns (Blow Out)
• Myth: Bitmap Indexes with high distinct columns (Supermassive Black Hole)
• So what is a good cardinality estimate for a bitmap index column?

Last Wednesday I had the chance to visit an Oracle seminar at the Marriot hotel in New York City. I was really happy since it was already a long time ago when I last visited an Oracle event (except the Oracle User Group Meeting but that’s not Oracle itself). There were four tracks to join: Database developer, .NET developer, Java developer and APEX developer. All of them sounded interesting but as you had to bring your own laptop and install a specific image for each track I had to decide for one. Well it wasn’t to hard for me and I took the Database developer track with Hands-On on SQL Developer, SQL Developer Data Modeler, TimesTen and last but not least XML database. I have to say, that I was really excited about the sessions. I use SQL Developer and the Data Modeler part already quite a long time so there wasn’t much new for me but still something to learn (there is always something to learn!) The TimesTen and the XML database part was totally new to me and I was surprised what Oracle can already do with all the other products the own. Funniest thing was that I won a book of SQL Developer 2.1 which is brand-new and still wet from the press! 😉 This was because I was the first who finished all SQL Developer related hands-on – I wasn’t aware that I can win a prize for this, but hey, a book is always good! 🙂

So the best things out of this seminar were:

• SQL Developer can debug PL/SQL (not really new)
• SQL Developer can build, maintain and execute Unit tests for PL/SQL
• SQL Developer can connect to TimesTen since 1.6
• SQL Developer has a new Query Executor which makes executing stuff against different databases much more flexible
• TimesTen is really fast if you use it right (made selfwritten tests on my laptop bringing me 8.6 times more performance)

Well at the end here it’s time for some pics:

And here the prize I won:

How often have you been in a situation where you wanted to do an IF/ELSE in a SQL query. How often have you then written “SELECT … DECODE (expression, value, return, value, return, default return) FROM….” or even worse and selected the values and did then an IF/ELSE in PL/SQL?

The good thing in Oracle is that you can do a really IF/ELSE in the SQL query using the CASE expression. The documentation says following:

CASE expressions let you use IF … THEN … ELSE logic in SQL statements without having to invoke procedures. The syntax is:

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

Ok here first a short example of the “simple_case_expression”:

SQL> SELECT name,
2            CASE salery
3                 WHEN 1000 THEN ‘Low’
4                 WHEN 2000 THEN ‘Medium’
5                 WHEN 3000 THEN ‘Medium’
6                 ELSE ‘High’
7           END
8           FROM employee;

NAME                      CASESA
————————- ——
John                      Low
Andreas               Medium
Harry                    Medium
Mike                      High

So here you can see a simple IF/ELSIF/ELSE based on a column value. But what if you have some more complex logic like comparing two values with and AND. Well also that works in the “searched_case_expression”:

SQL> select name,
2             CASE
3                 WHEN salery = 1000 THEN ‘Low’
4                 WHEN salery = 2000 OR salery = 3000 THEN ‘Medium’
5                 ELSE ‘High’
6             END
7             FROM employee;

NAME                      CASEWH
————————- ——
John                        Low
Andreas                 Medium
Harry                      Medium
Mike                         High

Here you can see that I’m using  an OR clause like in the ELSIF. That also works with bind variables of course!

So this should help you in future when you have a select and you think of DECODE or PL/SQL IF/ELSE!

This here is a link to an Oracle specified Google search: http://www.miracleoy.fi/search.html

It was created by the Finland Miracle group and searches especially for Oracle related sites and block all the untrusted sites from them. Good if you want to find your solution quick!

It’s a long time since I have posted something here, I know. As you might remember from my last post I moved over to New York and well, there where a lot of things to do the last months. Anyway, I’m not writing here to make excuses. Fact is that I have been very busy lately. In my (new) role as “Performance Engineer” in the company I got new tasks beside my Oracle tuning stuff. Actually I spent most of the time in the last few months with tuning our Java application and not Oracle anymore. But I’m happy with that as this gave and still gives me the opportunity to learn more about the art of tuning! But this just as a short clarification why my Oracle related posts are so rare lately.

Anyway, today I had the chance to attend the New York Oracle User Group meeting together with my working colleagues. This was my first user group meeting at all so I was a little bit excited about what will be waiting for me there! As I took this serious I printed off the agenda already a few days back and marked the sessions which seemed to be interesting for me. One great thing I discovered was a session with Tanel Poder who I already know because of his blog (which I have also in my Blogroll of course!). Tanel is one of the few guys who shares his scripts with you. Most of the DBAs hide their secret, magic scripts for themselves to be the number 1 DBA. But Tanel provides his scripts to the world. You can download the whole catalog as a simple zip file from his website and well believe me he has a lot of scripts! Anyway, from the four sessions I have chosen three of them where performance related. Actually all of the sessions that I wanted to attend were performance related but unfortunately the first session about performance on SSD was canceled so I attended Arup Nandas session about upgrading to 11g and how database replay/standby database can help you minimize the downtime. Actually I had such a session already back in February in Vienna at Oracle so there weren’t really new things for me at the presentation. The second session was then the one from Tanel. It was about (his) scripts and tools that (can) make your life easier. Third session was from Dave Anderson about 11g Result-Set Cache. He did some life demos on it what was really cool. It gave you the chance to see result set cache really working and improving your performance at the response time. Fourth and last session was from Dean Richards  – “Advanced SQL tuning”. This was a very, very interesting session as he had some real world issues in the presentation. He also gave an overview on which steps are important for performance tuning (knowing the background, triggering down the issue, etc.) After the sessions there were some vendor raffles. I hoped to win the 32″ TV but of course I did not! Actually I didn’t win anything! ;(

If I have to rate the sessions I attended then I would say that the best presenter was Tanel Poder. He was agile and brought some nice “real world” jokes into the session. There was not a single second in his session where you got bored or did pay more attention to your Blackberry than to him! The best session rated by the content would have been Dean Richards “Advanced SQL tuning”. He showed up important things about how to tune and how important it is to understand the whole story around it!

Well time for pictures: