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.

Why the "set showplan on" only works at first time running?

7 posts in General Discussion Last posting was on 2012-06-08 06:52:59.0Z
Eisen Posted on 2012-06-05 06:30:04.0Z
Sender: 7f72.4fcda5b4.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Why the "set showplan on" only works at first time running?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fcda76c.7fc5.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jun 2012 23:30:04 -0700
X-Trace: forums-1-dub 1338877804 172.20.134.41 (4 Jun 2012 23:30:04 -0700)
X-Original-Trace: 4 Jun 2012 23:30:04 -0700, 172.20.134.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31171
Article PK: 74060

Dear all
Now I have a procedure to tune, I need to check plan of
some part of this procedure. So I placed "set showplan on"
and "set showplan off" in this procedure like --
create procedure p_testp1
as
begin
...
set showplan on
set statistics io,time on
select ...
update ...
set statistics io,time off
set showplan off
...
end
go
But to my surprised, I found the "set showplan on" only
works fine at the first time after I deployed this procedure
to DB. If I need to check the plan again, I'd have to
redeploy this procedure again. But the "set statistics
io,time on" works fine every time. Would anyone know why it
happend and how to make the "set showplan on" works every
time I run it without redeployment? Thanks a lot.

Best Regards
Eisen


Manish Negandhi [TeamSybase] Posted on 2012-06-05 14:28:12.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4fcda76c.7fc5.1681692777@sybase.com>
Subject: Re: Why the "set showplan on" only works at first time running?
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fce177c@forums-1-dub>
Date: 5 Jun 2012 07:28:12 -0700
X-Trace: forums-1-dub 1338906492 10.22.241.152 (5 Jun 2012 07:28:12 -0700)
X-Original-Trace: 5 Jun 2012 07:28:12 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31173
Article PK: 74062

What is the exact ASE version ? also post output of sp_configure "procedure
deferred compilation"


Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4fcda76c.7fc5.1681692777@sybase.com...
> Dear all
> Now I have a procedure to tune, I need to check plan of
> some part of this procedure. So I placed "set showplan on"
> and "set showplan off" in this procedure like --
> create procedure p_testp1
> as
> begin
> ...
> set showplan on
> set statistics io,time on
> select ...
> update ...
> set statistics io,time off
> set showplan off
> ...
> end
> go
> But to my surprised, I found the "set showplan on" only
> works fine at the first time after I deployed this procedure
> to DB. If I need to check the plan again, I'd have to
> redeploy this procedure again. But the "set statistics
> io,time on" works fine every time. Would anyone know why it
> happend and how to make the "set showplan on" works every
> time I run it without redeployment? Thanks a lot.
>
> Best Regards
> Eisen


Eisen Posted on 2012-06-06 02:35:18.0Z
Sender: 640.4fcdc17b.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set showplan on" only works at first time running?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fcec1e6.33ff.1681692777@sybase.com>
References: <4fce177c@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Jun 2012 19:35:18 -0700
X-Trace: forums-1-dub 1338950118 172.20.134.41 (5 Jun 2012 19:35:18 -0700)
X-Original-Trace: 5 Jun 2012 19:35:18 -0700, 172.20.134.41
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31179
Article PK: 74068

Hi Manish
Thanks. My version is "Adaptive Server
Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX
5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010". And
the output of sp_configure "procedure deferred compilation"
is 1.
Best Regards
Eisen

> What is the exact ASE version ? also post output of
> sp_configure "procedure deferred compilation"
>
>
> Manish Negandhi
> [TeamSybase]
>
> <Eisen> wrote in message
> > news:4fcda76c.7fc5.1681692777@sybase.com... Dear all
> > Now I have a procedure to tune, I need to check plan
> > of some part of this procedure. So I placed "set
> > showplan on" and "set showplan off" in this procedure
> > like -- create procedure p_testp1
> > as
> > begin
> > ...
> > set showplan on
> > set statistics io,time on
> > select ...
> > update ...
> > set statistics io,time off
> > set showplan off
> > ...
> > end
> > go
> > But to my surprised, I found the "set showplan on"
> > only works fine at the first time after I deployed this
> > procedure to DB. If I need to check the plan again, I'd
> > have to redeploy this procedure again. But the "set
> > statistics io,time on" works fine every time. Would
> > anyone know why it happend and how to make the "set
> > showplan on" works every time I run it without
> redeployment? Thanks a lot. >
> > Best Regards
> > Eisen
>
>
>
>


