Procedure Cache fills up!

Hi,

I have a client who is experiencing problems with regard to PROCEDURE CACHE
filling up when initiating a specific query. I am unable to find any
information on these scenarios...Any suggestions would be welcomed!

============================================================================
=======

I have the following 2 scenarios :

select distinct CREDIT_REASON_KEY from JDE_repinfo_snapshot



1) When running the above query my procedure cache fills up. The table in
question has approx. 250 000 rows with no indexes.

    STEP 1

        The type of query is INSERT.

        The update mode is direct.

        Worktable1 created for DISTINCT.





        FROM TABLE

            JDE_repinfo_snapshot

        Nested iteration.

        Table Scan.

        Forward scan.

        Positioning at start of table.

        Using I/O Size 2 Kbytes for data pages.

        With MRU Buffer Replacement Strategy for data pages.

        TO TABLE

            Worktable1.





    STEP 2

        The type of query is SELECT.

        This step involves sorting.





        FROM TABLE

            Worktable1.

        Using GETSORTED

        Table Scan.

        Forward scan.

        Positioning at start of table.

        Using I/O Size 2 Kbytes for data pages.

        With MRU Buffer Replacement Strategy for data pages.



2) When running the above query with an index on CREDIT_REASON_KEY the

procedure cache does not fill up.



    STEP 1

        The type of query is SELECT.





        FROM TABLE

            JDE_repinfo_snapshot

        Nested iteration.

        Index : JDE_repinfo_snapshot_ndx1

        Forward scan.

        Positioning at index start.

        Index contains all needed columns. Base table will not be read.

        Using I/O Size 2 Kbytes for index leaf pages.

        With LRU Buffer Replacement Strategy for index leaf pages.

================================================







0
Shad
4/8/2002 2:53:36 PM
sybase.ase.general 8655 articles. 0 followers. Follow

1 Replies
398 Views

Similar Articles

[PageSpeed] 15

What version of ASE are we talking about?

Shawn Torkelson
Database Administrator
shawn_torkelson@msn.com

Shad Davids wrote:

> Hi,
>
> I have a client who is experiencing problems with regard to PROCEDURE CACHE
> filling up when initiating a specific query. I am unable to find any
> information on these scenarios...Any suggestions would be welcomed!
>
> ============================================================================
> =======
>
> I have the following 2 scenarios :
>
> select distinct CREDIT_REASON_KEY from JDE_repinfo_snapshot
>
> 1) When running the above query my procedure cache fills up. The table in
> question has approx. 250 000 rows with no indexes.
>
>     STEP 1
>
>         The type of query is INSERT.
>
>         The update mode is direct.
>
>         Worktable1 created for DISTINCT.
>
>         FROM TABLE
>
>             JDE_repinfo_snapshot
>
>         Nested iteration.
>
>         Table Scan.
>
>         Forward scan.
>
>         Positioning at start of table.
>
>         Using I/O Size 2 Kbytes for data pages.
>
>         With MRU Buffer Replacement Strategy for data pages.
>
>         TO TABLE
>
>             Worktable1.
>
>     STEP 2
>
>         The type of query is SELECT.
>
>         This step involves sorting.
>
>         FROM TABLE
>
>             Worktable1.
>
>         Using GETSORTED
>
>         Table Scan.
>
>         Forward scan.
>
>         Positioning at start of table.
>
>         Using I/O Size 2 Kbytes for data pages.
>
>         With MRU Buffer Replacement Strategy for data pages.
>
> 2) When running the above query with an index on CREDIT_REASON_KEY the
>
> procedure cache does not fill up.
>
>     STEP 1
>
>         The type of query is SELECT.
>
>         FROM TABLE
>
>             JDE_repinfo_snapshot
>
>         Nested iteration.
>
>         Index : JDE_repinfo_snapshot_ndx1
>
>         Forward scan.
>
>         Positioning at index start.
>
>         Index contains all needed columns. Base table will not be read.
>
>         Using I/O Size 2 Kbytes for index leaf pages.
>
>         With LRU Buffer Replacement Strategy for index leaf pages.
>
> ================================================

0
Shawn
4/26/2002 8:07:24 PM
Reply:

Similar Artilces:

how to clear out data cache, statement cache and procedure cache?
Hi there, does anyone know is there some commands to to clear out data cache, statement cache and procedure cache? (without recycle database server) And for both ASE 12.5 and ASE 15? we need to run some performance comparsion in ASE 12.5 and ASE 15. I'd make sure the data cache are similar and procedure/statement cache is clear (so cached query plan is not used) for each case. This all depends on the (very) specific version of ASE data cache: ------------ as of 15.0.3 - dbcc cachedataremove(dbid | dbname, objid | objname, partitionid | partitionname, indid | indexname)...

Problem with ASE 15 procedure cache.
Hi This week we installed Sybase ASE 15 Small Business Edition on a Windows 2003 server with 2GB RAM. Now we notice we're having some problems when running certain procedures, we get the following error message: "There is not enough procedure cache to run this procedure, trigger, or SQL Batch. Retry later, or ask you SA to reconfigure SQL Server with more procedure cache." SQLState returns 'ZZZZZ' We've configured the server's memory as follows: "Allocate Max Shared Memory" = 1 "Max Memory" = 1024M "Procedure Cache Si...

slowly filled up "procedure cache"
Dear colleagues, last weekend we changed our frontend-application and the used database. We did NOT change the ASE. And now we suffer from this message: "There is not enough procedure cache to run this procedure" and after a while we have to restart the ASE. We are running ASE 12.5.0.3 EBF 10688 on Solaris 8 (32 Bit) 8k pagesize Together with the EFTS engine Last week the server was configured with "procedure cache size =3d 50000" now even "procedure cache size =3d 250000" is too less. Currently we make a try with "procedur...

