Enable sessions tracing in sybase
cch...@gmail.com
I am not good in SyBase, just recently took over to support the
database. I am coming more from the Oracle and MSSQL background.
Anyway, we have a performance issues with Sybase 12.5.0.3. And
application wanted us to generate a trace files whenever they hit into
performance issues.
I have checked with my peers, and all was told that to run sp_sysmon
& sp_showplan
I think this is not sufficient. I understand that there are MDA tables
to query but I am not familiar the usage. It seems it only tells the
current session connected to, but I need to monitor other sessions
instead. Can that be done?
Alternatively, I searched the newsgroup and discover can use
dbcc pss
But this require the "sybase_ts_role" to be assigned. What's the
impact of assigning this role? Because it's a production system, I do
not want to mess it around.
Can highlight please?
My SyBase version:
Adaptive Server Enterprise/12.5.0.3/EBF 11332 ESD#3/P/Sun_svr4/OS 5.8/
rel12503/1933/64-bit/FBO/Fri Aug 1 07:31:48 2003
Rob Verschoor
especially when you want to use the MDA tables (indeed, that's recommended).
Get ASE 12.5.4.
As for your question: forget about sp_sysmon at this stage, and use the MDA
table instead. Indeed you can trace activity in other sessions with these.
For some pointers and examples, check out www.sypron.nl/mda .
There's no inherent risk itself of assigning sybase_ts_role -- this allows
you to run dbcc commands. Of course, that allows you to potentially major
damage if you don't know what you're doing, so that's where the risk is.
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
<cch...@gmail.com> wrote in message
news:1192521967.0...@e9g2000prf.googlegroups.com...
Carl Kayser
"Rob Verschoor" <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:471488b2$3@forums-1-dub...
> First, yoou badly need to upgrade your ASE version. 12.5.0.3 is *very*
> old, especially when you want to use the MDA tables (indeed, that's
> recommended). Get ASE 12.5.4.
> As for your question: forget about sp_sysmon at this stage, and use the
> MDA table instead. Indeed you can trace activity in other sessions with
> these. For some pointers and examples, check out www.sypron.nl/mda .
>
> There's no inherent risk itself of assigning sybase_ts_role -- this allows
> you to run dbcc commands. Of course, that allows you to potentially major
> damage if you don't know what you're doing, so that's where the risk is.
>
> HTH,
>
> Rob V.
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
>
(SNIP)
Interesting that you do not mention the trace facility in 15.0.2. Perhaps
because the migration from 12.0.x would be a substantial leap?
Rob Verschoor
"Carl Kayser" <kays...@bls.gov> wrote in message
news:4714933a$1@forums-1-dub...
Indeed -- it sounds like the OP would prefer less complexity at this point.
Rob V.
Mark A. Parsons
> application wanted us to generate a trace files whenever they hit into
> performance issues.
'performance issues' come in all shapes and sizes ... hardware configuration issues, network configuration issues,
Sybase/dataserver configuration issues, table/index design issues, query design issues, out-dated statistics issues,
application design issues, etc, etc, etc.
I know, I know, I know ... "Duh!" right? The point is that depending on the type of performance issue(s) you will need
to look at using different monitoring tools/capabilities. ("Duh!" again?)
------------
Hardware and network monitoring is pretty much outside the realm of Sybase monitoring tools. While some Sybase
monitoring tools may hint at a hardware/network issue, that's all it is ... a hint. In this case you'll have to rely on
your system/network admins to help out.
------------
Sybase provides a stored proc called sp_sysmon which is pretty good at pointing out dataserver-level issues, eg,
dataserver configuration issues, data cache configuration issues, possible bottlenecks at the hardware/network level,
etc. Sometimes you can use the sp_sysmon information to come up with a configuration change that helps with
performance; sometimes the sp_sysmon information can point you to a specific area where you can concentrate your
monitoring efforts.
sp_sysmon is a fairly lightweight process which does not adversely affect dataserver performance.
------------
Sybase provides a separate set of binaries called the Monitor Server (aka monserver) and Historical Server (aka
histserver). The monserver attaches to the the dataserver memory and pulls information/stats directly from the
dataserver memory; the downside to this tool is that there's no usable human interface, ie, you need another program to
provide a human interface. histserver provides the human interface into the monserver; with histserver you can create a
series of commands which are used to periodically pull data from the monserver. With monserver/histserver you can pull
quite a bit of information from the dataserver ... data cache stats, stored proc execution stats, table/index stats,
disk IO stats, cpu stats, network stats, and on and on and on ...
The MDA tables were introduced with ASE 12.5.0.3 and provide a SQL interface to much of the same information that you
can obtain from the monserver/histserver solution. While there is a good bit of overlap between the
monserver/histserver and MDA solutions, each solution (monserver/histserver vs MDA) does have some unique capabilities.
MDA tables come in a few different flavors ... those that show current activity (eg, who's logged in right this minute,
what SQL text is being executed right this minute) ... those that show recent/historical activity (eg, SQL text and
query plans from the last XX minutes) ... and those that show summary data since the dataserver was last booted (eg,
total cpu/disk stats, summary stats for tables/indexes).
Depending on the level of monitoring you may find that monserver/histserver and MDA tables can add a considerable load
to your dataserver. Point being that if you turn on all the bells and whistles you will a) get a *LOT* of information
that you'll have to wade through and b) you'll degrade the performance on your dataserver (not as bad as it sounds if
the monitoring results help you improve overall dataserver performance).
------------
Another option you have is to use SQL to access information from a handful of system tables (eg, master..sysprocesses,
master..syslocks, master..syslogshold, master..systransactions, sysobjects).
In this case you basically need to know what information you're looking for, write some SQL to pull the information you
want, and then put the SQL into a looping construct which periodically (eg, every 10 seconds) pulls the desired info.
The data can then be dumped to tables or (my preference) dumped to ascii text files out in the OS.
Most DBA's that have been around for awhile have implemented their own custom monitoring tools.
One example would be a set of queries that periodically pull information about currently active dataserver processes.
'information' could include the name of a currently executing stored proc, the line number in the stored proc that is
currently executing, current physical IO counts, number/type of locks and on what objects, how long a transaction has
been open, blocked and blocking processes (and how long blocked), host name/process of the client application, login
associated with a process, incoming network packet size, process status, SQL text currently being executed, current
query plans, etc., etc., etc.
This type of monitoring comes in handy when answering questions like ... what was running yesterday afternoon between
14:00 and 16:00? ... why did last night's XYZ batch job run 2 hours longer than normal? ... stored procedure ABC runs
for 20 minutes, where is it spending most of it's time? ... users were complaining about slow performance earlier in
the day, what was happening on the dataserver during said time frame?
You mention sp_showplan ... this is a stored proc which shows you the query plan of a currently running query.
Invocation of sp_showplan can be rolled into a custom monitoring script.
------------
Another monitoring option is to buy a 3rd party tool (eg, Patrol, Surveillance, etc).
All 3rd party tools basically put a nice wrapper around the various monitoring capabilities already mentioned above.
> I have checked with my peers, and all was told that to run sp_sysmon
> & sp_showplan
>
> I think this is not sufficient. I understand that there are MDA tables
> to query but I am not familiar the usage. It seems it only tells the
> current session connected to, but I need to monitor other sessions
> instead. Can that be done?
All of the previously mentioned monitoring 'tools' can monitor 'other sessions' ... you just have to decide what exactly
you're looking for and then use the appropriate tool.
As for deciding *what* to look for ... this comes back to a question of *what* kind of performance issues you're having.
Do you have a batch process that's running long?
Do you have a particular application process/function that's taking long to run?
Do you have periods of heavy blocking and/or deadlocking?
Is dataserver performance sluggish all of the time or just during certain activities?
Has performance degraded (noticeably) over a period of time?
Perhaps you have a hunch that performance isn't as great as it could be, but you're not sure if there actually *is* a
performance issue?
> Alternatively, I searched the newsgroup and discover can use
> dbcc pss
'dbcc pss' will generate a *LOT* of mostly-unreadable output. Unless you've got a really good understanding of Sybase
internals you'll find that most of the pss output is plain gibberish. You'll spend a *LOT* of time trying to decode the
pss output ... just to find that it provides you with little useful tuning information. (I'm not saying the pss output
is completely useless, but what info you could glean from the pss output is more readily available via other means.)
> But this require the "sybase_ts_role" to be assigned. What's the
> impact of assigning this role? Because it's a production system, I do
> not want to mess it around.
As Rob's already mentioned, there's no downside to assigning the sybase_ts_role role (or any other system-level role) to
someone's login.
The potential downside comes from how you use the permissions that come with the various system-level roles.
Manish Negandhi
should not run them together.You need to install MDA tables if they
are not installed already using installmontables script located in the
$SYBASE/ASE-12_5/scripts directory.MDA tables give more detailed
information to monitor the server performance and are not session
specific.You can also try to find out performance bottlenecks using
sp_sysmon. You can execute sp_sysmon during the time range when you
experience performance problems and save the output to flat file to
analyze it in future. You can post the sysmon output to this group if
you are not sure about what the result means
sybase_ts_role means sybase techsupport roles.This role is required to
execute few of the dbcc commands
thanks
Manish