 |
|
| Computers Forum Index » Computer - Databases - Informix » Is there a way to see what UPDATE STATISTICS level was... |
|
Page 1 of 1 |
|
| Author |
Message |
| steven_nospam at Yahoo! Canada... |
 Posted: Wed Oct 14, 2009 7:05 pm |
|
|
|
Guest
|
We have two Informix 9.4 FC7 instances (call them TEST and PROD) that
were inherited from a previous management team. As part of our
mandate, our goal is to upgrade the system to a more modern version of
Informix that is still certified by our third-party software vendor
with their product.
We recently upgraded the TEST instance to Informix 11.10 FC2 and after
doing so ran UPDATE STATISTICS LOW as most of the queries the software
does are SQL statements that only make use of one table, and they have
indexes designed specifically for those tables to reduce temporary
dbspace usage and the performance hit. The software that makes calls
to the databases uses hints to make sure that it uses the correct
index.
Our problem is that some of our people have been developing their own
queries that are more complex, and we have noticed a severe slowdown
in performance related to these queries. Single table queries: really
fast...Queries that use multiple tables and OUTER joins: really slow.
Example of a problem query:
BEFORE Informix 11: 381 rows per second
AFTER Informix 11: 89 rows per second
I don't want anyone to try and optimize the code or suggest ways to
improve doing our queries, as that is something our team has to deal
with internally.
What I would like to know is if sysmaster database or some other area
of the system keeps track of the UPDATE STATISTICS command(s) that
were used in a database previously?
For example, if I wanted to check the production (PROD) database to
see what the last settings were for calling UPDATE STATISTICS, to see
if individual tables may have been adjusted to make the searching more
efficient.
I ask this because the users are telling me that the previous DB team
had to issue some sort of special UPDATE STATISTICS commands to fine-
tune things on the production database, and being able to find out
some detail on this would save me a lot of running queries with SET
EXPLAIN on.
I'm afraid I'm more of an UNIX person than a DB person, so forgive me
if I left anything out or used the wrong terminology.
Steve |
|
|
| Back to top |
|
|
|
| informixdba... |
| Posted: Wed Oct 14, 2009 7:20 pm |
|
|
|
Guest
|
On Oct 14, 3:05 pm, "steven_nospam at Yahoo! Canada"
<steven_nos... at (no spam) yahoo.ca> wrote:
Quote: We have two Informix 9.4 FC7 instances (call them TEST and PROD) that
were inherited from a previous management team. As part of our
mandate, our goal is to upgrade the system to a more modern version of
Informix that is still certified by our third-party software vendor
with their product.
We recently upgraded the TEST instance to Informix 11.10 FC2 and after
doing so ran UPDATE STATISTICS LOW as most of the queries the software
does are SQL statements that only make use of one table, and they have
indexes designed specifically for those tables to reduce temporary
dbspace usage and the performance hit. The software that makes calls
to the databases uses hints to make sure that it uses the correct
index.
Our problem is that some of our people have been developing their own
queries that are more complex, and we have noticed a severe slowdown
in performance related to these queries. Single table queries: really
fast...Queries that use multiple tables and OUTER joins: really slow.
Example of a problem query:
BEFORE Informix 11: 381 rows per second
AFTER Informix 11: 89 rows per second
I don't want anyone to try and optimize the code or suggest ways to
improve doing our queries, as that is something our team has to deal
with internally.
What I would like to know is if sysmaster database or some other area
of the system keeps track of the UPDATE STATISTICS command(s) that
were used in a database previously?
For example, if I wanted to check the production (PROD) database to
see what the last settings were for calling UPDATE STATISTICS, to see
if individual tables may have been adjusted to make the searching more
efficient.
I ask this because the users are telling me that the previous DB team
had to issue some sort of special UPDATE STATISTICS commands to fine-
tune things on the production database, and being able to find out
some detail on this would save me a lot of running queries with SET
EXPLAIN on.
I'm afraid I'm more of an UNIX person than a DB person, so forgive me
if I left anything out or used the wrong terminology.
Steve
Here is a very poor query that will report back what level of update
stats was run on each column.
Ugly, but it works.
set isolation to dirty read;
select unique t.tabid, t.tabname, case t.nrows when 0 then '*' else '
' end nr, c.colname, c.colno, d.mode, d.constructed from sysdistrib d,
outer systables t, syscolumns c where d.tabid = t.tabid
and d.tabid = c.tabid
and d.colno = c.colno
and t.tabid > 99
order by t.tabid
into temp stats with no log;
select unique s.*, "1" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part1
union
select unique s.*, "2" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part2
union
select unique s.*, "3" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part3
union
select unique s.*, "4" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part4
union
select unique s.*, "5" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part5
union
select unique s.*, "6" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part6
union
select unique s.*, "7" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part7
union
select unique s.*, "8" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part8
union
select unique s.*, "9" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part9
union
select unique s.*, "10" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part10
union
select unique s.*, "11" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part11
union
select unique s.*, "12" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part12
union
select unique s.*, "13" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part13
union
select unique s.*, "14" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part14
union
select unique s.*, "15" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part15
union
select unique s.*, "16" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part16
into temp stats2 with no log;
select tabid, tabname[1,18], nr, colname[1,18], head, mode,
constructed from stats2 order by 7,1,4,5; |
|
|
| Back to top |
|
|
|
| Floyd Wellershaus... |
| Posted: Wed Oct 14, 2009 7:48 pm |
|
|
|
Guest
|
Not sure about version 11, we are running 10, but sysdistrib has the
tabid, last_constructed ( which I believe is the last time they were run
), mode ( medium, high low ), resolution and confidence. I think that's
all you need.
Floyd
----- Original Message -----
From: "informixdba" <roy.mercer at (no spam) gmail.com>
Sent: Wed, October 14, 2009 15:20
Subject:Re: Is there a way to see what UPDATE STATISTICS level was last run?
On Oct 14, 3:05 pm, "steven_nospam at Yahoo! Canada"
<steven_nos... at (no spam) yahoo.ca> wrote:
Quote: We have two Informix 9.4 FC7 instances (call them TEST and PROD) that
were inherited from a previous management team. As part of our
mandate, our goal is to upgrade the system to a more modern version of
Informix that is still certified by our third-party software vendor
with their product.
We recently upgraded the TEST instance to Informix 11.10 FC2 and after
doing so ran UPDATE STATISTICS LOW as most of the queries the software
does are SQL statements that only make use of one table, and they have
indexes designed specifically for those tables to reduce temporary
dbspace usage and the performance hit. The software that makes calls
to the databases uses hints to make sure that it uses the correct
index.
Our problem is that some of our people have been developing their own
queries that are more complex, and we have noticed a severe slowdown
in performance related to these queries. Single table queries: really
fast...Queries that use multiple tables and OUTER joins: really slow.
Example of a problem query:
BEFORE Informix 11: 381 rows per second
AFTER Informix 11: 89 rows per second
I don't want anyone to try and optimize the code or suggest ways to
improve doing our queries, as that is something our team has to deal
with internally.
What I would like to know is if sysmaster database or some other area
of the system keeps track of the UPDATE STATISTICS command(s) that
were used in a database previously?
For example, if I wanted to check the production (PROD) database to
see what the last settings were for calling UPDATE STATISTICS, to see
if individual tables may have been adjusted to make the searching more
efficient.
I ask this because the users are telling me that the previous DB team
had to issue some sort of special UPDATE STATISTICS commands to fine-
tune things on the production database, and being able to find out
some detail on this would save me a lot of running queries with SET
EXPLAIN on.
I'm afraid I'm more of an UNIX person than a DB person, so forgive me
if I left anything out or used the wrong terminology.
Steve
Here is a very poor query that will report back what level of update
stats was run on each column.
Ugly, but it works.
set isolation to dirty read;
select unique t.tabid, t.tabname, case t.nrows when 0 then '*' else '
' end nr, c.colname, c.colno, d.mode, d.constructed from sysdistrib d,
outer systables t, syscolumns c where d.tabid = t.tabid
and d.tabid = c.tabid
and d.colno = c.colno
and t.tabid > 99
order by t.tabid
into temp stats with no log;
select unique s.*, "1" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part1
union
select unique s.*, "2" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part2
union
select unique s.*, "3" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part3
union
select unique s.*, "4" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part4
union
select unique s.*, "5" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part5
union
select unique s.*, "6" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part6
union
select unique s.*, "7" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part7
union
select unique s.*, "8" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part8
union
select unique s.*, "9" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part9
union
select unique s.*, "10" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part10
union
select unique s.*, "11" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part11
union
select unique s.*, "12" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part12
union
select unique s.*, "13" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part13
union
select unique s.*, "14" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part14
union
select unique s.*, "15" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part15
union
select unique s.*, "16" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part16
into temp stats2 with no log;
select tabid, tabname[1,18], nr, colname[1,18], head, mode,
constructed from stats2 order by 7,1,4,5;
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
----- End of original message ----- |
|
|
| Back to top |
|
|
|
| steven_nospam at Yahoo! Canada... |
| Posted: Wed Oct 14, 2009 7:48 pm |
|
|
|
Guest
|
On Oct 14, 3:20 pm, informixdba <roy.mer... at (no spam) gmail.com> wrote:
Quote: On Oct 14, 3:05 pm, "steven_nospam at Yahoo! Canada"
steven_nos... at (no spam) yahoo.ca> wrote:
We have two Informix 9.4 FC7 instances (call them TEST and PROD) that
were inherited from a previous management team. As part of our
mandate, our goal is to upgrade the system to a more modern version of
Informix that is still certified by our third-party software vendor
with their product.
We recently upgraded the TEST instance to Informix 11.10 FC2 and after
doing so ran UPDATE STATISTICS LOW as most of the queries the software
does are SQL statements that only make use of one table, and they have
indexes designed specifically for those tables to reduce temporary
dbspace usage and the performance hit. The software that makes calls
to the databases uses hints to make sure that it uses the correct
index.
Our problem is that some of our people have been developing their own
queries that are more complex, and we have noticed a severe slowdown
in performance related to these queries. Single table queries: really
fast...Queries that use multiple tables and OUTER joins: really slow.
Example of a problem query:
BEFORE Informix 11: 381 rows per second
AFTER Informix 11: 89 rows per second
I don't want anyone to try and optimize the code or suggest ways to
improve doing our queries, as that is something our team has to deal
with internally.
What I would like to know is if sysmaster database or some other area
of the system keeps track of the UPDATE STATISTICS command(s) that
were used in a database previously?
For example, if I wanted to check the production (PROD) database to
see what the last settings were for calling UPDATE STATISTICS, to see
if individual tables may have been adjusted to make the searching more
efficient.
I ask this because the users are telling me that the previous DB team
had to issue some sort of special UPDATE STATISTICS commands to fine-
tune things on the production database, and being able to find out
some detail on this would save me a lot of running queries with SET
EXPLAIN on.
I'm afraid I'm more of an UNIX person than a DB person, so forgive me
if I left anything out or used the wrong terminology.
Steve
Here is a very poor query that will report back what level of update
stats was run on each column.
Ugly, but it works.
set isolation to dirty read;
select unique t.tabid, t.tabname, case t.nrows when 0 then '*' else '
' end nr, c.colname, c.colno, d.mode, d.constructed from sysdistrib d,
outer systables t, syscolumns c where d.tabid = t.tabid
and d.tabid = c.tabid
and d.colno = c.colno
and t.tabid > 99
order by t.tabid
into temp stats with no log;
select unique s.*, "1" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part1
union
select unique s.*, "2" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part2
union
select unique s.*, "3" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part3
union
select unique s.*, "4" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part4
union
select unique s.*, "5" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part5
union
select unique s.*, "6" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part6
union
select unique s.*, "7" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part7
union
select unique s.*, "8" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part8
union
select unique s.*, "9" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part9
union
select unique s.*, "10" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part10
union
select unique s.*, "11" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part11
union
select unique s.*, "12" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part12
union
select unique s.*, "13" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part13
union
select unique s.*, "14" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part14
union
select unique s.*, "15" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part15
union
select unique s.*, "16" Head
from stats s, sysindexes i
where s.tabid = i.tabid
and s.colno = i.part16
into temp stats2 with no log;
select tabid, tabname[1,18], nr, colname[1,18], head, mode,
constructed from stats2 order by 7,1,4,5;- Hide quoted text -
- Show quoted text -
Thanks, it worked. Basically showed me that the production database
was run at "HIGH" (mode = H) while we were using "LOW" in the test
database. I'm going to change the UPDATE STATISTICS in test area and
have our users try their personal queries again.
Steve |
|
|
| Back to top |
|
|
|
| Art Kagel... |
| Posted: Thu Oct 15, 2009 12:39 am |
|
|
|
Guest
|
In your older versions (ie 9.40) only the data distributions recorded their
create times. Look at the sysdistrib catalog table, you will find the
'constructed' column which has the date on which the distributions for that
column were last calculated and the 'mode', 'resolution', and 'confidence'
columns indicate what level and quality of distributions were generated.
Details about when LOW distributions were run was not retained in this
version.
In 11.10 and later, there is additional information available including the
time that distributions were gathered and the date and time that LOW
statistics were generated are kept in the syscolumns and sysindices tables
as well as details about sampling size for MEDIUM distributions which is a
new option in 11.10 and later.
As to what stats to gather, it is best to follow the recommendations in the
Performance Guide and in John Miller III's white paper on the subject, or,
just get my dostats utility which implements these protocols automatically.
Dostats is part of the package utils2_ak which you can download free from
either the Oninit WEB site (www.oninit.com/utils) or from the IIUG Software
Repository (www.iiug.org/software).
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art at (no spam) iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Wed, Oct 14, 2009 at 3:05 PM, steven_nospam at Yahoo! Canada <
steven_nospam at (no spam) yahoo.ca> wrote:
Quote: We have two Informix 9.4 FC7 instances (call them TEST and PROD) that
were inherited from a previous management team. As part of our
mandate, our goal is to upgrade the system to a more modern version of
Informix that is still certified by our third-party software vendor
with their product.
We recently upgraded the TEST instance to Informix 11.10 FC2 and after
doing so ran UPDATE STATISTICS LOW as most of the queries the software
does are SQL statements that only make use of one table, and they have
indexes designed specifically for those tables to reduce temporary
dbspace usage and the performance hit. The software that makes calls
to the databases uses hints to make sure that it uses the correct
index.
Our problem is that some of our people have been developing their own
queries that are more complex, and we have noticed a severe slowdown
in performance related to these queries. Single table queries: really
fast...Queries that use multiple tables and OUTER joins: really slow.
Example of a problem query:
BEFORE Informix 11: 381 rows per second
AFTER Informix 11: 89 rows per second
I don't want anyone to try and optimize the code or suggest ways to
improve doing our queries, as that is something our team has to deal
with internally.
What I would like to know is if sysmaster database or some other area
of the system keeps track of the UPDATE STATISTICS command(s) that
were used in a database previously?
For example, if I wanted to check the production (PROD) database to
see what the last settings were for calling UPDATE STATISTICS, to see
if individual tables may have been adjusted to make the searching more
efficient.
I ask this because the users are telling me that the previous DB team
had to issue some sort of special UPDATE STATISTICS commands to fine-
tune things on the production database, and being able to find out
some detail on this would save me a lot of running queries with SET
EXPLAIN on.
I'm afraid I'm more of an UNIX person than a DB person, so forgive me
if I left anything out or used the wrong terminology.
Steve
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
|
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sun Mar 21, 2010 11:34 pm
|
|