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.

same SQL 2 slightly diff showplans

3 posts in Performance and Tuning Last posting was on 2012-09-12 16:34:33.0Z
Isabella Posted on 2012-09-06 20:36:24.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: same SQL 2 slightly diff showplans
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: <50490948$1@forums-1-dub>
Date: 6 Sep 2012 13:36:24 -0700
X-Trace: forums-1-dub 1346963784 172.20.134.152 (6 Sep 2012 13:36:24 -0700)
X-Original-Trace: 6 Sep 2012 13:36:24 -0700, vip152.sybase.com
Lines: 101
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13248
Article PK: 91748

I'm having a issues with an update SQL will take to long time to run in
one of the ASE, both ASE same version,same data , bellow are the
showplans, in the ASE with LRU show plan the SQL will run in 2 min , in
ASE using MRU will take hours??

ASE1 :Adaptive Server Enterprise/15.5/EBF 19399 SMP
ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
02:45:22 2011

QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Deferred Parallel Mode


STEP 1
The type of query is UPDATE.

3 operator(s) under root

|ROOT:EMIT Operator (VA = 3)

|
| |UPDATE Operator (VA = 2)
| | The update mode is direct.
| |
| | |TOP Operator (VA = 1)
| | | Top Limit: 500000
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | Node
| | | | 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.
| |
| | TO TABLE
| | Node
| | Using I/O Size 2 Kbytes for data pages.


Total estimated I/O cost for statement 2 (at line 1): 24904693.
****************************************************************
ASE2:Adaptive Server Enterprise/15.5/EBF 19399 SMP
ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
02:45:22 2011

update top 500000 Node set foo = 0 where foo is null;

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.
Executing a newly cached statement (SSQL_ID = 372142043).

Total estimated I/O cost for statement 1 (at line 1): 0.


QUERY PLAN FOR STATEMENT 1 (at line 0).


STEP 1
The type of query is DECLARE.

Total estimated I/O cost for statement 1 (at line 0): 0.


QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Deferred Parallel Mode


STEP 1
The type of query is UPDATE.

3 operator(s) under root

|ROOT:EMIT Operator (VA = 3)
|
| |UPDATE Operator (VA = 2)
| | The update mode is direct.
| |
| | |TOP Operator (VA = 1)
| | | Top Limit: 500000
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | Node
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With MRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | Node
| | Using I/O Size 2 Kbytes for data pages.


Total estimated I/O cost for statement 2 (at line 1): 29357923.


Isabella Posted on 2012-09-12 16:34:33.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: same SQL 2 slightly diff showplans
References: <50490948$1@forums-1-dub>
In-Reply-To: <50490948$1@forums-1-dub>
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: <5050b999$1@forums-1-dub>
Date: 12 Sep 2012 09:34:33 -0700
X-Trace: forums-1-dub 1347467673 172.20.134.152 (12 Sep 2012 09:34:33 -0700)
X-Original-Trace: 12 Sep 2012 09:34:33 -0700, vip152.sybase.com
Lines: 169
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13254
Article PK: 91747

Hi Mark,



Thank you for info , I still believe there is something to do with
cache the ASE using MRU had a lots of cache cfg dault data cache 107 GB
, see bellow cache config but the exec time si very slow . The other ASE
with 32 GB RAM is using LRU in as cache strategy and same SQL completes
in 3 minutes, the first one ASE after 2 1/2 the SQL still runs and I
have to kill it .

here are the caches from slowest ASE :
Cache Name Status Type Config Value Run Value
------------------ -------- -------- -------------- ------------
default data cache Active Default 107520.00 Mb 107520.00 Mb
log_cache Active Log Only 5120.00 Mb 5120.00 Mb
tempdb_cache Active Mixed 10240.00 Mb 10240.00 Mb
tempdb_sa_cache Active Mixed 10240.00 Mb 10240.00 Mb
------------ ------------
Total 133120 Mb 133120 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 107520.00 Mb, Run Size: 107520.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 16, Run Partition: 16

