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...@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;
On Oct 14, 3:20 pm, informixdba <roy.mer...@gmail.com> wrote:
> On Oct 14, 3:05 pm, "steven_nospam at Yahoo! Canada"
>
>
>
>
>
> <steven_nos...@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