Re: Oracle WTF candidate

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 26 Sep 2012 20:54:17 +0000 (UTC)
Message-ID: <pan.2012.09.26.20.53.20_at_gmail.com>

On Wed, 26 Sep 2012 13:26:22 -0700, dombrooks wrote:

> I don't think that is very fair and I don't get what is the WTF.
>
> Nikolay has some excellent advice on his blog and the quality of his
> replies on the OTN forums are consistently good.

I haven't followed his replies on the OTN forum, I'm not participating in the discussions there so I cannot comment on that.

The main problem with his blog entry is in the title: "A sqlplus script for diagnosing poor SQL plans". It is essentially a script to parse the execution plan of a SQL statement. That's all there is: just the plain, old DBMS_XPLAN, nothing more. No diagnosing poor SQL plans at all, nothing that would help me discern poor plans.

So bombastic of a title with such a trivial content is worthy of WTF, at least in my humble opinion. In addition to that, his script is using ALLSTATS LAST, without even mentioning plan statistics. Without the plan statistics, ALLSTATS LAST will not work:

SQL> set serveroutput off;
SQL> select count(*) from emp;

  COUNT(*)

---

        14

Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

---

SQL_ID g59vz2u4cu404, child number 0

---

select count(*) from emp

Plan hash value: 2937609675

---

| Id | Operation | Name | E-Rows |

---

|   0 | SELECT STATEMENT |	  |	   |
|   1 |  SORT AGGREGATE  |	  |	 1 |
|   2 |   INDEX FULL SCAN| PK_EMP |	14 |
--------------------------------------------

Note

---

• Warning: basic plan statistics not available. These are only collected when :
  • hint 'gather_plan_statistics' is used for the statement or
  • parameter 'statistics_level' is set to 'ALL', at session or system leve l

20 rows selected.

Elapsed: 00:00:00.57

Nothing special there, as you can see. No all important E-rows and Arows,  which is why ALLSTATS LAST was made available.

I believe that so bombastic of a title, followed by such a trivial and incomplete information is worthy of the WTF status. At least, that was my reaction when I finished reading his blog entry. A question for you: what is not fair? What I find as unfair is wasting my time with such a bombastic title and then providing nothing in return. I wouldn't have read a post with a title like "how to see the execution plan of a SQL statement". Incidentally, that would be an appropriate title. This is the price to pay for unjustifiably bombastic titles: the reader will say WTF...
Having said that, I wish him many good posts and replies on the OTN forum and even more good advice on his blog site.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Wed Sep 26 2012 - 15:54:17 CDT