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 and 'with recompile'

9 posts in General Discussion Last posting was on 2011-02-28 14:40:52.0Z
Pieter Coene Posted on 2011-02-23 16:34:58.0Z
Sender: 25a.4d653460.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.ase.general
Subject: Showplan and 'with recompile'
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d653731.2cf.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Feb 2011 08:34:58 -0800
X-Trace: forums-1-dub 1298478898 10.22.241.41 (23 Feb 2011 08:34:58 -0800)
X-Original-Trace: 23 Feb 2011 08:34:58 -0800, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30000
Article PK: 79227

Hi,

when using "with recompile" on a proc, asking for a showplan
always says 'To be Optimized at runtime using Deferred
Compilation' for queries with local variables as arguments.
I have read the new feature 'Deferred Compilation' so i get
the point of this. But now i can't see what showplan the SP
really used during execution of it.

I really need the 'with recompile' option cause of this
'new' behaviour, because our SP arguments can differ a lot,
creating some 'bad' showplans for some queries (even forcing
indexes does not resolve al our problems)

So to get back at my question, how can i see the used plan?

Greetz
Pieter


J Posted on 2011-02-23 17:18:48.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan and 'with recompile'
Reply-To: J@bogusemailAddress.com
Message-ID: <4d654103.1732350000@forums.sybase.com>
References: <4d653731.2cf.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 Feb 2011 09:18:48 -0800
X-Trace: forums-1-dub 1298481528 10.22.241.152 (23 Feb 2011 09:18:48 -0800)
X-Original-Trace: 23 Feb 2011 09:18:48 -0800, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30002
Article PK: 79233

On 23 Feb 2011 08:34:58 -0800, Pieter Coene wrote:

There is a sp_configure "procedure deferred compilation" option on
15.0.3 ESD#1 and up (traceflag before this). You could turn it off
when the procs are created.

J

>Hi,
>
>when using "with recompile" on a proc, asking for a showplan
>always says 'To be Optimized at runtime using Deferred
>Compilation' for queries with local variables as arguments.
>I have read the new feature 'Deferred Compilation' so i get
>the point of this. But now i can't see what showplan the SP
>really used during execution of it.
>
>I really need the 'with recompile' option cause of this
>'new' behaviour, because our SP arguments can differ a lot,
>creating some 'bad' showplans for some queries (even forcing
>indexes does not resolve al our problems)
>
>So to get back at my question, how can i see the used plan?
>
>Greetz
>Pieter


"Mark A. Parsons" <iron_horse Posted on 2011-02-23 21:28:08.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan and 'with recompile'
References: <4d653731.2cf.1681692777@sybase.com>
In-Reply-To: <4d653731.2cf.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: <4d657be8@forums-1-dub>
Date: 23 Feb 2011 13:28:08 -0800
X-Trace: forums-1-dub 1298496488 10.22.241.152 (23 Feb 2011 13:28:08 -0800)
X-Original-Trace: 23 Feb 2011 13:28:08 -0800, vip152.sybase.com
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30003
Article PK: 79235

I'm not sure I understand what you mean by "now i can't see what showplan the SP really used" ... ?

While it's true the first pass through the query plans will show the message about deferred compilation, a query plan
will be generated when the deferred-compilation query is reached during the execution phase.

So for example, assuming you have queries @ lines 100, 200 and 300 ... and line 200 is to be processed via deferred
compilation ... you should see something like:

============================
<query plan for line 100>

'To be optimized at runtime using Deferred Compilation' for line 200

<query plan for line 300>

line 100 executed

<query plan for line 200>

line 200 executed

line 300 executed
============================

If you're still not seeing the query plan for line 200, and you've scrolled all the way down through your query plan
output, could you provide more details on how/when you're seeing the query plans, eg:

- are you running an instrumented test with 'set showplan on'

- are you trying to pull the query plans from monSysPlanText, and if so, have you verified that your plan text pipe is
big enough to insure the plan does not get pushed off the end before you can capture it

- are you using a GUI front-end (which could be masking/throwing-away some plans; best bet is to use the isql command
line tool to verify query plans are being generated)

On 02/23/2011 11:34, Pieter Coene wrote:
> Hi,
>
> when using "with recompile" on a proc, asking for a showplan
> always says 'To be Optimized at runtime using Deferred
> Compilation' for queries with local variables as arguments.
> I have read the new feature 'Deferred Compilation' so i get
> the point of this. But now i can't see what showplan the SP
> really used during execution of it.
>
> I really need the 'with recompile' option cause of this
> 'new' behaviour, because our SP arguments can differ a lot,
> creating some 'bad' showplans for some queries (even forcing
> indexes does not resolve al our problems)
>
> So to get back at my question, how can i see the used plan?
>
> Greetz
> Pieter