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.

Showplan

5 posts in General Discussion Last posting was on 2012-07-24 12:13:54.0Z
Mike Posted on 2012-07-13 08:33:04.0Z
Sender: 648d.4fffdb73.1804289383@sybase.com
From: Mike
Newsgroups: sybase.public.ase.general
Subject: Showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fffdd3f.64c8.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Jul 2012 01:33:04 -0700
X-Trace: forums-1-dub 1342168384 172.20.134.41 (13 Jul 2012 01:33:04 -0700)
X-Original-Trace: 13 Jul 2012 01:33:04 -0700, 172.20.134.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31248
Article PK: 74137

Guys, what options should be used along with set showplan on
to get the following details

1. table scan
2. index used or not
3. cpu used
4 logical and physical count

htnnks


Rob V Posted on 2012-07-13 11:42:08.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan
References: <4fffdd3f.64c8.1681692777@sybase.com>
In-Reply-To: <4fffdd3f.64c8.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: <50000990$1@forums-1-dub>
Date: 13 Jul 2012 04:42:08 -0700
X-Trace: forums-1-dub 1342179728 172.20.134.152 (13 Jul 2012 04:42:08 -0700)
X-Original-Trace: 13 Jul 2012 04:42:08 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31249
Article PK: 74138


On 13-Jul-2012 10:33, Mike wrote:
> Guys, what options should be used along with set showplan on
> to get the following details
>
> 1. table scan
> 2. index used or not
> 3. cpu used
> 4 logical and physical count
>
> htnnks
>

'set showplan on' shows 1 + 2.
For 4, you need to run 'set statistics io on' as well. Or alternatively,
don't use 'set showplan on' at all, but use 'set statistics plancost on'
which shows 1+2+4 but in a different layout.

CPU time spent applies only to some types of query operators. It is not
very relevant in most cases since the index use and LIO/PIO aspects are
always more important anyway. I wouldn't bother about it.

--
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"
"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
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2012-07-13 13:16:26.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.general
Subject: Re: Showplan
References: <4fffdd3f.64c8.1681692777@sybase.com> <50000990$1@forums-1-dub>
In-Reply-To: <50000990$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50001faa$1@forums-1-dub>
Date: 13 Jul 2012 06:16:26 -0700
X-Trace: forums-1-dub 1342185386 172.20.134.152 (13 Jul 2012 06:16:26 -0700)
X-Original-Trace: 13 Jul 2012 06:16:26 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31251
Article PK: 74141

With pre ASE 15 queries I'd agree that cpu monitoring probably doesn't mean much, but for ASE 15 queries cpu usage stats
do come in handy in a few scenarios ...

- when monitoring a process that includes 100's, even 1000's, of queries the ability to search/sort cpu timings can help
zero in on the handful of queries that need to be researched

- if a process and/or stored proc is undergoing a good bit of recompilation then the ability to search/sort cpu timings
for parse&compile operations may help in tracking down not only excessive compilation overhead but also where in a proc
the recompilation is occurring

- while cpu usage is closely related to the volume of logical IOs when using nested loop joins (ie, monitoring logical
IOs is usually sufficient to find queries with high cpu metrics), it's quite possible that queries using merge/hash
joins will show (relatively) low logical IOs but excessively high cpu usage

So, some ways to capture cpu usage:

