ftp.pasteur.org/FAQ//ftp-pasteur-org-FAQ.zip/FAQ/databases/sybase-faq/part10

Internet Message Format | 2004-04-21 | 12.0 KB

Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
Message-ID: <databases/sybase-faq/[e-mail]>
Supersedes: <databases/sybase-faq/[e-mail]>
Expires: 2 Aug 2004 13:43:10 GMT
References: <databases/sybase-faq/[e-mail]>
X-Last-Updated: 2003/03/02
From: [e-mail] (David Owen)
Newsgroups: comp.databases.sybase,comp.answers,news.answers
Subject: Sybase FAQ: 10/19 - ASE Admin (7 of 7)
Reply-To: [e-mail] (David Owen)
Followup-To: comp.databases.sybase
Distribution: world
Organization: Midsomer Consultants Inc.
Approved: [e-mail]
Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
Originator: [e-mail]
Date: 20 Apr 2004 13:45:07 GMT
Lines: 291
NNTP-Posting-Host: penguin-lust.mit.edu
X-Trace: 1082468707 senator-bedfellow.mit.edu 568 18.181.0.29
Xref: senator-bedfellow.mit.edu comp.databases.sybase:106208 comp.answers:56954 news.answers:270294

Archive-name: databases/sybase-faq/part10
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
   A how-to-find-the-FAQ article is posted on the intervening months.

1.5.9: You and showplan output

-------------------------------------------------------------------------------

As recently pointed out in the Sybase-L list, the showplan information that was
here is terribly out of date. It was written back when the output from ASE and
MS SQL Server were identical. (To see just how differenet they have become,
have a look at the O'Reilly book "Transact-SQL Programming". It does a line for
line comparison.) The write up in the Performance and Tuning Guide is
excellent, and this section was doing nothing but causing problems.

If you do have a need for the original document, then it can be found here, but
it will no longer be considered part of the official FAQ.

Back to top

-------------------------------------------------------------------------------

1.5.10: Poor man's sp_sysmon

-------------------------------------------------------------------------------

This is needed for System 10 and Sybase 4.9.2 where there is no sp_sysmon
command available.

Fine tune the waitfor for your application. You may need TS Role -- see Q3.1.

use master
go
dbcc traceon(3604)
dbcc monitor ("clear", "all", "on")
waitfor delay "00:01:00"
dbcc monitor ("sample", "all", "on")
dbcc monitor ("select", "all", "on")
dbcc traceon(8399)
select field_name, group_name, value
  from sysmonitors
dbcc traceoff(8399)
go
dbcc traceoff(3604)
go

Back to top

-------------------------------------------------------------------------------

1.5.11: View MRU-LRU procedure cache chain

-------------------------------------------------------------------------------

dbcc procbuf gives a listing of the current contents of the procedure cache. By
repeating the process at intervals it is possible to watch procedures moving
down the MRU-LRU chain, and so to see how long procedures remain in cache. The
neat thing about this approach is that you can size your cache according to
what is actually happening, rather than relying on estimates based on
assumptions that may not hold on your site.

To run it:

dbcc traceon(3604)
go
dbcc procbuf
go

If you use sqsh it's a bit easier to grok the output:

dbcc traceon(3604);
dbcc procbuf;|fgrep <pbname>

See Q1.5.7 regarding procedure cache sizing.

Back to top

-------------------------------------------------------------------------------

1.5.12: Improving Text/Image Type Performance

-------------------------------------------------------------------------------

If you know that you are going to be using a text/insert column immediately,
insert the row setting the column to a non-null value.

There's a noticeable performance gain.

Unfortunately, text and image datatypes cannot be passed as parameters to
stored procedures. The address of the text or image location must be created
and returned where it is then manipulated by the calling code. This means that
transactions involving both text and image fields and stored procedures are not
atomic. However, the datatypes can still be declared as not null in the table
definition.

Given this example -

        create table key_n_text
        (
            key         int     not null,
            notes       text    not null
        )

This stored procedure can be used -

        create procedure sp_insert_key_n_text
            @key        int,
            @textptr    varbinary(16)   output
        as

        /*
        ** Generate a valid text pointer for WRITETEXT by inserting an
        ** empty string in the text field.
        */
        insert key_n_text
        (
            key,
            notes
        )
        values
        (
            @key,
            ""
        )

        select  @textptr = textptr(notes)
        from    key_n_text
        where   key      = @key

        return 0
        go

The return parameter is then used by the calling code to update the text field,
via the dbwritetext() function if using DB-Library for example.

Back to top

-------------------------------------------------------------------------------

Server Monitoring General Troubleshooting ASE FAQ

                               Server Monitoring



    1.6.1   What is Monitor Server and how do I configure it?
    1.6.2   OK, that was easy, how do I configure a client?

Platform Specific Issues - Solaris Performance and Tuning ASE FAQ

-------------------------------------------------------------------------------

1.6.1: How do I configure Monitor Server?

-------------------------------------------------------------------------------

Monitor Server is a separate server from the normal dataserver. Its purpose, as
the name suggests, is to monitor ASE. It uses internal counters to determine
what is happening. On its own, it does not actually do a lot. You need to hook
up a client of some sort in order to be able to view the results.

