Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Showplan is confusing

5 posts in General Discussion Last posting was on 2010-03-16 15:01:40.0Z
Jim Thomas Posted on 2010-03-03 20:50:39.0Z
Sender: 66f4.4b8ec9a4.1804289383@sybase.com
From: Jim Thomas
Newsgroups: sybase.public.ase.general
Subject: Showplan is confusing
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b8ecb9e.671c.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4b8ecb9e"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Mar 2010 12:50:39 -0800
X-Trace: forums-1-dub 1267649439 10.22.241.41 (3 Mar 2010 12:50:39 -0800)
X-Original-Trace: 3 Mar 2010 12:50:39 -0800, 10.22.241.41
Lines: 2686
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29018
Article PK: 78257

I used showplan six months ago and it was very
straightforward. Today I had a slow query that I thought I
would use showplan on. The output is incomprehensible.

It is a simple query, shown at the top of the attached
document, to merely select the last name from one record in
a table indexed by a unique member id. The showplan output
doesn't even display a FROM statement including the table
name being used! What am I looking at?


"Mark A. Parsons" <iron_horse Posted on 2010-03-03 21:22:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan is confusing
References: <4b8ecb9e.671c.1681692777@sybase.com>
In-Reply-To: <4b8ecb9e.671c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100301-0, 03/01/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8ed323$1@forums-1-dub>
Date: 3 Mar 2010 13:22:43 -0800
X-Trace: forums-1-dub 1267651363 10.22.241.152 (3 Mar 2010 13:22:43 -0800)
X-Original-Trace: 3 Mar 2010 13:22:43 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29019
Article PK: 78256

Well, you (obviously?) didn't capture the query plan for the SELECT, but rather the query plan for some other batch of
SQL (possibly a stored proc invocation).

If you're trying to capture the query plan for a query within the same session/spid, try:

=====================
set showplan on
go
select ....
go
set showplan off
go
=====================

If you're trying to capture the query plan of a query that is *CURRENTLY* running under a different session/spid, try:

=====================
sp_showplan <other_spid>,null,null,null
go
=====================

If you're trying to capture the query plan of any session/spid you can also look at using the monSysPlanText (MDA)
table. monSysPlanText maintains a history of query plans for *recently* executed queries.

Whether or not monSysPlanText captures the desired query plan will depend on how large you size 'plan text pipe max
messages' and how much activity you've had on the dataserver since the query-in-question was run (ie, 'old' query plans
are pushed off the end of the monSysPlanText queue as new plans are generated).

Jim Thomas wrote:
> I used showplan six months ago and it was very
> straightforward. Today I had a slow query that I thought I
> would use showplan on. The output is incomprehensible.
>
> It is a simple query, shown at the top of the attached
> document, to merely select the last name from one record in
> a table indexed by a unique member id. The showplan output
> doesn't even display a FROM statement including the table
> name being used! What am I looking at?


Sherlock, Kevin [TeamSybase] Posted on 2010-03-03 22:37:15.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b8ecb9e.671c.1681692777@sybase.com>
Subject: Re: Showplan is confusing
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b8ee49b$1@forums-1-dub>
Date: 3 Mar 2010 14:37:15 -0800
X-Trace: forums-1-dub 1267655835 10.22.241.152 (3 Mar 2010 14:37:15 -0800)
X-Original-Trace: 3 Mar 2010 14:37:15 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29020
Article PK: 78259

your looking at the showplan for some other session.

Can you tell us how you obtained the showplan output exactly?

<Jim Thomas> wrote in message news:4b8ecb9e.671c.1681692777@sybase.com...
>I used showplan six months ago and it was very
> straightforward. Today I had a slow query that I thought I
> would use showplan on. The output is incomprehensible.
>
> It is a simple query, shown at the top of the attached
> document, to merely select the last name from one record in
> a table indexed by a unique member id. The showplan output
> doesn't even display a FROM statement including the table
> name being used! What am I looking at?
>


Jim Thomas Posted on 2010-03-16 14:19:29.0Z
Sender: 274f.4b9f9136.1804289383@sybase.com
From: Jim Thomas
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan is confusing
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b9f9371.27ba.1681692777@sybase.com>
References: <4b8ee49b$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Mar 2010 06:19:29 -0800
X-Trace: forums-1-dub 1268749169 10.22.241.41 (16 Mar 2010 06:19:29 -0800)
X-Original-Trace: 16 Mar 2010 06:19:29 -0800, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29047
Article PK: 78286

Not sure why my response doesn't appear on your forum. Am
re-entering it here:

The commands I entered were:

set showplan on
go
select m.memb_l_name from member m where m.memb_id =
"1234567"
go
set showplan off
go

Without the showplan commands the select statement correctly
returns one row. When I add those showplan commands the text
which appears in the Messages tab of Interactive SQL is that
which I attached previously. Oddly, the select statement no
longer returns the member row as before?!? (ie the results
tab is non-existent).

> your looking at the showplan for some other session.
>
> Can you tell us how you obtained the showplan output
> exactly?
>
> <Jim Thomas> wrote in message
> news:4b8ecb9e.671c.1681692777@sybase.com... >I used
> > showplan six months ago and it was very straightforward.
> > Today I had a slow query that I thought I would use
> showplan on. The output is incomprehensible. >
> > It is a simple query, shown at the top of the attached
> > document, to merely select the last name from one record
> > in a table indexed by a unique member id. The showplan
> > output doesn't even display a FROM statement including
> > the table name being used! What am I looking at?
> >
>
>