Backup cached plan

[DilipVoora]

Hi Experts,

Due to a slowness issue we want to delete a cached plan(referring SAP note 2114568) for the 1st parallel process of a stored procedure (that gets executed as 30 parallel processes). Is there a way to backup an existing cached query plan before I delete it? Also, what steps we need to follow to put it backup incase if the newly generated plan is not giving better results? Please comment.

Details:

ASE version: 16.0 SP03 PL12 HF1 with ebf 30601 (a 1-off to us)
OS version : AIX 7.2
Logical page size : 8k

NOTE: After deleting the plan we will update statistics of non-clustered indexes of tables that will be used by sp.

Regards,
Dilip Voora

moncachedproccedures.txt

[luc_vanderveurst]

Hi Dilip,

I'm not aware of a procedure to backup and restore cached statements.

But what you can do is using dbcc prsqlcache(id, 0) to show the query and dbcc prsqlcache(id, 1) to show the query plan of the cached statement with the ids you found in moncachedprocedures.

When the query is cached, it uses the generated query plan for subsequent executions, so the query plan is based on the values in the query the first time that it's executed. It might be that this isn't the right query plan for other values resulting in bad performance.

I've seen this a while ago when we had a procedure with a department as parameter containing a select that didn't return values for the given department. That generated a bad query plan for executions of the procedure with a department that generated many rows.

By using prsqlcache, I was able to identify the occurences with the bad query plan and removed them from the cache.

So if you delete a cached statement of which you know the query plan is wrong, it's very unlikely that you want to put it back.

Best Regards,

Luc.

[DilipVoora]

Hi Luc,

Does dbcc prsqlcache capture/show the statements that are part of the stored procedure when it is executed? I tried to simulate with a test procedure with a simple select inside it but it didn't show up in the output of prsqlcache. However, when I run the 'select' statement manually it showed up. See attached and advise.dbcc-prsqlcache.txt

Regards,
Dilip Voora