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.

Clear Procedure cache

4 posts in General Discussion Last posting was on 2009-11-24 05:59:50.0Z
senthil Posted on 2009-11-23 20:31:57.0Z
Sender: 7edf.4b0aeff4.1804289383@sybase.com
From: Senthil
Newsgroups: sybase.public.ase.general
Subject: Clear Procedure cache
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0af13d.7f26.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Nov 2009 12:31:57 -0800
X-Trace: forums-1-dub 1259008317 10.22.241.41 (23 Nov 2009 12:31:57 -0800)
X-Original-Trace: 23 Nov 2009 12:31:57 -0800, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28682
Article PK: 77924

I have a procedure which is run very slow (5mins to 10mins)
at very first execution. After subsequent execution of that
procedure is very very fast. I like to tune the sql's on
that procedure. I guess its running from cache so I am not
able to identify the performance of my changes. Does any one
know how to clear the stored procedure cache without
restarting the server?

I have tried to running big queries and big reports but
still it is running from the cache.

Thanks,
Senthil


"Mark A. Parsons" <iron_horse Posted on 2009-11-23 20:51:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Clear Procedure cache
References: <4b0af13d.7f26.1681692777@sybase.com>
In-Reply-To: <4b0af13d.7f26.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091120-0, 11/20/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0af5e5$1@forums-1-dub>
Date: 23 Nov 2009 12:51:49 -0800
X-Trace: forums-1-dub 1259009509 10.22.241.152 (23 Nov 2009 12:51:49 -0800)
X-Original-Trace: 23 Nov 2009 12:51:49 -0800, vip152.sybase.com
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28683
Article PK: 77925

I don't recall a way to purge procedure cache but fwiw ...

- execute a proc with an explicit 'with recompile' clause to force a new compilation at execution time [NOTE: This is
different than 'create proc/with recompile' which will force the proc to be recompiled *each* time it's executed.]

- I'm fairly sure that when you drop/recreate a proc you always end up with a new compilation for the first execution of
the newly (re)created proc; point being that the simple step of running 'create proc' should insure the first invocation
goes through a new compilation

Senthil wrote:
> I have a procedure which is run very slow (5mins to 10mins)
> at very first execution. After subsequent execution of that
> procedure is very very fast. I like to tune the sql's on
> that procedure. I guess its running from cache so I am not
> able to identify the performance of my changes. Does any one
> know how to clear the stored procedure cache without
> restarting the server?
>
> I have tried to running big queries and big reports but
> still it is running from the cache.
>
> Thanks,
> Senthil


Sherlock, Kevin [TeamSybase] Posted on 2009-11-23 21:37:44.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b0af13d.7f26.1681692777@sybase.com>
Subject: Re: Clear Procedure cache
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0b00a8@forums-1-dub>
Date: 23 Nov 2009 13:37:44 -0800
X-Trace: forums-1-dub 1259012264 10.22.241.152 (23 Nov 2009 13:37:44 -0800)
X-Original-Trace: 23 Nov 2009 13:37:44 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28684
Article PK: 77926

Well... just to be clear, the "procedure cache" is used to store compiled
query plans. So, clearing that cache will simply mean that you will incur
the time spent compiling a new query plan for your proc. Unless your
procedure is thousands of lines long, or extremely complex, my guess is that
procedure compilation time is not your issue.

Now, the "data cache" is completely different. That will contain the most
recently touched pages of your tables and that might be why you see better
performance after the first execution of your procedure.

You didn't post your ASE version, but in more recent versions:

Clearing procedure cache:
dbcc proc_cache(free_unused)

However, you can simply incur a procedure recompile by simply adding "with
recompile" to your execution:
exec myproc with recompile

Clearing data cache:
dbcc cachedataremove ( db_id | db_name [, object_id | object_name [,ix_id |
ix_name [, ptn_id | ptn_name ]]] )

or unbind objects from their cache, and rebind.

<Senthil> wrote in message news:4b0af13d.7f26.1681692777@sybase.com...
>I have a procedure which is run very slow (5mins to 10mins)
> at very first execution. After subsequent execution of that
> procedure is very very fast. I like to tune the sql's on
> that procedure. I guess its running from cache so I am not
> able to identify the performance of my changes. Does any one
> know how to clear the stored procedure cache without
> restarting the server?
>
> I have tried to running big queries and big reports but
> still it is running from the cache.
>
> Thanks,
> Senthil


Eddie_A Posted on 2009-11-24 05:59:50.0Z
From: Eddie_A <renzo.aquino@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Clear Procedure cache
Date: Mon, 23 Nov 2009 21:59:50 -0800 (PST)
Organization: http://groups.google.com
Lines: 53
Message-ID: <1a928ea3-abce-496a-91aa-001cf2fd2742@m20g2000vbp.googlegroups.com>
References: <4b0af13d.7f26.1681692777@sybase.com> <4b0b00a8@forums-1-dub>
NNTP-Posting-Host: 167.230.104.96
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1259042391 9500 127.0.0.1 (24 Nov 2009 05:59:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 24 Nov 2009 05:59:51 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m20g2000vbp.googlegroups.com; posting-host=167.230.104.96; posting-account=tGPBogoAAADskRZsAjqsJ5xZWzZ8rxax
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20,gzip(gfe),gzip(gfe)
X-Original-Bytes: 3392
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m20g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28686
Article PK: 77927

As ordinary user this is what I am doing right now.
1 - Use a different parameter, date range, values etc., where you
think previous rows will not be on the same criteria.
2 - Overload the cache with different data, this is only applicable
for small cache env
3 - Bcp Out/In the same data, this will refresh your pages
4 - rebuild your clustered indexes, this will also refresh your page

The DBCC commands provided are only for higher roles
(sybase_ts_role or sa)

On Nov 24, 5:37 am, "Sherlock, Kevin [TeamSybase]"

<kevin.sherl...@teamsybase.com> wrote:
> Well... just to be clear, the "procedure cache" is used to store compiled
> query plans. So, clearing that cache will simply mean that you will incur
> the time spent compiling a new query plan for your proc. Unless your
> procedure is thousands of lines long, or extremely complex, my guess is that
> procedure compilation time is not your issue.
>
> Now, the "data cache" is completely different. That will contain the most
> recently touched pages of your tables and that might be why you see better
> performance after the first execution of your procedure.
>
> You didn't post your ASE version, but in more recent versions:
>
> Clearing procedure cache:
> dbcc proc_cache(free_unused)
>
> However, you can simply incur a procedure recompile by simply adding "with
> recompile" to your execution:
> exec myproc with recompile
>
> Clearing data cache:
> dbcc cachedataremove ( db_id | db_name [, object_id | object_name [,ix_id |
> ix_name [, ptn_id | ptn_name ]]] )
>
> or unbind objects from their cache, and rebind.
>
> <Senthil> wrote in messagenews:4b0af13d.7f26.1681692777@sybase.com...
> >I have a procedure which is run very slow (5mins to 10mins)
> > at very first execution. After subsequent execution of that
> > procedure is very very fast. I like to tune the sql's on
> > that procedure. I guess its running from cache so I am not
> > able to identify the performance of my changes. Does any one
> > know how to clear the stored procedure cache without
> > restarting the server?
>
> > I have tried to running big queries and big reports but
> > still it is running from the cache.
>
> > Thanks,
> > Senthil