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.

Sybase optimizations and the procedure cache under high concurrency

4 posts in General Discussion Last posting was on 2009-10-13 19:46:38.0Z
Whatty Posted on 2009-10-12 18:02:40.0Z
From: "Whatty" <steven.whatmore@lynxdev.com>
Newsgroups: sybase.public.ase.general
Subject: Sybase optimizations and the procedure cache under high concurrency
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad36f40$1@forums-1-dub>
Date: 12 Oct 2009 11:02:40 -0700
X-Trace: forums-1-dub 1255370560 10.22.241.152 (12 Oct 2009 11:02:40 -0700)
X-Original-Trace: 12 Oct 2009 11:02:40 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28452
Article PK: 77695

Good morning (or Happy Thanksgiving in Canada)

I am going through a Sybase 15.x optimizations course and several times it
has mentioned that a SP will use the cached optimization plan unless that SP
is currently being used in which the SP procedure will be reloaded and a new
query plan generated.

Does this not infer that a system under high-concurrency will not be able to
benefit from the cached query plan and that it will in fact cause
sub-optimal performance on the SP(s) that are highly utilized.

Just a question that is nagging me.

Thanks in advance.

Whatty


Sherlock, Kevin [TeamSybase] Posted on 2009-10-12 20:04:53.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4ad36f40$1@forums-1-dub>
Subject: Re: Sybase optimizations and the procedure cache under high concurrency
Lines: 42
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; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad38be5$1@forums-1-dub>
Date: 12 Oct 2009 13:04:53 -0700
X-Trace: forums-1-dub 1255377893 10.22.241.152 (12 Oct 2009 13:04:53 -0700)
X-Original-Trace: 12 Oct 2009 13:04:53 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28453
Article PK: 77696

Well, in my opinion, No, it doesn't infer that. BUT, it all depends on your
workload, stored procedure code/design, and available resources (ASE
configuration). If your proc cache is under-configured (or if other
configurations are under-configured), you will have a better chance of
seeing problems of course ("duh Mark!").

Here is one example: You have 50 concurrent connections all executing the
same stored procedure over and over again. The query plan for one instance
of that procedure occupies 100kb of memory (proc cache). In the WORST case,
each connection executes the procedure when ALL other plans are currently in
use by another connection (highly unlikely imho). In this case, you need
5Mb of proc cache set aside from other procs to simply avoid cache misses
for these 50 connections executing this proc. Each connection (in the worst
case) sustains 1 proc cache miss, and from there on, enjoys a proc cache hit
for every subsequent invocation of the procedure (since there are 50 query
plans in cache).

Again, this all depends on how complex/slow the procedure is, how often it
is called, and how your ASE is configured (including proc cache, data cache,
etc).

"Whatty" <steven.whatmore@lynxdev.com> wrote in message
news:4ad36f40$1@forums-1-dub...
> Good morning (or Happy Thanksgiving in Canada)
>
> I am going through a Sybase 15.x optimizations course and several times it
> has mentioned that a SP will use the cached optimization plan unless that
> SP is currently being used in which the SP procedure will be reloaded and
> a new query plan generated.
>
> Does this not infer that a system under high-concurrency will not be able
> to benefit from the cached query plan and that it will in fact cause
> sub-optimal performance on the SP(s) that are highly utilized.
>
> Just a question that is nagging me.
>
> Thanks in advance.
>
> Whatty
>


Bret Halford [Sybase] Posted on 2009-10-13 16:16:27.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase optimizations and the procedure cache under high concurrency
References: <4ad36f40$1@forums-1-dub>
In-Reply-To: <4ad36f40$1@forums-1-dub>
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: <4ad4a7db$1@forums-1-dub>
Date: 13 Oct 2009 09:16:27 -0700
X-Trace: forums-1-dub 1255450587 10.22.241.152 (13 Oct 2009 09:16:27 -0700)
X-Original-Trace: 13 Oct 2009 09:16:27 -0700, vip152.sybase.com
Lines: 37
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28463
Article PK: 77709


