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.

need info on showplan

2 posts in General Discussion Last posting was on 2012-07-13 11:43:54.0Z
Joe Posted on 2012-07-13 06:11:47.0Z
Sender: 5c6b.4fff9a7f.1804289383@sybase.com
From: Joe
Newsgroups: sybase.public.ase.general
Subject: need info on showplan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fffbc23.6099.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 12 Jul 2012 23:11:47 -0700
X-Trace: forums-1-dub 1342159907 172.20.134.41 (12 Jul 2012 23:11:47 -0700)
X-Original-Trace: 12 Jul 2012 23:11:47 -0700, 172.20.134.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31247
Article PK: 74136

Hi
I have generated a showplan for a stored procedure. I am
looking for below information but not able to get it.

1. Physical and logical page scan by each table
2. CPU used by the SP

thanks
-Joe


Rob V Posted on 2012-07-13 11:43:54.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: need info on showplan
References: <4fffbc23.6099.1681692777@sybase.com>
In-Reply-To: <4fffbc23.6099.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: <500009fa$1@forums-1-dub>
Date: 13 Jul 2012 04:43:54 -0700
X-Trace: forums-1-dub 1342179834 172.20.134.152 (13 Jul 2012 04:43:54 -0700)
X-Original-Trace: 13 Jul 2012 04:43:54 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31250
Article PK: 74139


On 13-Jul-2012 08:11, Joe wrote:
> Hi
> I have generated a showplan for a stored procedure. I am
> looking for below information but not able to get it.
>
> 1. Physical and logical page scan by each table
> 2. CPU used by the SP
>
> thanks
> -Joe
>

You need to run 'set statistics io on' for I/O counts. Or alternatively,
don't use 'set showplan on' at all, but use 'set statistics plancost on'
which shows the same info but in a different layout.

CPU time spent is not accounted for on SP level. It applies only to some
types of individual query operators. However 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
-----------------------------------------------------------------