Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Oracle WTF candidate
101 views
Skip to first unread message
Mladen Gogala
Sep 26, 2012, 12:54:52 PM9/26/12
to
I was browsing through Orana when I encountered the following pearl of
wisdom:
http://savvinov.com/2012/09/24/a-sqlplus-script-for-diagnosing-poor-sql-
plans/
This gentleman calls himself "Oracle Diagnostician" and the title of his
blog article is "A sqlplus script for diagnosing poor SQL plans".
The script is very simple and the essence of it is the following
statement:
select * from table(dbms_xplan.display_cursor(null, null, 'allstats
last'));
That, of course, will parse only the poor SQL plans, not the rich ones.
In addition to that, this statement will not do much if there isn't plan
statistics. Plan statistics is, as we all know, gathered either by using
GATHER_PLAN_STATISTICS hint or by setting the parameter STATISTICS_LEVEL
to ALL, either on the session level or on the system level.
PS:
Now that I am a traveling DB consultant, I am thinking about starting a
blog. Catching pearls of wisdom like this would be fun. I will call
myself an Oracle Whisperer. I am not just a lover, I'm a leader.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
wisdom:
http://savvinov.com/2012/09/24/a-sqlplus-script-for-diagnosing-poor-sql-
plans/
This gentleman calls himself "Oracle Diagnostician" and the title of his
blog article is "A sqlplus script for diagnosing poor SQL plans".
The script is very simple and the essence of it is the following
statement:
select * from table(dbms_xplan.display_cursor(null, null, 'allstats
last'));
That, of course, will parse only the poor SQL plans, not the rich ones.
In addition to that, this statement will not do much if there isn't plan
statistics. Plan statistics is, as we all know, gathered either by using
GATHER_PLAN_STATISTICS hint or by setting the parameter STATISTICS_LEVEL
to ALL, either on the session level or on the system level.
PS:
Now that I am a traveling DB consultant, I am thinking about starting a
blog. Catching pearls of wisdom like this would be fun. I will call
myself an Oracle Whisperer. I am not just a lover, I'm a leader.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Gerard H. Pille
Sep 26, 2012, 2:44:55 PM9/26/12
to
Mladen Gogala wrote:
>
> PS:
> Now that I am a traveling DB consultant, I am thinking about starting a
> blog. Catching pearls of wisdom like this would be fun. I will call
> myself an Oracle Whisperer. I am not just a lover, I'm a leader.
>
Do not forget to pay Goggle handsomely so that your blog comes before Blurreson's.
>
> PS:
> Now that I am a traveling DB consultant, I am thinking about starting a
> blog. Catching pearls of wisdom like this would be fun. I will call
> myself an Oracle Whisperer. I am not just a lover, I'm a leader.
>
Mladen Gogala
Sep 26, 2012, 3:58:01 PM9/26/12
to
On Wed, 26 Sep 2012 20:44:55 +0200, Gerard H. Pille wrote:
> Do not forget to pay Goggle handsomely so that your blog comes before
> Blurreson's.
I don't understand? Why would I pay Google anything? And who's
> Do not forget to pay Goggle handsomely so that your blog comes before
> Blurreson's.
"Blurreson"? If you are talking about Don Burleson, then I doubt that my
blog will compete with his, as I will not be running a purely technical
blog, if at all. If you have any problem with Don Burleson, please take
it up with him and drop me out of the discussion.
I am aware of some ruckus centered on Don but I have collaborated with
him in the past and found him to be quite agreeable and amiable. That is
the reason for never taking sides in the debate. The fact that I haven't
intervened at all in several arguments both here and OTN forums makes me
wonder how did you make a connection between him and me? Are we
inextricably linked until death takes us apart because of his publishing
my book? I'll have to tell that to my wife, she believes that it is her
with who I am linked until death takes us apart, not Don Burleson. Don
has also published many other books, by Arup Nanda and Tim Hall, among
others. Both of them are running blogs. Are they related to Don in the
same way? Are you suggesting that Tim and Arup are paying Google because
of Don? I am not quite sure what do you have in mind, please explain
yourself.
dombrooks
Sep 26, 2012, 4:26:22 PM9/26/12
to
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.
All that he seems to be advising on this blog is that you wrap your slow SQL query in a sql script called slow_query.sql, and he has provided a wrapper of the various sql*plus commands to a) surpress the output of the query and to b) set statistics level to all and you will then get the excellent DBMS_XPLAN.DISPLAY_CURSOR output of the last executed query (hence the NULL sql id etc) with the all important actual execution statistics to compare against the estimates - the cornerstone of the cardinality feedback tuning methodology.
I see nothing WTF about this advice and in fact imagine it will be a good point of reference on the OTN forums for the stream of "my query is slow" threads.
If you think I have missed something, then please highlight the WTF.
Cheers,
Dominic
Nikolay has some excellent advice on his blog and the quality of his replies on the OTN forums are consistently good.
All that he seems to be advising on this blog is that you wrap your slow SQL query in a sql script called slow_query.sql, and he has provided a wrapper of the various sql*plus commands to a) surpress the output of the query and to b) set statistics level to all and you will then get the excellent DBMS_XPLAN.DISPLAY_CURSOR output of the last executed query (hence the NULL sql id etc) with the all important actual execution statistics to compare against the estimates - the cornerstone of the cardinality feedback tuning methodology.
I see nothing WTF about this advice and in fact imagine it will be a good point of reference on the OTN forums for the stream of "my query is slow" threads.
If you think I have missed something, then please highlight the WTF.
Cheers,
Dominic
Mladen Gogala
Sep 26, 2012, 4:54:17 PM9/26/12
to
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
> 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.
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 A-
rows, 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.
dombrooks
Sep 26, 2012, 5:03:49 PM9/26/12
to
Maybe we're reading different articles or maybe different browsers display it differently (or rss readers?) ...
He says:
- Copy your SQL into a file (let’s call it slow_query.sql)
- run the wrapping script as shown below
The wrapping script which does amongst other things:
- alter session set statistics_level = all;
- @slow_query.sql;
He says:
- Copy your SQL into a file (let’s call it slow_query.sql)
- run the wrapping script as shown below
The wrapping script which does amongst other things:
- alter session set statistics_level = all;
- @slow_query.sql;
Mladen Gogala
Sep 26, 2012, 5:07:24 PM9/26/12
to
On Wed, 26 Sep 2012 20:54:17 +0000, Mladen Gogala wrote:
> Without the plan
> statistics, ALLSTATS LAST will not work:
I re-read his blog post and he did mention "row source statistics". There
> Without the plan
> statistics, ALLSTATS LAST will not work:
is also alter session set statistics_level=all in his script but the
discussion of that is far from clear. The title is still too bombastic.
Mladen Gogala
Sep 26, 2012, 5:09:58 PM9/26/12
to
On Wed, 26 Sep 2012 14:03:49 -0700, dombrooks wrote:
> He says:
> - Copy your SQL into a file (let’s call it slow_query.sql)
> - run the wrapping script as shown below
>
> The wrapping script which does amongst other things:
> - alter session set statistics_level = all;
> - @slow_query.sql;
No, I made a mistake, I misread his "row source statistics". It's usually
> He says:
> - Copy your SQL into a file (let’s call it slow_query.sql)
> - run the wrapping script as shown below
>
> The wrapping script which does amongst other things:
> - alter session set statistics_level = all;
> - @slow_query.sql;
called plan statistics, at least that's what the hint is for. The title
is still too bombastic for such a trivial content. Far too bombastic.
Gerard H. Pille
Sep 27, 2012, 12:58:48 PM9/27/12
to
Mladen Gogala
Sep 27, 2012, 3:51:23 PM9/27/12
to
On Thu, 27 Sep 2012 18:58:48 +0200, Gerard H. Pille wrote:
> Once upon a time there were beautifil cars with your initials.
Plonk.
> Once upon a time there were beautifil cars with your initials.
Mladen Gogala
Sep 28, 2012, 10:01:05 AM9/28/12
to
On Wed, 26 Sep 2012 21:09:58 +0000, Mladen Gogala wrote:
> No, I made a mistake, I misread his "row source statistics". It's
> usually called plan statistics, at least that's what the hint is for.
> The title is still too bombastic for such a trivial content. Far too
> bombastic.
The title has changed. All is well in the world again.
> No, I made a mistake, I misread his "row source statistics". It's
> usually called plan statistics, at least that's what the hint is for.
> The title is still too bombastic for such a trivial content. Far too
> bombastic.
Mark D Powell
Sep 28, 2012, 10:05:21 AM9/28/12
to
On Wednesday, September 26, 2012 5:09:58 PM UTC-4, Mladen Gogala wrote:
> On Wed, 26 Sep 2012 14:03:49 -0700, dombrooks wrote: > He says: > - Copy your SQL into a file (let’s call it slow_query.sql) > - run the wrapping script as shown below > > The wrapping script which does amongst other things: > - alter session set statistics_level = all; > - @slow_query.sql; No, I made a mistake, I misread his "row source statistics". It's usually called plan statistics, at least that's what the hint is for. The title is still too bombastic for such a trivial content. Far too bombastic. -- Mladen Gogala The Oracle Whisperer http://mgogala.byethost5.com
Mladen, I think your response to the article has been more boombastic than the title. The newspaper industry is famous for such titles. You have a point in that article titles should match up to the content but 'simple' pieces of code like the subject can be very useful.
> On Wed, 26 Sep 2012 14:03:49 -0700, dombrooks wrote: > He says: > - Copy your SQL into a file (let’s call it slow_query.sql) > - run the wrapping script as shown below > > The wrapping script which does amongst other things: > - alter session set statistics_level = all; > - @slow_query.sql; No, I made a mistake, I misread his "row source statistics". It's usually called plan statistics, at least that's what the hint is for. The title is still too bombastic for such a trivial content. Far too bombastic. -- Mladen Gogala The Oracle Whisperer http://mgogala.byethost5.com
If you are going to blog, remember that you have limited time, limited space, and your readers will have limited time to spend on the article so you have to make compromises.
Good luck with the solo career.
HTH -- Mark D Powell --
Mladen Gogala
Sep 28, 2012, 10:19:27 AM9/28/12
to
On Fri, 28 Sep 2012 07:05:21 -0700, Mark D Powell wrote:
> Mladen, I think your response to the article has been more boombastic
> than the title. The newspaper industry is famous for such titles. You
> have a point in that article titles should match up to the content but
> 'simple' pieces of code like the subject can be very useful.
>
> If you are going to blog, remember that you have limited time, limited
> space, and your readers will have limited time to spend on the article
> so you have to make compromises.
>
> Good luck with the solo career.
>
> HTH -- Mark D Powell --
Thanks for the nice words. I chose the best of both worlds, I am a
> Mladen, I think your response to the article has been more boombastic
> than the title. The newspaper industry is famous for such titles. You
> have a point in that article titles should match up to the content but
> 'simple' pieces of code like the subject can be very useful.
>
> If you are going to blog, remember that you have limited time, limited
> space, and your readers will have limited time to spend on the article
> so you have to make compromises.
>
> Good luck with the solo career.
>
> HTH -- Mark D Powell --
consultant working for a company. My employer is Commvault Systems, a
backup software vendor who has quite a few customers with Oracle. As a
matter of fact, I will spend the month of October in San Francisco, at a
client site. No OOW for me, I'm sorry to say.
joel garry
Sep 28, 2012, 11:45:44 AM9/28/12
to
jg
--
@home.com is bogus.
Mr. Mullet deserves the Most Ironic Aptonym Ever award:
http://www.utsandiego.com/news/2012/sep/20/amish-guilty-of-hate-crimes-in-ohio-hair-attacks/
Mladen Gogala
Sep 28, 2012, 12:51:14 PM9/28/12
to
On Fri, 28 Sep 2012 08:45:44 -0700, joel garry wrote:
> On Battery Street?
No, not on Battery Street. What's on Battery Street, anyway?
> On Battery Street?
No, not on Battery Street. What's on Battery Street, anyway?
joel garry
Sep 28, 2012, 2:20:25 PM9/28/12
to
On Sep 28, 9:51 am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Fri, 28 Sep 2012 08:45:44 -0700, joel garry wrote:
> > On Battery Street?
>
> No, not on Battery Street. What's on Battery Street, anyway?
>
Levi Strauss, Commvault lists them as a customer. It was worth a
> On Fri, 28 Sep 2012 08:45:44 -0700, joel garry wrote:
> > On Battery Street?
>
> No, not on Battery Street. What's on Battery Street, anyway?
>
shot.
You don’t have to be an attendee at Oracle OracleWorld in order to
attend OakTable World. Free beer.
0 new messages