[ новости /+++ | форум | теги | ]

Archive-name: databases/sybase-faq/part10
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/01/17
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

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

Platform Specific Issues - Solaris General Troubleshooting ASE FAQ

-- 

- David Alex Lamb, one of the *.answers moderators
[email protected]

Партнёры:

Хостинг: