Kerry Osborne's Oracle Blog

Well apparently you can teach an old dog new tricks. I recently sent the following email to Enkitec’s internal techie email list:

Begin forwarded message:

Have you ever tried to decipher a really ugly, unformatted SQL statement that someone gave you or you pulled out of one the database views or an AWR report? Tim Fox turned me on to the SQL formatting capability of SQL Developer a few weeks ago (actually Brian Hill found it first and showed Tim). It is awesome! It’s a little unintuitive to use though. Here’s how I use it.

1. start up the SQL Developer (there are versions for Mac OS and Windows by the way)
2. Click File->New and choose the SQL File option
– this opens a Query Builder Pane (you don’t need to connect to a database)
3. Pick any random file to open
4. Paste your nasty 10 page long query into the Query Builder pane replacing the text from your random file
5. Right click on the text in the window (brings up a long menu)
6. Click the Format menu Item (it’s at the bottom of the menu in version 3.0)

Viola – nicely formatted SQL text

The latest version of SQL Developer (3.0.x) is actually much better than the previous version by the way.

I haven’t closed SQL Developer since I found out about this capability!

SQL Developer is a free tool that can be downloaded from Oracle’s web site here:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Note version 3.1 is now available but I haven’t tried it yet.

Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).

The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:

This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.

But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:

1. run a statement that uses an index and check the plan
2. create a Baseline on the statement using the index (using my create_baseline.sql script)
3. check the hints stored with the baseline (using my baselines_hints.sql script)
4. run the statement again and check the real plan to see that the Baseline was used
5. use the display_sql_plan_baseline function to show the Baseline plan
6. make the index invisible (thus rendering the Baseline plan non-reproducible)
7. execute the statement again and check the real plan
8. use the display_sql_plan_baseline function to show the Baseline plan

So here’s the test:

Continue reading ‘Displaying SQL Baseline Plans’ »

It’s almost time for Hotsos’s 10th annual Symposium. This year’s conference will be held March 4 – 8 in Irving, Texas. The Hotsos Symposium is probably the best performance oriented Oracle conferences in the world. I am happy and humbled to be speaking at it again this year as the lineup of speakers is once again world class. It’s great to have several other Enkitec’ies on the bill as well. Both Karen Morton and Tanel Poder will be presenting too. Enkitec is also a sponsor of the event this year, so we’ll be hosting a couple of Exadata focused happy hours as well. There’s still time to register. Hope to see you there.

I’ll be speaking at a couple of conferences over the next month or so. Here’s the schedule:

February 16th, in Denver at RMOUG Training Days 2012 – Topic: DIY Exadata
February 23rd, in Redwood Shores at NoCOUG Winter Conference – Topic: DIY Exadata
March 7th, in Dallas at Hotsos Symposium 2012 – Topic: Exadata Optimization – Case Studies

Hope to see you at one of these events.

Chris Date is one of the founding fathers of relational databases. Having worked with Ted Codd at IBM during the time when relational databases were being defined gives Chris a perspective that most of us just don’t have. I’ve had the good fortune to hear him speak in the past (at the Hotsos Symposium) and thought I would do a quick post to highlight the fact that he is scheduled to speak in Dallas the week of Jan 30. Method-R is hosting the event in the Enkitec training facilities in Dallas. So maybe I’ll get to hang around with Chris and Cary that week – that would be cool! Anyway, there are actually 2 classes:

SQL and Relational Theory: How to Write Accurate SQL Code
Normal Forms and All That Jazz: a Database Professional’s Guide to Database Design Theory

And here’s a link to the registration page:   C. J. Date Seminar Registration

By the way, I think every developer and every database architect should have a clear understanding of how the SQL language is designed to work and how relational databases were intended to be laid out. Chris obviously has a unique insight into those topics. One of the tenants of Chris’s teaching is that SQL is a complicated language and since comprehensive testing is almost never really feasible, it is important to write SQL using a disciplined approach based on the underlying relational theory. As a side note, I was talking to a few cohorts around the coffee pot today and was shocked to hear that one of the guys had a CS degree but was not required to take a relational theory class. Back when I got started that was the first class that people took, probably because there were almost no real implementations of the theory at that point. Oracle was just getting started and DB2 was still a distant gleam in Mr. Codd’s eye. But I digress.

It does seem to me that we have an awful lot of systems running on Oracle these days that were designed and written by people without a strong background in relational database fundamentals. I can’t even begin to count the number of times I’ve worked on systems that performed poorly due to poor SQL coding techniques and/or poor database design. Chris’s courses are designed to help you avoid these issues. So this is your chance to learn how to know for sure that your SQL is correct.

Hope to you see you there!