Whatty wrote:
> Good morning (or Happy Thanksgiving in Canada)
>
> I am going through a Sybase 15.x optimizations course and several times
> it has mentioned that a SP will use the cached optimization plan unless
> that SP is currently being used in which the SP procedure will be
> reloaded and a new query plan generated.
>
> Does this not infer that a system under high-concurrency will not be
> able to benefit from the cached query plan and that it will in fact
> cause sub-optimal performance on the SP(s) that are highly utilized.
>
> Just a question that is nagging me.
>
> Thanks in advance.
>
> Whatty
>

There can be multiple copies of a plan in cache. Under high
concurrency, if there isn't a free plan in cache then the
session will have to generate a new plan - but that plan will
then be in cache for future processes once this session is done
with it. Once enough plans for a proc are in cache, future
executions will all find a cached plan and benefit.

If you had unlimited procedure cache, you would end up with
a number of plans for a procedure that was equal to the maximum
concurrency of the procedure. In practice, there are usually some
limits on procedure cache, so eventually plans that are unused for
some time will age out as other processes need to allocate space
in procedure cache. The smaller procedure cache is configured, the
faster unused plans will be aged out and the more frequently
replacements might need to be recompiled. Occasional recompilation
generally isn't bad, frequent recompilation is. Finding the right
balance is the goal for tuning the size of procedure cache.


Whatty Posted on 2009-10-13 19:46:38.0Z
From: "Whatty" <steven.whatmore@lynxdev.com>
Newsgroups: sybase.public.ase.general
References: <4ad36f40$1@forums-1-dub> <4ad4a7db$1@forums-1-dub>
In-Reply-To: <4ad4a7db$1@forums-1-dub>
Subject: Re: Sybase optimizations and the procedure cache under high concurrency
Lines: 46
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-MIMEOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad4d91e$1@forums-1-dub>
Date: 13 Oct 2009 12:46:38 -0700
X-Trace: forums-1-dub 1255463198 10.22.241.152 (13 Oct 2009 12:46:38 -0700)
X-Original-Trace: 13 Oct 2009 12:46:38 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28466
Article PK: 77708

Thank-you, the course I was taking did not make it immediately clear that
this would be the case, and now that it is further explained it makes
complete sense.

TY

"Bret Halford [Sybase]" <bret@sybase.com> wrote in message
news:4ad4a7db$1@forums-1-dub...
> Whatty wrote:
>> Good morning (or Happy Thanksgiving in Canada)
>>
>> I am going through a Sybase 15.x optimizations course and several times
>> it has mentioned that a SP will use the cached optimization plan unless
>> that SP is currently being used in which the SP procedure will be
>> reloaded and a new query plan generated.
>>
>> Does this not infer that a system under high-concurrency will not be able
>> to benefit from the cached query plan and that it will in fact cause
>> sub-optimal performance on the SP(s) that are highly utilized.
>>
>> Just a question that is nagging me.
>>
>> Thanks in advance.
>>
>> Whatty
>>
>
> There can be multiple copies of a plan in cache. Under high concurrency,
> if there isn't a free plan in cache then the
> session will have to generate a new plan - but that plan will
> then be in cache for future processes once this session is done
> with it. Once enough plans for a proc are in cache, future
> executions will all find a cached plan and benefit.
>
> If you had unlimited procedure cache, you would end up with
> a number of plans for a procedure that was equal to the maximum
> concurrency of the procedure. In practice, there are usually some
> limits on procedure cache, so eventually plans that are unused for
> some time will age out as other processes need to allocate space
> in procedure cache. The smaller procedure cache is configured, the
> faster unused plans will be aged out and the more frequently replacements
> might need to be recompiled. Occasional recompilation
> generally isn't bad, frequent recompilation is. Finding the right balance
> is the goal for tuning the size of procedure cache.
>