how to manage multiple query plan for the same trigger/stored procedure

[Former Member]

Hi,

ASE 15.7.1 on Linux.

Maintenance job during week end (reorg,update statistics), on Monday users start to work and all stored procedure and triggers are recompiled and get a new query plan. One trigger get 2 different query plan with very different performance. trigger recreated with a forced index, now only one plan and it works fine.

For curiosity I checked how many stored proc and trigger has more than one query plan and I have found that just after 2 days after maintenance job run there are more than 400 compiled objects with 2 or more query plan and very different performance.

How ASE manage these different plans ? Is there anything that remove the worst ones ?

select * from (
select count(*) as "number", ExecutionTime/RequestCnt as "averageTime", DBName,ObjectType,ObjectName from monCachedProcedures where ObjectName not like '*%*' group by DBName, ObjectType, ObjectName ) a
where number > 1 order by DBName, ObjectType, ObjectName, averageTime

number averageTime DBName ObjectType ObjectName
----------- ----------- ------------------------------ -------------------------------- ------------------------------

9 9 15 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 17 31504 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 19 44073 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 140 600 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 334 1 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 2682 32 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 3169 17 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 7048 1 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll
9 25417 2 MEDUSA_PROD stored procedure AvailabilityUnprocessedPoll

I wrote the query above, looks this example of a stored procedure with 9 different query plans, the average execution time goes from 9 milliseconds to 25 seconds and the faster is not the most frequent selected.

I know there is dbcc deleteplan but it is unsupported and I do not like to use it in production. Is there any other best practice ? Each time the maintenance job runs is it possible that a procedure or trigger start to run slowly ? What a dba can do to prevent this ?

Several question mark just to give you more point of view of the same problem.

[M-B]

Hi Massimo,

Are you creating #tables with an index in those sp/trigger with ddl in tran turned on by any chance? Please take a look at this KBA article:

https://launchpad.support.sap.com/#/notes/2224058

Regards

Mehrab

[former_member89972]

Thanks Mehrab for the feedback.

We recently upgraded to ASE 16, will keep this useful note handy.

Cheers

Thanks

[sladebe]

The way I read the SAP/Sybase documentation, if there are multiple users executing a stored proc (or trigger) in parallel, then multiple copies of the stored proc get compiled into procedure cache memory. Here's a quote from the Performance and Tuning Series: Basics -> Memory Use and Performance -> Procedure Cache document page:

"If no plan is in memory, or if all copies are in use, the query tree for the procedure is read from the sysprocedures table. The query tree is then optimized, using the parameters provided to the procedure, and placed at the MRU end of the chain, and execution begins. Plans at the LRU end of the page chain that are not in use are aged out of the cache."

So my question is, if you have a request to execute a stored proc , and that requires compiling (right word?) the proc into memory, it's going to use the "parameters provided to the procedure" to decide what to do. I think it would be possible for certain parameters to cause the stored proc to be compiled differently, causing performance differences when the cached/compiled memory copy gets reused.

Also, when you say there's a large variation in execution times for different plans for the same stored proc, could there be variation based on the number of rows searched and/or returned in different executions? (ie, for different calling parameters). Can you tell if any of the bad stored proc plans contain erroneous table scans?

I can't seem to find a way to display the query plan for a cached compiled copy of a stored procedure. The monCachedProcedures table has a PlanID field, but there doesn't seem to be any call for displaying the query plan for ObjectID & PlanID (there is for statement cache stuff, but not stored procs)

Ben near Washington DC

[sladebe]

Re: display the query plan for a stored proc in procedure cache?

It looks like it should be possible to use the PlanID from monCachedProcedures to call the show_plan() function in 16.0 SP03 PL06 and higher. The calling syntax is:

show_plan(<DBID>, <procedure_ID>, <plan_ID> [, <statement_number>])

https://help.sap.com/viewer/791c41982ee345a19c4ec4b774222c4f/16.0.3.8/en-US/adca355a9d774e89a6850782...