Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Participant
0 Kudos
I wanted to analyze some replication slowness for data being replicated to an ASE server.

I was hoping to use monCachedStatement to look at write activity via the various "PIO" (aka Physical I/O) columns.   But it turns out that "PIO" means read physical I/O (although it doesn't say "read" in the manual")

Surprisingly, there's no write activity monitoring in monCachedStatement.

But you can play a trick.  It turns out that cached statements show up as "lightweight stored procedures" in the monCachedProcedures table with procedure names like *ss1719010174_1175458873ss*, where 1719010174 (in this case) is the ObjectId of the lightweight stored proc and corresponds to the SSQLID in the monCachedStatement.

Once you know this, it's possible to write a fancy-dancy query to show execute stats for both stored procs and cached statements, including write activity:
select top 50
ExecTime=CP.ExecutionTime, ExecCnt=CP.ExecutionCount, TimePerExec=CP.ExecutionTime/CP.ExecutionCount,
CP.CPUTime, PhysReads=CP.PhysicalReads, LogReads=CP.LogicalReads, PhysWrites=CP.PhysicalWrites,
DBName=case when ObjectName like "*ss[0-9][0-9]%"
then substring(CS.DBName,1,16)
else substring(CP.DBName,1,16)
CachedProcOrStatement=case when CP.ObjectName like "*ss[0-9][0-9]%"
then substring(show_cached_text(CP.ObjectID),1,110)
else substring(CP.ObjectName,1,110)
from monCachedProcedures CP
left outer join monCachedStatement CS on CP.ObjectID=CS.SSQLID
order by CP.ExecutionTime desc

Producing output like:
 ExecTime    ExecCnt     TimePerExec CPUTime     PhysReads   LogReads    PhysWrites  DBName           CachedProcOrStatement
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------- -----------------------------------------------------------------------------------------
1040412 928495 1 9771 0 0 6159 mydb1 insert into ThisTable (f1, f2) values (@@@V0_INT, @@@V1_INT)(@@@V0_INT INT, @@@V1_INT INT)
850124 180026 4 10220 0 0 0 mydb2 insert into ThatTable ...snip...
645990 121866 5 36531 0 0 16248 mydb3 insert into WhatTable ...snip...

If you have longer database names, increase the length of the substring commands, and so on.

It's too bad that monCachedStatement didn't include PhysicalWrites.  It would be useful in situations like this.

After using this query in production, it turns out that statements like update and delete don't show a lot of write activity.   Maybe that's because modified pages are actually written later by a checkpoint operation.

In any case, this is still a useful "go to" query for getting a summary of the most active stored procedures and/or SQL statements in your ASE server in a single query.

Labels in this area