optimizing sybase sql queries?

yawnmoth

unread,

Aug 8, 2008, 1:40:12 PM8/8/08

to

In MySQL, if you want to see what the bottle neck of an SQL query is,
you can prepend the query with EXPLAIN.

Does Sybase offer similar functionality?

Carl Kayser

unread,

Aug 8, 2008, 2:18:35 PM8/8/08

to

"yawnmoth" <terr...@yahoo.com> wrote in message
news:7a1aae78-dd5e-4157...@w7g2000hsa.googlegroups.com...

> In MySQL, if you want to see what the bottle neck of an SQL query is,
> you can prepend the query with EXPLAIN.
>
> Does Sybase offer similar functionality?

Exactly what does EXPLAIN do? Does it display "In line NNN you should
replace <this SQL> with <that SQL>"? Or how much time is spent with each
SQL command? Or what join techniques are used? Or something else?

In short, the answer for Sybase ASE is "highly probable" (e.g., set
shpowplan on, set statistics io on, etc) but one can't provide a definitive
answer to an ambiguous question.

yawnmoth

unread,

Aug 8, 2008, 4:29:59 PM8/8/08

to

On Aug 8, 1:18 pm, "Carl Kayser" <kayse...@bls.gov> wrote:
> "yawnmoth" <terra1...@yahoo.com> wrote in message

I thought I had provided a link... sorry:

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
http://www.sitepoint.com/print/optimizing-mysql-application (§
Analyzing Index Efficiency)

ThanksButNo

unread,

Aug 8, 2008, 5:34:55 PM8/8/08

to

I don't know what EXPLAIN does, but have you read the documentation
for "sp_showplan"?

Carl Kayser

unread,

Aug 11, 2008, 9:48:02 AM8/11/08

to

"yawnmoth" <terr...@yahoo.com> wrote in message

news:8a5174f8-f256-46ce...@a70g2000hsh.googlegroups.com...

I would say that "set showplan on" would be the closest equivalent to
EXPLAIN and (UNIX-level) optdiag would approximate ANALYZE TABLE. (There is
also the stored procedure sp_showplan by Kevin Sherlock which provides 99%
of the optdiag output.) There are also the traceflags 302 and 310 which can
generate voluminous output. The following is from a 12.5.3 server. With
15.x the showplan output has a graphical nature. Examples of the outputs
follow. (Commands are seperated by blocks of dashes.)

sp__optdiag syslogins

sp__optdiag/1.9.4/0/P/KJS/AnyPlat/AnyOS/G/Mon Feb 02 14:48:15 2004

Adaptive Server Enterprise/12.5.3/EBF 12593 ESD#3/P/Sun_svr4/OS
5.8/ase1253/1911/64-bit/FBO/Fri Jul 8 13:31:40 2005

Server name: "XXXX"

Specified database: "master"
Specified table owner: not specified
Specified table: "syslogins"
Specified column: not specified

Table owner: "dbo"
Table name: "syslogins"

Statistics for index: "syslogins" (clustered)
Index column list: "suid"
Data page count: 20
Empty data page count: 0
Data row count: 98.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 5.0000000000000000
OAM + allocation page count: 3
First extent leaf pages: 0
Data row size: 118.2708333333333286
Index height: 1

Derived statistics:
Data page cluster ratio: 0.8823529411764706
Space utilization: 0.2874638310185185
Large I/O efficiency: 0.5483870967741935

Statistics for index: "ncsyslogins" (nonclustered)
Index column list: "name"
Leaf count: 4
Empty leaf page count: 0
Data page CR count: 60.0000000000000000
Index page CR count: 1.0000000000000000
Data row CR count: 88.0000000000000000
First extent leaf pages: 0
Leaf row size: 20.1354166666666679
Index height: 1

Derived statistics:
Data page cluster ratio: 0.3294117647058823
Index page cluster ratio: 1.0000000000000000
Data row cluster ratio: 0.1282051282051282

Space utilization: 0.2447012442129630
Large I/O efficiency: 1.0000000000000000

Statistics for column: "name"
Last update of column statistics: Aug 3 2008 9:08:24:366AM

Range cell density: 0.0104166666666667
Total density: 0.0104166666666667
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "name"
Column datatype: varchar(30)
Requested step count: 20
Actual step count: 20

Step Weight Value

1 0.00000000 <= "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
2 0.05208333 <= "BBBBBB"
3 0.05208333 <= "CAAAAAAA"
4 0.05208333 <= "CNNNNNN"
5 0.05208333 <= "CZZZZZZ"
6 0.05208333 <= "DAAAAAA"
7 0.05208333 <= "DZZZZZZZZZZZ"
8 0.05208333 <= "GGGGGGGGG"
9 0.05208333 <= "HHHHHHH"
10 0.05208333 <= "KAAAAAA"
11 0.05208333 <= "KZZZ"
12 0.05208333 <= "LLLLL"
13 0.05208333 <= "MAAAAAAAAA"
14 0.05208333 <= "MZZZZZZZZZZZZZZZ"
15 0.05208333 <= "NNNNNNNN"
16 0.05208333 <= "RRRRRRRRR`"
17 0.05208333 <= "SSSSSS"
18 0.05208333 <= "WAAAAAAA"
19 0.05208333 <= "WZZZZZZZZ"
20 0.06250000 <= "sa"

Statistics for column: "suid"
Last update of column statistics: Aug 3 2008 9:08:24:366AM

Range cell density: 0.0104166666666667
Total density: 0.0104166666666667
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "suid"
Column datatype: integer
Requested step count: 20
Actual step count: 20

Step Weight Value

1 0.00000000 <= 0
2 0.05208333 <= 5
3 0.05208333 <= 27
4 0.05208333 <= 109
5 0.05208333 <= 167
6 0.05208333 <= 228
7 0.05208333 <= 255
8 0.05208333 <= 305
9 0.05208333 <= 332
10 0.05208333 <= 350
11 0.05208333 <= 377
12 0.05208333 <= 390
13 0.05208333 <= 398
14 0.05208333 <= 407
15 0.05208333 <= 419
16 0.05208333 <= 433
17 0.05208333 <= 438
18 0.05208333 <= 444
19 0.05208333 <= 452
20 0.06250000 <= 458

No statistics for remaining columns: "accdate"
(default values used) "audflags"
"dbname"
"fullname"
"language"
"logincount"
"password"
"procid"
"pwdate"
"resultlimit"
"spacelimit"
"srvname"
"status"
"timelimit"
"totcpu"
"totio"

(return status = 0)
----------------------------------------------------
----------------------------------------------------
----------------------------------------------------

set showplan on
select count (l.suid)
from syslogins l,
sysloginroles lr
where l.suid > 99
and lr.suid = l.suid

QERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1

The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
sysloginroles
lr
Nested iteration.
Using Clustered Index.
Index : csysloginroles
Forward scan.
Positioning by key.
Keys are:
suid ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE

syslogins
l
Nested iteration.
Using Clustered Index.
Index : syslogins
Forward scan.
Positioning by key.
Keys are:
suid ASC
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

STEP 2
The type of query is SELECT.

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

-----------
36

(1 row affected)
----------------------------------------------------
----------------------------------------------------
----------------------------------------------------

set showplan off

QUERY PLAN FOR STATEMENT 1 (at line 4).

STEP 1
The type of query is SET OPTION OFF.

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

dbcc traceon (3604, 310)

DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.

select count (l.suid)
from syslogins l,
sysloginroles lr
where l.suid > 99
and lr.suid = l.suid

*******************************

Beginning selection of qualifying indexes for table 'syslogins',
correlation name 'l', varno = 0, objectid 33.
The table (Allpages) has 98 rows, 20 pages,
Data Page Cluster Ratio 0.882353

Table scan cost is 98 rows, 20 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the SEARCH CLAUSE:
syslogins.suid > 99

Estimated selectivity for suid,
selectivity = 0.850102, upper limit = 0.895833.

Estimating selectivity of index 'syslogins', indid 1
scan selectivity 0.850102, filter selectivity 0.850102
83 rows, 19 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.882353

The best qualifying index is 'syslogins' (indid 1)
costing 19 pages,
with an estimate of 83 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Search argument selectivity is 0.850102.

*******************************
*******************************

Beginning selection of qualifying indexes for table 'sysloginroles',
correlation name 'lr', varno = 1, objectid 49.
The table (Allpages) has 61 rows, 1 pages,
Data Page Cluster Ratio 0.000000

Table scan cost is 61 rows, 1 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the SEARCH CLAUSE:
sysloginroles.suid > 99

Estimated selectivity for suid,
selectivity = 0.563772, upper limit = 0.586207.

Estimating selectivity of index 'csysloginroles', indid 1
scan selectivity 0.563772, filter selectivity 0.563772
34 rows, 2 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.000000

The best qualifying index is 'csysloginroles' (indid 1)
costing 2 pages,
with an estimate of 34 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Search argument selectivity is 0.563772.

*******************************
*******************************

Beginning selection of qualifying indexes for table 'syslogins',
correlation name 'l', varno = 0, objectid 33.
The table (Allpages) has 98 rows, 20 pages,
Data Page Cluster Ratio 0.882353

Table scan cost is 98 rows, 20 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the JOIN CLAUSE:
syslogins.suid = lr.suid
syslogins.suid > 99

Estimated selectivity for suid,
selectivity = 0.010417, upper limit = 0.895833.
Unique clustered index found, returns 1 row, 2 pages

Estimating selectivity of index 'syslogins', indid 1
scan selectivity 0.010417, filter selectivity 0.010417
1 rows, 2 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.882353

The best qualifying Nested Loop join index is 'syslogins' (indid 1)
costing 2 pages,
with an estimate of 1 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Join selectivity is 0.010204.

If this access path is selected for a merge join, this table will be sorted.

*******************************
*******************************

Beginning selection of qualifying indexes for table 'sysloginroles',
correlation name 'lr', varno = 1, objectid 49.
The table (Allpages) has 61 rows, 1 pages,
Data Page Cluster Ratio 0.000000

Table scan cost is 61 rows, 1 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the JOIN CLAUSE:
sysloginroles.suid = l.suid
sysloginroles.suid > 99

Estimated selectivity for suid,
selectivity = 0.035077, upper limit = 0.586207.

Estimating selectivity of index 'csysloginroles', indid 1
scan selectivity 0.035077, filter selectivity 0.035077
2 rows, 2 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.000000

The best qualifying Nested Loop join index is 'csysloginroles' (indid 1)
costing 2 pages,
with an estimate of 2 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Join selectivity is 0.035077.

If this access path is selected for a merge join, this table will be sorted.

*******************************

QUERY IS CONNECTED
Number of tables in join: 2
Number of tables considered at a time: 2
Table count setting: 0 (default value used)

0 - 1 -

NEW PLAN (total cost = 10823):

varno=0 (syslogins) indexid=1 (syslogins)
path=0x10010a0b800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=83 joinsel=1.000000 scanpgs=19
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=19 scanpio=4
corder=1

varno=1 (sysloginroles) indexid=1 (csysloginroles)
path=0x10013235800 pathtype=join
method=REFORMATTING
scanthreads=1
outerrows=83 rows=100 joinsel=0.035077 jnpgs_per_scan=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=167 scanpio=1
corder=1
jnvar=0 refcost=10361 refpages=2 reftotpages=1 ordercol[0]=1 ordercol[1]=1

NEW PLAN (total cost = 498):

varno=0 (syslogins) indexid=1 (syslogins)
path=0x10010a0b800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=83 joinsel=1.000000 scanpgs=19
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=19 scanpio=4
corder=1

varno=1 (sysloginroles) indexid=1 (csysloginroles)
path=0x10013235800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=83 rows=100 joinsel=0.035077 jnpgs_per_scan=2
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=167 scanpio=3
corder=1

jnvar=0 refcost=10361 refpages=2 reftotpages=1 ordercol[0]=1 ordercol[1]=1

NEW PLAN (total cost = 480):
varno=0 (syslogins) indexid=1 (syslogins)
path=0x10010a0b800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=83 joinsel=1.000000 scanpgs=19
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=19 scanpio=4
corder=1

varno=1 (sysloginroles) indexid=1 (csysloginroles)
path=0x10010701800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=83 rows=100 joinsel=0.035077 scanpgs=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=167 scanpio=2
corder=1

NEW PLAN (total cost = 296):

varno=0 (syslogins) indexid=1 (syslogins)
path=0x10010a0b800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=83 joinsel=1.000000 scanpgs=19
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=19 scanpio=4
corder=1

varno=1 (sysloginroles) indexid=0 ()
path=0x10010701800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=83 rows=100 joinsel=0.035077 scanpgs=1
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=84 scanpio=1
corder=0

1 - 0 -

NEW PLAN (total cost = 268):

varno=1 (sysloginroles) indexid=1 (csysloginroles)
path=0x10010701800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=34 joinsel=1.000000 scanpgs=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=2 scanpio=2
corder=1

varno=0 (syslogins) indexid=1 (syslogins)
path=0x10013664800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=34 rows=29 joinsel=0.010204 jnpgs_per_scan=2
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=69 scanpio=5
corder=1

jnvar=1 refcost=10926 refpages=2 reftotpages=1 ordercol[0]=1 ordercol[1]=1

TOTAL # PERMUTATIONS: 2

TOTAL # PLANS CONSIDERED: 13

CACHE USED BY THIS PLAN:

CacheID = 0: (2K) 1 (4K) 0 (8K) 0 (16K) 5

PARALLEL:
number of worker processes = 7
max parallel degree = 7
min(configured,set) parallel degree = 7
min(configured,set) hash scan parallel degree = 7

FINAL PLAN (total cost = 268):

varno=1 (sysloginroles) indexid=1 (csysloginroles)
path=0x10010701800 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=34 joinsel=1.000000 scanpgs=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=2 scanpio=2
corder=1

varno=0 (syslogins) indexid=1 (syslogins)
path=0x10013664800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=34 rows=29 joinsel=0.010204 jnpgs_per_scan=2
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=69 scanpio=5
corder=1

jnvar=1 refcost=10926 refpages=2 reftotpages=1 ordercol[0]=1 ordercol[1]=1

-----------
36

(1 row affected)

----------------------------------------------------
----------------------------------------------------
----------------------------------------------------
set showplan off
dbcc traceoff (310)
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.
dbcc traceon (302)
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.

select count (l.suid)
from syslogins l,
sysloginroles lr
where l.suid > 99
and lr.suid = l.suid

*******************************

Beginning selection of qualifying indexes for table 'syslogins',
correlation name 'l', varno = 0, objectid 33.
The table (Allpages) has 98 rows, 20 pages,
Data Page Cluster Ratio 0.882353

Table scan cost is 98 rows, 20 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the SEARCH CLAUSE:
syslogins.suid > 99

Estimated selectivity for suid,
selectivity = 0.850102, upper limit = 0.895833.

Estimating selectivity of index 'syslogins', indid 1
scan selectivity 0.850102, filter selectivity 0.850102
83 rows, 19 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.882353

The best qualifying index is 'syslogins' (indid 1)
costing 19 pages,
with an estimate of 83 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Search argument selectivity is 0.850102.