Procedure Cache
I am seeing a high value for Procedure Removals at times but the number of stored procedures loaded doesn't match. Is there any good documentation on this (or advice)? Thx Richard Kinread Couple of things that might be worth mentioning: 1. You could have many copies of procedures in cache. Not sure if multiple copies of procedures in cache are counted when you say "number of sp's loaded doesn't match". Is this from sp_sysmon output? 2. Procedure cache gets used for so many things other than procedures. Replication affects procedure cache as well...

After upgrade to ASE 15.5 facing procedure cache problem
Hi, We are in process of upgrading ASE from 15.0.3 to ASE 15.5 After upgrading to ASE 15.5 performance has degrade drastically. Main difference we have seen is sp_sysmon report is showing procedure cache from disk value more than 50%. I have checked all confioguration parameter and they have not changed ( In fact we are allocating more memeory to procedure cache comapre to ASE 15.0.3) Can somebody throw some light where thing could have been gone wrong ??? Thanks Pradip > We are in process of upgrading ASE from 15.0.3 to ASE 15.5 > > After upgrading to ASE 15...

Caching, Caching,Caching,Caching,Caching Why nobody know???
I have posted this problem quite a few times but never got a decent answer and I have still not found a solution. Every time I have a gridview when the page is refreshed the data in the gridview does not refresh. You might say well and GridView.DataBind() to the pageload method but I think there is a performance problem if you bind your gridviews every time. For example I have a dropdownlist, a gridview and button and the gridview is supposed to refresh depending on the value in the dropdown when the button is pushed. if you click on the button the gridview data does not change unless y...

There is not enough procedure cache to run this procedure
We have a 16GB memory server configuration and 1.5GB procedure cache size. But we receive the error "There is not enough procedure cache to run this procedure..." occasionally, even after the procedure cache was increased from 1.2GB to 1.5GB. If the cache is not enough, does Sybase server rotate LRU procedures/triggers out of the cache and let the new procedure to run, instead of failing the procedure or trigger? We run 12.5.0.3 on HP box. Is there a bug in this Sybase version? Any comment is appreciated! Procedure cache is used for quite a few things other than proce...

About Procedure cache & Data cache..
Hi, 1. Procedure cache: I had to reconfigure my procedure cache with sp_configure (sp_configure "procedure cache", XX) but... I was wondering how could the procedure cache run out of capacity/memory? If there is not enough place in the procedure cache for a new procedure plan didn't the server "flushes" out the oldest used procedure query plan from cache and let the place for the newest ? I would like to know more on how the Procedure cache works... (I just know that it's the memory -RAM- allocated for ASE stored procedures--> compiled store procedure...

Capacity Planning
We are looking at adding more users to our systems. Is there any documentation on how to plan for the amount of additional data and procedure that is required (and if it is required)? We have some capacity planning folks who are looking at O/S statistics and I think we should be looking at what ASE is doing. Thanks Richard Kinread Richard Kinread wrote: > > We are looking at adding more users to our systems. Is there any > documentation on how to plan for the amount of additional data and procedure > that is required (and if it is required)? Each user c...

Procedure Cache and Statement Cache (reported in sysmon)
All - In reviewing my sysmons for a reporting dataserver, I found that my 'Statements not cached' were non-zero with my 'statement cache size' configured at '0'. So, for testing, I increased that configuration to '100' MB to see what effects this would have in sysmon. Before starting my tests, I also looked at 'Procedure Removals' which have been consistently ~0 for months. Immediately upon testing and my subsequent sysmons, my 'Statements not Cached' drop to 0, but my 'Procedure Removal' jumped anywhere from 12-49 for each...

Procedure cache error on subquery inside stored procedure
This is a multi-part message in MIME format. --------------CE75065201FF1E71E2FFFFD6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, Adaptive Server Enterprise/11.9.2.3/1159/P/SWR 9221 ESD 3/HP9000-735/HP-UX 10.2/FBO/Tue Sep 5 15:56:02 2000 I have a strange situation where a client recieves the following message: ----------- ClibServerMsgCb: DB Server message: Procedure 'spc_val_ad_ordr_done' Line 3186 Message String: There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to ...

Procedure Cache
Greetings: I am making a plea to anyone who could possibly help. We seem to have a problem with the procedure cache filling up (no matter how high we set it). We have done multiple dbtraces and dumps etc to isolate the problem. We re-boot the server every morning to no avail. We are literally at wits-end. Has anyone one else run into this issue and, perhaps, found a work-around? Sybase engineering has had this issue since 10/10/2002 and we have heard nothing from them (they have every dump in the world from us). Any bit of information (either good or bad) would be appreciated. I ...

Procedure Cache
Hi, How did I know , the maximum from procedure cache my server has used ? Jorge Donato ...

The procedure cache?
hi,all: In our project ,our database use ASE 11.5 ,We have about 320 tables,we encounted the memory problem: Message 701:"There is not enough procedure cache to run this procedure trigger or sql batch" when use :dbcc memusage Memory Usage: Meg. 2K Blks Bytes Configured Memory: 17.5781 9000 18432000 Code size: 1.0000 512 1048576 Kernel Structures: 0.1694 87 177659 Server Structures: 5.0412 2582 5286096 Cache Memory: 5.4961 2814 5763072 Proc Buffe...

Web resources about - Procedure Cache fills up! - sybase.ase.general

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 12/9/2015 7:09:25 PM