No shortcuts for SQL tuning…

From time to time I get questions like,

“How can I make this SQL statement run faster?”

When I see these questions I’m filled with dread. I don’t want to say nothing, but at the same time there is often little I can do to help because SQL tuning requires some knowledge of what you’re trying to achieve. So this post will serve as my generic answer.

Identify poorly performing SQL statements using Statspack, AWR or ADDM.

Once you’ve identified a problem SQL statement, trace it using SQL Trace to get hold of the execution plan and wait states, which should give you some idea of why it is performing so badly. Use this information to help you decide if you should do one or more of the following:

  • Rewrite the SQL statement.
  • Add suitable indexes to the underlying tables.
  • Force appropriate index usage with optimizer hints. Remember, forcing full scans by avoiding indexes may be appropriate in some situations (suggested by Jeff).
  • Check the statistics used by the optimizer are up to date. Gathering histograms may help. (suggested by Don and Nuno)

During the rewrite process Explain Plan and DBMS_XPLAN will provide you with the adjusted execution plan, without having to rerun the statement.

If you’re using Oracle 10g you might want to consider using the SQL Tuning Advisor or the SQL Access Advisor. I’ve seen limited success with these, but you may find them more useful.

At some point you may realize that no amount of tweaking is going to solve your performance problems and a more radical approach is necessary. In these cases it may be necessary to use materialized views to “pre-fetch” the data for your problem query, or just redesign that section of your application to make it more efficient.

If there are any shortcuts in this process please pass them on, because I’ve not found them yet 🙂

Cheers

Tim…

PS. I’ve made a couple of modifications based on comments 🙂

Assorted rants…

Just a quick point about a post on Pete-s blog, which in turn references a comment on Tom Kyte’s blog. Pete is totally on the money! Here are a few thoughts related to this:

  • You can throw as much hardware as you want at a bad application and it will still be bad. If you design and code applications properly you can run them on less/cheaper hardware and everyone is happy!
  • Vendor neutral = lowest common denominator = crap!
  • Take a good technology and use it badly and you can easily convince yourself and everyone else that the technology is bad.
  • Sometimes, things that sound good in theory are terrible in practice. Did I hear someone mention J2EE? 😉

Over the years I’ve found these to be true time and time again. Remember, it’s just my opinion 🙂

On a different note, my rating super-fan is back again. The DBMS_EPG article once again went to 5 stars for 1 vote, now it’s 3.5 stars for 4 votes. Who is this mystery super-fan?

I recently found out that a couple of friends/acquaintances now read my blog. The majority of my readership for both the blog and the website are based in Asia and the USA. Having some people so close to home seems kinda weird 🙂

Cheers

Tim…

DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database R2

I’ve been playing around with the DBMS_EPG package in Oracle 10g Database Release 2. This allows you to configure Database Access Descriptors (DADs) to run mod_plsql applications directly from the database without using an application server or the HTTP server from the companion CD. Instead it uses the XML DB HTTP server. I’ve written an article on it on my website:

DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database R2

It definitely works, but I’ve tried it on both RHEL4 and Windows and it feels a little dodgy. I think I’ve already found one bug, mentioned in the article, but it might just be me doing something wrong. I’m not sure if it’s ready for a production application, but it’s certainly a nice idea.

Cheers

Tim…

Pipelined table functions and cursor_sharing don’t mix…

So you want to use pipelined table functions and cursor_sharing in 10.1.0. Check this out…

First create this test package:

CREATE OR REPLACE PACKAGE ptf_test AS
TYPE t_ptf_row IS RECORD (
dummy dual.dummy%TYPE
);

TYPE t_ptf_tab IS TABLE OF t_ptf_row;

FUNCTION get_data_1
RETURN t_ptf_tab PIPELINED;

FUNCTION get_data_2 (p_in IN VARCHAR2)
RETURN t_ptf_tab PIPELINED;

