Running 'Update statistics'

[unknown]

Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

[unknown]

Sybase manual should be able to provide you good help on
update statistics. How often should you run it, depends on
your environment - how often the changes are taking place.
It helps optimizer to generate optimal plan based on the
stats value. You can also use optdiag for the same purpose.

[Tommy Phillips]

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table had
changed.

In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the optimizer
and cause unnecessary table scans.

One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.

As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Eugene Korolkov]

Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Stefan Karlsson]

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm

HTH,

/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Eugene Korolkov]

Stefan,

It looks cool, but unfortunately only for 15.x ? Right ? :-(

Regards,
Eugene

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Stefan Karlsson]

Post by Eugene Korolkov
Stefan,
It looks cool, but unfortunately only for 15.x ? Right ? :-(

Yes, the datachange() function is yet another new handy feature in 15.0 and
yet another reason to consider that version :)

/Stefan

Post by Eugene Korolkov
Regards,
Eugene

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Shahryar G. Hashemi]

Damn Sybase and all its new VERSION features :). But of course I wait
for ASE 15.1 :)

Shahryar

Post by Stefan Karlsson

Post by Eugene Korolkov
Stefan,
It looks cool, but unfortunately only for 15.x ? Right ? :-(

Yes, the datachange() function is yet another new handy feature in 15.0 and
yet another reason to consider that version :)
/Stefan

Post by Eugene Korolkov
Regards,
Eugene

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Eugene Korolkov]

As for now I am just running it every night for every table
(only update statistics being replaced by update index statistics,
actually I would not say that it is real index statistics because knowing
that 'a' is 2 times in col1 and 'b' is 5 times in col2 not allowing you
to know
how many times 'ab' happened)

HTH,
Eugene

Post by Shahryar G. Hashemi
Damn Sybase and all its new VERSION features :). But of course I wait
for ASE 15.1 :)
Shahryar

Post by Stefan Karlsson

Post by Eugene Korolkov
Stefan,
It looks cool, but unfortunately only for 15.x ? Right ? :-(

Yes, the datachange() function is yet another new handy feature in
15.0 and yet another reason to consider that version :)
/Stefan

Post by Eugene Korolkov
Regards,
Eugene

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that option.

[Sherlock, Kevin]

"ab" densities are in the column group stats. That combined with both histograms ought to be statistically sufficient. Don't you think?
"Eugene Korolkov" <***@davidsohn.com> wrote in message news:***@davidsohn.com...
As for now I am just running it every night for every table
(only update statistics being replaced by update index statistics,
actually I would not say that it is real index statistics because knowing
that 'a' is 2 times in col1 and 'b' is 5 times in col2 not allowing you to know
how many times 'ab' happened)

HTH,
Eugene
Shahryar G. Hashemi wrote:

Damn Sybase and all its new VERSION features :). But of course I wait for ASE 15.1 :)

Shahryar

Stefan Karlsson wrote:


"Eugene Korolkov" <***@davidsohn.com> wrote in message news:***@davidsohn.com...



Stefan,

It looks cool, but unfortunately only for 15.x ? Right ? :-(



Yes, the datachange() function is yet another new handy feature in 15.0 and yet another reason to consider that version :)

/Stefan




Regards,
Eugene

Stefan Karlsson wrote:



"Eugene Korolkov" <***@davidsohn.com> wrote in message news:***@davidsohn.com...




Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)




See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm

HTH,

/Stefan





That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Tommy Phillips wrote:





AJ wrote:





Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA




The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.

In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.

One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics. This gives
you a fighting chance of having good query plans at least for your
stored procedures.

As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that
option.

[Eugene Korolkov]

No, but IBM already have patent for that (histograms, not the density only), so
apparently sooner or later (maybe already ? :-) ) it is going
to be incorporated in DB2 optimizer. Oracle already have index_histogram view which is
more sophisticated then simple density, though not histogram so far.

Regards,
Eugene

Post by Sherlock, Kevin
"ab" densities are in the column group stats. That combined with both
histograms ought to be statistically sufficient. Don't you think?
As for now I am just running it every night for every table
(only update statistics being replaced by update index statistics,
actually I would not say that it is real index statistics because knowing
that 'a' is 2 times in col1 and 'b' is 5 times in col2 not
allowing you to know
how many times 'ab' happened)
HTH,
Eugene

Post by Shahryar G. Hashemi
Damn Sybase and all its new VERSION features :). But of course I
wait for ASE 15.1 :)
Shahryar

Post by Stefan Karlsson

Post by Eugene Korolkov
Stefan,
It looks cool, but unfortunately only for 15.x ? Right ? :-(

Yes, the datachange() function is yet another new handy feature
in 15.0 and yet another reason to consider that version :)
/Stefan

Post by Eugene Korolkov
Regards,
Eugene

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data
has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

Post by Tommy Phillips

Post by unknown
Hello,
How important it is to run 'update statistics' and how often
should I do this. What's the best method of doing this and
where can I find some good doc on this, also, can it be done
when db is online?
TIA

