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.

Get execution plan for stored procs (ASE 12.5.2)

7 posts in General Discussion Last posting was on 2007-11-02 12:55:48.0Z
Gareth Davies Posted on 2007-10-31 17:40:29.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
Subject: Get execution plan for stored procs (ASE 12.5.2)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <4728cc1d$1@forums-1-dub>
Date: 31 Oct 2007 10:40:29 -0700
X-Trace: forums-1-dub 1193856029 87.194.125.184 (31 Oct 2007 10:40:29 -0700)
X-Original-Trace: 31 Oct 2007 10:40:29 -0700, 87-194-125-184.bethere.co.uk
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:505
Article PK: 1088190

If I try to run this for a stored proc, I'm only seeing the first step
in the results. The documentation says each step should be presented in
a dropdown list but it isn't.

Any ideas why not?

Thanks

Example output:
SQL:
exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000



QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.


Mike Crocker Posted on 2007-11-01 12:10:23.0Z
From: "Mike Crocker" <crocker@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <4728cc1d$1@forums-1-dub>
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vpn-concord-045.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-045.sybase.com
Message-ID: <4729d03f$1@forums-1-dub>
Date: 1 Nov 2007 05:10:23 -0700
X-Trace: forums-1-dub 1193922623 158.159.8.45 (1 Nov 2007 05:10:23 -0700)
X-Original-Trace: 1 Nov 2007 05:10:23 -0700, vpn-concord-045.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:508
Article PK: 1088193

Gareth,

In order to get all the info in the drop down you need to do the following:
-Make sure you are in the Databae Development perspective
-Open the stored procedure by double clicking on it in the Database Explorer
-Right-Click on the stored procedure in the edit pane and select "Get
Execution Plan". You will be prompted for any input params.

This will open up the SQL Execution Plan view which has the drop down you
are looking for.

Let me know if you have any other questions.

Mike

"Gareth Davies" <gruff@sitemaker.cc> wrote in message
news:4728cc1d$1@forums-1-dub...
> If I try to run this for a stored proc, I'm only seeing the first step in
> the results. The documentation says each step should be presented in a
> dropdown list but it isn't.
>
> Any ideas why not?
>
> Thanks
>
> Example output:
> SQL:
> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.


Gareth Davies Posted on 2007-11-01 16:00:27.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
References: <4728cc1d$1@forums-1-dub> <4729d03f$1@forums-1-dub>
In-Reply-To: <4729d03f$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472a062b$1@forums-1-dub>
Date: 1 Nov 2007 09:00:27 -0700
X-Trace: forums-1-dub 1193936427 87.194.125.184 (1 Nov 2007 09:00:27 -0700)
X-Original-Trace: 1 Nov 2007 09:00:27 -0700, 87-194-125-184.bethere.co.uk
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:511
Article PK: 1088197

Hi Mike

There is no dropdown at all, just the result I pasted below. Would you
like me to send you screenshot?

Mike Crocker wrote:
> Gareth,
>
> In order to get all the info in the drop down you need to do the following:
> -Make sure you are in the Databae Development perspective
> -Open the stored procedure by double clicking on it in the Database Explorer
> -Right-Click on the stored procedure in the edit pane and select "Get
> Execution Plan". You will be prompted for any input params.
>
> This will open up the SQL Execution Plan view which has the drop down you
> are looking for.
>
> Let me know if you have any other questions.
>
> Mike
>
> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
> news:4728cc1d$1@forums-1-dub...
>> If I try to run this for a stored proc, I'm only seeing the first step in
>> the results. The documentation says each step should be presented in a
>> dropdown list but it isn't.
>>
>> Any ideas why not?
>>
>> Thanks
>>
>> Example output:
>> SQL:
>> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>>
>>
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is EXECUTE.
>
>


Mike Crocker Posted on 2007-11-01 18:55:51.0Z
From: "Mike Crocker" <crocker@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <4728cc1d$1@forums-1-dub> <4729d03f$1@forums-1-dub> <472a062b$1@forums-1-dub>
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
Lines: 60
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vpn-concord-045.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-045.sybase.com
Message-ID: <472a2f47$1@forums-1-dub>
Date: 1 Nov 2007 11:55:51 -0700
X-Trace: forums-1-dub 1193946951 158.159.8.45 (1 Nov 2007 11:55:51 -0700)
X-Original-Trace: 1 Nov 2007 11:55:51 -0700, vpn-concord-045.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:514
Article PK: 1088199

Gareth,

As I indicated in the email I sent you. Stored Proecedure level Execution
Plans and Graphic Execution Plans are only supported by ASE 15.

You can highlight selected lines in the stored procedure and get an
execution plan for those lines.

Hope this helps.

mike

