Re: Performance issue on 15.0.2 versus 12.5.3

Sherlock, Kevin

unread,

May 9, 2008, 5:04:59 PM5/9/08

to

1. You should go to ESD4 on ASE 15.0.2. There are some important optimizer
CR's in this ESD.
2. What is the optimization goal for your 15.x server?
3. You should read: http://www.sybase.com/detail?id=1056206
and http://www.sybase.com/detail?id=1056243

"Steve" <st...@nospam.com> wrote in message news:48249cab@forums-1-dub...
> When I run the sql in the attached (SQL_to_tune.txt ) on a 12.5.3 server
> vs a 15.0.2 server, the performance is drastically different. I have
> also attached the results files from each server with the showplan,
> statistics io and statistics time turned on. I noted the start and end
> times for the SQL to complete and on the 12.5.3 server it takes about 200
> milliseconds while on the 15.0.2 server it takes over 2000 milliseconds.
> Any ideas?
>
> Server versions are listed below:
>
> 12.5.3 --> Adaptive Server Enterprise/12.5.3/EBF 12873
> ESD#4/P/HP9000-879/HP-UX 11.0/ase1253/1923/64-bit/FBO/Thu Sep 8 16:33:15
> 2005
>
>
>
> 15.0.2 --> Adaptive Server Enterprise/15.0.2/EBF 14329/P/HP9000-879/HP-UX
> 11.11/ase1502/2486/64-bit/FBO/Thu May 24 21:14:31 2007
>
>
> Thanks..
>
> Steve
>
>

Sherlock, Kevin

unread,

May 12, 2008, 11:24:50 AM5/12/08

to

Please post the output of the following from both 12.5.3, and 15.0.2:

sp_configure 'memory'
sp_helpcache

"Steve" <st...@nospam.com> wrote in message news:48285b8a@forums-1-dub...
> Responses:
>
> 1. Apparently our DBA group is trying to intstall this ESD and is having
> problems. They have an open case with Sybase on the issue ( 11441168 )
>
> 2. allrow_mix is the optimization goal ( I am guessing our dba's just took
> the default ). Any suggestions here?
>
> 3. Currently looking at these documents..
>
> Additional questions:
>
> 1) It appears to be reading from disk in 15.0.2? Any idea as to why
> this is occurring. I am attaching our sp_configure settings from the
> 15.0.2 server.
>
> 2) Why the compile and parse time? This wasn't occuring in 12.5.3
>
> Thanks Kevin
>
> "Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message
> news:4824bc7b$1@forums-1-dub...

Sherlock, Kevin

unread,

May 12, 2008, 4:28:18 PM5/12/08

to

Here is the IO summary for 12.5.3:

Table Name Scans LReads LR/Scan PReads

--------------- -----   ------ ------- ------
fund              1197    2476    2.07     0
invoice_account   1195    5206    4.36     0
pending_invoice   2340    9876    4.22     0

===============
{TOTALS} 4732 17558 3.71 0

Here is the IO summary for 15.0.2:

Table Name Scans LReads LR/Scan PReads

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

fund                 3   96 32.00     14
invoice_account   1195   5112   4.28      0
pending_invoice      6   3536   589.33   1320

===============
{TOTALS} 1204 8744 7.26 1334

So as you can see , 15.0.2 resulted in many fewer (almost 1/2) logical reads, but 1,334 additional physical reads. That's probably where your performance difference is.

The difference lies in the query plans generated for line 25, and line 61/62:

UPDATE pending_invoice_v1
SET fund_id = f.fundcode_fixed
from pending_invoice_v1 pi, fund..fund_v3 f
WHERE @invoice_id = pi.invoice_id and
convert(char(10),f.cusip) = pi.input_fund

UPDATE pending_invoice_v1
SET fund_id = f.fundcode_fixed
from pending_invoice_v1 pi, fund..fund_v3 f
WHERE @invoice_id = pi.invoice_id and
f.fundcode_fixed = pi.input_fund

The plans below indicate that line 25 in 15.0.2 during the merge join uses only the first key in the pending_invoice index which is invoice_id. The plan for line 61/62 in 15.0.2 scans the "fund_i1" index, where the 12.5.3 optimizer uses the "fundcode_fixed" colum to key position.

These are the 12.5.3 NLJ plans for the above two statements:

=========================

QUERY PLAN FOR STATEMENT 9 (at line 25).

    STEP 1
        The type of query is UPDATE.
        The update mode is deferred.

        FROM TABLE
            fund
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            pending_invoice
        Nested iteration.
        Index : pending_invoice_i1
        Forward scan.
        Positioning by key.
        Keys are:
            invoice_id ASC
            input_fund ASC
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            pending_invoice
        Using I/O Size 2 Kbytes for data pages.

QUERY PLAN FOR STATEMENT 21 (at line 61).

    STEP 1
        The type of query is UPDATE.
        The update mode is deferred_varcol.

        FROM TABLE
            pending_invoice
        Nested iteration.
        Index : pending_invoice_i1
        Forward scan.
        Positioning by key.
        Keys are:
            invoice_id ASC
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            fund
        EXISTS TABLE : nested iteration.
        Index : fund_i5
        Forward scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            fundcode_fixed ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        TO TABLE
            pending_invoice
        Using I/O Size 2 Kbytes for data pages.

These are the 15.0.2 MJ plans:

=========================

QUERY PLAN FOR STATEMENT 9 (at line 25).
STEP 1
The type of query is UPDATE.

6 operator(s) under root

QUERY PLAN FOR STATEMENT 21 (at line 62).
STEP 1
The type of query is UPDATE.

5 operator(s) under root

"Steve" <st...@nospam.com> wrote in message news:48286b58$1@forums-1-dub...

> See attached files.
>
> Thanks Kevin!
>
> "Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message
> news:48286142$1@forums-1-dub...

st...@nospam.com> wrote in message news:48285b8a@forums-1-dub...

kevin.s...@teamsybase.com> wrote in message
>>> news:4824bc7b$1@forums-1-dub...

http://www.sybase.com/detail?id=1056206

Steve

unread,

May 12, 2008, 11:31:03 PM5/12/08

to

Thanks Kevin. So back to your original request for the sp_configure 'memory' and sp_helpcache. Did you see anything from this output that could cause this issue?

What about 16K vs 2K pools?

Could bad statistics cause this?

Can you think of anything that I could try?

I know you mentioned the ESD#4, but we may be a few days away from getting that installed.

Thanks again for you insight.

"Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message news:4828a862@forums-1-dub...

Sherlock, Kevin

unread,

May 13, 2008, 10:35:09 AM5/13/08

to

10M default data cache seems mighty small to me, but maybe your tables just aren't very large. You have about 200M available to configure (max memory - logical memory). You should consider increasing your data cache as 10M just isn't very big.

Statistics are important in ASE 15.0.2. Particularly stats on non-leading colums of indexes. I would try "update indext statistics" on all tables as a baseline to go with. Merge Joins seem to be the biggest plan difference, and if those are giving you problems, either disable them (set plan merge_join off), or go with a different optimization goal of "allrows_oltp".

First things first though - Get to ESD4 asap. You are running with 15.0.2/P which needs to be upgraded. The application of ESD4 to an existing 15.0.2 server should not be giving you problems. Is there a specific issue that you are having with this?

"Steve" <st...@nospam.com> wrote in message news:48290b77$1@forums-1-dub...

Steve

unread,

May 13, 2008, 11:31:10 AM5/13/08

to

Thanks for the info Kevin. The quality and turnaround of your feedback has been awesome. I will work with the dba's on the 10M default cache. I did do update statistics, update index statistics, etc, none of which did anything. I dropped and recreated the indexes, but that didn't help either. I tried many different SQL/index modification solutions and haven't been able to resolve the issue. I will try your optimization suggestions. I tried allrows_mix, allrows_dss but didn't try allrows_oltp, nor did I try turning the merge_join off independently, so I will try that next.

As far as the upgrade goes, I looked at the ticket, and I believe there is some dba inexperience going on with installing an upgrade, and eventually they will get it installed. I will let you know the resolution once we arrive at it. Thanks again for all your help. Much appreciated.. One last question:

1) I noticed that there are two ESDs for 15.0.2 on an HP PA RISC system. After further research, our dbas are installing ESD #2 not ESD #4. They are risk adverse and don't want to install the most current patch as they say they have been "bit" in the past. What is your thought/experience here? Does ESD #2 have anything to offer with respect to this issue?

Thanks...

"Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message news:4829a71d@forums-1-dub...

Sherlock, Kevin

unread,

May 13, 2008, 11:42:40 AM5/13/08

to

My thoughts are to go with ESD4. There are many optimizer fixes in that ESD, and it's not that much newer than ESD2.

Note: optimization goals can be set at the query/session level, as well as the server level. Reference the whitepapers I listed earlier in this thread.

I would be curious to know if the IO profile of the 15.0.2 query is ALWAYS giving you the performance hit. That is, I saw lots of physical io in your output, but if you run the proc a second time, do those go away (and therefore the proc runs faster)? Or are you basically suffering from the MERGE JOIN choice by the optimizer and processing too many rows in the "pending_invoice" table?

"Steve" <st...@nospam.com> wrote in message news:4829b43e$1@forums-1-dub...

Steve

unread,

May 13, 2008, 12:10:23 PM5/13/08

to

Every time I run it, I get the hit. That is why I was interested in the data/procedure cache. I thought that the query and/or data would be cached after running the query the first time. The test version of the pending invoice table only has about 37000 rows, and the fund table that I am joining to has about 1200 rows.

"Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message news:4829b6f0@forums-1-dub...

Sherlock, Kevin

unread,

May 13, 2008, 2:34:08 PM5/13/08

to

Try making your 10M data cache a 100M data cache, and increase your procedure cache as well.

"Steve" <st...@nospam.com> wrote in message news:4829bd6f$1@forums-1-dub...

Cory Sane

unread,

May 13, 2008, 10:02:13 PM5/13/08

to

I totally agree with Kevin,

You need to give memory to the default data cache. The 10MB sounds like a
ASE default value. It looked like the procedure cache was 20MB which is
larger than default cache.

--
Cory Sane
[Member of TeamSybase]
Certified Sybase Associate DBA for ASE 15.0
not a Sybase Inc. employee

"Sherlock, Kevin" <kevin.s...@teamsybase.com> wrote in message

news:4829df20@forums-1-dub...