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.

Update statistics command on many big tables

4 posts in General Discussion Last posting was on 2009-10-19 21:58:15.0Z
Yaniv C. Posted on 2009-10-19 17:42:04.0Z
Sender: 84d.4adca39a.1804289383@sybase.com
From: Yaniv C.
Newsgroups: sybase.public.ase.general
Subject: Update statistics command on many big tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4adca4ec.8cd.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Oct 2009 10:42:04 -0700
X-Trace: forums-1-dub 1255974124 10.22.241.41 (19 Oct 2009 10:42:04 -0700)
X-Original-Trace: 19 Oct 2009 10:42:04 -0700, 10.22.241.41
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28504
Article PK: 77745

Hi
Please advice regarding update statistics issue I am facing.
I am using ASE 15.0.2ESD2 on Linux. 8 engines, 16 GB RAM.
On the server I have 4 databases, each one has like 20-30
tables, each table has 2-3 billion records.partitioned.
It take time to run update statistics. a lot of time.
I am running update index statistics.
I do not care much with the time, The problem is CPU
consuming and much more I/O consuming.
Yes, I am using the function datachange.
what the function datachange helps if I am running update
statistics on all the 3 billion rows
and not just on the 1 percentage change?
the one percent change is very important. New important
values, (and a lot of them) inserted to the table.
Some key values (a lot)deleted, and it is important to have
up to date statistics even after 1 percent.
But why to update the statistics to all other 99% records...

What do you think?
advices?
Thanks,
Yaniv


Sherlock, Kevin [TeamSybase] Posted on 2009-10-19 17:53:59.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4adca4ec.8cd.1681692777@sybase.com>
Subject: Re: Update statistics command on many big tables
Lines: 36
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: <4adca7b7$1@forums-1-dub>
Date: 19 Oct 2009 10:53:59 -0700
X-Trace: forums-1-dub 1255974839 10.22.241.152 (19 Oct 2009 10:53:59 -0700)
X-Original-Trace: 19 Oct 2009 10:53:59 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28505
Article PK: 77747

Partitioned? What kind of partitions? Semantic or round-robin?

Statistics about data is not just about new values, it's about distribution
relative to all other values as well. Also, if you have lots of "deleted"
values, its going to be difficult to know what is gone without looking at
all values, right? One of the features of semantic partitions is the
capability to separate data physically so that you would only have to update
stats on "new/changed" data. I know it's not always that way, but at least
in theory it's possible with semantic partitions.

<Yaniv C.> wrote in message news:4adca4ec.8cd.1681692777@sybase.com...
> Hi
> Please advice regarding update statistics issue I am facing.
> I am using ASE 15.0.2ESD2 on Linux. 8 engines, 16 GB RAM.
> On the server I have 4 databases, each one has like 20-30
> tables, each table has 2-3 billion records.partitioned.
> It take time to run update statistics. a lot of time.
> I am running update index statistics.
> I do not care much with the time, The problem is CPU
> consuming and much more I/O consuming.
> Yes, I am using the function datachange.
> what the function datachange helps if I am running update
> statistics on all the 3 billion rows
> and not just on the 1 percentage change?
> the one percent change is very important. New important
> values, (and a lot of them) inserted to the table.
> Some key values (a lot)deleted, and it is important to have
> up to date statistics even after 1 percent.
> But why to update the statistics to all other 99% records...
>
> What do you think?
> advices?
> Thanks,
> Yaniv


Yaniv C. Posted on 2009-10-19 18:20:16.0Z
Sender: 84d.4adca39a.1804289383@sybase.com
From: Yaniv C.
Newsgroups: sybase.public.ase.general
Subject: Re: Update statistics command on many big tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4adcade0.b6b.1681692777@sybase.com>
References: <4adca7b7$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Oct 2009 11:20:16 -0700
X-Trace: forums-1-dub 1255976416 10.22.241.41 (19 Oct 2009 11:20:16 -0700)
X-Original-Trace: 19 Oct 2009 11:20:16 -0700, 10.22.241.41
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28506
Article PK: 77749

Hi. Thanks for the reply.
Regarding partitions. Is it really matters if it is Semantic
or round-Robin?
My Storage has aggregation of 65 physical disks.
The devices are spread all over them.In that case it does
not really mater.
I do not agree that deleted key or new records can not be
save in smart way to update just the "update" pages in
statistics.
It is exists in other vendors.

Any other advices?
Thanks,
Yaniv

> Partitioned? What kind of partitions? Semantic or
> round-robin?
>
> Statistics about data is not just about new values, it's
> about distribution relative to all other values as well.
> Also, if you have lots of "deleted" values, its going to
> be difficult to know what is gone without looking at all
> values, right? One of the features of semantic partitions
> is the capability to separate data physically so that you
> would only have to update stats on "new/changed" data. I
> know it's not always that way, but at least in theory
> it's possible with semantic partitions.
>
> <Yaniv C.> wrote in message
> > news:4adca4ec.8cd.1681692777@sybase.com... Hi
> > Please advice regarding update statistics issue I am
> > facing. I am using ASE 15.0.2ESD2 on Linux. 8 engines,
> > 16 GB RAM. On the server I have 4 databases, each one
> > has like 20-30 tables, each table has 2-3 billion
> > records.partitioned. It take time to run update
> > statistics. a lot of time. I am running update index
> > statistics. I do not care much with the time, The
> > problem is CPU consuming and much more I/O consuming.
> > Yes, I am using the function datachange.
> > what the function datachange helps if I am running
> > update statistics on all the 3 billion rows
> > and not just on the 1 percentage change?
> > the one percent change is very important. New important
> > values, (and a lot of them) inserted to the table.
> > Some key values (a lot)deleted, and it is important to
> > have up to date statistics even after 1 percent.
> > But why to update the statistics to all other 99%
> records... >
> > What do you think?
> > advices?
> > Thanks,
> > Yaniv
>
>


Sherlock, Kevin [TeamSybase] Posted on 2009-10-19 21:58:15.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4adca7b7$1@forums-1-dub> <4adcade0.b6b.1681692777@sybase.com>
Subject: Re: Update statistics command on many big tables
Lines: 23
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: <4adce0f7$1@forums-1-dub>
Date: 19 Oct 2009 14:58:15 -0700
X-Trace: forums-1-dub 1255989495 10.22.241.152 (19 Oct 2009 14:58:15 -0700)
X-Original-Trace: 19 Oct 2009 14:58:15 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28507
Article PK: 77748


<Yaniv C.> wrote in message news:4adcade0.b6b.1681692777@sybase.com...
> Hi. Thanks for the reply.
> Regarding partitions. Is it really matters if it is Semantic
> or round-Robin?

Why would I ask if it didn't matter?

> My Storage has aggregation of 65 physical disks.
> The devices are spread all over them.In that case it does
> not really mater.

???.

> I do not agree that deleted key or new records can not be
> save in smart way to update just the "update" pages in
> statistics.
> It is exists in other vendors.
> Any other advices?

nope. I don't think you would be interested. Good luck.