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.

upd stat same old question

9 posts in Performance and Tuning Last posting was on 2008-06-14 23:32:01.0Z
Developer Posted on 2008-06-12 21:06:42.0Z
Sender: 2031.485130d1.1804289383@sybase.com
From: developer
Newsgroups: sybase.public.ase.performance+tuning
Subject: upd stat same old question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48518fe2.3069.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Jun 2008 14:06:42 -0700
X-Trace: forums-1-dub 1213304802 10.22.241.41 (12 Jun 2008 14:06:42 -0700)
X-Original-Trace: 12 Jun 2008 14:06:42 -0700, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10941
Article PK: 89553

I know this might have been asked many time but i am unable
to google and get the old links...

update stats vs update index stats;

i know what update index stats does; I have gone through
many of our tables opt diag o/p comparing update stat and
update index stat;

I want to know how the additional information
generated/stored by update index stats is used by the
optimier and how does it help optimiser (in what scenerios)

my test on a table and index on three columns reported same
logical i/o's with update stats and with update index stats
(if leading col is include and if excluded)

so i am not sure if update index stats help;


Sherlock, Kevin Posted on 2008-06-13 16:16:45.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <48518fe2.3069.1681692777@sybase.com>
Subject: Re: upd stat same old question
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48529d6d@forums-1-dub>
Date: 13 Jun 2008 09:16:45 -0700
X-Trace: forums-1-dub 1213373805 10.22.241.152 (13 Jun 2008 09:16:45 -0700)
X-Original-Trace: 13 Jun 2008 09:16:45 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10947
Article PK: 89557

Well, there are two things here:

1. "update index statistics" is only different from "update statistics" in
that it will generate column level statistics (histograms, densities, etc)
on ALL columns which participate in the key of an index. "update
statistics" will only do so for leading columns of those indexes.

2. The fact that your query plan/performance didn't change after running
"update index statistics" would not be unusual at all. Remember that the
optimizer determines an access path to take to get to the data. If your
table has one or more indexes, then a query which only references that table
will either use one of those indexes, or none of them (table scan) to reach
the data. If the statistical information needed to determine the best
access path (best index to use) is sufficient enough with "update
statistics", then that's as far as you have to go -- for that query. It
all depends on what your query (and other queries that you are tuning) looks
like, the distribution of the data in the key columns, the structure of the
index(es), etc. So, if you are using "update index statistics" but you have
no search arguments on those additional columns that now have statistical
info for the optimizer, well, then there will probably be no difference in
what index is chosen, and your performance will be no different.

In order to make this a more useful exchange, you should post your table and
index DDL, the optdiag output for the table, and the text of your SQL query.
This way, we can speak more in terms of a specific example to illustrate the
above point.

<developer> wrote in message news:48518fe2.3069.1681692777@sybase.com...
>I know this might have been asked many time but i am unable
> to google and get the old links...
>
> update stats vs update index stats;
>
> i know what update index stats does; I have gone through
> many of our tables opt diag o/p comparing update stat and
> update index stat;
>
> I want to know how the additional information
> generated/stored by update index stats is used by the
> optimier and how does it help optimiser (in what scenerios)
>
> my test on a table and index on three columns reported same
> logical i/o's with update stats and with update index stats
> (if leading col is include and if excluded)
>
> so i am not sure if update index stats help;


Developer Posted on 2008-06-13 17:04:48.0Z
Sender: 2031.485130d1.1804289383@sybase.com
From: developer
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: upd stat same old question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4852a8b0.618b.1681692777@sybase.com>
References: <48529d6d@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jun 2008 10:04:48 -0700
X-Trace: forums-1-dub 1213376688 10.22.241.41 (13 Jun 2008 10:04:48 -0700)
X-Original-Trace: 13 Jun 2008 10:04:48 -0700, 10.22.241.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10949
Article PK: 89560


> Well, there are two things here:
>
> 1. "update index statistics" is only different from
> "update statistics" in that it will generate column level
> statistics (histograms, densities, etc) on ALL columns
> which participate in the key of an index. "update
> statistics" will only do so for leading columns of those
> indexes.
>

I am aware of the difference related to histograms and
densities between update statistics and update index
statistics, but wanted to know how optimizer is using these
things to a good cause(better queries plans - fast queries),
in other words if we have one index on a table the optimizer
is always going to pick up index if the leading col is part
of the query, if leading col is not part of the where clause
then query will still go for a table scan (running update
index statistics or update stat wont do any good).

It seems that running update statistics is enough if you
have the right indexes for your queries; update index
statistics dosent seem to do much(!) it just takes up too
much time to run and eats up tempdb space when running.

If you have the right indexes there is no point in running
update index statistics;


Sherlock, Kevin Posted on 2008-06-13 19:36:12.0Z
From: "Sherlock, Kevin" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <48529d6d@forums-1-dub> <4852a8b0.618b.1681692777@sybase.com>
Subject: Re: upd stat same old question
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4852cc2c@forums-1-dub>
Date: 13 Jun 2008 12:36:12 -0700
X-Trace: forums-1-dub 1213385772 10.22.241.152 (13 Jun 2008 12:36:12 -0700)
X-Original-Trace: 13 Jun 2008 12:36:12 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10950
Article PK: 89561


