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.

Cost Calculation

2 posts in General Discussion Last posting was on 2013-01-24 12:39:26.0Z
Karthik M Posted on 2013-01-21 08:41:26.0Z
Sender: 2389.50fcfaa9.1804289383@sybase.com
From: Karthik M
Newsgroups: sybase.public.ase.general
Subject: Cost Calculation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50fcff36.24e8.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Jan 2013 00:41:26 -0800
X-Trace: forums-1-dub 1358757686 172.20.134.41 (21 Jan 2013 00:41:26 -0800)
X-Original-Trace: 21 Jan 2013 00:41:26 -0800, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31666
Article PK: 1159178

I have seen the below formula to calculate the cost.

Basic units of costing:

Cost = All physical IOs * 18 + All logical IOs * 2

http://download.sybase.com/pdfdocs/asg1250e/ptoptab.pdf

My query :

select * from sub_fund

It has only 110 rows for now.

Table: sub_fund scan count 1, logical reads: (regular=2
apf=0 total=2), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0

so the cost is

Cost = All physical IOs * 18 + All logical IOs * 2
Cost = 0*18 + 2*2
Cost = 0 + 4
Cost = 4

is this correct ?


Rob V Posted on 2013-01-24 12:39:26.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cost Calculation
References: <50fcff36.24e8.1681692777@sybase.com>
In-Reply-To: <50fcff36.24e8.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: <51012b7e$1@forums-1-dub>
Date: 24 Jan 2013 04:39:26 -0800
X-Trace: forums-1-dub 1359031166 172.20.134.152 (24 Jan 2013 04:39:26 -0800)
X-Original-Trace: 24 Jan 2013 04:39:26 -0800, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31678
Article PK: 1307147


On 21-Jan-2013 09:41, Karthik M wrote:
> I have seen the below formula to calculate the cost.
>
> Basic units of costing:
>
> Cost = All physical IOs * 18 + All logical IOs * 2
>
> http://download.sybase.com/pdfdocs/asg1250e/ptoptab.pdf
>
> My query :
>
> select * from sub_fund
>
> It has only 110 rows for now.
>
> Table: sub_fund scan count 1, logical reads: (regular=2
> apf=0 total=2), physical reads: (regular=0 apf=0 total=0),
> apf IOs used=0
>
> so the cost is
>
> Cost = All physical IOs * 18 + All logical IOs * 2
> Cost = 0*18 + 2*2
> Cost = 0 + 4
> Cost = 4
>
> is this correct ?
>

The calculation is correct. But note that the cost is a unitless number
and has no meaning by itself. It serves only during query optimization
as a basis to compare different possible query plans to find the 'best'
plan, where 'best' means: lowest cost.
The actual cost during executing which you refer to here is just the
same formula applied to execution-time metrics. That 'actual cost'
number is still unitless and can only be used to compare the actual
execution with the optimizer's estimates that produced the final plan.
Interpreting such differences, which may well occur frequently, is very
difficult -- we're in the middle of advanced optimizer internals here;
moreover, the cost numbers alone are not very meaningul by themselves
and ASE 12.x does simply not provide the underlying details in an easily
accessible way.
If this sort of analysis has your interest, it is better to compare the
output from 'set statistics plancost on' in ASE 15.x, as it compares the
actual and extimated numbers of LIO/PIO per query plan operator, which
is not available in ASE 12.x. See my article "ASE 15 Query Tuning with
plancost" (see http://www.sypron.nl/downloads.html) for an introduction.

BTW, the cost calculation formula in ASE 15.x is different fromt he 12.x
formula, namely: 2*LIO + 25*PIO + 0.1*(number of rows compared or scanned)

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