- 'set statistics time on'; shows times for parse&compile overhead; shows cpu stats for individual queries [NOTE: can be
combined with time and/or plancost: 'set statistics io, time on', 'set statistics plancost, time on', 'set statistics
io, time, plancost on']

- 'set statistics plancost on'; good for showing the *nodes* in a query plan with 'high' cpu usage due to operations
unique to merge/hash joins

- pulling records from the MDA/monSysStatement table; parse&compile overhead for a query is usually combined with the
cpu run time metrics to give a single cpu number for a given query

On 07/13/2012 05:42, Rob V wrote:
> On 13-Jul-2012 10:33, Mike wrote:
>> Guys, what options should be used along with set showplan on
>> to get the following details
>>
>> 1. table scan
>> 2. index used or not
>> 3. cpu used
>> 4 logical and physical count
>>
>> htnnks
>>
>
> 'set showplan on' shows 1 + 2.
> For 4, you need to run 'set statistics io on' as well. Or alternatively, don't use 'set showplan on' at all, but use
> 'set statistics plancost on' which shows 1+2+4 but in a different layout.
>
> CPU time spent applies only to some types of query operators. It is not very relevant in most cases since the index use
> and LIO/PIO aspects are always more important anyway. I wouldn't bother about it.
>


Mike Posted on 2012-07-17 07:33:17.0Z
Sender: 490f.500514b1.1804289383@sybase.com
From: Mike
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5005153d.4921.1681692777@sybase.com>
References: <50000990$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Jul 2012 00:33:17 -0700
X-Trace: forums-1-dub 1342510397 172.20.134.41 (17 Jul 2012 00:33:17 -0700)
X-Original-Trace: 17 Jul 2012 00:33:17 -0700, 172.20.134.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31255
Article PK: 74143

thanks, i tried using the io option but it also include
result for some of the query in it making showplan file big.
Any idea how can we get rid of it


thanks

> On 13-Jul-2012 10:33, Mike wrote:
> > Guys, what options should be used along with set
> > showplan on to get the following details
> >
> > 1. table scan
> > 2. index used or not
> > 3. cpu used
> > 4 logical and physical count
> >
> > htnnks
> >
>
> 'set showplan on' shows 1 + 2.
> For 4, you need to run 'set statistics io on' as well. Or
> alternatively, don't use 'set showplan on' at all, but
> use 'set statistics plancost on' which shows 1+2+4 but in
> a different layout.
>
> CPU time spent applies only to some types of query
> operators. It is not very relevant in most cases since
> the index use and LIO/PIO aspects are always more
> important anyway. I wouldn't bother about it.
>
> --
> 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"
> "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
> ----------------------------------------------------------
> -------
>
>


Rob V Posted on 2012-07-24 12:13:54.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Showplan
References: <50000990$1@forums-1-dub> <5005153d.4921.1681692777@sybase.com>
In-Reply-To: <5005153d.4921.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: <500e9182$1@forums-1-dub>
Date: 24 Jul 2012 05:13:54 -0700
X-Trace: forums-1-dub 1343132034 172.20.134.152 (24 Jul 2012 05:13:54 -0700)
X-Original-Trace: 24 Jul 2012 05:13:54 -0700, vip152.sybase.com
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31269
Article PK: 74157

The 'set statistics io' or 'set showplan' options do not affect whether
any result sets are included; whatever result sets are sent to the
client will show up amidst the output from these options.

Anyway, you can either change those 'select' queries into
'select...into', or you can run the undocumented command 'set nodata
on', which will suppress any result sets from being sent to the client
(but otherwise executes the query normally). The latter requires ASE 15.x.

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"
"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 17-Jul-2012 09:33, Mike wrote:
> thanks, i tried using the io option but it also include
> result for some of the query in it making showplan file big.
> Any idea how can we get rid of it
>
>
> thanks
>
>> On 13-Jul-2012 10:33, Mike wrote:
>>> Guys, what options should be used along with set
>>> showplan on to get the following details
>>>
>>> 1. table scan
>>> 2. index used or not
>>> 3. cpu used
>>> 4 logical and physical count
>>>
>>> htnnks
>>>
>>
>> 'set showplan on' shows 1 + 2.
>> For 4, you need to run 'set statistics io on' as well. Or
>> alternatively, don't use 'set showplan on' at all, but
>> use 'set statistics plancost on' which shows 1+2+4 but in
>> a different layout.
>>
>> CPU time spent applies only to some types of query
>> operators. It is not very relevant in most cases since
>> the index use and LIO/PIO aspects are always more
>> important anyway. I wouldn't bother about it.
>>
>> --
>> 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"
>> "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
>> ----------------------------------------------------------
>> -------
>>
>>