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.

Optimizer statistics and other information

9 posts in Product Futures Discussion Last posting was on 2003-03-11 17:30:24.0Z
putnamr Posted on 2003-03-08 17:27:02.0Z
From: putnamr@river.it.gvsu.edu
Date: Sat, 8 Mar 2003 12:27:02 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Optimizer statistics and other information
Message-ID: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums>
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:990
Article PK: 95229

Hello,

I would like to be able to specify statistics for a table in a set
statement so that I can change/modify query plans by modifying the
statistical information that is used by the optimizer. Some, if not all,
can be done through building plans for a query, but I feel that this easier
to do and is likely to cause issues in a changing system.

Example,

SET QCOST TABLE "dbo.table_a" ROWS=10000, SIZE=1000K, etc...
INDEX table_a_ix1 SIZE=100K, HEIGHT=3, etc...
go

These statistics would override the statistics for systabstats and
sysstatistics when present.


KR Posted on 2003-03-09 09:18:21.0Z
Reply-To: "KR" <zzb26@mail.com.NOJUNK>
From: "KR" <zzb26@mail.com.NOJUNK>
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums>
Subject: Re: Optimizer statistics and other information
Date: Sun, 9 Mar 2003 10:18:21 +0100
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <3823b#h5CHA.182@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: network.net81-65-231.noos.fr 81.65.231.0
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:991
Article PK: 95230

I would debate this! I am not convinced that this is a good idea. The
problem that I see is that databases are changing on a daily basis. Your
assumptions may be correct one day and incorrect a few months down the road.
I am currently managing a server with well over 500 stored procedure with
some of them in the 5000+ lines of code. Honestly, I would rather have
Sybase do the statistic/query optimisation than rely on a developer doing
it. Sure, Sybase don't always get it right, but its pretty good. Also, the
times when it gets it wrong, giving a hint as to what index to use usually
solves the problem. Your approach could lead to unpredicatble behavior down
the road. So I say, "No thanks"!

KR

<putnamr@river.it.gvsu.edu> wrote in message
news:67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums...
> Hello,
>
> I would like to be able to specify statistics for a table in a set
> statement so that I can change/modify query plans by modifying the
> statistical information that is used by the optimizer. Some, if not all,
> can be done through building plans for a query, but I feel that this
easier
> to do and is likely to cause issues in a changing system.
>
> Example,
>
> SET QCOST TABLE "dbo.table_a" ROWS=10000, SIZE=1000K, etc...
> INDEX table_a_ix1 SIZE=100K, HEIGHT=3, etc...
> go
>
> These statistics would override the statistics for systabstats and
> sysstatistics when present.
>


putnamr Posted on 2003-03-09 18:13:15.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 9 Mar 2003 13:13:15 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Optimizer statistics and other information
Message-ID: <419134BEE8793AE30064172C85256CE4.0039D94585256CE4@webforums>
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums> <3823b#h5CHA.182@forums.sybase.com>
Lines: 6
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:992
Article PK: 95232

