SEO

#1 (**permalink**) 04-08-2008, 06:19 PM

What is the Sybase equivalent of Oracle's Explain Plan?

I need to be able to determine what tables a query is dependent upon.
In oracle I can use Explain Plan to do that. How would I do it in
Sybase?

Thanks in advance for helping a n00b.

#2 (**permalink**) 04-08-2008, 06:19 PM

Log into the dataserver and:

1 - to get a high level view of the query plan being used by the dataserver:

set showplan on
go
<run query>
go

2 - to get the nitty gritty details on how the optimizer is costing a
query, as well as how it's determining the join order:

dbcc traceon(3604,302,310)
go
<run query>
go

NOTE: As a n00b this output will be way over your head ... trust me! ;-)

3 - I also find it helpful a lot of times to know a) how many times an
inner table is scanned, the number of logical ios (cache hits) and the
number of physical io's (for pages not in cache):

set statistics io on
go
<run query>
go

-------------------

You can run all, some or none of the above 3 options prior to running your
query.

To disable the options ... replace 'on' with 'off'.

badbrownie@gmail.com wrote:

> What is the Sybase equivalent of Oracle's Explain Plan?
>
> I need to be able to determine what tables a query is dependent upon.
> In oracle I can use Explain Plan to do that. How would I do it in
> Sybase?
>
> Thanks in advance for helping a n00b.
>

#3 (**permalink**) 04-08-2008, 06:19 PM

Thanks Mark. That was Exactly the information I was hoping to gain
from this group.

#4 (**permalink**) 04-08-2008, 06:19 PM

By the way is it possible to use 'show plan' programmatically? I
understand how it can be used from the console but I need to use it
from within java (and parse its results programmatically too of course)

#5 (**permalink**) 04-08-2008, 06:19 PM

For anyone else who's interested in the answer to that question, it
appears to be here...

http://groups.google.com/group/sybas...d20bde6d550146

#6 (**permalink**) 04-08-2008, 06:19 PM

I haven't looked at the link you posted but, generally speaking, yes you
can implement these commands programmatically.

Obviously (?):

1 - you would want to be able to configure this on-the-fly; there's a
performance hit for generating/sending all that text to the client, so only
use it when trying to track down a performance issue

2 - the client has to be (re)configured/(re)written to handle the results
of these commands

badbrownie@gmail.com wrote:

> By the way is it possible to use 'show plan' programmatically? I
> understand how it can be used from the console but I need to use it
> from within java (and parse its results programmatically too of course)
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode