Re: Is there a way to see what UPDATE STATISTICS level was last run?
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@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...@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;
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
----- End of original message -----