"Gareth Davies" <gruff@sitemaker.cc> wrote in message
news:472a062b$1@forums-1-dub...
> Hi Mike
>
> There is no dropdown at all, just the result I pasted below. Would you
> like me to send you screenshot?
>
> Mike Crocker wrote:
>> Gareth,
>>
>> In order to get all the info in the drop down you need to do the
>> following:
>> -Make sure you are in the Databae Development perspective
>> -Open the stored procedure by double clicking on it in the Database
>> Explorer
>> -Right-Click on the stored procedure in the edit pane and select "Get
>> Execution Plan". You will be prompted for any input params.
>>
>> This will open up the SQL Execution Plan view which has the drop down you
>> are looking for.
>>
>> Let me know if you have any other questions.
>>
>> Mike
>>
>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>> news:4728cc1d$1@forums-1-dub...
>>> If I try to run this for a stored proc, I'm only seeing the first step
>>> in the results. The documentation says each step should be presented in
>>> a dropdown list but it isn't.
>>>
>>> Any ideas why not?
>>>
>>> Thanks
>>>
>>> Example output:
>>> SQL:
>>> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>>>
>>>
>>>
>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>
>>>
>>> STEP 1
>>> The type of query is EXECUTE.
>>


Gareth Davies Posted on 2007-11-02 09:38:12.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
References: <4728cc1d$1@forums-1-dub> <4729d03f$1@forums-1-dub> <472a062b$1@forums-1-dub> <472a2f47$1@forums-1-dub>
In-Reply-To: <472a2f47$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472af004@forums-1-dub>
Date: 2 Nov 2007 01:38:12 -0800
X-Trace: forums-1-dub 1193996292 87.194.125.184 (2 Nov 2007 01:38:12 -0800)
X-Original-Trace: 2 Nov 2007 01:38:12 -0800, 87-194-125-184.bethere.co.uk
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:515
Article PK: 1088200

Thanks Mike

I understand not being able to get graphical plans but not being able to
see the text plan seems a bit odd as I can get full text-based query
plans for stored procs in 12.5 using any simple client such as SQL
advantage. Is this just a bug?

Mike Crocker wrote:
> Gareth,
>
> As I indicated in the email I sent you. Stored Proecedure level Execution
> Plans and Graphic Execution Plans are only supported by ASE 15.
>
> You can highlight selected lines in the stored procedure and get an
> execution plan for those lines.
>
> Hope this helps.
>
> mike
>
> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
> news:472a062b$1@forums-1-dub...
>> Hi Mike
>>
>> There is no dropdown at all, just the result I pasted below. Would you
>> like me to send you screenshot?
>>
>> Mike Crocker wrote:
>>> Gareth,
>>>
>>> In order to get all the info in the drop down you need to do the
>>> following:
>>> -Make sure you are in the Databae Development perspective
>>> -Open the stored procedure by double clicking on it in the Database
>>> Explorer
>>> -Right-Click on the stored procedure in the edit pane and select "Get
>>> Execution Plan". You will be prompted for any input params.
>>>
>>> This will open up the SQL Execution Plan view which has the drop down you
>>> are looking for.
>>>
>>> Let me know if you have any other questions.
>>>
>>> Mike
>>>
>>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>>> news:4728cc1d$1@forums-1-dub...
>>>> If I try to run this for a stored proc, I'm only seeing the first step
>>>> in the results. The documentation says each step should be presented in
>>>> a dropdown list but it isn't.
>>>>
>>>> Any ideas why not?
>>>>
>>>> Thanks
>>>>
>>>> Example output:
>>>> SQL:
>>>> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>>>>
>>>>
>>>>
>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>
>>>>
>>>> STEP 1
>>>> The type of query is EXECUTE.
>


Mike Crocker Posted on 2007-11-02 11:12:50.0Z
From: "Mike Crocker" <crocker@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <4728cc1d$1@forums-1-dub> <4729d03f$1@forums-1-dub> <472a062b$1@forums-1-dub> <472a2f47$1@forums-1-dub> <472af004@forums-1-dub>
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
Lines: 79
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vpn-concord-041.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-041.sybase.com
Message-ID: <472b0632$1@forums-1-dub>
Date: 2 Nov 2007 03:12:50 -0800
X-Trace: forums-1-dub 1194001970 158.159.8.41 (2 Nov 2007 03:12:50 -0800)
X-Original-Trace: 2 Nov 2007 03:12:50 -0800, vpn-concord-041.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:516
Article PK: 1088201

Gareth,

No it's not a bug. What you can do for ASE 12.5 is get Execution Plans for
statements within the stored procedure by high lighting them and asking for
an execution plan.

mike

