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.

waiting for last i/o on MASS to complete

2 posts in Performance and Tuning Last posting was on 2012-07-02 16:01:05.0Z
Sidh Posted on 2012-07-02 15:35:48.0Z
Sender: 41a9.4ff1b541.1804289383@sybase.com
From: Sidh
Newsgroups: sybase.public.ase.performance+tuning
Subject: waiting for last i/o on MASS to complete
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ff1bfd4.4370.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 Jul 2012 08:35:48 -0700
X-Trace: forums-1-dub 1341243348 172.20.134.41 (2 Jul 2012 08:35:48 -0700)
X-Original-Trace: 2 Jul 2012 08:35:48 -0700, 172.20.134.41
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13170
Article PK: 91658

We have a performance issue in our batch, where the query
has a wait time.This batch runs every 30 mins.

Our performance monitoring tool shows that this process is
waiting at a where condition (select query) between 2 temp
tables #test1 & #aaa and the wait type as "waiting for last
i/o on MASS to complete". We have a clustered index created
on #aaa table, which the optimizer is using (below query
plan). #test1 table doesn't have any indexes, but has only
1000 rows.

1. My question is if we create a index on #test1 table too,
will it help? Especially this table has only 1000 rows
2. What other ways can we use to see why the process is
waiting for?

1> select WaitEventID,Waits,WaitTime from monProcessWaits
where SPID=228
2> go
WaitEventID Waits WaitTime
----------- ------- -----------
29 1439 38100
31 1254 11900
36 5142 33500
37 1 0
41 5912 41700
51 15548 206800 -- waiting for last i/o on MASS
to complete
52 17191 239300 -- waiting for i/o on MASS
initated by another task
53 1 0
54 20 200
55 1897 7200
150 2306 22600
214 2164 5800
250 6 200
251 27 700
272

STEP 1
The type of query is INSERT.

4 operator(s) under root

|ROOT:EMIT Operator (VA = 4)

|
| |INSERT Operator (VA = 3)
| | The update mode is direct.
| |
| | |NESTED LOOP JOIN Operator (VA = 2) (Join
Type: left Outer Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | #test1
| | | | leb
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 32 Kbytes for data
pages.
| | | | With LRU Buffer Replacement Strategy
for data pages.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | #aaa
| | | | lsp
| | | | Using Clustered Index.
| | | | Index : i_aaa
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | XXX ASC
| | | | YYY ASC
| | | | Using I/O Size 4 Kbytes for data
pages.
| | | | With LRU Buffer Replacement Strategy
for data pages.
| |
| | TO TABLE
| | XXXXXXXXXX
| | Using I/O Size 4 Kbytes for data pages.


Rob V Posted on 2012-07-02 16:01:05.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: waiting for last i/o on MASS to complete
References: <4ff1bfd4.4370.1681692777@sybase.com>
In-Reply-To: <4ff1bfd4.4370.1681692777@sybase.com>
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: <4ff1c5c1$1@forums-1-dub>
Date: 2 Jul 2012 09:01:05 -0700
X-Trace: forums-1-dub 1341244865 172.20.134.152 (2 Jul 2012 09:01:05 -0700)
X-Original-Trace: 2 Jul 2012 09:01:05 -0700, vip152.sybase.com
Lines: 124
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13171
Article PK: 91648

You cannot conclude that the waiting is happening at a specific moment
in time during the processing of the query; also, this cannot be
related directly to the existence or absence of particular indexes.

Wait events 51 and 52 are basically just waiting for disk I/Os to
complete, there is nothing wrong with that. If you want to improve on
this aspect, you need to try and improve the cache hit rate, for example
by making the data cache larger. if you can get rid of these wait events
by eliminating disk I/O, you'll see other wait events show up, such as
scheduling-related waits. There will always be *something* ASE is
waiting for and that is in fact normal.

To diagnose your performance issue, first look at the I/O counts for the
query you are executing ('set statics io on' or 'set statistics plancost
on'), and try to understand if the amount of *logical* I/O is reasonable
for what you expect the query to do.
Your query plan looks good at first sight though.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 02-Jul-2012 17:35, Sidh wrote:
> We have a performance issue in our batch, where the query
> has a wait time.This batch runs every 30 mins.
>
> Our performance monitoring tool shows that this process is
> waiting at a where condition (select query) between 2 temp
> tables #test1& #aaa and the wait type as "waiting for last
> i/o on MASS to complete". We have a clustered index created
> on #aaa table, which the optimizer is using (below query
> plan). #test1 table doesn't have any indexes, but has only
> 1000 rows.
>
> 1. My question is if we create a index on #test1 table too,
> will it help? Especially this table has only 1000 rows
> 2. What other ways can we use to see why the process is
> waiting for?
>
> 1> select WaitEventID,Waits,WaitTime from monProcessWaits
> where SPID=228
> 2> go
> WaitEventID Waits WaitTime
> ----------- ------- -----------
> 29 1439 38100
> 31 1254 11900
> 36 5142 33500
> 37 1 0
> 41 5912 41700
> 51 15548 206800 -- waiting for last i/o on MASS
> to complete
> 52 17191 239300 -- waiting for i/o on MASS
> initated by another task
> 53 1 0
> 54 20 200
> 55 1897 7200
> 150 2306 22600
> 214 2164 5800
> 250 6 200
> 251 27 700
> 272
>
> STEP 1
> The type of query is INSERT.
>
> 4 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 4)
> |
> | |INSERT Operator (VA = 3)
> | | The update mode is direct.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 2) (Join
> Type: left Outer Join)
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | #test1
> | | | | leb
> | | | | Table Scan.
> | | | | Forward Scan.
> | | | | Positioning at start of table.
> | | | | Using I/O Size 32 Kbytes for data
> pages.
> | | | | With LRU Buffer Replacement Strategy
> for data pages.
> | | |
> | | | |SCAN Operator (VA = 1)
> | | | | FROM TABLE
> | | | | #aaa
> | | | | lsp
> | | | | Using Clustered Index.
> | | | | Index : i_aaa
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Keys are:
> | | | | XXX ASC
> | | | | YYY ASC
> | | | | Using I/O Size 4 Kbytes for data
> pages.
> | | | | With LRU Buffer Replacement Strategy
> for data pages.
> | |
> | | TO TABLE
> | | XXXXXXXXXX
> | | Using I/O Size 4 Kbytes for data pages.