(1 row affected)
IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 1572864 Kb 31744.00 Mb 31744.00 Mb 10
16 Kb 757760 Kb 75776.00 Mb 75776.00 Mb 10
==========================================================================
Cache: log_cache, Status: Active, Type: Log Only
Config Size: 5120.00 Mb, Run Size: 5120.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 1, Run Partition: 1

IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 49152 Kb 5120.00 Mb 5120.00 Mb 10
==========================================================================
Cache: tempdb_cache, Status: Active, Type: Mixed
Config Size: 10240.00 Mb, Run Size: 10240.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 16, Run Partition: 16

IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 983040 Kb 2048.00 Mb 2048.00 Mb 10
4 Kb 838656 Kb 8192.00 Mb 8192.00 Mb 10
==========================================================================
Cache: tempdb_sa_cache, Status: Active, Type: Mixed
Config Size: 10240.00 Mb, Run Size: 10240.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 16, Run Partition: 16

IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 439072 Kb 2144.00 Mb 2144.00 Mb 10
4 Kb 983040 Kb 8096.00 Mb 8096.00 Mb 10

(return status = 0)
[63] cadc_mas.archive.1>

On 09/06/2012 01:36 PM, Isabella wrote:
>
> I'm having a issues with an update SQL will take to long time to run in
> one of the ASE, both ASE same version,same data , bellow are the
> showplans, in the ASE with LRU show plan the SQL will run in 2 min , in
> ASE using MRU will take hours??
>
> ASE1 :Adaptive Server Enterprise/15.5/EBF 19399 SMP
> ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
> 02:45:22 2011
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
> Optimized using Deferred Parallel Mode
>
>
> STEP 1
> The type of query is UPDATE.
>
> 3 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 3)
> |
> | |UPDATE Operator (VA = 2)
> | | The update mode is direct.
> | |
> | | |TOP Operator (VA = 1)
> | | | Top Limit: 500000
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | Node
> | | | | 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.
> | |
> | | TO TABLE
> | | Node
> | | Using I/O Size 2 Kbytes for data pages.
>
>
> Total estimated I/O cost for statement 2 (at line 1): 24904693.
> ****************************************************************
> ASE2:Adaptive Server Enterprise/15.5/EBF 19399 SMP
> ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
> 02:45:22 2011
>
> update top 500000 Node set foo = 0 where foo is null;
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a newly cached statement (SSQL_ID = 372142043).
>
> Total estimated I/O cost for statement 1 (at line 1): 0.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 0).
>
>
> STEP 1
> The type of query is DECLARE.
>
> Total estimated I/O cost for statement 1 (at line 0): 0.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
> Optimized using Deferred Parallel Mode
>
>
> STEP 1
> The type of query is UPDATE.
>
> 3 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 3)
> |
> | |UPDATE Operator (VA = 2)
> | | The update mode is direct.
> | |
> | | |TOP Operator (VA = 1)
> | | | Top Limit: 500000
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | Node
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
> | |
> | | TO TABLE
> | | Node
> | | Using I/O Size 2 Kbytes for data pages.
>
>
> Total estimated I/O cost for statement 2 (at line 1): 29357923.
>


"Mark A. Parsons" <iron_horse Posted on 2012-09-06 22:47:48.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.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.performance+tuning
Subject: Re: same SQL 2 slightly diff showplans
References: <50490948$1@forums-1-dub>
In-Reply-To: <50490948$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120811-1, 08/11/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50492814$1@forums-1-dub>
Date: 6 Sep 2012 15:47:48 -0700
X-Trace: forums-1-dub 1346971668 172.20.134.152 (6 Sep 2012 15:47:48 -0700)
X-Original-Trace: 6 Sep 2012 15:47:48 -0700, vip152.sybase.com
Lines: 135
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13249
Article PK: 155123