END;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY ptf_test AS
FUNCTION get_data_1
RETURN t_ptf_tab PIPELINED
AS
l_row t_ptf_row;
BEGIN
SELECT dummy
INTO l_row
FROM dual;

PIPE ROW(l_row);
RETURN;
END;

FUNCTION get_data_2 (p_in IN VARCHAR2)
RETURN t_ptf_tab PIPELINED
AS
l_row t_ptf_row;
BEGIN
SELECT dummy
INTO l_row
FROM dual;

PIPE ROW(l_row);
RETURN;
END;
END;
/
SHOW ERRORS

Then turn on cursor_sharing and query the pipelined table functions:

SQL> ALTER SESSION SET CURSOR_SHARING=SIMILAR;

Session altered.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_1);

D
-
X

1 row selected.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_2('x'));
FROM TABLE(ptf_test.get_data_2('x'))
*
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item

SQL>

Now turn it off and try again:

SQL> ALTER SESSION SET CURSOR_SHARING=EXACT;

Session altered.

sys@db10g> SELECT *
2  FROM TABLE(ptf_test.get_data_1);

D
-
X

1 row selected.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_2('x'));

D
-
X

1 row selected.

SQL>

So you can’t use cursor_sharing and pipelined table functions with parameters together. It took me a while to figure out the problem, but when I did it wasn’t long before support came back with an unfixed bug relating to it (Bug 4178323).

The solution? Don’t use cursor_sharing 🙂

Cheers

Tim…

Note. Script changed to use ALTER SESSION to prevent anyone leaving their test instance in an dodgy state. Thanks for the heads-up Gary 😉

Weird issue hidden by outlines…

I used a stored outline in a production environment for the first time yesterday. I can’t say I’ve ever seen the need to do this before, but this time it masked a pretty serious problem. Normally I try to let the optimizer do its own thing. I even avoid optimizer hints if I can, but in this case the stored outline was the only solution.

The issue was that an SQL statement generated by a finder method in a J2EE application was acting differently depending on JDBC driver being used to make the database connection. When the connection was made via the thin driver the SQL statement used the correct execution plan and worked real quick. When the OCI driver was used the same SQL statement was using a ridiculous index, causing it to trawl through loads of data, making it take around 4 seconds to complete. In this case the impact was massive as this same statement is executed many times throughout the system.

This begs two questions:

  • Why is it happening?
  • Is there a way to force a good execution plan until we can solve the real issue?

The answer to the first questions is still under investigation, but my answer to the second questions was to use optimizer hints! Nice idea, but it won’t work! Unfortunately the SQL is generated at runtime by the OC4J container so we have little influence over the actual statement produced. Then I thought *stored outlines*. A couple of minutes later I had created an outline for the query and both drivers were giving the same performance!

I knocked up a quick article on Stored Outlines and Plan Stability on my website.

In this case the stored outline saved my bacon by hiding a problem until we can resolve it, but they kinda scare me. When you add a hint to an SQL statement it’s there for the whole world to see. People will only know the outline exists if I tell them about it. It’s going to be really easy for everyone to forget about it, then get confused when another environment doesn’t react in the same way.

I guess I’ll put stored outlines in the same category as triggers. Useful at times, but avoid if possible because sooner or later they are going to screw you up 🙂

My list of things to do now includes:

  • Find out why the same statement acts differently with the two drivers.
  • Try to convince everyone that not every SQL problem we’ve ever had can be fixed using stored outlines.
  • Convince the world that Java is a great language, but J2EE sucks. Runtime generation of SQL is certainly fuel for the fire 🙂

Cheers

Tim…

PS. The latest IOUG SELECT magazine has one of my articles in it. I even made the front page. If you’re bored turn to page 6 and check it out. Next stop Rolling Stone…

Can you answer this question…

I’ve just been asked a question on mass loading of images into the database (Optimizing BLOBs for SQL*Loader) and to be perfectly honest I’m not sure what the best solution is.