Manish Negandhi [TeamSybase] Posted on 2012-06-07 10:59:27.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4fce177c@forums-1-dub> <4fcec1e6.33ff.1681692777@sybase.com>
Subject: Re: Why the "set showplan on" only works at first time running?
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fd0898f$1@forums-1-dub>
Date: 7 Jun 2012 03:59:27 -0700
X-Trace: forums-1-dub 1339066767 10.22.241.152 (7 Jun 2012 03:59:27 -0700)
X-Original-Trace: 7 Jun 2012 03:59:27 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31180
Article PK: 74069

It does not display showplan for me in ASE 15.5 even for first time , can
you try on any other server running on different version ?

-Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4fcec1e6.33ff.1681692777@sybase.com...
> Hi Manish
> Thanks. My version is "Adaptive Server
> Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX
> 5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010". And
> the output of sp_configure "procedure deferred compilation"
> is 1.
> Best Regards
> Eisen
>
>> What is the exact ASE version ? also post output of
>> sp_configure "procedure deferred compilation"
>>
>>
>> Manish Negandhi
>> [TeamSybase]
>>
>> <Eisen> wrote in message
>> > news:4fcda76c.7fc5.1681692777@sybase.com... Dear all
>> > Now I have a procedure to tune, I need to check plan
>> > of some part of this procedure. So I placed "set
>> > showplan on" and "set showplan off" in this procedure
>> > like -- create procedure p_testp1
>> > as
>> > begin
>> > ...
>> > set showplan on
>> > set statistics io,time on
>> > select ...
>> > update ...
>> > set statistics io,time off
>> > set showplan off
>> > ...
>> > end
>> > go
>> > But to my surprised, I found the "set showplan on"
>> > only works fine at the first time after I deployed this
>> > procedure to DB. If I need to check the plan again, I'd
>> > have to redeploy this procedure again. But the "set
>> > statistics io,time on" works fine every time. Would
>> > anyone know why it happend and how to make the "set
>> > showplan on" works every time I run it without
>> redeployment? Thanks a lot. >
>> > Best Regards
>> > Eisen
>>
>>
>>
>>


Eisen Posted on 2012-06-08 06:49:53.0Z
Sender: 7bae.4fd065c9.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set showplan on" only works at first time running?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fd1a091.30e8.1681692777@sybase.com>
References: <4fd0898f$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 7 Jun 2012 23:49:53 -0700
X-Trace: forums-1-dub 1339138193 172.20.134.41 (7 Jun 2012 23:49:53 -0700)
X-Original-Trace: 7 Jun 2012 23:49:53 -0700, 172.20.134.41
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31182
Article PK: 74071

Hi Manish
Thanks. I tried on a ASE12.5.4 on Linux, the "set
showplan on" inside procedure works fine every time without
redeployment.

Best Regards
Eisen

> It does not display showplan for me in ASE 15.5 even for
> first time , can you try on any other server running on
> different version ?
>
> -Manish Negandhi
> [TeamSybase]
>
>
> <Eisen> wrote in message
> > news:4fcec1e6.33ff.1681692777@sybase.com... Hi Manish
> > Thanks. My version is "Adaptive Server
> > Enterprise/15.0.3/EBF 17775 ESD#4/P/RS6000/AIX
> > 5.3/ase1503/2768/64-bit/FBO/Thu Aug 26 07:36:52 2010".
> > And the output of sp_configure "procedure deferred
> > compilation" is 1.
> > Best Regards
> > Eisen
> >
> >> What is the exact ASE version ? also post output of
> >> sp_configure "procedure deferred compilation"
> >>
> >>
> >> Manish Negandhi
> >> [TeamSybase]
> >>
> >> <Eisen> wrote in message
> >> > news:4fcda76c.7fc5.1681692777@sybase.com... Dear all
> >> > Now I have a procedure to tune, I need to check
> plan >> > of some part of this procedure. So I placed "set
> >> > showplan on" and "set showplan off" in this procedure
> >> > like -- create procedure p_testp1
> >> > as
> >> > begin
> >> > ...
> >> > set showplan on
> >> > set statistics io,time on
> >> > select ...
> >> > update ...
> >> > set statistics io,time off
> >> > set showplan off
> >> > ...
> >> > end
> >> > go
> >> > But to my surprised, I found the "set showplan on"
> >> > only works fine at the first time after I deployed
> this >> > procedure to DB. If I need to check the plan
> again, I'd >> > have to redeploy this procedure again. But
> the "set >> > statistics io,time on" works fine every
> time. Would >> > anyone know why it happend and how to
> make the "set >> > showplan on" works every time I run it
> without >> redeployment? Thanks a lot. >
> >> > Best Regards
> >> > Eisen
> >>
> >>
> >>
> >>
>
>


