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.

"...not enough procedure cache..."

3 posts in General Discussion Last posting was on 2009-08-30 20:03:48.0Z
Trasco Posted on 2009-08-27 15:05:10.0Z
Sender: 2cf2.4a96a044.1804289383@sybase.com
From: Trasco
Newsgroups: sybase.public.ase.general
Subject: "...not enough procedure cache..."
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a96a0a6.2d08.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 27 Aug 2009 08:05:10 -0700
X-Trace: forums-1-dub 1251385510 10.22.241.41 (27 Aug 2009 08:05:10 -0700)
X-Original-Trace: 27 Aug 2009 08:05:10 -0700, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28226
Article PK: 77471

Hi, when I run the following select on ASE 12.5, I get
errors from the database.

SELECT tr1.ame_a, tr1.ame_b FROM tr1
WHERE ((tr1.transfer_date between '7/1/09 0:00:00' and
'8/25/09 23:59:59') OR
(tr1.transfer_date = null AND tr1.mod_date
between '7/1/09 0:00:00' and '8/25/09 23:59:59')) ;

“There is not enough procedure cache to run this
procedure, trigger, or SQL batch. Retry later, or ask your
SA to reconfigure SQL Server with more procedure cache.”
“Internal error encountered in merging rows; resubmit
query batch.”


However, when I run it with a larger date range (below) I
don't get the errors. Can anyone explain why a larger date
range would not cause the error? It seems the logic here is
backwards, that a smaller date range would be less likely to
trigger an error vs. a wider date range.

SELECT tr1.ame_a, tr1.ame_b FROM tr1
WHERE ((tr1.transfer_date between '12/1/08 0:00:00' and
'8/25/09 23:59:59') OR
(tr1.transfer_date = null AND tr1.mod_date
between '12/1/08 0:00:00' and '8/25/09 23:59:59')) ;

Thanks,
Trasco


Bret Halford [Sybase] Posted on 2009-08-27 20:22:23.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: "...not enough procedure cache..."
References: <4a96a0a6.2d08.1681692777@sybase.com>
In-Reply-To: <4a96a0a6.2d08.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a96eaff@forums-1-dub>
Date: 27 Aug 2009 13:22:23 -0700
X-Trace: forums-1-dub 1251404543 10.22.241.152 (27 Aug 2009 13:22:23 -0700)
X-Original-Trace: 27 Aug 2009 13:22:23 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28228
Article PK: 77472


Trasco wrote:
> Hi, when I run the following select on ASE 12.5, I get
> errors from the database.
>
> SELECT tr1.ame_a, tr1.ame_b FROM tr1
> WHERE ((tr1.transfer_date between '7/1/09 0:00:00' and
> '8/25/09 23:59:59') OR
> (tr1.transfer_date = null AND tr1.mod_date
> between '7/1/09 0:00:00' and '8/25/09 23:59:59')) ;
>
> “There is not enough procedure cache to run this
> procedure, trigger, or SQL batch. Retry later, or ask your
> SA to reconfigure SQL Server with more procedure cache.”
> “Internal error encountered in merging rows; resubmit
> query batch.”
>
>
> However, when I run it with a larger date range (below) I
> don't get the errors. Can anyone explain why a larger date
> range would not cause the error? It seems the logic here is
> backwards, that a smaller date range would be less likely to
> trigger an error vs. a wider date range.
>
> SELECT tr1.ame_a, tr1.ame_b FROM tr1
> WHERE ((tr1.transfer_date between '12/1/08 0:00:00' and
> '8/25/09 23:59:59') OR
> (tr1.transfer_date = null AND tr1.mod_date
> between '12/1/08 0:00:00' and '8/25/09 23:59:59')) ;
>
> Thanks,
> Trasco

The error is regarding procedure cache rather than data cache,
so it isn't the amount of data that is an issue. Probably
the optimizer is generating different query plans and the one
for the smaller date range is a larger plan. Use "set showplan on"
to check that theory out.

Cheers,
-bret


Jeff Tallman [Sybase] Posted on 2009-08-30 20:03:48.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: "...not enough procedure cache..."
References: <4a96a0a6.2d08.1681692777@sybase.com>
In-Reply-To: <4a96a0a6.2d08.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a9adb24@forums-1-dub>
Date: 30 Aug 2009 13:03:48 -0700
X-Trace: forums-1-dub 1251662628 10.22.241.152 (30 Aug 2009 13:03:48 -0700)
X-Original-Trace: 30 Aug 2009 13:03:48 -0700, vip152.sybase.com
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28234
Article PK: 77477

The amount of proc cache used per query depends on the number of
histograms available for the table and other histograms used for other
queries. For example, it *might* be that you ran the second query with
a larger date range when fewer queries were being optimized/executed
concurrently, and as a result, ASE was able to flush other table
histograms out of cache to make room. Of course, the assumption here
is that it was the concurrency as a factor and that you were not running
the queries in isolation.....

...it is also true (as Bret says) that a different plan could be causing
the problem. For example, in the smaller case, the optimizer may be
electing to choose a reformatting strategy due to the lack of an index
or that the transfer_date only appears as a non-leading column in the
index it thinks is best. In the larger case, it may be choosing a table
scan due to row estimates.......

Things I would check are:

1) How many histogram steps exist for the table tr1 (use optdiag) and
whether statistics exists for transfer_date/mod_date

2) How much proc cache you have as a percentage of ASE memory (should be
at least 20% for ASE 15). Proc cache is crucial for query optimization
(as well as parsing, etc.).

3) is tr1 really a table or is it a "monster view" that is misused???



Rather than showplan as Bret suggests, I would rather see:

set statistics plancost, resource on
set showplan on
set option show_missing_stats on
select * from master..sysoptions where spid=@@spid
go
-- query
go
set statistics plancost, resource off
set showplan off
set option show_missing_stats off
go


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Trasco wrote:
> Hi, when I run the following select on ASE 12.5, I get
> errors from the database.
>
> SELECT tr1.ame_a, tr1.ame_b FROM tr1
> WHERE ((tr1.transfer_date between '7/1/09 0:00:00' and
> '8/25/09 23:59:59') OR
> (tr1.transfer_date = null AND tr1.mod_date
> between '7/1/09 0:00:00' and '8/25/09 23:59:59')) ;
>
> “There is not enough procedure cache to run this
> procedure, trigger, or SQL batch. Retry later, or ask your
> SA to reconfigure SQL Server with more procedure cache.”
> “Internal error encountered in merging rows; resubmit
> query batch.”
>
>
> However, when I run it with a larger date range (below) I
> don't get the errors. Can anyone explain why a larger date
> range would not cause the error? It seems the logic here is
> backwards, that a smaller date range would be less likely to
> trigger an error vs. a wider date range.
>
> SELECT tr1.ame_a, tr1.ame_b FROM tr1
> WHERE ((tr1.transfer_date between '12/1/08 0:00:00' and
> '8/25/09 23:59:59') OR
> (tr1.transfer_date = null AND tr1.mod_date
> between '12/1/08 0:00:00' and '8/25/09 23:59:59')) ;
>
> Thanks,
> Trasco