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.
Indeed, very often in OTN forums or at work or elsewhere, one is handed a 60-operation EXPLAIN PLAN of some lengthy query on a database he has never seen in his life, and is expected to read it as though it were some magic crystal ball. Unfortunately, it’s not.
Let’s start with what EXPLAIN PLAN can do. It can show a probable execution plan for a given statement. It can warn you about certain suspicious things in this probable; plan, like full object scans of big objects (tables/indexes or table/index partitions) or cartesian merge joins. If it’s showing a high cost, this can signal that something could be wrong here. That’s basically it.
And while this can be helpful in some cases — for example, if you want to know whether or not a query would pick up a certain index — it’s nearly completely useless for troubleshooting a poorly performing query, especially on an unfamiliar database when no additional information (like dumps from DBA_TAB_STATISTICS, DBA_TAB_COL_STATISTICS, DBA_HISTOGRAMS).
The critical information for tuning a query is:
1) cardinality (number of rows per operation)
3) physical and logical I/O per operation.
None of this is available in EXPLAIN PLAN output.
Better approach: DBMS_XPLAN
Fortunately, it’s quite easy to obtain this with DBMS_XPLAN. Of course, in order to use DBMS_XPLAN one needs a bit more privileges compared to EXPLAIN PLAN, namely:
- select on v$session
- select on v$sql
- select on v$sql_plan
- select on v$sql_plan_statistics_all
(note that v$… are synonyms, not the names of actual views, so when granting the privileges one needs to use actual view names, e.g. grant select on v_$session to <username> etc.). But it is absolutely necessary for a developer to have such privileges at least on a non-production database, so it’s worth to spend a few minutes talking to your DBA or raising the ticket etc. Otherwise not only you won’t be able to do anything about performance of your queries yourself, but won’t be able to seek help from any experts, either.
Also, you need to be on 10g or higher to be able to use the advanced features of DBMS_XPLAN — so if you’re on 9i or below, that’s just too bad.
Using DBMS_XPLAN is really simple:
1) Enable rowsource stats by either ALTER SESSION SET STATISTICS_LEVEL = ALL or by using gather_plan_statistics hint (the latter can provide inaccurate timings, but in some cases you may be unable to do the ALTER SESSION)
2) Run the query
3) Run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>’allstats last’)).
When posting or sending the output, keep in mind that it’s only readable with monospace fonts, so in HTML it should be enclosed in <pre></pre> or <code></code> tags.