dbexport skipped update statistics high
User reported that dbexport on IDS 11.70 generates only "medium" statistics in the export file, not "high" statistics, requiring manual update stats high after import for acceptable performance. IBM developer confirmed this is by design—high statistics on index leading columns are automatically generated during index creation, so exporting them is unnecessary. User acknowledged this explains the behavior but found it inconvenient.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows Server 2008 sp2 . I find that the update statistics in the export file has only âmediumâ in it. When I import into another development server I need to remember to update stats high or things will creep along. Is there something to set in the onconfig file to get âhighâ stats to be in the export file?
This needs checking... But I imagine it skips update statistics high since this is done for columns heading an index... and these are run when the index is created, so it makes sense. But you say you need to run the stats... are you using v11 on the development server? Regards. On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton <garage_dba@hotmail.com>wrote: > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows > Server 2008 sp2 . > > I find that the update statistics in the export file has only medium in > it. > When I import into another development server I need to remember to update > stats high or things will creep along. > Is there something to set in the onconfig file to get high stats to be > in > the export file? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3074b1b44c1b4a04b34ed97e
You are correct, when an import occurs there is no reason to do high
because
the create index does a high on the lead column.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic46662.gif)
ids-bounces@iiug.org wrote on 12/04/2011 06:12:25 PM:
> From: "Fernando Nunes" <domusonline@gmail.com>
> To: ids@iiug.org
> Date: 12/04/2011 06:13 PM
> Subject: Re: dbexport skipped update statistics high [25530]
> Sent by: ids-bounces@iiug.org
>
> This needs checking... But I imagine it skips update statistics high
since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
<garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only “medium”
in
> > it.
> > When I import into another development server I need to remember to
update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get “high” stats to
be
> > in
> > the export file?
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Your email client is hosed?
-----Original Message-----
From: John Miller iii
Sent: Sunday, December 04, 2011 8:38 PM
To: ids@iiug.org
Subject: Re: dbexport skipped update statistics high [25531]
You are correct, when an import occurs there is no reason to do high
because
the create index does a high on the lead column.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic46662.gif)
ids-bounces@iiug.org wrote on 12/04/2011 06:12:25 PM:
> From: "Fernando Nunes" <domusonline@gmail.com>
> To: ids@iiug.org
> Date: 12/04/2011 06:13 PM
> Subject: Re: dbexport skipped update statistics high [25530]
> Sent by: ids-bounces@iiug.org
>
> This needs checking... But I imagine it skips update statistics high
since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
<garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only “medium”
in
> > it.
> > When I import into another development server I need to remember to
update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get “high” stats to
be
> > in
> > the export file?
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Both export server and import server are running 11.70.C3 32 bit.
The export was from win2008 32bit. The import was onto win2008 64 bit.
That makes no difference since import/export uses ascii output/input.
As I said, the export file has no high statistics anywhere.
We have another win2008 64 bit server that does the same thing upon
dbexport.
The only reason I run stats after import is the import server runs slow
without high stats being done.
The sysdistribs table is not part of an export. That is why you always had
to update stats after import.
I used to update stats all the time on 9.4 but I thought the new one was
doing it because I saw all of those
update stats statements. One day I took a closer look and found it was only
Medium stats - big surprise.
I guess I just need to keep doing what I always did.
I thought that maybe someone else had noticed this and reported it.
They IBM guys may as well remove the code for "medium" since it is just
slowing the export for no gain to us.
Apparently there are not many folks using 11.70 at this time or they just
are not using import/export very often.
It is also annoying that PDQPRIORITY will not work on the free versions.
This makes import/export darn slow.
For those of us doing only OLTP there seems not much use for pdq except for
this purpose).
I understand that IBM wants to use pdq to extort money out of us. Not a
complaint here - great product.
We have used IDS since 7.3 .
p.s.: I just now exported a database from the win2008 64 bit server that I
had imported into a few days ago and updated statistics upon. It has the
"high" stats in the export file.
-----Original Message-----
From: Fernando Nunes
Sent: Sunday, December 04, 2011 8:12 PM
To: ids@iiug.org
Subject: Re: dbexport skipped update statistics high [25530]
This needs checking... But I imagine it skips update statistics high since
this is done for columns heading an index... and these are run when the
index is created, so it makes sense.
But you say you need to run the stats... are you using v11 on the
development server?
Regards.
On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
<garage_dba@hotmail.com>wrote:
> This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows
> Server 2008 sp2 .
>
> I find that the update statistics in the export file has only âmediumâ in
> it.
> When I import into another development server I need to remember to update
> stats high or things will creep along.
> Is there something to set in the onconfig file to get âhighâ stats to be
> in
> the export file?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3074b1b44c1b4a04b34ed97e
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Bill:
In version 11 when you create an index, the act of creating an
index will create high distributions and low statistics for that index.
This is part of the create index process. When creating an index
the data is scanned and sort, the output of this is now leveraged
for both creating the index, high distributions on the lead key and
low statistics.
It is no longer required to run update statistics high on the lead column
of an index after it has been created.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 12/04/2011 08:45:49 PM:
> From: "Bill Hamilton" <garage_dba@hotmail.com>
> To: ids@iiug.org
> Date: 12/04/2011 08:47 PM
> Subject: Re: dbexport skipped update statistics high [25533]
> Sent by: ids-bounces@iiug.org
>
> Both export server and import server are running 11.70.C3 32 bit.
> The export was from win2008 32bit. The import was onto win2008 64 bit.
> That makes no difference since import/export uses ascii output/input.
>
> As I said, the export file has no high statistics anywhere.
> We have another win2008 64 bit server that does the same thing upon
> dbexport.
>
> The only reason I run stats after import is the import server runs slow
> without high stats being done.
> The sysdistribs table is not part of an export. That is why you always
had
> to update stats after import.
> I used to update stats all the time on 9.4 but I thought the new one was
> doing it because I saw all of those
> update stats statements. One day I took a closer look and found it was
only
> Medium stats - big surprise.
> I guess I just need to keep doing what I always did.
> I thought that maybe someone else had noticed this and reported it.
> They IBM guys may as well remove the code for "medium" since it is just
> slowing the export for no gain to us.
> Apparently there are not many folks using 11.70 at this time or they just
> are not using import/export very often.
>
> It is also annoying that PDQPRIORITY will not work on the free versions.
> This makes import/export darn slow.
> For those of us doing only OLTP there seems not much use for pdq except
for
> this purpose).
> I understand that IBM wants to use pdq to extort money out of us. Not a
> complaint here - great product.
> We have used IDS since 7.3 .
>
> p.s.: I just now exported a database from the win2008 64 bit server that
I
> had imported into a few days ago and updated statistics upon. It has the
> "high" stats in the export file.
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Sunday, December 04, 2011 8:12 PM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25530]
>
> This needs checking... But I imagine it skips update statistics high
since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> <garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only â
€œmediumâ€
> in
> > it.
> > When I import into another development server I need to remember to
update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get “highâ€
> stats to be
> > in
> > the export file?
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Or in human language:
Bill:
In version 11 when you create an index, the act of creating an
index will create high distributions and low statistics for that index.
This is part of the create index process. When creating an index
the data is scanned and sort, the output of this is now leveraged
for both creating the index, high distributions on the lead key and
low statistics.
It is no longer required to run update statistics high on the lead column
of an index after it has been created.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
On 05.12.2011. 7:49, John Miller iii wrote:
> Bill:
>
>
> In version 11 when you create an index, the act of creating an
> index will create high distributions and low statistics for that index.
> This is part of the create index process. When creating an index
> the data is scanned and sort, the output of this is now leveraged
> for both creating the index, high distributions on the lead key and
> low statistics.
>
> It is no longer required to run update statistics high on the lead column
> of an index after it has been created.
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
>
> ids-bounces@iiug.org wrote on 12/04/2011 08:45:49 PM:
>
> > From: "Bill Hamilton" <garage_dba@hotmail.com>
> > To: ids@iiug.org
> > Date: 12/04/2011 08:47 PM
> > Subject: Re: dbexport skipped update statistics high [25533]
> > Sent by: ids-bounces@iiug.org
> >
> > Both export server and import server are running 11.70.C3 32 bit.
> > The export was from win2008 32bit. The import was onto win2008 64 bit.
> > That makes no difference since import/export uses ascii output/input.
> >
> > As I said, the export file has no high statistics anywhere.
> > We have another win2008 64 bit server that does the same thing upon
> > dbexport.
> >
> > The only reason I run stats after import is the import server runs slow
> > without high stats being done.
> > The sysdistribs table is not part of an export. That is why you always
> had
> > to update stats after import.
> > I used to update stats all the time on 9.4 but I thought the new one was
> > doing it because I saw all of those
> > update stats statements. One day I took a closer look and found it was
> only
> > Medium stats - big surprise.
> > I guess I just need to keep doing what I always did.
> > I thought that maybe someone else had noticed this and reported it.
> > They IBM guys may as well remove the code for "medium" since it is just
> > slowing the export for no gain to us.
> > Apparently there are not many folks using 11.70 at this time or they just
>
> > are not using import/export very often.
> >
> > It is also annoying that PDQPRIORITY will not work on the free versions.
> > This makes import/export darn slow.
> > For those of us doing only OLTP there seems not much use for pdq except
> for
> > this purpose).
> > I understand that IBM wants to use pdq to extort money out of us. Not a
> > complaint here - great product.
> > We have used IDS since 7.3 .
> >
> > p.s.: I just now exported a database from the win2008 64 bit server that
> I
> > had imported into a few days ago and updated statistics upon. It has the
> > "high" stats in the export file.
> >
> > -----Original Message-----
> > From: Fernando Nunes
> > Sent: Sunday, December 04, 2011 8:12 PM
> > To: ids@iiug.org
> > Subject: Re: dbexport skipped update statistics high [25530]
> >
> > This needs checking... But I imagine it skips update statistics high
> since
> > this is done for columns heading an index... and these are run when the
> > index is created, so it makes sense.
> > But you say you need to run the stats... are you using v11 on the
> > development server?
> >
> > Regards.
> >
> > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > <garage_dba@hotmail.com>wrote:
> >
> > > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
> Windows
> > > Server 2008 sp2 .
> > >
> > > I find that the update statistics in the export file has only â
> €œmediumâ€
> > in
> > > it.
> > > When I import into another development server I need to remember to
> update
> > > stats high or things will creep along.
> > > Is there something to set in the onconfig file to get “highâ€
> > stats to be
> > > in
> > > the export file?
> > >
> > >
> > >
> > >
> >
> >
> *******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --20cf3074b1b44c1b4a04b34ed97e
> >
> >
> >
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
On Mon, Dec 5, 2011 at 4:45 AM, Bill Hamilton <garage_dba@hotmail.com>wrote:
> Both export server and import server are running 11.70.C3 32 bit.
> The export was from win2008 32bit. The import was onto win2008 64 bit.
> That makes no difference since import/export uses ascii output/input.
>
As already said, this is expected behavior, since it's unnecessary. In fact
putting the HIGH stats in the export would cause delays since you'd be
calculating the same thing twice.
> The only reason I run stats after import is the import server runs slow
> without high stats being done.
>
This is strange and you should verify if you don't have histograms after
dbimport (you're using dbimport right?)
> The sysdistribs table is not part of an export. That is why you always had
> to update stats after import.
>
Yes, it's not part of the export as none of the other system catalog. It's
true you should update statistics after importing, and that's why dbexport
includes the instructions to do os (although there are quicker ways to do
it - like in parallel -, so from my point of view it should be optional)
> I used to update stats all the time on 9.4 but I thought the new one was
> doing it because I saw all of those
> update stats statements. One day I took a closer look and found it was only
> Medium stats - big surprise.
>
This should be no surprise because the 11.10 optimization means it creates
distributions while creating indexes.
> I guess I just need to keep doing what I always did.
> I thought that maybe someone else had noticed this and reported it.
> They IBM guys may as well remove the code for "medium" since it is just
> slowing the export for no gain to us.
>
The inclusion of the instructions in the SQL file should cause no
noticeable delay. It's not updating the stats... just writing the
instructions.
> Apparently there are not many folks using 11.70 at this time or they just
> are not using import/export very often.
>
I know a few customers using 11.70. But most of them had just used the
dbimport (for the migration). Maybe when 12(?) comes out they use the
dbexport more :)
> It is also annoying that PDQPRIORITY will not work on the free versions.
> This makes import/export darn slow.
> For those of us doing only OLTP there seems not much use for pdq except for
> this purpose).
>
I'm wondering if there is something specific here for the free versions...
My tests were done using the full product...
> p.s.: I just now exported a database from the win2008 64 bit server that I
> had imported into a few days ago and updated statistics upon. It has the
> "high" stats in the export file.
>
Which is strange... If you could check this in a simple "stores" database
it could be interesting. You test results don't match mine...
Regards.
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Sunday, December 04, 2011 8:12 PM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25530]
>
> This needs checking... But I imagine it skips update statistics high since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> <garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only medium
> in
> > it.
> > When I import into another development server I need to remember to
> update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get high stats to be
> > in
> > the export file?
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e648a630fe101804b35652b9
Fernando: CREATE INDEX only does MEDIUM on the key columns!
Bill: Did you double check the source database? Dbexport should be
exporting exactly the stats that existed on the source, HIGH, MEDIUM,
whatever.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <garage_dba@hotmail.com>wrote:
> Both export server and import server are running 11.70.C3 32 bit.
> The export was from win2008 32bit. The import was onto win2008 64 bit.
> That makes no difference since import/export uses ascii output/input.
>
> As I said, the export file has no high statistics anywhere.
> We have another win2008 64 bit server that does the same thing upon
> dbexport.
>
> The only reason I run stats after import is the import server runs slow
> without high stats being done.
> The sysdistribs table is not part of an export. That is why you always had
> to update stats after import.
> I used to update stats all the time on 9.4 but I thought the new one was
> doing it because I saw all of those
> update stats statements. One day I took a closer look and found it was only
> Medium stats - big surprise.
> I guess I just need to keep doing what I always did.
> I thought that maybe someone else had noticed this and reported it.
> They IBM guys may as well remove the code for "medium" since it is just
> slowing the export for no gain to us.
> Apparently there are not many folks using 11.70 at this time or they just
> are not using import/export very often.
>
> It is also annoying that PDQPRIORITY will not work on the free versions.
> This makes import/export darn slow.
> For those of us doing only OLTP there seems not much use for pdq except for
> this purpose).
> I understand that IBM wants to use pdq to extort money out of us. Not a
> complaint here - great product.
> We have used IDS since 7.3 .
>
> p.s.: I just now exported a database from the win2008 64 bit server that I
> had imported into a few days ago and updated statistics upon. It has the
> "high" stats in the export file.
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Sunday, December 04, 2011 8:12 PM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25530]
>
> This needs checking... But I imagine it skips update statistics high since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> <garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only medium
> in
> > it.
> > When I import into another development server I need to remember to
> update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get high stats to be
> > in
> > the export file?
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd4b5b207151104b356c8c5
Art: Did you check that? That's not what the manual says and it's not what
I thought... But I admit I didn't check it.
Meanwhile, accordingly to the manual, it should use a precision of 1.0 for
tables with less than 1M rows and usually it uses 0.5.
Maybe this explains the bad performance...
Regards.
On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Fernando: CREATE INDEX only does MEDIUM on the key columns!
>
> Bill: Did you double check the source database? Dbexport should be
> exporting exactly the stats that existed on the source, HIGH, MEDIUM,
> whatever.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <garage_dba@hotmail.com
> >wrote:
>
> > Both export server and import server are running 11.70.C3 32 bit.
> > The export was from win2008 32bit. The import was onto win2008 64 bit.
> > That makes no difference since import/export uses ascii output/input.
> >
> > As I said, the export file has no high statistics anywhere.
> > We have another win2008 64 bit server that does the same thing upon
> > dbexport.
> >
> > The only reason I run stats after import is the import server runs slow
> > without high stats being done.
> > The sysdistribs table is not part of an export. That is why you always
> had
> > to update stats after import.
> > I used to update stats all the time on 9.4 but I thought the new one was
> > doing it because I saw all of those
> > update stats statements. One day I took a closer look and found it was
> only
> > Medium stats - big surprise.
> > I guess I just need to keep doing what I always did.
> > I thought that maybe someone else had noticed this and reported it.
> > They IBM guys may as well remove the code for "medium" since it is just
> > slowing the export for no gain to us.
> > Apparently there are not many folks using 11.70 at this time or they just
> > are not using import/export very often.
> >
> > It is also annoying that PDQPRIORITY will not work on the free versions.
> > This makes import/export darn slow.
> > For those of us doing only OLTP there seems not much use for pdq except
> for
> > this purpose).
> > I understand that IBM wants to use pdq to extort money out of us. Not a
> > complaint here - great product.
> > We have used IDS since 7.3 .
> >
> > p.s.: I just now exported a database from the win2008 64 bit server that
> I
> > had imported into a few days ago and updated statistics upon. It has the
> > "high" stats in the export file.
> >
> > -----Original Message-----
> > From: Fernando Nunes
> > Sent: Sunday, December 04, 2011 8:12 PM
> > To: ids@iiug.org
> > Subject: Re: dbexport skipped update statistics high [25530]
> >
> > This needs checking... But I imagine it skips update statistics high
> since
> > this is done for columns heading an index... and these are run when the
> > index is created, so it makes sense.
> > But you say you need to run the stats... are you using v11 on the
> > development server?
> >
> > Regards.
> >
> > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > <garage_dba@hotmail.com>wrote:
> >
> > > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
> Windows
> > > Server 2008 sp2 .
> > >
> > > I find that the update statistics in the export file has only medium
> > in
> > > it.
> > > When I import into another development server I need to remember to
> > update
> > > stats high or things will creep along.
> > > Is there something to set in the onconfig file to get high stats to
> be
> > > in
> > > the export file?
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --20cf3074b1b44c1b4a04b34ed97e
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --000e0cd4b5b207151104b356c8c5
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e648a63022ce3c04b357804d
You are correct Fernando. Don't know where my head was earlier. CREATE
INDEX also produces HIGH distributions for the first column of each index.
However, create index does NOT produce and stats for columns other than the
single lead column, and it does so indescriminately - meaning that if
several indexes start with the same column you incur the cost of the
distributions for that single lead column several times over. Anyway, the
HIGH stats on the second or later columns that differ when multiple
indexes start with the same subset key are not performed and that can make
a big difference.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> Art: Did you check that? That's not what the manual says and it's not what
> I thought... But I admit I didn't check it.
> Meanwhile, accordingly to the manual, it should use a precision of 1.0 for
> tables with less than 1M rows and usually it uses 0.5.
> Maybe this explains the bad performance...
> Regards.
>
> On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> >
> > Bill: Did you double check the source database? Dbexport should be
> > exporting exactly the stats that existed on the source, HIGH, MEDIUM,
> > whatever.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <garage_dba@hotmail.com
> > >wrote:
> >
> > > Both export server and import server are running 11.70.C3 32 bit.
> > > The export was from win2008 32bit. The import was onto win2008 64 bit.
> > > That makes no difference since import/export uses ascii output/input.
> > >
> > > As I said, the export file has no high statistics anywhere.
> > > We have another win2008 64 bit server that does the same thing upon
> > > dbexport.
> > >
> > > The only reason I run stats after import is the import server runs slow
> > > without high stats being done.
> > > The sysdistribs table is not part of an export. That is why you always
> > had
> > > to update stats after import.
> > > I used to update stats all the time on 9.4 but I thought the new one
> was
> > > doing it because I saw all of those
> > > update stats statements. One day I took a closer look and found it was
> > only
> > > Medium stats - big surprise.
> > > I guess I just need to keep doing what I always did.
> > > I thought that maybe someone else had noticed this and reported it.
> > > They IBM guys may as well remove the code for "medium" since it is just
> > > slowing the export for no gain to us.
> > > Apparently there are not many folks using 11.70 at this time or they
> just
> > > are not using import/export very often.
> > >
> > > It is also annoying that PDQPRIORITY will not work on the free
> versions.
> > > This makes import/export darn slow.
> > > For those of us doing only OLTP there seems not much use for pdq except
> > for
> > > this purpose).
> > > I understand that IBM wants to use pdq to extort money out of us. Not a
> > > complaint here - great product.
> > > We have used IDS since 7.3 .
> > >
> > > p.s.: I just now exported a database from the win2008 64 bit server
> that
> > I
> > > had imported into a few days ago and updated statistics upon. It has
> the
> > > "high" stats in the export file.
> > >
> > > -----Original Message-----
> > > From: Fernando Nunes
> > > Sent: Sunday, December 04, 2011 8:12 PM
> > > To: ids@iiug.org
> > > Subject: Re: dbexport skipped update statistics high [25530]
> > >
> > > This needs checking... But I imagine it skips update statistics high
> > since
> > > this is done for columns heading an index... and these are run when the
> > > index is created, so it makes sense.
> > > But you say you need to run the stats... are you using v11 on the
> > > development server?
> > >
> > > Regards.
> > >
> > > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > > <garage_dba@hotmail.com>wrote:
> > >
> > > > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
> > Windows
> > > > Server 2008 sp2 .
> > > >
> > > > I find that the update statistics in the export file has only
> medium
> > > in
> > > > it.
> > > > When I import into another development server I need to remember to
> > > update
> > > > stats high or things will creep along.
> > > > Is there something to set in the onconfig file to get high stats to
> > be
> > > > in
> > > > the export file?
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --20cf3074b1b44c1b4a04b34ed97e
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --000e0cd4b5b207151104b356c8c5
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016e648a63022ce3c04b357804d
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>@@NL@
On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> You are correct Fernando. Don't know where my head was earlier. CREATE
> INDEX also produces HIGH distributions for the first column of each index.
>
However, create index does NOT produce and stats for columns other than the
> single lead column, and it does so indescriminately - meaning that if
> several indexes start with the same column you incur the cost of the
>
True. Just HIGH for the heading column of the index. I cannot quantify the
overhead of doing it "repeatedly" for several indexes starting with the
same column.
My feeling is that the biggest impact is on the ordering but that must be
performed anyway.
In any case this is a good point. Any performance architect around? :)
> distributions for that single lead column several times over. Anyway, the
> HIGH stats on the second or later columns that differ when multiple
> indexes start with the same subset key are not performed and that can make
> a big difference.
>
Yes. Depending on the script you use for the stats. From your words I
imagine your's take this into consideration. I would have to check mine...
In reality I only witnessed this in one situation (SAP system), but I'm
sure it can happen in a lot of cases
Maybe Bill is hitting something like this. But in any case, the base
problem should not tbe he lack of the UPDATE STATISTICS HIGH in the export
file...
Regards
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > Art: Did you check that? That's not what the manual says and it's not
> what
> > I thought... But I admit I didn't check it.
> > Meanwhile, accordingly to the manual, it should use a precision of 1.0
> for
> > tables with less than 1M rows and usually it uses 0.5.
> > Maybe this explains the bad performance...
> > Regards.
> >
> > On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> > >
> > > Bill: Did you double check the source database? Dbexport should be
> > > exporting exactly the stats that existed on the source, HIGH, MEDIUM,
> > > whatever.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those of
> > > other individuals affiliated with any entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <garage_dba@hotmail.com
> > > >wrote:
> > >
> > > > Both export server and import server are running 11.70.C3 32 bit.
> > > > The export was from win2008 32bit. The import was onto win2008 64
> bit.
> > > > That makes no difference since import/export uses ascii output/input.
> > > >
> > > > As I said, the export file has no high statistics anywhere.
> > > > We have another win2008 64 bit server that does the same thing upon
> > > > dbexport.
> > > >
> > > > The only reason I run stats after import is the import server runs
> slow
> > > > without high stats being done.
> > > > The sysdistribs table is not part of an export. That is why you
> always
> > > had
> > > > to update stats after import.
> > > > I used to update stats all the time on 9.4 but I thought the new one
> > was
> > > > doing it because I saw all of those
> > > > update stats statements. One day I took a closer look and found it
> was
> > > only
> > > > Medium stats - big surprise.
> > > > I guess I just need to keep doing what I always did.
> > > > I thought that maybe someone else had noticed this and reported it.
> > > > They IBM guys may as well remove the code for "medium" since it is
> just
> > > > slowing the export for no gain to us.
> > > > Apparently there are not many folks using 11.70 at this time or they
> > just
> > > > are not using import/export very often.
> > > >
> > > > It is also annoying that PDQPRIORITY will not work on the free
> > versions.
> > > > This makes import/export darn slow.
> > > > For those of us doing only OLTP there seems not much use for pdq
> except
> > > for
> > > > this purpose).
> > > > I understand that IBM wants to use pdq to extort money out of us.
> Not a
> > > > complaint here - great product.
> > > > We have used IDS since 7.3 .
> > > >
> > > > p.s.: I just now exported a database from the win2008 64 bit server
> > that
> > > I
> > > > had imported into a few days ago and updated statistics upon. It has
> > the
> > > > "high" stats in the export file.
> > > >
> > > > -----Original Message-----
> > > > From: Fernando Nunes
> > > > Sent: Sunday, December 04, 2011 8:12 PM
> > > > To: ids@iiug.org
> > > > Subject: Re: dbexport skipped update statistics high [25530]
> > > >
> > > > This needs checking... But I imagine it skips update statistics high
> > > since
> > > > this is done for columns heading an index... and these are run when
> the
> > > > index is created, so it makes sense.
> > > > But you say you need to run the stats... are you using v11 on the
> > > > development server?
> > > >
> > > > Regards.
> > > >
> > > > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > > > <garage_dba@hotmail.com>wrote:
> > > >
> > > > > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
> > > Windows
> > > > > Server 2008 sp2 .
> > > > >
> > > > > I find that the update statistics in the export file has only
> > medium
> > > > in
> > > > > it.
> > > > > When I import into another development server I need to remember to
> > > > update
> > > > > stats high or things will creep along.
> > > > > Is there something to set in the onconfig file to get high stats
> to
> > > be
> > > > > in
> > > > > the export file?
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --
> > > > Fernando Nunes
> > > > Portugal
> > > >
> > > > http://informix-technology.blogsp
Many thanks to all that replied to my post!
I had forgotten about the stats being built when indexes are created.
I should know because it is my intent to remove stats from any proc that is
currently doing stats on a temp table if we ever get all customers onto
11.70 or above.
I think the thing that messed me up is the fact that I still have to import
into a server that is using ids9.4 .
The programmers that use that bitch like Hell if I forget to update stats on
that.
My own box is using the latest IDS and it's not so bad, but even there I
think that update stats seems to help.
Maybe that is just conditioning.
I will experiment as Fernando has suggested.
-----Original Message-----
From: Fernando Nunes
Sent: Monday, December 05, 2011 5:07 AM
To: ids@iiug.org
Subject: Re: dbexport skipped update statistics high [25536]
On Mon, Dec 5, 2011 at 4:45 AM, Bill Hamilton <garage_dba@hotmail.com>wrote:
> Both export server and import server are running 11.70.C3 32 bit.
> The export was from win2008 32bit. The import was onto win2008 64 bit.
> That makes no difference since import/export uses ascii output/input.
>
As already said, this is expected behavior, since it's unnecessary. In fact
putting the HIGH stats in the export would cause delays since you'd be
calculating the same thing twice.
> The only reason I run stats after import is the import server runs slow
> without high stats being done.
>
This is strange and you should verify if you don't have histograms after
dbimport (you're using dbimport right?)
> The sysdistribs table is not part of an export. That is why you always had
> to update stats after import.
>
Yes, it's not part of the export as none of the other system catalog. It's
true you should update statistics after importing, and that's why dbexport
includes the instructions to do os (although there are quicker ways to do
it - like in parallel -, so from my point of view it should be optional)
> I used to update stats all the time on 9.4 but I thought the new one was
> doing it because I saw all of those
> update stats statements. One day I took a closer look and found it was
> only
> Medium stats - big surprise.
>
This should be no surprise because the 11.10 optimization means it creates
distributions while creating indexes.
> I guess I just need to keep doing what I always did.
> I thought that maybe someone else had noticed this and reported it.
> They IBM guys may as well remove the code for "medium" since it is just
> slowing the export for no gain to us.
>
The inclusion of the instructions in the SQL file should cause no
noticeable delay. It's not updating the stats... just writing the
instructions.
> Apparently there are not many folks using 11.70 at this time or they just
> are not using import/export very often.
>
I know a few customers using 11.70. But most of them had just used the
dbimport (for the migration). Maybe when 12(?) comes out they use the
dbexport more :)
> It is also annoying that PDQPRIORITY will not work on the free versions.
> This makes import/export darn slow.
> For those of us doing only OLTP there seems not much use for pdq except
> for
> this purpose).
>
I'm wondering if there is something specific here for the free versions...
My tests were done using the full product...
> p.s.: I just now exported a database from the win2008 64 bit server that I
> had imported into a few days ago and updated statistics upon. It has the
> "high" stats in the export file.
>
Which is strange... If you could check this in a simple "stores" database
it could be interesting. You test results don't match mine...
Regards.
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Sunday, December 04, 2011 8:12 PM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25530]
>
> This needs checking... But I imagine it skips update statistics high since
> this is done for columns heading an index... and these are run when the
> index is created, so it makes sense.
> But you say you need to run the stats... are you using v11 on the
> development server?
>
> Regards.
>
> On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> <garage_dba@hotmail.com>wrote:
>
> > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on Windows
> > Server 2008 sp2 .
> >
> > I find that the update statistics in the export file has only âmediumâ
> in
> > it.
> > When I import into another development server I need to remember to
> update
> > stats high or things will creep along.
> > Is there something to set in the onconfig file to get âhighâ stats to
be
> > in
> > the export file?
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3074b1b44c1b4a04b34ed97e
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e648a630fe101804b35652b9
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Mon, Dec 5, 2011 at 5:06 PM, Bill Hamilton <garage_dba@hotmail.com>wrote:
> Many thanks to all that replied to my post!
> I had forgotten about the stats being built when indexes are created.
> I should know because it is my intent to remove stats from any proc that is
> currently doing stats on a temp table if we ever get all customers onto
> 11.70 or above.
>
It's in 11.10+
> I think the thing that messed me up is the fact that I still have to import
> into a server that is using ids9.4 .
> The programmers that use that bitch like Hell if I forget to update stats
> on
> that.
> My own box is using the latest IDS and it's not so bad, but even there I
> think that update stats seems to help.
> Maybe that is just conditioning.
>
Well... there are two possible differences between custom statistics and
automatic done while creating the Index:
1- The automatic use a precision of 1.0. By default the update statistics
high uses 0.5 (more detailed). This may impact your system. It really
depends on the data model and value distributions
2- A custom script may run "high" for other columns besides the one heading
indexes
But for 9.4 you sure need stats.... (and a new version... :) )
Regards.
>
> I will experiment as Fernando has suggested.
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Monday, December 05, 2011 5:07 AM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25536]
>
> On Mon, Dec 5, 2011 at 4:45 AM, Bill Hamilton <garage_dba@hotmail.com
> >wrote:
>
> > Both export server and import server are running 11.70.C3 32 bit.
> > The export was from win2008 32bit. The import was onto win2008 64 bit.
> > That makes no difference since import/export uses ascii output/input.
> >
>
> As already said, this is expected behavior, since it's unnecessary. In fact
> putting the HIGH stats in the export would cause delays since you'd be
> calculating the same thing twice.
>
> > The only reason I run stats after import is the import server runs slow
> > without high stats being done.
> >
>
> This is strange and you should verify if you don't have histograms after
> dbimport (you're using dbimport right?)
>
> > The sysdistribs table is not part of an export. That is why you always
> had
> > to update stats after import.
> >
>
> Yes, it's not part of the export as none of the other system catalog. It's
> true you should update statistics after importing, and that's why dbexport
> includes the instructions to do os (although there are quicker ways to do
> it - like in parallel -, so from my point of view it should be optional)
>
> > I used to update stats all the time on 9.4 but I thought the new one was
> > doing it because I saw all of those
> > update stats statements. One day I took a closer look and found it was
> > only
> > Medium stats - big surprise.
> >
>
> This should be no surprise because the 11.10 optimization means it creates
> distributions while creating indexes.
>
> > I guess I just need to keep doing what I always did.
> > I thought that maybe someone else had noticed this and reported it.
> > They IBM guys may as well remove the code for "medium" since it is just
> > slowing the export for no gain to us.
> >
>
> The inclusion of the instructions in the SQL file should cause no
> noticeable delay. It's not updating the stats... just writing the
> instructions.
>
> > Apparently there are not many folks using 11.70 at this time or they just
> > are not using import/export very often.
> >
>
> I know a few customers using 11.70. But most of them had just used the
> dbimport (for the migration). Maybe when 12(?) comes out they use the
> dbexport more :)
>
> > It is also annoying that PDQPRIORITY will not work on the free versions.
> > This makes import/export darn slow.
> > For those of us doing only OLTP there seems not much use for pdq except
> > for
> > this purpose).
> >
>
> I'm wondering if there is something specific here for the free versions...
> My tests were done using the full product...
>
> > p.s.: I just now exported a database from the win2008 64 bit server that
> I
> > had imported into a few days ago and updated statistics upon. It has the
> > "high" stats in the export file.
> >
>
> Which is strange... If you could check this in a simple "stores" database
> it could be interesting. You test results don't match mine...
>
> Regards.
>
> >
> > -----Original Message-----
> > From: Fernando Nunes
> > Sent: Sunday, December 04, 2011 8:12 PM
> > To: ids@iiug.org
> > Subject: Re: dbexport skipped update statistics high [25530]
> >
> > This needs checking... But I imagine it skips update statistics high
> since
> > this is done for columns heading an index... and these are run when the
> > index is created, so it makes sense.
> > But you say you need to run the stats... are you using v11 on the
> > development server?
> >
> > Regards.
> >
> > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > <garage_dba@hotmail.com>wrote:
> >
> > > This concerns IBM Informix Dynamic Server Version 11.70.TC3IE on
> Windows
> > > Server 2008 sp2 .
> > >
> > > I find that the update statistics in the export file has only medium
> > in
> > > it.
> > > When I import into another development server I need to remember to
> > update
> > > stats high or things will creep along.
> > > Is there something to set in the onconfig file to get high stats to
> be
> > > in
> > > the export file?
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --20cf3074b1b44c1b4a04b34ed97e
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016e648a630fe101804b35652b9
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://info
Truth.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > You are correct Fernando. Don't know where my head was earlier. CREATE
> > INDEX also produces HIGH distributions for the first column of each
> index.
> >
> However, create index does NOT produce and stats for columns other than the
> > single lead column, and it does so indescriminately - meaning that if
> > several indexes start with the same column you incur the cost of the
> >
>
> True. Just HIGH for the heading column of the index. I cannot quantify the
> overhead of doing it "repeatedly" for several indexes starting with the
> same column.
> My feeling is that the biggest impact is on the ordering but that must be
> performed anyway.
> In any case this is a good point. Any performance architect around? :)
>
> > distributions for that single lead column several times over. Anyway, the
> > HIGH stats on the second or later columns that differ when multiple
> > indexes start with the same subset key are not performed and that can
> make
> > a big difference.
> >
>
> Yes. Depending on the script you use for the stats. From your words I
> imagine your's take this into consideration. I would have to check mine...
> In reality I only witnessed this in one situation (SAP system), but I'm
> sure it can happen in a lot of cases
> Maybe Bill is hitting something like this. But in any case, the base
> problem should not tbe he lack of the UPDATE STATISTICS HIGH in the export
> file...
> Regards
>
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > Art: Did you check that? That's not what the manual says and it's not
> > what
> > > I thought... But I admit I didn't check it.
> > > Meanwhile, accordingly to the manual, it should use a precision of 1.0
> > for
> > > tables with less than 1M rows and usually it uses 0.5.
> > > Maybe this explains the bad performance...
> > > Regards.
> > >
> > > On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com>
> wrote:
> > >
> > > > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> > > >
> > > > Bill: Did you double check the source database? Dbexport should be
> > > > exporting exactly the stats that existed on the source, HIGH, MEDIUM,
> > > > whatever.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> > any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <
> garage_dba@hotmail.com
> > > > >wrote:
> > > >
> > > > > Both export server and import server are running 11.70.C3 32 bit.
> > > > > The export was from win2008 32bit. The import was onto win2008 64
> > bit.
> > > > > That makes no difference since import/export uses ascii
> output/input.
> > > > >
> > > > > As I said, the export file has no high statistics anywhere.
> > > > > We have another win2008 64 bit server that does the same thing upon
> > > > > dbexport.
> > > > >
> > > > > The only reason I run stats after import is the import server runs
> > slow
> > > > > without high stats being done.
> > > > > The sysdistribs table is not part of an export. That is why you
> > always
> > > > had
> > > > > to update stats after import.
> > > > > I used to update stats all the time on 9.4 but I thought the new
> one
> > > was
> > > > > doing it because I saw all of those
> > > > > update stats statements. One day I took a closer look and found it
> > was
> > > > only
> > > > > Medium stats - big surprise.
> > > > > I guess I just need to keep doing what I always did.
> > > > > I thought that maybe someone else had noticed this and reported it.
> > > > > They IBM guys may as well remove the code for "medium" since it is
> > just
> > > > > slowing the export for no gain to us.
> > > > > Apparently there are not many folks using 11.70 at this time or
> they
> > > just
> > > > > are not using import/export very often.
> > > > >
> > > > > It is also annoying that PDQPRIORITY will not work on the free
> > > versions.
> > > > > This makes import/export darn slow.
> > > > > For those of us doing only OLTP there seems not much use for pdq
> > except
> > > > for
> > > > > this purpose).
> > > > > I understand that IBM wants to use pdq to extort money out of us.
> > Not a
> > > > > complaint here - great product.
> > > > > We have used IDS since 7.3 .
> > > > >
> > > > > p.s.: I just now exported a database from the win2008 64 bit server
> > > that
> > > > I
> > > > > had imported into a few days ago and updated statistics upon. It
> has
> > > the
> > > > > "high" stats in the export file.
> > > > >
> > > > > -----Original Message-----
> > > > > From: Fernando Nunes
> > > > > Sent: Sunday, December 04, 2011 8:12 PM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: dbexport skipped update statistics high [25530]
> > > > >
> > > > > This needs checking... But I imagine it skips update statistics
> high
> > > > since
> > > > > this is done for columns heading an index... and these are run when
> > the
> > > > > index is created, so it makes sense.
> > > > > But you say you need to run the stats... are you using v11 on the
> > > > > development server?
> > > > >
> > > > > Regards.
> > > > >
> > > > > On Mon, Dec 5, 2011 at 12:28 AM, Bill Hamilton
> > > > > <garage_dba@hotmail.com>wrote:
> > > > >
> > > > > > This con
If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
which is 1. Then only if the table has changed will a duplicate update
statistics
command be run unless you add the new keyword "FORCE.
If you run update stats high on table t1 followed by the same
update stats command, The second command will be ignored.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 12/05/2011 10:47 AM
> Subject: Re: dbexport skipped update statistics high [25544]
> Sent by: ids-bounces@iiug.org
>
> Truth.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
nor
> those of the entities themselves.
>
> On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
<domusonline@gmail.com>wrote:
>
> > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > You are correct Fernando. Don't know where my head was earlier.
CREATE
> > > INDEX also produces HIGH distributions for the first column of each
> > index.
> > >
> > However, create index does NOT produce and stats for columns otherthan
the
> > > single lead column, and it does so indescriminately - meaning that if
> > > several indexes start with the same column you incur the cost of the
> > >
> >
> > True. Just HIGH for the heading column of the index. I cannot quantify
the
> > overhead of doing it "repeatedly" for several indexes starting with the
> > same column.
> > My feeling is that the biggest impact is on the ordering but that must
be
> > performed anyway.
> > In any case this is a good point. Any performance architect around? :)
> >
> > > distributions for that single lead column several times over. Anyway,
the
> > > HIGH stats on the second or later columns that differ when multiple
> > > indexes start with the same subset key are not performed and that can
> > make
> > > a big difference.
> > >
> >
> > Yes. Depending on the script you use for the stats. From your words I
> > imagine your's take this into consideration. I would have to check
mine...
> > In reality I only witnessed this in one situation (SAP system), but I'm
> > sure it can happen in a lot of cases
> > Maybe Bill is hitting something like this. But in any case, the base
> > problem should not tbe he lack of the UPDATE STATISTICS HIGH in the
export
> > file...
> > Regards
> >
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those
of
> > > other individuals affiliated with any entity with which I am
affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
> > > >wrote:
> > >
> > > > Art: Did you check that? That's not what the manual says and it's
not
> > > what
> > > > I thought... But I admit I didn't check it.
> > > > Meanwhile, accordingly to the manual, it should use a precision of
1.0
> > > for
> > > > tables with less than 1M rows and usually it uses 0.5.
> > > > Maybe this explains the bad performance...
> > > > Regards.
> > > >
> > > > On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com>
> > wrote:
> > > >
> > > > > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> > > > >
> > > > > Bill: Did you double check the source database? Dbexport should
be
> > > > > exporting exactly the stats that existed on the source, HIGH,
MEDIUM,
> > > > > whatever.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions
> > > > > and do not reflect on my employer, Advanced DataTools, the IIUG,
nor
> > > any
> > > > > other organization with which I am associated either explicitly,
> > > > > implicitly, or by inference. Neither do those opinions reflect
those
> > of
> > > > > other individuals affiliated with any entity with which I am
> > affiliated
> > > > nor
> > > > > those of the entities themselves.
> > > > >
> > > > > On Sun, Dec 4, 2011 at 11:45 PM, Bill Hamilton <
> > garage_dba@hotmail.com
> > > > > >wrote:
> > > > >
> > > > > > Both export server and import server are running 11.70.C3 32
bit.
> > > > > > The export was from win2008 32bit. The import was onto win2008
64
> > > bit.
> > > > > > That makes no difference since import/export uses ascii
> > output/input.
> > > > > >
> >
I tried translating this from the original Klingon. Alas, I had no luck.
--EEM
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>John Miller iii
>Sent: Monday, December 05, 2011 3:13 PM
>To: ids@iiug.org
>Subject: Re: dbexport skipped update statistics high [25549]
>
>If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
>which is 1. Then only if the table has changed will a duplicate update
>statistics
>command be run unless you add the new keyword "FORCE.
>
>If you run update stats high on table t1 followed by the same
>up
John, You're speaking in tongues again. j. On Dec 5, 2011, at 4:13 PM, John Miller iii wrote: > = SWYgeW91IGFyZSBvbiAxMS43MCBhbmQgeW91IGhhdmUgdGhlIGRlZmF1bHQgc2V0dGluZyBmb3= Ig=20 > = QVVUT19TVEFUU19NT0RFLA0Kd2hpY2ggaXMgMS4gIFRoZW4gb25seSBpZiB0aGUgdGFibGUgaG= Fz=20 > = IGNoYW5nZWQgd2lsbCBhIGR1cGxpY2F0ZSB1cGRhdGUNCnN0YXRpc3RpY3MNCmNvbW1hbmQgYm= Ug=20 > = cnVuIHVubGVzcyB5b3UgYWRkIHRoZSBuZXcga2V5d29yZCAiRk9SQ0UuDQoNCklmIHlvdSBydW= 4g=20 > = dXBkYXRlIHN0YXRzIGhpZ2ggb24gdGFibGUgdDEgZm9sbG93ZWQgYnkgdGhlIHNhbWUNCnVwZG= F0=20 > = ZSBzdGF0cyBjb21tYW5kLCAgVGhlIHNlY29uZCBjb21tYW5kIHdpbGwgYmUgaWdub3JlZC4NCg= 0K=20 > = DQoNCkpvaG4gRi4gTWlsbGVyIElJSQ0KU1RTTSwgRW1iZWRhYmlsaXR5IEFyY2hpdGVjdA0KbW= ls=20 > = bGVyM0B1cy5pYm0uY29tDQo1MDMtNTc4LTU2NDUNCklCTSBJbmZvcm1peCBEeW5hbWljIFNlcn= Zl=20 > = ciAoSURTKQ0KDQppZHMtYm91bmNlc0BpaXVnLm9yZyB3cm90ZSBvbiAxMi8wNS8yMDExIDEwOj= Q1=20 > [deletia] > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
Its Romulan
Cheers
Paul
> I tried translating this from the original Klingon. Alas, I had no luck.
>
> --EEM
>
>>-----Original Message-----
>>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>>John Miller iii
>>Sent: Monday, December 05, 2011 3:13 PM
>>To: ids@iiug.org
>>Subject: Re: dbexport skipped update statistics high [25549]
>>
>>If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
>>which is 1. Then only if the table has changed will a duplicate update
>>statistics
>>command be run unless you add the new keyword "FORCE.
>>
>>If you run update stats high on table t1 followed by the same
>>up
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
Paul Watson
Tel: +1 913-674-0360
Mob: +1 913-387-7529
Web: www.oninit.com
www.advancedatatools.com
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
Duh. Silly me.
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>Paul Watson
>Sent: Monday, December 05, 2011 3:30 PM
>To: ids@iiug.org
>Subject: RE: dbexport skipped update statistics high [25552]
>
>Its Romulan
>
>Cheers
>Paul
>
>> I tried translating this from the original Klingon. Alas, I had no
>luck.
>>
>> --EEM
>>
>>>-----Original Message-----
>>>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>>>John Miller iii
>>>Sent: Monday, December 05, 2011 3:13 PM
>>>To: ids@iiug.org
>>>Subject: Re: dbexport skipped update statistics high [25549]
>>>
>>>If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
>>>which is 1. Then only if the table has changed will a duplicate update
>>>statistics
>>>command be run unless you add the new keyword "FORCE.
>>>
>>>If you run update stats high on table t1 followed by the same
>>>Vw
>>
>>
>>
>************************************************************************
>*******
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>--
>Paul Watson
>Tel: +1 913-674-0360
>Mob: +1 913-387-7529
>Web: www.oninit.com
>
>www.advancedatatools.com
>
>Failure is not as frightening as regret.
>If you want to improve, be content to be thought foolish and stupid.
>
>
>************************************************************************
>*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
I took it in stride the first time, John, now it's just trash talk! ;-)
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Dec 5, 2011 at 4:13 PM, John Miller iii <miller3@us.ibm.com> wrote:
>
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 12/05/2011 10:47 AM
> > Subject: Re: dbexport skipped update statistics high [25544]
> > Sent by: ids-bounces@iiug.org
> >
> > Truth.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
> <domusonline@gmail.com>wrote:
> >
> > > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > >
> > > > You are correct Fernando. Don't know where my head was earlier.
> CREATE
> > > > INDEX also produces HIGH distributions for the first column of each
> > > index.
> > > >
> > > However, create index does NOT produce and stats for columns otherthan
> the
> > > > single lead column, and it does so indescriminately - meaning that if
>
> > > > several indexes start with the same column you incur the cost of the
> > > >
> > >
> > > True. Just HIGH for the heading column of the index. I cannot quantify
> the
> > > overhead of doing it "repeatedly" for several indexes starting with the
>
> > > same column.
> > > My feeling is that the biggest impact is on the ordering but that must
> be
> > > performed anyway.
> > > In any case this is a good point. Any performance architect around? :)
> > >
> > > > distributions for that single lead column several times over. Anyway,
> the
> > > > HIGH stats on the second or later columns that differ when multiple
> > > > indexes start with the same subset key are not performed and that can
>
> > > make
> > > > a big difference.
> > > >
> > >
> > > Yes. Depending on the script you use for the stats. From your words I
> > > imagine your's take this into consideration. I would have to check
> mine...
> > > In reality I only witnessed this in one situation (SAP system), but I'm
>
> > > sure it can happen in a lot of cases
> > > Maybe Bill is hitting something like this. But in any case, the base
> > > problem should not tbe he lack of the UPDATE STATISTICS HIGH in the
> export
> > > file...
> > > Regards
> > >
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
>
> > > > >wrote:
> > > >
> > > > > Art: Did you check that? That's not what the manual says and it's
> not
> > > > what
> > > > > I thought... But I admit I didn't check it.
> > > > > Meanwhile, accordingly to the manual, it should use a precision of
> 1.0
> > > > for
> > > > > tables with less than 1M rows and usually it uses 0.5.
> > > > > Maybe this explains the bad performance...
> > > > > Regards.
> > > > >
> > > > > On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com>
> > > wrote:
> > > > >
> > > > > > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> > > > > >
> > > > > > Bill: Did you double check the source database? Dbexport should
> be
> > > > > > exporting exactly the stats that existed on the source, HIGH,
> MEDIUM,
> > > > > > whatever.
> > > > > >
> > > > > > Art
> > > > > >
> > > > > > Art S. Kagel
> > > > > > Advance
Luckily last time I was at Remus I met Tal'Aura
Here's translation from Romulan:
If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
which is 1. Then only if the table has changed will a duplicate update
statistics
command be run unless you add the new keyword "FORCE.
If you run update stats high on table t1 followed by the same
update stats command, The second command will be ignored.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
On 05.12.2011. 22:13, John Miller iii wrote:
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 12/05/2011 10:47 AM
> > Subject: Re: dbexport skipped update statistics high [25544]
> > Sent by: ids-bounces@iiug.org
> >
> > Truth.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
> <domusonline@gmail.com>wrote:
> >
> > > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > >
> > > > You are correct Fernando. Don't know where my head was earlier.
> CREATE
> > > > INDEX also produces HIGH distributions for the first column of each
> > > index.
> > > >
> > > However, create index does NOT produce and stats for columns otherthan
> the
> > > > single lead column, and it does so indescriminately - meaning that if
>
> > > > several indexes start with the same column you incur the cost of the
> > > >
> > >
> > > True. Just HIGH for the heading column of the index. I cannot quantify
> the
> > > overhead of doing it "repeatedly" for several indexes starting with the
>
> > > same column.
> > > My feeling is that the biggest impact is on the ordering but that must
> be
> > > performed anyway.
> > > In any case this is a good point. Any performance architect around? :)
> > >
> > > > distributions for that single lead column several times over. Anyway,
> the
> > > > HIGH stats on the second or later columns that differ when multiple
> > > > indexes start with the same subset key are not performed and that can
>
> > > make
> > > > a big difference.
> > > >
> > >
> > > Yes. Depending on the script you use for the stats. From your words I
> > > imagine your's take this into consideration. I would have to check
> mine...
> > > In reality I only witnessed this in one situation (SAP system), but I'm
>
> > > sure it can happen in a lot of cases
> > > Maybe Bill is hitting something like this. But in any case, the base
> > > problem should not tbe he lack of the UPDATE STATISTICS HIGH in the
> export
> > > file...
> > > Regards
> > >
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
>
> > > > >wrote:
> > > >
> > > > > Art: Did you check that? That's not what the manual says and it's
> not
> > > > what
> > > > > I thought... But I admit I didn't check it.
> > > > > Meanwhile, accordingly to the manual, it should use a precision of
> 1.0
> > > > for
> > > > > tables with less than 1M rows and usually it uses 0.5.
> > > > > Maybe this explains the bad performance...
> > > > > Regards.
> > > > >
> > > > > On Mon, Dec 5, 2011 at 11:40 AM, Art Kagel <art.kagel@gmail.com>
> > > wrote:
> > > > >
> > > > > > Fernando: CREATE INDEX only does MEDIUM on the key columns!
> > > > > >
> > > > > > Bill: Did you double check the source database? Dbexport should
> be
> > > > > > exporting exactly the stats that existed on the source, HIGH,
> MEDIUM,
> > > > > > whatever.
> > > > > >
> > > > > > Art
> > > > > >
> > > > > > Art S. Kagel
> > > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > > Blog: http://informix-myview.blogspot.com/
> > > > > >
> > > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > opinions
> > > > > > and do not reflect on my employer, Advanced DataTools, the IIUG,
> nor
> > > > any
> > > > > > other organization with which I am associated either explicitly,
> > > > > > implicitly, or by inference. Neither do tho
I just tested this one i n11.70.FC3 yesterday with AUTO_STAT_MODE set to
1. If I build multiple indexes starting with the same column the create
datetime on the sysdistrib records for the lead column on that table will
reflect the time of creation of the last of these indexes not the first.
So, the AUTO_STAT_MODE may be effective for manual update statistics
commands (tested that a while ago) but it does not seem to affect the stats
created when you build an index.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 6, 2011 at 2:33 AM, Hrvoje Zokovic <hzokovic.iiug@gmail.com>wrote:
> Luckily last time I was at Remus I met Tal'Aura
> Here's translation from Romulan:
>
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> On 05.12.2011. 22:13, John Miller iii wrote:
> >
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 12/05/2011 10:47 AM
> > Subject: Re: dbexport skipped update statistics high [25544]
> > Sent by: ids-bounces@iiug.org
> >
> > Truth.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
> <domusonline@gmail.com>wrote:
> >
> > > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > >
> > > > You are correct Fernando. Don't know where my head was earlier.
> CREATE
> > > > INDEX also produces HIGH distributions for the first column of each
> > > index.
> > > >
> > > However, create index does NOT produce and stats for columns otherthan
> the
> > > > single lead column, and it does so indescriminately - meaning that if
>
> > > > several indexes start with the same column you incur the cost of the
> > > >
> > >
> > > True. Just HIGH for the heading column of the index. I cannot quantify
> the
> > > overhead of doing it "repeatedly" for several indexes starting with the
>
> > > same column.
> > > My feeling is that the biggest impact is on the ordering but that must
> be
> > > performed anyway.
> > > In any case this is a good point. Any performance architect around? :)
> > >
> > > > distributions for that single lead column several times over. Anyway,
> the
> > > > HIGH stats on the second or later columns that differ when multiple
> > > > indexes start with the same subset key are not performed and that can
>
> > > make
> > > > a big difference.
> > > >
> > >
> > > Yes. Depending on the script you use for the stats. From your words I
> > > imagine your's take this into consideration. I would have to check
> mine...
> > > In reality I only witnessed this in one situation (SAP system), but I'm
>
> > > sure it can happen in a lot of cases
> > > Maybe Bill is hitting something like this. But in any case, the base
> > > problem should not tbe he lack of the UPDATE STATISTICS HIGH in the
> export
> > > file...
> > > Regards
> > >
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Mon, Dec 5, 2011 at 7:31 AM, Fernando Nunes <domusonline@gmail.com
>
> > > > >wrote:
> > > >
> > > > > Art: Did you check t
Just curious .... How do you test this? Use dbschema -hd <table> over and
over and compare outputs?
-----Original Message-----
From: Art Kagel
Sent: Tuesday, December 06, 2011 5:16 AM
To: ids@iiug.org
Subject: Re: dbexport skipped update statistics high [25558]
I just tested this one i n11.70.FC3 yesterday with AUTO_STAT_MODE set to
1. If I build multiple indexes starting with the same column the create
datetime on the sysdistrib records for the lead column on that table will
reflect the time of creation of the last of these indexes not the first.
So, the AUTO_STAT_MODE may be effective for manual update statistics
commands (tested that a while ago) but it does not seem to affect the stats
created when you build an index.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 6, 2011 at 2:33 AM, Hrvoje Zokovic
<hzokovic.iiug@gmail.com>wrote:
> Luckily last time I was at Remus I met Tal'Aura
> Here's translation from Romulan:
>
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> On 05.12.2011. 22:13, John Miller iii wrote:
> >
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 12/05/2011 10:47 AM
> > Subject: Re: dbexport skipped update statistics high [25544]
> > Sent by: ids-bounces@iiug.org
> >
> > Truth.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
> <domusonline@gmail.com>wrote:
> >
> > > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > >
> > > > You are correct Fernando. Don't know where my head was earlier.
> CREATE
> > > > INDEX also produces HIGH distributions for the first column of each
> > > index.
> > > >
> > > However, create index does NOT produce and stats for columns otherthan
> the
> > > > single lead column, and it does so indescriminately - meaning that if
>
> > > > several indexes start with the same column you incur the cost of the
> > > >
> > >
> > > True. Just HIGH for the heading column of the index. I cannot quantify
> the
> > > overhead of doing it "repeatedly" for several indexes starting with the
>
> > > same column.
> > > My feeling is that the biggest impact is on the ordering but that must
> be
> > > performed anyway.
> > > In any case this is a good point. Any performance architect around? :)
> > >
> > > > distributions for that single lead column several times over. Anyway,
> the
> > > > HIGH stats on the second or later columns that differ when multiple
> > > > indexes start with the same subset key are not performed and that can
>
> > > make
> > > > a big difference.
> > > >
> > >
> > > Yes. Depending on the script you use for the stats. From your words I
> > > imagine your's take this into consideration. I would have to check
> mine...
> > > In reality I only witnessed this in one situation (SAP system), but I'm
>
> > > sure it can happen in a lot of cases
> > > Maybe Bill is hitting something like this. But in any case, the base
> > > problem should not tbe he lack of the UPDATE STATISTICS HIGH in the
> export
> > > file...
> > > Regards
> > >
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
I actually just look at the sysdistrib records for the table before and
after each CREATE INDEX. I build three or four indexes that all began with
the same column and the constr_time column value changed after each index
build. But using dbschema -hd would work just as well.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 6, 2011 at 6:01 PM, Bill Hamilton <garage_dba@hotmail.com>wrote:
> Just curious .... How do you test this? Use dbschema -hd <table> over and
> over and compare outputs?
>
> -----Original Message-----
> From: Art Kagel
> Sent: Tuesday, December 06, 2011 5:16 AM
> To: ids@iiug.org
> Subject: Re: dbexport skipped update statistics high [25558]
>
> I just tested this one i n11.70.FC3 yesterday with AUTO_STAT_MODE set to
> 1. If I build multiple indexes starting with the same column the create
> datetime on the sysdistrib records for the lead column on that table will
> reflect the time of creation of the last of these indexes not the first.
> So, the AUTO_STAT_MODE may be effective for manual update statistics
> commands (tested that a while ago) but it does not seem to affect the stats
> created when you build an index.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Tue, Dec 6, 2011 at 2:33 AM, Hrvoje Zokovic
> <hzokovic.iiug@gmail.com>wrote:
>
> > Luckily last time I was at Remus I met Tal'Aura
> > Here's translation from Romulan:
> >
> > If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> > which is 1. Then only if the table has changed will a duplicate update
> > statistics
> > command be run unless you add the new keyword "FORCE.
> >
> > If you run update stats high on table t1 followed by the same
> > update stats command, The second command will be ignored.
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > On 05.12.2011. 22:13, John Miller iii wrote:
> > >
> >
> If you are on 11.70 and you have the default setting for AUTO_STATS_MODE,
> which is 1. Then only if the table has changed will a duplicate update
> statistics
> command be run unless you add the new keyword "FORCE.
>
> If you run update stats high on table t1 followed by the same
> update stats command, The second command will be ignored.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 12/05/2011 10:45:16 AM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 12/05/2011 10:47 AM
> > Subject: Re: dbexport skipped update statistics high [25544]
> > Sent by: ids-bounces@iiug.org
> >
> > Truth.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 5, 2011 at 11:41 AM, Fernando Nunes
> <domusonline@gmail.com>wrote:
> >
> > > On Mon, Dec 5, 2011 at 4:10 PM, Art Kagel <art.kagel@gmail.com> wrote:
> > >
> > > > You are correct Fernando. Don't know where my head was earlier.
> CREATE
> > > > INDEX also produces HIGH distributions for the first column of each
> > > index.
> > > >
> > > However, create index does NOT produce and stats for columns otherthan
> the
> > > > single lead column, and it does so indescriminately - meaning that if
>
> > > > several indexes start with the same column you incur the cost of the
> > > >
> > >
> > > True. Just HIGH for the heading column of the index. I cannot quantify
> the
> > > overhead of doing it "repeatedly" for several indexes starting with the
>
> > > same column.
> > > My feeling is that the biggest impact is on the ordering but that must
> be
> > > performed anyway.
> > > In any case this is a good point. Any performance architect around? :)
> > >
> > > > distributions for that single lead column several times over. Anyway,
> the
> > > > HIGH stats on the second or later columns that differ when multiple
> > > > indexes start with the same subset key are not performed and that can
>
> > > make
> > > > a big difference.
> > > >
> > >
> > > Yes. Depending on the script you use for the stats. F