Showing results for 
Search instead for 
Did you mean: 

How to monitor stored proc recompile time at a server level (mon table?)

Active Participant
0 Kudos

I occasionally see a process with sysprocesses.cmd == "COMMAND". We're not using the statement cache (because we had replication problems with it where a simple update statement scans a big table, hanging replication). We *do* have "procedure deferred compilation" turned on (for when we create schemas from ddlgen output, it let's all objects get created successfully).

I wanted to monitor what percentage of the time ASE spends compiling statements and/or stored procs. I can't seem to find a mon table to do it. Any suggestions?

Thanks in advance
Ben Slade

View Entire Topic
Active Contributor

The MDA tables monProcessStatement, monProcessActivity, and monSysStatement have a column "QueryOptimizationTime" that may be useful.

Active Participant
0 Kudos

Interesting. Looking at the online docs, I see QueryOptimizationTime is supposed to be in the monProcessActivity table in ASE 16.0 SP03 (CR 800312 in the ASE release notes), but that doesn't seem to be the case (at least for my SP03, EBF 27167 on 64 bit Linux). I have a SP03 PL02 ASE server, but it's not there either.

So I bet it shows up in a slightly later release. I look forward to using it someday. Thanks for the info.