I’ve worked on a number of projects where images are stored in the database, but in all cases the images have been loaded on a one-by-one basis as they arrived. Apart from exports and imports between schemas I can’t remember ever doing mass loads of images into the database.

Anyone got any suggestions, preferably based on experience of this?

Cheers

Tim…

Life before these features existed…

Some Oracle features are so useful you quickly get to a point where you can’t remember what life was like before them. Three that spring to mind today are the DBA_SCHEDULER_JOB_RUN_DETAILS view, the DBMS_MONITOR package and the DBMS_METADATA package.

When using the new 10g scheduler, the DBA_SCHEDULER_JOB_RUN_DETAILS view provides a history of previous job runs. It’s cool to query this and see how the run duration of particular jobs varies during the course of the day, or between days, allowing you to do trend analysis over time. It’s not rocket science to store this information in a table yourself, but it’s nice that it happens straight out of the box.

The DBMS_MONITOR package is part of the end-to-end tracing available in 10g. It allows you to initiate tracing for one or more sessions, where the sessions are identified using combinations of criteria including: service, module, action, client_identifier or just the plain old sid. The really cool thing about this is that it doesn’t matter how many sessions match the criteria, or which RAC nodes they are running on, they all get traced. You can then consolidate all the trace files into a single file using the trcsess utility so you can use TKPROF as normal. This is way cool!

The DBMS_METADATA package was introduced in Oracle9i to allow you to extract DDL or XML definitions of database objects. Most tools like PL/SQL Developer and TOAD allow you to do this easily, but from the server it was always a pain. I used to spend ages writing and maintaining scripts to do this, and invariably they didn’t work for some features. This package threw all that out of the window. Today a friend asked me how he could dump out a schema creation script and I was in the middle of explaining about DBMS_METADATA when I remembered he works on 8i. Bummer…

I use these things all the time so the thought of moving back to 8i or 9i fills me with dread!

Someone mentioned recently that Oracle 11(xyz) is scheduled for release in November next year. That sounds a little early, but if it’s true I wonder how long it will be before I’m saying, “I’d rather quit than work with Oracle 10g!” 🙂

Cheers

Tim…

RAC Success Story…

Yesterday I did an new installation of 10g RAC on a 2 node Tru64 cluster. It was totally clean, unlike the 9i to 10g upgrades I’ve done before.

Once the software was installed I had to create a copy of our current production system on it from an RMAN backup done using HP Data Protector. The whole restore and recovery went through without a hitch.

Days like that restore your faith in Oracle, and your backups 🙂

Cheers

Tim…

10g CRS restart after power failure, feature or bug?

In 10g RAC the Cluster Ready Services (CRS) software is installed in it’s own $ORACLE_HOME, for the sake or argument let’s call this $CRS_HOME. In this directory there are a number of subdirectories including:

  • $CRS_HOME/crs/init
  • $CRS_HOME/css/init
  • $CRS_HOME/evm/init

When the CRS daemons are running these directories contain an assortment of files with names like:

  • myserver.mydomain.com.pid
  • .lock-myserver.mydomain.com
  • myserver.mydomain.com.lck

When CRS is shutdown cleanly these files are managed such that CRS will start up again without manual intervention, but when there is a power failure on one or more nodes the files aren’t cleaned up. The affect of this is that the CRS daemons won’t start properly until you manually clean up the mess.

RAC is a high availability solution, but it is crippled by a power failure. Is that a bug or a feature?

Note. I’m talking about the way CRS (10.1.0.3.0) works on Tru64. I’d be interested to know if it’s the same for CRS on other platforms. Also, I believe some changes have happened to the startup and shutdown of CRS in 10.1.0.4.0, but that’s not released for Tru64 yet, and a recent message on a HP forum suggests that Oracle will skip this patch and wait for 10.1.0.5.0 for Tru64.

Fun, fun, fun…

Cheers

Tim…