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)
2) timings
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.
Yep, that was nice :) Oracle has made an improvement and will continue working on it.
Hi Ranjit,
thanks for your comment. You’re right, and that’s exactly the point I was trying to make — tools get better, so we should keep up with the times and use modern tools!
Best regards,
Nikolay
Hello Nik,
This really very informative, Kindly send me complete usage and reading tips of dbms_xplan..
Thanks
Aman
Hi Aman,
thank you very much for your comment. I am thinking of making another post on dbms_xplan options, interpreting its output, and various pitfalls and gotchas one may encounter during that. Hope that post would be interesting to you. BTW my blog now has “subscribe for email updates” option which you may find useful to receive notifications when new posts arrive. Also, feel free to suggest any topics for new posts (you can leave a comment with your suggestion in “ask me a question” page).
Hope to see you again soon!
Best regards,
Nikolay
You have noted very interesting details! ps decent web site.
Thank you very much!
Well, the dbms_xplan.display_cursor with allstatslast gives plenty of information about the query but I have seen it comes with a cost.
A cost of high elapsed times. I ran one sql statement with parallel hint 8 and gather_plan_statistics hint.Normally it use to take 3 minutes to complete but with that additional hint took 10mins to complete. It would be interesting to trace the query and see where the extra 7 mins go?
Hi Yasir,
thanks for your comment. I agree that there may be a performance price to pay for obtaining diagnostic information, but I think that’s true regardless to what diagnostic tool we chose.
However, I must say I have never seen that STATISTICS_LEVEL=ALL had such a big effect. It would be interesting to run your query a few times varying _rowsource_statistics_sampfreq parameter to see if changing rowsource sampling frequency would make a difference.
Sir..Iam newbie to tuning and am doing sample application using scott schema
SQL> exec dbms_stats.gather_table_stats(user, 'emp', method_opt => 'for all colu
mns size 1');
PL/SQL procedure successfully completed.
SQL> select /*+ gather_plan_statistics */ e.empno,e.ename,e.sal,e.deptno,d.deptn
o,d.dname,d.loc
2 from emp e, dept d
3 where e.deptno=d.deptno;
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
----- --------------- ----- ------ ------ -------------- -------------
7369 SMITH 800 20 20 RESEARCH DALLAS
7499 ALLEN 1600 30 30 SALES CHICAGO
7521 WARD 1250 30 30 SALES CHICAGO
7566 JONES 2975 20 20 RESEARCH DALLAS
7654 MARTIN 1250 30 30 SALES CHICAGO
7698 BLAKE 2850 30 30 SALES CHICAGO
7782 CLARK 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 20 RESEARCH DALLAS
7839 KING 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 30 SALES CHICAGO
7876 ADAMS 1100 20 20 RESEARCH DALLAS
7900 JAMES 950 30 30 SALES CHICAGO
7902 FORD 3000 20 20 RESEARCH DALLAS
7934 MILLER 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
SQL_ID 5rtg4hdm9vpan, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.empno,e.ename,e.sal,e.deptno,d.deptno,d.d
name,d.loc from emp
e,
dept d where e.deptno=d.deptno
Plan hash value: 615168685
--------------------------------------------------------------------------------
--------------------
------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers | OMem | 1Mem
| Used-Mem |
--------------------------------------------------------------------------------
--------------------
------------
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 |
15 | 825K| 825K
| 714K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 |
7 | |
| |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 |
8 | |
| |
--------------------------------------------------------------------------------
--------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
24 rows selected.
SQL>
Here E-rows and A-rows are same..and elapsing time also not even a second.So i can consider this is a fine tuned query..
Sir..i have one more doubt..what are things i need to check when tuning with cardinality feedback..how can i know where is the bottleneck in the query..Kindly reply me
Hi Srinvas,
thanks for your question! Let me answer it in the “Ask me” page (it feels more appropriate for larger questions like yours).
Thank you Sir..been wait for your reply and also mainly i want to know..how to find out bottlenecks in the plan output..Many thanks