"Gareth Davies" <gruff@sitemaker.cc> wrote in message
news:472af004@forums-1-dub...
> Thanks Mike
>
> I understand not being able to get graphical plans but not being able to
> see the text plan seems a bit odd as I can get full text-based query plans
> for stored procs in 12.5 using any simple client such as SQL advantage.
> Is this just a bug?
>
> Mike Crocker wrote:
>> Gareth,
>>
>> As I indicated in the email I sent you. Stored Proecedure level
>> Execution Plans and Graphic Execution Plans are only supported by ASE 15.
>>
>> You can highlight selected lines in the stored procedure and get an
>> execution plan for those lines.
>>
>> Hope this helps.
>>
>> mike
>>
>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>> news:472a062b$1@forums-1-dub...
>>> Hi Mike
>>>
>>> There is no dropdown at all, just the result I pasted below. Would you
>>> like me to send you screenshot?
>>>
>>> Mike Crocker wrote:
>>>> Gareth,
>>>>
>>>> In order to get all the info in the drop down you need to do the
>>>> following:
>>>> -Make sure you are in the Databae Development perspective
>>>> -Open the stored procedure by double clicking on it in the Database
>>>> Explorer
>>>> -Right-Click on the stored procedure in the edit pane and select "Get
>>>> Execution Plan". You will be prompted for any input params.
>>>>
>>>> This will open up the SQL Execution Plan view which has the drop down
>>>> you are looking for.
>>>>
>>>> Let me know if you have any other questions.
>>>>
>>>> Mike
>>>>
>>>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>>>> news:4728cc1d$1@forums-1-dub...
>>>>> If I try to run this for a stored proc, I'm only seeing the first step
>>>>> in the results. The documentation says each step should be presented
>>>>> in a dropdown list but it isn't.
>>>>>
>>>>> Any ideas why not?
>>>>>
>>>>> Thanks
>>>>>
>>>>> Example output:
>>>>> SQL:
>>>>> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>>>>>
>>>>>
>>>>>
>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>
>>>>>
>>>>> STEP 1
>>>>> The type of query is EXECUTE.
>>


Gareth Davies Posted on 2007-11-02 12:55:48.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
Subject: Re: Get execution plan for stored procs (ASE 12.5.2)
References: <4728cc1d$1@forums-1-dub> <4729d03f$1@forums-1-dub> <472a062b$1@forums-1-dub> <472a2f47$1@forums-1-dub> <472af004@forums-1-dub> <472b0632$1@forums-1-dub>
In-Reply-To: <472b0632$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
X-Original-NNTP-Posting-Host: 87-194-125-184.bethere.co.uk
Message-ID: <472b1e54@forums-1-dub>
Date: 2 Nov 2007 04:55:48 -0800
X-Trace: forums-1-dub 1194008148 87.194.125.184 (2 Nov 2007 04:55:48 -0800)
X-Original-Trace: 2 Nov 2007 04:55:48 -0800, 87-194-125-184.bethere.co.uk
Lines: 82
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:517
Article PK: 1088203

That doesn't work in the case where I'm operating on a temporary table
from a previous statement...

Mike Crocker wrote:
> Gareth,
>
> No it's not a bug. What you can do for ASE 12.5 is get Execution Plans for
> statements within the stored procedure by high lighting them and asking for
> an execution plan.
>
> mike
>
> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
> news:472af004@forums-1-dub...
>> Thanks Mike
>>
>> I understand not being able to get graphical plans but not being able to
>> see the text plan seems a bit odd as I can get full text-based query plans
>> for stored procs in 12.5 using any simple client such as SQL advantage.
>> Is this just a bug?
>>
>> Mike Crocker wrote:
>>> Gareth,
>>>
>>> As I indicated in the email I sent you. Stored Proecedure level
>>> Execution Plans and Graphic Execution Plans are only supported by ASE 15.
>>>
>>> You can highlight selected lines in the stored procedure and get an
>>> execution plan for those lines.
>>>
>>> Hope this helps.
>>>
>>> mike
>>>
>>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>>> news:472a062b$1@forums-1-dub...
>>>> Hi Mike
>>>>
>>>> There is no dropdown at all, just the result I pasted below. Would you
>>>> like me to send you screenshot?
>>>>
>>>> Mike Crocker wrote:
>>>>> Gareth,
>>>>>
>>>>> In order to get all the info in the drop down you need to do the
>>>>> following:
>>>>> -Make sure you are in the Databae Development perspective
>>>>> -Open the stored procedure by double clicking on it in the Database
>>>>> Explorer
>>>>> -Right-Click on the stored procedure in the edit pane and select "Get
>>>>> Execution Plan". You will be prompted for any input params.
>>>>>
>>>>> This will open up the SQL Execution Plan view which has the drop down
>>>>> you are looking for.
>>>>>
>>>>> Let me know if you have any other questions.
>>>>>
>>>>> Mike
>>>>>
>>>>> "Gareth Davies" <gruff@sitemaker.cc> wrote in message
>>>>> news:4728cc1d$1@forums-1-dub...
>>>>>> If I try to run this for a stored proc, I'm only seeing the first step
>>>>>> in the results. The documentation says each step should be presented
>>>>>> in a dropdown list but it isn't.
>>>>>>
>>>>>> Any ideas why not?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Example output:
>>>>>> SQL:
>>>>>> exec sitemaker1.dbo.sp_get_user_communities_admin;1 4000
>>>>>>
>>>>>>
>>>>>>
>>>>>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>>>>>
>>>>>>
>>>>>> STEP 1
>>>>>> The type of query is EXECUTE.
>
>