I understand that this is potentially dangerous, but so is forceplan,
forceindex, abstract plans (more so than this actually. There could also
be a sp_configure (server option) "dynamic qcost" that could be turned
on/off so that a DBA could control whether such functionality would be
allowed or not. I guess I fail to see the danger in this. I believe that
the more functionality and control of a system a DBA can have the better.


Eric Miner Posted on 2003-03-10 20:02:01.0Z
Message-ID: <3E6CEF39.8B42895C@sybase.com>
Date: Mon, 10 Mar 2003 12:02:01 -0800
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: putnamr@river.it.gvsu.edu
CC: eminer@sybase.com
Subject: Re: Optimizer statistics and other information
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 45
NNTP-Posting-Host: 10.22.91.122
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:994
Article PK: 95233

Actually, I think this would be a very reasonable and useful feature. It would
be particularly useful when working with 'fake tables' (temp tables and
worktables) where the optimizer isn't aware of their page and row counts at
compile time.

There are a number of ways to do this. But, my feeling is that it would best to
get as accurate information about the fake table as possible. So, I would think
this should be done under the covers for fake tables.

But, you're proposing allowing this on real tables too. It's an interesting new
'hint' mechanism. You say that the set command would override stats values from
systabstats and sysstatistics. What column level stats are you proposing be
included? Density values and default selectivity values could be handled and
would be useful. But, I'm not sure it would be practical for users to input
possibly massive amounts of histogram related values.

I also a agree with you that the more control we give you the better you can
use the product. At the same time we should also provide you with a way to
allow things to be automated.

Later,

Eric

putnamr@river.it.gvsu.edu wrote:

> Hello,
>
> I would like to be able to specify statistics for a table in a set
> statement so that I can change/modify query plans by modifying the
> statistical information that is used by the optimizer. Some, if not all,
> can be done through building plans for a query, but I feel that this easier
> to do and is likely to cause issues in a changing system.
>
> Example,
>
> SET QCOST TABLE "dbo.table_a" ROWS=10000, SIZE=1000K, etc...
> INDEX table_a_ix1 SIZE=100K, HEIGHT=3, etc...
> go
>
> These statistics would override the statistics for systabstats and
> sysstatistics when present.


putnamr Posted on 2003-03-10 21:14:22.0Z
From: putnamr@river.it.gvsu.edu
Date: Mon, 10 Mar 2003 16:14:22 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Optimizer statistics and other information
Message-ID: <5A0F547F8F14D2990074ABE085256CE5.0072AB9B85256CE5@webforums>
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums> <3E6CEF39.8B42895C@sybase.com>
Lines: 27
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:995
Article PK: 95235

Hello,

My view is that this would only be modifible at a batch or session level.
Not permanent. You would have the ability to specify any table ( so I
guess temp tables, worktables, and real tables would all be included).

I would like to specify the density values and selectivity values as well.
However, if not present a default would be provided from what is already
known in systabstats and sysstatistics about the object.

Specification of column statistics would be done as an override to help
avoid any confusion and so that it is known that they need not be
specified. If an override is not specified a default would be calculated
systematically from what is already known about the object.

Example)
SET QCOST TABLE "dbo.table_a" ROWS=10000, SIZE=1000K, etc...
INDEX table_a_ix1 SIZE=100K, HEIGHT=3, etc...
OVERRIDE COLUMN STATISTICS table_a_col_a "A" .5000
OVERRIDE COLUMN STATISTICS table_a_col_a "B" .4999
OVERRIDE COLUMN STATISTICS table_a_col_b "1" .5000
OVERRIDE COLUMN STATISTICS table_a_col_b "2" .4999
.
.
.

Thanks.


Sherlock, Kevin Posted on 2003-03-10 23:04:36.0Z
Message-ID: <3E6D19DC.AEF87A2F@qwest.com.nospam>
Date: Mon, 10 Mar 2003 17:04:36 -0600
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Optimizer statistics and other information
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums>
Content-Type: multipart/mixed; boundary="------------AFE96F458DB7F11DC86EA57F"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 55
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:996
Article PK: 95237

To some extent, this could be done today with "set statistics simulate".
One would only have to write a stored procedure which modifies
systabstats, sysstatistics ala optdiag input mode for simulated stats.
Perhaps this could be a Sybase provided system procedure (as an
enhancement request).

/* sp_simulate_statistics <tablename>,<indexname>,<attribute>,<value> */
exec sp_simulate_statistics "dbo.table_a","cl_index","data rows",10000
exec sp_simulate_statistics "dbo.table_a","cl_index","data pages",1340
exec sp_simulate_statistics "dbo.table_a","cl_index","index height",3
etc.

set statistics simulate on
go
select ...

go
set statistics simulate off
go

A similar procedure for histogram info could be also be written.