<developer> wrote in message news:4852a8b0.618b.1681692777@sybase.com...
>> Well, there are two things here:
>>
>> 1. "update index statistics" is only different from
>> "update statistics" in that it will generate column level
>> statistics (histograms, densities, etc) on ALL columns
>> which participate in the key of an index. "update
>> statistics" will only do so for leading columns of those
>> indexes.
>>
>
> I am aware of the difference related to histograms and
> densities between update statistics and update index
> statistics, but wanted to know how optimizer is using these
> things to a good cause(better queries plans - fast queries),
> in other words if we have one index on a table the optimizer
> is always going to pick up index if the leading col is part
> of the query, if leading col is not part of the where clause
> then query will still go for a table scan (running update
> index statistics or update stat wont do any good).
>
> It seems that running update statistics is enough if you
> have the right indexes for your queries; update index
> statistics dosent seem to do much(!) it just takes up too
> much time to run and eats up tempdb space when running.
>
> If you have the right indexes there is no point in running
> update index statistics;

No. That's not correct to state as a general rule. In the context of a
single query, it may be true, but definitely not in general. As just one
example, column level statistics on columns which participate as part of
join criteria help the optimizer determine how many rows may qualify for the
join. There are way too many other reasons why it could be a good idea to
run 'update index stats'. Specifically, this becomes important to the new
query processing engine in ASE 15.0 and up.

You are correct when you state the it can be resource intensive, so yes, if
you are running it, you should be running it for a reason (ie, you have
queries that need up-to-date stats for best performance).

Just do a quick search for postings that state "why doesn't ASE use my index
on this table ... "

You could write all of your queries with index forcing and forego all
"update statistics" operations if you really want to get down to it. I
wouldn't recommend that of course, but as with all options, there are costs,
and there are benefits.

If you don't need "update index stats" for your queries, all the power to
you, don't waste your time. It takes testing and careful consideration to
decide whether you need them or not.

For what it's worth, ASE 15.0.2 ESD2 includes a change to the behavior of
update stats for non-leading columns of an index that GREATLY reduce the
tempdb usage, and speed up the operation by a significant factor. It allows
ASE to scan an index to gather histogram values rather than the data page
chain. Kinda like index covering for update stats. Reference to CR 406631
.


Developer Posted on 2008-06-13 21:32:58.0Z
Sender: 2031.485130d1.1804289383@sybase.com
From: developer
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: upd stat same old question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4852e78a.6a2b.1681692777@sybase.com>
References: <4852cc2c@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jun 2008 14:32:58 -0700
X-Trace: forums-1-dub 1213392778 10.22.241.41 (13 Jun 2008 14:32:58 -0700)
X-Original-Trace: 13 Jun 2008 14:32:58 -0700, 10.22.241.41
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10952
Article PK: 89563

i know, sybase fails to provide cases where the update index
statistics would help;
my test are primitive and simple, but when to use update
index statistics should NOT be a rocket science, i still
fail to understand; sybase should not make one to put years
to figure out when to use update index stats

maybe they still have a document, if someone can point me to
the link !

> <developer> wrote in message
> news:4852a8b0.618b.1681692777@sybase.com... >> Well, there
> are two things here: >>
> >> 1. "update index statistics" is only different from
> >> "update statistics" in that it will generate column
> level >> statistics (histograms, densities, etc) on ALL
> columns >> which participate in the key of an index.
> "update >> statistics" will only do so for leading columns
> of those >> indexes.
> >>
> >
> > I am aware of the difference related to histograms and
> > densities between update statistics and update index
> > statistics, but wanted to know how optimizer is using
> > these things to a good cause(better queries plans - fast
> > queries), in other words if we have one index on a table
> > the optimizer is always going to pick up index if the
> > leading col is part of the query, if leading col is not
> > part of the where clause then query will still go for a
> > table scan (running update index statistics or update
> stat wont do any good). >
> > It seems that running update statistics is enough if you
> > have the right indexes for your queries; update index
> > statistics dosent seem to do much(!) it just takes up
> > too much time to run and eats up tempdb space when
> running. >
> > If you have the right indexes there is no point in
> > running update index statistics;
>
> No. That's not correct to state as a general rule. In
> the context of a single query, it may be true, but
> definitely not in general. As just one example, column
> level statistics on columns which participate as part of
> join criteria help the optimizer determine how many rows
> may qualify for the join. There are way too many other
> reasons why it could be a good idea to run 'update index
> stats'. Specifically, this becomes important to the new
> query processing engine in ASE 15.0 and up.
>
> You are correct when you state the it can be resource
> intensive, so yes, if you are running it, you should be
> running it for a reason (ie, you have queries that need
> up-to-date stats for best performance).
>
> Just do a quick search for postings that state "why
> doesn't ASE use my index on this table ... "
>
> You could write all of your queries with index forcing and
> forego all "update statistics" operations if you really
> want to get down to it. I wouldn't recommend that of
> course, but as with all options, there are costs, and
> there are benefits.
>
> If you don't need "update index stats" for your queries,
> all the power to you, don't waste your time. It takes
> testing and careful consideration to decide whether you
> need them or not.
>
> For what it's worth, ASE 15.0.2 ESD2 includes a change to
> the behavior of update stats for non-leading columns of
> an index that GREATLY reduce the tempdb usage, and speed
> up the operation by a significant factor. It allows ASE
> to scan an index to gather histogram values rather than
> the data page chain. Kinda like index covering for
> update stats. Reference to CR 406631 ..
>
>