I'm guessing a cache (or wash area) that's too small, combined with other activity, is causing pages to be flushed from
cache before they can be accessed ... or perhaps some extended blocking of the UPDATE statement ... or some really slow
disks ... or heavy descriptor re-use ... or ... or ...

I'd suggest you gather the following for analysis ...

- complete results of sp_cacheconfig from both dataservers (you're looking for differences in cache, pool and wash area
sizes) for the cache where the table is being processed (if the table is not bound to a cache then you're interested in
the default data cache)

- running the updates with 'set statistics io,time on' enabled would be good

- grab snapshots of monProcessWaits and monProcessActivity after each query finishes (suggest starting with a brand new
connection into the database for each query run so that the monProcessXXXX tables start out empty)

- a couple snapshots of monProcessObject *while* the slow query is running would be of interest (provides point-in-time
snapshots of individual table/index io stats)

- run a 10- or 15-minute sp_sysmon session while the slow update is running; areas of interest would include cache
metrics, page splits, descriptor re-use ...

- check 'sp_monitorconfig "all"' for signs of descriptor (re)use issues

- is the table structure (locking scheme, indexes, datatypes/nullability, space management settings) exactly the same on
both dataservers? do the tables have comparable volumes of rows sitting on a comparable chunk of disk (ie, are their
allocated/used sizes similar)?

- if the table uses allpages locking (APL) ... is there a non-unique clustered index? (wondering if excessive overflow
pages could be causing excessive disk IOs)

- if the table uses datapages/datarows locking (DOL) ... what does optdiag and systabstats show in terms of (potential)
fragmentation, eg, is there a high volume of forwarded rows or empty leaf pages on for the copy of the table that takes
a long time to update?

- if you force the use of LRU for the 'slow' dataserver, do you a) see LRU in the showplan and b) does the query run
quickly? (eg, update node set foo = 0 from node (lru) where foo is null)

On 09/06/2012 14:36, Isabella wrote:
>
> I'm having a issues with an update SQL will take to long time to run in one of the ASE, both ASE same version,same data
> , bellow are the showplans, in the ASE with LRU show plan the SQL will run in 2 min , in ASE using MRU will take hours??
>
> ASE1 :Adaptive Server Enterprise/15.5/EBF 19399 SMP ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
> 02:45:22 2011
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
> Optimized using Deferred Parallel Mode
>
>
> STEP 1
> The type of query is UPDATE.
>
> 3 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 3)
> |
> | |UPDATE Operator (VA = 2)
> | | The update mode is direct.
> | |
> | | |TOP Operator (VA = 1)
> | | | Top Limit: 500000
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | Node
> | | | | 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.
> | |
> | | TO TABLE
> | | Node
> | | Using I/O Size 2 Kbytes for data pages.
>
>
> Total estimated I/O cost for statement 2 (at line 1): 24904693.
> ****************************************************************
> ASE2:Adaptive Server Enterprise/15.5/EBF 19399 SMP ESD#5/P/x86_64/Enterprise Linux/asear155/2568/64-bit/FBO/Fri Dec 9
> 02:45:22 2011
>
> update top 500000 Node set foo = 0 where foo is null;
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a newly cached statement (SSQL_ID = 372142043).
>
> Total estimated I/O cost for statement 1 (at line 1): 0.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 0).
>
>
> STEP 1
> The type of query is DECLARE.
>
> Total estimated I/O cost for statement 1 (at line 0): 0.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 1).
> Optimized using Deferred Parallel Mode
>
>
> STEP 1
> The type of query is UPDATE.
>
> 3 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 3)
> |
> | |UPDATE Operator (VA = 2)
> | | The update mode is direct.
> | |
> | | |TOP Operator (VA = 1)
> | | | Top Limit: 500000
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | Node
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 16 Kbytes for data pages.
> | | | | With MRU Buffer Replacement Strategy for data pages.
> | |
> | | TO TABLE
> | | Node
> | | Using I/O Size 2 Kbytes for data pages.
>
>
> Total estimated I/O cost for statement 2 (at line 1): 29357923.
>