Hello,

We are facing in a production system after upgrading to Sybase ASE 15.0.2
(15.0.2/EBF 14331/P/Linux) a massive performance issue with our ESQL/C
applications. It took us a while to understand the underlaying issue is
full table scan, where it should use the Index(es). We can now fully
reproduce this as well with SQL:

the 1st SQL statement just returns in milliseconds:

select * from d01buch where d01ort = "2010.6597" or d01ort2 = "2010.6597"
go

while the same with CURSOR needs for 12.000.000 rows 45 seconds:

DECLARE d01buch_seq CURSOR FOR
select * from d01buch where
d01ort = "2010.6597" or d01ort2 = "2010.6597"
go
OPEN d01buch_seq
go

FETCH d01buch_seq
go

CLOSE d01buch_seq
go

The columns 'd01ort' and 'd01ort2' both are Index in table 'd01buch'. And of
course we checked them and as well 'update statistics' was done.

Any ideas what is causing the big diff between using or not using CURSOR?
Our ESQL/C app needs cursor for positioning in the hit list.

Thanks for any idea

matthias

Queries using cursors may use different query plans than the same query
without a cursor; this is known and documented.
You should check the query plan for both these cases to see if this is
the case.

Rob V.

-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

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

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



On 19-Apr-2011 16:18, rebelde wrote:

Rob V wrote:

Yes, we saw in the query plan that with using CURSOR the Indices are
ignored :-( Isn't this a bug, rather than a feature?

Well, we have changed now the ESQL/C statement and don't use the OR clause
anymore and we don't see any full table scans anymore; but the 10
dataserver engines show very high CPU utilization:

Tasks: 701 total, 11 running, 690 sleeping, 0 stopped, 0 zombie
Cpu(s): 60.0%us, 1.5%sy, 0.0%ni, 37.6%id, 0.9%wa, 0.0%hi, 0.1%si,
0.0%st
Mem: 37039588k total, 15899712k used, 21139876k free, 370844k buffers
Swap: 3911752k total, 1568k used, 3910184k free, 12722600k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3851 sybase 25 0 3484m 2.9g 2.9g R 100 8.3 1031:18 dataserver
3853 sybase 25 0 3484m 3.0g 3.0g R 99 8.4 1077:46 dataserver
2629 sybase 24 0 3484m 1.3g 1.3g R 99 3.6 128:32.58 dataserver
3275 sybase 25 0 3484m 2.6g 2.6g R 99 7.3 124:35.89 dataserver
3848 sybase 25 0 3484m 3.3g 3.3g R 98 9.4 1477:05 dataserver
21081 sybase 23 0 3484m 2.9g 2.9g R 97 8.3 200:52.69 dataserver
3586 sybase 25 0 3492m 3.0g 3.0g R 97 8.5 1668:11 dataserver
3850 sybase 17 0 3484m 3.0g 3.0g R 94 8.4 1036:16 dataserver
3849 sybase 21 0 3484m 2.9g 2.9g R 93 8.3 1721:21 dataserver
3852 sybase 17 0 3484m 2.9g 2.9g R 89 8.3 1010:59 dataserver

I have checked one of the dataserver proc for 30 seconds with strace(1) and
I see:

# fgrep seek /tmp/ase-21081.tr | wc -l
100
# fgrep read /tmp/ase-21081.tr | wc -l
70

i.e. there are only a very few number of i/o operations and esp. no full
table scans;


# fgrep recv /tmp/ase-21081.tr | wc -l
4860
# fgrep send /tmp/ase-21081.tr | wc -l
6684

i.e. the dataserver received ~4000 requests on its sockets and sent ~6000
answers to the ESQL/C clients; looks like normal;

BUT:

# wc -l /tmp/ase-21081.tr
70690 /tmp/ase-21081.tr
# fgrep select /tmp/ase-21081.tr | fgrep Timeout | wc -l
53768

i.e. of the ~70000 sys calls in 30 secs around 54000 have been select(2)
calls and found no file descriptor ready to deal with, and because the
timeout value have been set by ASE to (0,0) the select returned imediately
and ASE issued it again and again:

select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)
select(55, [12 13 18 19 20 21 22 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 45 46 48 52 54], NULL, NULL, {0, 0}) = 0 (Timeout)

the result of this is high CPU utilization even in IDLE situations -- can
the timeout value somehow set as an ASE parameter?

Thanks

matthias

That may be perfectly valid. Cursors have more strict requirements about
which indexes they can use than non-cursor queries.
You may want to check out the documentation on this point;
http://infocenter.sybase.com/help/in...ing/X33356.htm


HTH,

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

Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

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

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




On 20-Apr-2011 15:01, rebelde wrote: