Showplan in a Procedure
Michael Loftus
cutting/pasting sql from "bad" procedures into an isql session with
showplan turned-on, then running the sql. This is too slow a process to be
very useful. Monitor Server identified a handfull of procs that have AVG
CONNECT TIME's of .8+ and very high but unexplained Logical Read rates. It
is killing us.
When I try to embed "set showplan on" and "set noexec on" inside a proc
itself it only seems to work for very small procs. I need to diagnose some
very large procs, up to 56 printed-pages in length (I did not write them!).
Can anyone share a good way to do this?
Thanks.
Michael Loftus
Bret Halford
[This is an undocumented command that is, as far as I know, only
mentioned in the whitepaper "Analyzing and Resolving Optimizer
Problems", docid 2602 at http://techinfo.sybase.com. Highly
recommended reading].
--
Bret Halford
Sybase Technical Support
3665 Discovery Drive
Boulder, CO 80303
Michael Peppler
>
> try "set fmtonly on" in place of "set noexec on"
>
> [This is an undocumented command that is, as far as I know, only
> mentioned in the whitepaper "Analyzing and Resolving Optimizer
> Problems", docid 2602 at http://techinfo.sybase.com. Highly
> recommended reading].
<bookmarks the document for future reading...>
Thanks for that reference, Brett!
Michael
--
Michael Peppler -||- Data Migrations Inc.
mpep...@mbay.net -||- http://www.mbay.net/~mpeppler
Int. Sybase User Group -||- http://www.isug.com
Sybase on Linux mailing list: ase-lin...@isug.com
Anthony Mandic
Setting noexec on isn't the right thing to do. I think John McV.
already pointed this out. But basically, you don't need to go thru
everything to work out what the problem is. Generally, a small
sproc that demonstrates the symptoms is ideal. How you could
do it simply is just use a small script that sets your options
on and then calls the target sproc. Pump this into isql and save
the output into another file. You can then run this as many
times as you want while tuning. Of course, there are tools
available. I think CAST Workbench is one.
-am
Anthony Mandic
>
> Bret Halford wrote:
> >
> > try "set fmtonly on" in place of "set noexec on"
> >
> > [This is an undocumented command that is, as far as I know, only
> > mentioned in the whitepaper "Analyzing and Resolving Optimizer
> > Problems", docid 2602 at http://techinfo.sybase.com. Highly
> > recommended reading].
>
> <bookmarks the document for future reading...>
>
> Thanks for that reference, Brett!
When I was at my last employer, Spin baby (now he was a real
perl guru - at least he understood the scoping rules) and I
developed a perl program (called sqlstats.pl) that you could
use to invoke sprocs. It would throw away the results and
just display the stats and showplans (if set). It used the
GNU readline routine (something that Jeff W. reckoned he
know all about - well he earbashed me about it for at least
a week!) to allow you to scroll thru your command history
and rerun commands. It proved to be very useful if somewhat
limited.
Anyway, the reason I'm saying all this Michael, is that you
could easily whip the same thing up and put it on your web
page - if you're interested. However, I'm thinking now that
a GUI tool might better. This would allow for scrolling thru
long showplans and stats.
-am
Randy Reiter
engineer 1meg+ of showplan and I/O statistics in a few seconds.
Performs SQL code coverage analysis, graphs call trees with I/O statistics,
pinpoints inefficient SQL statements and much more.
Only $1,200 (NOT the $15,000 that other vendors charge) and includes a
desktop Sql Power SniFFFer.
Randy Reiter
Michael Loftus wrote in message ...
>I have been trying to track-down poor query plans in procedures by
>cutting/pasting sql from "bad" procedures into an isql session with
>showplan turned-on, then running the sql. This is too slow a process to be
>very useful. Monitor Server identified a handfull of procs that have AVG
>CONNECT TIME's of .8+ and very high but unexplained Logical Read rates. It
>is killing us.
>
>When I try to embed "set showplan on" and "set noexec on" inside a proc
>itself it only seems to work for very small procs. I need to diagnose some
>very large procs, up to 56 printed-pages in length (I did not write them!).
>Can anyone share a good way to do this?
>
>Thanks.
>
>
>Michael Loftus
>
>
Michael Peppler
Indeed.
Maybe some form of webified display?
Actually - a CGI script that did this (it could even present the existing
procs in a list and suggest parameters :-) could be pretty cool. The issue
there is how to allow/deny users (even on your private net) to access the
server to run procs with showplan/statistics set on.
Although I guess that could be managed with an .htaccess file.
Anthony Mandic
> Maybe some form of webified display?
Sounds even better!
> Actually - a CGI script that did this (it could even present the existing
> procs in a list and suggest parameters :-) could be pretty cool. The issue
> there is how to allow/deny users (even on your private net) to access the
> server to run procs with showplan/statistics set on.
Prompting for a password perhaps?
> Although I guess that could be managed with an .htaccess file.
-am