Bret Halford Posted on 2012-06-07 16:39:03.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set showplan on" only works at first time running?
References: <4fcda76c.7fc5.1681692777@sybase.com>
In-Reply-To: <4fcda76c.7fc5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fd0d927@forums-1-dub>
Date: 7 Jun 2012 09:39:03 -0700
X-Trace: forums-1-dub 1339087143 10.22.241.152 (7 Jun 2012 09:39:03 -0700)
X-Original-Trace: 7 Jun 2012 09:39:03 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31181
Article PK: 74070

Try creating the procedure without the
SET commands in it.
Turn the SET options on in your current
session.
To force the optimizer to generate a new
query plan each time you execute the procedure,
with the "with recompile" option. I think
you will then get showplan output each time.

execute p_testp1 with recompile

On 6/5/2012 12:30 AM, Eisen wrote:
> Dear all
> Now I have a procedure to tune, I need to check plan of
> some part of this procedure. So I placed "set showplan on"
> and "set showplan off" in this procedure like --
> create procedure p_testp1
> as
> begin
> ...
> set showplan on
> set statistics io,time on
> select ...
> update ...
> set statistics io,time off
> set showplan off
> ...
> end
> go
> But to my surprised, I found the "set showplan on" only
> works fine at the first time after I deployed this procedure
> to DB. If I need to check the plan again, I'd have to
> redeploy this procedure again. But the "set statistics
> io,time on" works fine every time. Would anyone know why it
> happend and how to make the "set showplan on" works every
> time I run it without redeployment? Thanks a lot.
>
> Best Regards
> Eisen


Eisen Posted on 2012-06-08 06:52:59.0Z
Sender: 7bae.4fd065c9.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set showplan on" only works at first time running?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fd1a14b.310e.1681692777@sybase.com>
References: <4fd0d927@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 7 Jun 2012 23:52:59 -0700
X-Trace: forums-1-dub 1339138379 172.20.134.41 (7 Jun 2012 23:52:59 -0700)
X-Original-Trace: 7 Jun 2012 23:52:59 -0700, 172.20.134.41
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31183
Article PK: 74073

Hi Bret
Thanks a lot. the "with recompile" solved my issue.
Thanks again.

Best Regards
Eisen

> Try creating the procedure without the
> SET commands in it.
> Turn the SET options on in your current
> session.
> To force the optimizer to generate a new
> query plan each time you execute the procedure,
> with the "with recompile" option. I think
> you will then get showplan output each time.
>
> execute p_testp1 with recompile
>
>
>
> On 6/5/2012 12:30 AM, Eisen wrote:
> > Dear all
> > Now I have a procedure to tune, I need to check plan
> > of some part of this procedure. So I placed "set
> > showplan on" and "set showplan off" in this procedure
> > like -- create procedure p_testp1
> > as
> > begin
> > ...
> > set showplan on
> > set statistics io,time on
> > select ...
> > update ...
> > set statistics io,time off
> > set showplan off
> > ...
> > end
> > go
> > But to my surprised, I found the "set showplan on"
> > only works fine at the first time after I deployed this
> > procedure to DB. If I need to check the plan again, I'd
> > have to redeploy this procedure again. But the "set
> > statistics io,time on" works fine every time. Would
> > anyone know why it happend and how to make the "set
> > showplan on" works every time I run it without
> redeployment? Thanks a lot. >
> > Best Regards
> > Eisen
>