Archive | SQL plans RSS feed for this section

SQL efficiency

11 Jun

Bad plan or something else?

The first step in any tuning activity is determining the scope of an issue — so if a query is not performing satisfactory, then the first question is whether it’s query’s execution plan or something else (e.g. a global database issue or even a problem external to the database). Full diagnostics may be time-consuming or even inaccessible, so it’s desirable to resolve this question by just looking at the query and its basic statistics. There is a method of doing that using SQL efficiency, and in this post I’m going to describe it.

The method is not my invention. I read about it in Christian Antognini’s book “Troubleshooting Oracle Performance”, but in fact I was using it in a slightly different form long before. Continue reading →

Tags: , , ,

DBMS_XPLAN.DISPLAY_CURSOR

14 May

In this post, I continue on the topic of examining SQL plans. I will talk about one DBMS_XPLAN function, DISPLAY_CURSOR (because it’s probably the most useful one when troubleshooting ongoing performance issues, and also because other functions have a lot of similiarity to it). I will discuss frequently used options and some common problems.

Preparation

As already mentioned in my previous post on the subject, using DBMS_XPLAN to display rowsource stats requires a bit of preparation. Namely, one needs to either set STATISTICS_LEVEL parameter to ALL (can be done on the session level), or use gather_plan_statistics hint in the query, and then run the query.

Usage DBMS_XPLAN.display_cursor

Once the statement is executed, the plan with row source statistics can be obtained in a convenient format using DBMS_XPLAN.display_cursor.

DBMS_XPLAN.display_cursor takes three parameters, all of which are optional:

Continue reading →

Tags: , ,

Reading SQL plans

17 Apr

SQL tuning is the key to database performance tuning, and yet this seems to be a “blind spot” for many database specialists. I think it’s probably because it falls right on the boundary between DBA’s and developer’s responsibilities: so while a DBA expects database developers take care of performance while developing code, developers often neglect that, relying on a “develop first, let the DBA tune it later” approach. Also, until recently, there were surprisingly few good (and accessible to a newbie) descriptions of how to read a SQL plan.

Now that we have Chritian Antognini’s great book “Troubleshooting Oracle Performance”, the situation has improved dramatically. But still, I think that a blog post on that subject won’t hurt: after all, it’s free and it’s written by someone who still remembers difficulty his first analyzing SQL plans. :)

Continue reading →

Tags: , ,

Why EXPLAIN PLAN is useless

12 Apr

It is really amazing how many people in this day and age are still relying on good old EXPLAIN PLAN when having access to much more precise diagnostic tools.  There are probably several reasons for this:

1) many people haven’t heard of dbms_xplan or don’t have a very good idea of what it can do

2) tracing is often pictured as some utterly complex activity producing results “unreadable”, if not undecipherable, results

3) finally, many people have unrealistically high expectations of EXPLAIN PLAN.

Continue reading →

Follow

Get every new post delivered to your Inbox.

Join 72 other followers