putnamr@river.it.gvsu.edu wrote:
>
> Hello,
>
> I would like to be able to specify statistics for a table in a set
> statement so that I can change/modify query plans by modifying the
> statistical information that is used by the optimizer. Some, if not all,
> can be done through building plans for a query, but I feel that this easier
> to do and is likely to cause issues in a changing system.
>
> Example,
>
> SET QCOST TABLE "dbo.table_a" ROWS=10000, SIZE=1000K, etc...
> INDEX table_a_ix1 SIZE=100K, HEIGHT=3, etc...
> go
>
> These statistics would override the statistics for systabstats and
> sysstatistics when present.


Download VCard ksherlo.vcf


putnamr Posted on 2003-03-11 00:00:49.0Z
From: putnamr@river.it.gvsu.edu
Date: Mon, 10 Mar 2003 19:00:49 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Optimizer statistics and other information
Message-ID: <5FAF3CCA275652E10000135F85256CE6.00834FE285256CE5@webforums>
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums> <3E6D19DC.AEF87A2F@qwest.com.nospam>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:997
Article PK: 95236

Hello,

Yeah I know that you can do that, but I do not think I want to. Using
these statistics is not as straight forward as you have mentioned. Also, I
believe that you can have only one copy of simulated statistics to use. I
would like to present to the optimizer at any time for any user
(potentially concurrently), statistical information about the object.

Thanks for the input.


Sherlock, Kevin Posted on 2003-03-11 15:56:51.0Z
Message-ID: <3E6E0740.CE5E1E03@qwest.com.nospam>
Date: Tue, 11 Mar 2003 09:56:51 -0600
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST Wireless
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Subject: Re: Optimizer statistics and other information
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums> <3E6D19DC.AEF87A2F@qwest.com.nospam> <5FAF3CCA275652E10000135F85256CE6.00834FE285256CE5@webforums>
Content-Type: multipart/mixed; boundary="------------75199DE1DDB3F18268B8CFF9"
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 40
NNTP-Posting-Host: np45.qwest.com 155.70.39.45
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1005
Article PK: 95241


putnamr@river.it.gvsu.edu wrote:
>
> Hello,
>
> Yeah I know that you can do that, but I do not think I want to. Using
> these statistics is not as straight forward as you have mentioned. Also, I
> believe that you can have only one copy of simulated statistics to use. I
> would like to present to the optimizer at any time for any user
> (potentially concurrently), statistical information about the object.

Yes, that's why I qualified this with "to some extent". There would be
concurrency issues if multiple users wanted to do this against permanent
user tables.

I'm not sure I understand your assertion about "using these statistics
is not as straight forward...". This method could work quite well for
#temp tables, and it could work for user tables where you have
controlled access to the stats.


Download VCard ksherlo.vcf


putnamr Posted on 2003-03-11 17:30:24.0Z
From: putnamr@river.it.gvsu.edu
Date: Tue, 11 Mar 2003 12:30:24 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Optimizer statistics and other information
Message-ID: <E7DF62144DC1F0AC00602AB785256CE6.005DD3F585256CE6@webforums>
References: <67CA7FD92568371A005FDBEE85256CE3.005FDC1285256CE3@webforums> <3E6D19DC.AEF87A2F@qwest.com.nospam> <5FAF3CCA275652E10000135F85256CE6.00834FE285256CE5@webforums> <3E6E0740.CE5E1E03@qwest.com.nospam>
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1007
Article PK: 95245

True, but I think I would want to create a named set of statistics then,
rather than using simulate. This could be handy because you could maybe
say:

SET STATISTICS table_a_named_set_001 ON

SELECT *
FROM table_a
WHERE table_a_col_a = "something"

OR

SELECT *
FROM table_a (STATISTICS table_a_named_set_001)
WHERE table_a_col_a = "something"

Would still want to create multiple sets of statistics through this means.
Maybe Sybase can extend functionality.