Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

track spid, can i?

2 posts in General Discussion Last posting was on 2011-07-22 21:56:26.0Z
jobless Posted on 2011-07-22 20:33:41.0Z
Sender: 6459.4e288ac5.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: track spid, can i?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e29dea5.d7d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Jul 2011 13:33:41 -0700
X-Trace: forums-1-dub 1311366821 10.22.241.41 (22 Jul 2011 13:33:41 -0700)
X-Original-Trace: 22 Jul 2011 13:33:41 -0700, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30366
Article PK: 72545

Is there a way to track following:
For a given spid:
- Cmd Executed
- Starttime of Cmd
- Endtime of Cmd

I checked monProcess, monProcessesActivity and other
monProcess* tables, only 1 might be useful and gives
starttime (monProcessStatement.StartTime).

Is there a way to trace above 3 attributes for a spid?

Simple scenerio: Say there is application product which
connects to Sybase ASE server and does stuff. Support team
complains the application is slow.

As a DBA I could check many things. But can I generate a
trace and prove the commands completes in ms or seconds ?

If this is possible then I can get the monkey off the DBA
shoulder. They can go around kicking OS, Network and
application team.


"Mark A. Parsons" <iron_horse Posted on 2011-07-22 21:56:26.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: track spid, can i?
References: <4e29dea5.d7d.1681692777@sybase.com>
In-Reply-To: <4e29dea5.d7d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e29f20a$1@forums-1-dub>
Date: 22 Jul 2011 14:56:26 -0700
X-Trace: forums-1-dub 1311371786 10.22.241.152 (22 Jul 2011 14:56:26 -0700)
X-Original-Trace: 22 Jul 2011 14:56:26 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30367
Article PK: 72546

monSysStatement gives you start time, cpu time and wait time (ie, end time ~= start time + cpu time + wait time)

Of course it's not *that* easy to answer your original question about tracking an application ..

1 - monSysStatement is a pipe table so you have to continually pole it looking for new records

2 - the 'slow' application could be triggering thousands of statements, so you'll want to capture as many as possible, ymmv

3 - you'll have to know what SPID(s) you're looking for (ie, how to match a given application login with the associated
records in monSysStatement)

You could also take snapshots of monProcessActivity and monWaits to give you a high-level view of a process's cpu, disk,
waits, logging, temp table, etc usage.

monProcessSQLText (if spid is still running), or monSysSQLText (if spid is no longer running), may be able to provide
you with the top-level database call; though I've found with ASE 15.x that if the spid triggers some exec immediate
(exec()) calls, then those calls tend to overwrite what's in monProcessSQLText, ymmv.

You could also look at implementing some level of application tracing (if you're running ASE 15.0.2+; see login triggers
and application tracking).

On 07/22/2011 16:33, jobless wrote:
> Is there a way to track following:
> For a given spid:
> - Cmd Executed
> - Starttime of Cmd
> - Endtime of Cmd
>
> I checked monProcess, monProcessesActivity and other
> monProcess* tables, only 1 might be useful and gives
> starttime (monProcessStatement.StartTime).
>
> Is there a way to trace above 3 attributes for a spid?
>
> Simple scenerio: Say there is application product which
> connects to Sybase ASE server and does stuff. Support team
> complains the application is slow.
>
> As a DBA I could check many things. But can I generate a
> trace and prove the commands completes in ms or seconds ?
>
> If this is possible then I can get the monkey off the DBA
> shoulder. They can go around kicking OS, Network and
> application team.