Sherlock, Kevin Posted on 2008-06-14 01:10:57.0Z
From: "Sherlock, Kevin" <ksherlock@tconl.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4852cc2c@forums-1-dub> <4852e78a.6a2b.1681692777@sybase.com>
Subject: Re: upd stat same old question
Lines: 35
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48531aa1@forums-1-dub>
Date: 13 Jun 2008 18:10:57 -0700
X-Trace: forums-1-dub 1213405857 10.22.241.152 (13 Jun 2008 18:10:57 -0700)
X-Original-Trace: 13 Jun 2008 18:10:57 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10954
Article PK: 89566


<developer> wrote in message news:4852e78a.6a2b.1681692777@sybase.com...
>i know, sybase fails to provide cases where the update index
> statistics would help;
> my test are primitive and simple, but when to use update
> index statistics should NOT be a rocket science, i still
> fail to understand; sybase should not make one to put years
> to figure out when to use update index stats
>
> maybe they still have a document, if someone can point me to
> the link !

I don't understand. What do you want this magical document to say? Every
application is different, every query can be written hundreds of different
ways. Databases can be designed in any manner. Database tables, index
design, data content. It can be anything...

All Sybase needs to do is provide you with the tools to do the _work_
necessary to determine how you design and architect your systems. That is
why companies post job descriptions for DBA's, and not GUI OPERATORS. Look
at ASE 15 and the features it provides you, and especially the feature which
tells you which columns are missing statistics and how stats on those
columns might help. Look at the ability to simulate statistics with
optdiag.

The link you are looking for is the series of Performance and Tuning
Guides. It is a must read, and having a thorough understanding of it takes
a bit more than combing through Google hits or knowledge base articles. I
can almost guarantee that if you take a few days to read through and apply
it's principles, then you won't have to put years into figuring out when
column based statistics might help you.

http://infocenter.sybase.com/help/topic/com.sybase.dc00743_1500/html/qp_abstrpln/title.htm


Sherlock, Kevin Posted on 2008-06-14 02:50:07.0Z
From: "Sherlock, Kevin" <ksherlock@tconl.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <48531aa1@forums-1-dub> <48531cfb.6e93.1681692777@sybase.com>
Subject: Re: upd stat same old question
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <485331df$1@forums-1-dub>
Date: 13 Jun 2008 19:50:07 -0700
X-Trace: forums-1-dub 1213411807 10.22.241.152 (13 Jun 2008 19:50:07 -0700)
X-Original-Trace: 13 Jun 2008 19:50:07 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10956
Article PK: 89568


<developer> wrote in message news:48531cfb.6e93.1681692777@sybase.com...
>> <developer> wrote in message
>> news:4852e78a.6a2b.1681692777@sybase.com... >i know,
>> > sybase fails to provide cases where the update index
>> > statistics would help; my test are primitive and simple,
>> > but when to use update index statistics should NOT be a
>> > rocket science, i still fail to understand; sybase
>> > should not make one to put years to figure out when to
>> use update index stats >
>> > maybe they still have a document, if someone can point
>> > me to the link !
>>
>> I don't understand. What do you want this magical
>> document to say? Every application is different, every
>> query can be written hundreds of different ways.
>> Databases can be designed in any manner. Database tables,
>> index design, data content. It can be anything...
>>
>
> How about how and when and why the optimizer would use the
> additional statistics generated by the update index stats,
> in such case how it would behave differently then if these
> stats where not present;
>
> but when its not clear to guys they surely might come up
> with generic explanation !
>
>
>> All Sybase needs to do is provide you with the tools to do
>> the _work_ necessary to determine how you design and
>> architect your systems. That is why companies post job
>> descriptions for DBA's, and not GUI OPERATORS. Look at
>> ASE 15 and the features it provides you, and especially
>> the feature which tells you which columns are missing
>> statistics and how stats on those columns might help.
>> Look at the ability to simulate statistics with optdiag.
>>
>
> i have more to say but would leave it at this since this is
> not relevant to my post.

Ok. Fair enough. Mea culpa.

So, lets get back to this:

> How about how and when and why the optimizer would use the
> additional statistics generated by the update index stats,
> in such case how it would behave differently then if these
> stats where not present;

Can you tell me (after reading the doc link provided below) if it doesn't
exactly address that:
http://infocenter.sybase.com/help/topic/com.sybase.dc00743_1500/html/qp_abstrpln/title.htm