Is there a way to find out how many times a stored procedure was invoked since last DB start

Question

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably in Sybase) how many times a give stored proc was invoked so that we can gain some confidence in removing procs .that were never called recently.

Answer 1

Sybase doesn't store execution counts for stored procedures, but you can use Sybase Central's activity monitors to track SP usage (but only from when you start the session). If you don't like Sybase Central, the other option is to add a line to each SP that increments a counter (i.e. create a table to keep track).

Answer 2

If you can take the hit on performance, enable tracing for a "typical" session (set tracefile "/some/path/" for some_spid), and using set show_sqltext on you can see all the SQL executed. Then you will need to parse the output looking for stored procedures. Of course this approach cannot give you 100% confidence (e.g. maybe you have a batch job that runs once per year) but it's a start.