Configuration is easy. The Sybase documentation is very good on this one for
either Unix or NT. Rather than repeat myself, go to the Sybase web site and
check out the Monitor Server User's Guide. Obviously the link should take you
to the HTML edition of the book. There is also a PDF available. Look for
"monbook.pdf". If Sybase has skipped to ASE 99.9 and this link no longer works,
then you will have to go search the Sybase home pages.

Back to top

-------------------------------------------------------------------------------

1.6.2: OK, that was easy, how do I configure a client?

-------------------------------------------------------------------------------

I see that you like a challenge! Syase offer a Java client to view the output
from Monitor Server. It is accessible either standalone or via the Win32
edition of Sybase Central.

Standalone on NT/2000

I could not find anything about setting up the clients in the standard
documentation set. However, there is a small paper on it here (towards the
bottom). It does miss out a couple of important details, but is helpful for all
that.

I did not try too hard to get the 11.9.2 version running, since the 12.5
version will monitor 11.9 servers.

I do not have a boxed release of ASE 12.5 for NT, just the developers release.
This does not come with all of the necessary files. In order to run the Monitor
Client, you will need the PC Client CD that came with the boxed release. If all
you have is the developer's edition, you might be stuck. It would be worth
getting in touch with Sybase to see if they could ship you one. There is
probably a charge!

You will need to install the client software. If you have a release of ASE
already installed and running you might want to install this into a separate
area. I am not sure what files it includes and versions etc, but if you have
the space I recommend saving yourself some hassle. If you have an older edition
of ASE installed, the installation will ask if you want to overwrite two files,
mclib.dll and mchelp.dll, both of which should reside in your winnt/system32
directory. It is important that you accept both of the overwrites. The older
versions of these files do not seem to work.

Once installed, you will also need to spend some time playing with environment
variables. I have got 3 editions of ASE all running successfully on the one
machine (see Q1.3.9). I chose to have one user for each ASE instance, each with
their own local environment variables pointing to the relevant installation for
them, plus a generic account for my main user that I configured to use the
software installed from the client CD. I adjusted the variables so that each
user had their own set of variables and all of the installations worked OK.

Next, you need a copy of Java 1.1.8 installed. The client CD has a copy of JDK
1.1.8 in the "ASEP_Win32" directory. This is the one to go for, as I am sure
that it was the one that the Monitor Client was built with. I did try a version
from Sun's Java archive, but it failed.

Next, set up the JAVA_HOME environment variable. If you installed the JDK into
its default location, that will be C:\jdk1.1.8.

Check to ensure that your CLASSPATH is defined as (assuming that you installed
the client into C:\Sybase_Client):

C:\Sybase_Client\ASEP_Win32\monclass.zip;C:\Sybase_Client\ASEP_Win32\3pclass.zip;%JAVA_HOME%\lib\rt.jar

You may want to check that the files mclib.dll and mchelp.dll exist in your
winnt/system32 directory if you were not asked to replace them earlier. You may
also want to check that the defauly Java command is correct with java -version.
It should return

java version "1.1.8"

You should now be able to fire up the main window with:

java sybase.monclt.mcgui.procact.ProcActApp  12.5  sa "sa_password" en 0 sccsen.hlp

(The paper says that you should use "jre" and not "java". That gives me a
cosistent "Class not found...". I do not know why.)

You should be presented with a screen like this, which will fill with process
information after 10 seconds. Choose "File->Monitors >" to choose a monitoring
graph. Here are a couple of screenshots from various monitors:

  * Performance Summary
  * Performance Trends...
  * Process Current SQL Statement
  * Network Activity

Obviously, all of this can be set from the command line or via a batch script.
Shove the following into a file called mon.bat and invoke using mon ASE_SERVER
MON_SERVER PASSWORD

  SET JAVA_HOME=C:\JDK1.1.8
  SET PATH=%JAVA_HOME%\bin;%PATH%
  SET CLASSPATH=C:\SYBASE_CLIENT\ASEP_Win32\monclass.zip;C:\SYBASE_CLIENT\ASEP_Win32\3pclass.zip
  java sybase.monclt.mcgui.procact.ProcActApp %1 12.5 %2 sa "%3" en 0 scssen.hlp

Obviously, you will need to replace "C:\SYBASE_CLIENT" with the correct string
pointing to your Sybase ASE installation.

Via Sybase Central on NT/2000

You will need to have installed the version of the Java Development Kit that
comes with your CD, as per standalone installation. Next, create a shortcut to
the file %SYBASE%\Sybase Central 3.2\win32\scview.exe. This is the Win 32
version of Sybase Central. Next, edit the shortcut's properties (right click on
the shortcut and select "Properties"). Now, edit the "Start In" field to be "C:
\jdk1.1.8\bin", assuming that you installed the JDK into its default location.

Now, assuming that both the ASE and Monitor servers are running, start up this
version of Sybase Central. Unlike the Java edition, all of the Servers from the
SQL.INI file are displayed at startup. Right click on the ASE server you wish
to monitor and select "Properties". This brings up a triple tabbed screen.
Select the "Monitor Server" tab and use the drop down to select the appropriate
monitor server. Now, connect to the ASE server and you will see another level
in the options tree called "Monitors". Click on it and you should see a
complete list of the monitors you can choose from. Double clicking on one
should display it. The output is exactly the same as for standalone operation.

Back to top

-------------------------------------------------------------------------------

Platform Specific Issues - Solaris Performance and Tuning ASE FAQ