The rule of thumb back when I was teaching classes for Sybase was that
you should try to update statistics whenever about 10% of the table
had changed.
In fact, sometimes you need to be even more aggressive than this. For
instance, in many tables, most of the activity is on the most recent
entries. Statistics that are even a few days old may fool the
optimizer and cause unnecessary table scans.
One way to improve your odds in this kind of situation is to execute
sp_recompile on tables after you have updated statistics.
This gives
you a fighting chance of having good query plans at least for your
stored procedures.
As for doing it when the database is online, you cannot do it when the
database is offline. However, be aware that it does cause a lot of
activity and contention, so you are better off to do it during
low-activity times, or during a maintenance window, if you have that
option.

[Eric Miner]

Hi Stefan,

datachange() is a nice function and can be useful in some limited
situations. But, I would never advise a DBA to depend on it alone for
knowing when to run update stats. There are just too many situations
where very small changes completely change query plans - where changing
huge numbers of rows has no affect on query plans - and vice versa.
Managing the statistics is more of an art than a science. This function
try to make it cut and dry....and if you notice it reports the
percentage of change.....the same measure the old myth uses. I won't go
into how the group that wrote the function has little or no real-world
experience with stats management cases....ooopppps, I just did :-)

Later,

Eric Miner

Post by Stefan Karlsson

Post by Eugene Korolkov
Is there any procedural way to check that you table data has been
changed by 9% or 11% ? :-)

See the datachange() function at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks134.htm
HTH,
/Stefan

Post by Eugene Korolkov
That myth (10% changed data) already was discussed (see for example
Eric Miner's optimizer articles).

[Jesus M. Salvo Jr.]

Post by Eric Miner
Hi Stefan,
datachange() is a nice function and can be useful in some limited
situations. But, I would never advise a DBA to depend on it alone for
knowing when to run update stats. There are just too many situations
where very small changes completely change query plans - where changing
huge numbers of rows has no affect on query plans - and vice versa.
Managing the statistics is more of an art than a science. This function
try to make it cut and dry....and if you notice it reports the
percentage of change.....the same measure the old myth uses. I won't go
into how the group that wrote the function has little or no real-world
experience with stats management cases....ooopppps, I just did :-)
Later,
Eric Miner

Have to agree with Eric here ....

I have a few large tables where all activity is at the "end" of the table
( lots of inserts and some updates of the newly inserted rows ).

The fact that these large tables that I have grow quite large does not seem
affect __existing__ queries at all, even if the statistics have not been
updated.

I recall that statistics does NOT need to be updated if the "distribution"
of the data ( is that the right term ?? ) is always the same.

For example, in the case above, that large table has 2 columns called
( outgoing_status, sent_status ).

outgoing_status = 0 ( incoming message )
outgoing_status = 1 ( outgoing message )
sent_status = 0 ( incoming message not processed, if outgoing_status = 0 )
sent_status = 1 ( incoming message processed, if outgoing_status = 0 )
sent_status = 1 ( outgoing message not processed, if outgoing_status = 1 )
sent_status = 2 ( outgoing message processed, if outgoing_status = 1 )

One of the queries to this table ( to process unprocessed messages ) is:

...
where outgoing_status = 0 and sent_status = 0


An application continually does the above query and then updates the
sent_status to 1.

Thus, the number of rows where ( outgoing_status, sent_status ) = ( 0, 0 )
is always relatively very very small.

The statistics for this table was made loooong time ago when it was first
migrated from an Oracle database. Even if I want to update the statistics
on this table, I could not since this was one of those versions where
"update sample statistics" is not available yet, and the table is several
GBs ( 100 or more ), and I could not have that large of a tempdb device
anywhere.

Having mentioned that, what do Sybase DBAs did in the past before "update
sample statistics" existed for tables that are very large and where
therefore running "update statistics" would fill the entire tempdb ??

[Sherlock, Kevin]

"Jesus M. Salvo Jr." <***@noone.org> wrote in message news:***@forums-1-dub...
<snip>

Post by Jesus M. Salvo Jr.
The statistics for this table was made loooong time ago when it was first
migrated from an Oracle database. Even if I want to update the statistics
on this table, I could not since this was one of those versions where
"update sample statistics" is not available yet, and the table is several
GBs ( 100 or more ), and I could not have that large of a tempdb device
anywhere.
Having mentioned that, what do Sybase DBAs did in the past before "update
sample statistics" existed for tables that are very large and where
therefore running "update statistics" would fill the entire tempdb ??

When tempdb is used for a worktable for update stats, it is really done on a
column by column basis. Therefore, the number of rows, and the width of the
column (more or less plus a few bytes overhead per row) is the factor in sizing
the tempdb needs for update statistics. Even for very large tables, I would
have to think that your tempdb sizes for these kinds of environments would be
large enough to fit one column's data.

If not, then one possible workaround is to create a non-clustered index on the
column which would generate a histogram for the column (and the nc index stats).
You could then either drop the nc index afterwards or keep it around I guess.

The other obvious workaround is to extend tempdb to be large enough.

Even a 3rd workaround is that if you already have a non clustered index that
includes the column in the index key, but it's not the leading column, you can
use a trick I developed using proxy tables to force ASE to use the non-clustered
index when generating the histogram instead of scanning the base table and
creating a worktable in tempdb. I do wish Sybase engineering would build this
into the intelligence of update statistics. IE, if statistics are requested for
a column of a table that is not the leading column of an index, choose the
smallest index (or table) which contains the column to do the scan. Kind of
like "index covering" for update stats. Details of the proxy table trick are
here: http://www.teamsybase.net/kevin.sherlock/misc/tw2003/