IDS Forum

Re: Query to find in-place alter pending tables running forever (Detecting

Posted By: Jonathan Leffler
Date: Wednesday, 3 November 2004, at 3:30 p.m.

In Response To: Query to find in-place alter pending tables running forever (Vineet Mehrotra )

forum.subscriber@iiug.org wrote on 11/03/2004 08:53:56 AM:

> IHAC who is planning to upgrade from IDS 7.31 UD6 to
> 9.40 FC4. Now as part
> of the upgrade plan, we would like to run a dummy
> update for tables which
> are in in-place alter pending state.
>
> We have 5 prodction instances on two separate unix
> servers. On one of the
> servers, the query is running fine and returned the
> result set pretty
> fast. But on the other instance, it is just sleeping
> forever. I tried
> running set explain on and the place where it is
> running slow, it is doing
> a sequential scan on one of the tables. Now my
> question is, how can I
> change the behavior to do index scan.
>
> here is what I am doing.
>
> 1. Set OPTCOMPIND to 0.
>
> 2. Run the query
>
> dbaccess sysmaster << EOF
>
> set isolation to dirty read;
> select pg_partnum + pg_pagenum - 1 partn
> from syspaghdr, sysdbspaces a
> where pg_partnum = 1048576 * a.dbsnum + 1
> and pg_next != 0
> into temp pp with no log;
>
> select b.dbsname database, b.tabname table
> from systabnames b, pp where partn =
> partnum;
> EOF
>
> 3. Here is the set explain where it is running fine
>
> QUERY:
> ------
> select pg_partnum + pg_pagenum - 1 partn
> from syspaghdr, sysdbspaces a
> where pg_partnum = 1048576 * a.dbsnum + 1
> and pg_next != 0
> into temp pp with no log
>
> Estimated Cost: 6
> Estimated # of Rows Returned: 90
>
> 1) informix.sysdbstab: INDEX PATH
>
> (1) Index Keys: dbsnum (Key-Only)
> Lower Index Filter: informix.sysdbstab.dbsnum
> > 0
>
> 2) informix.syspaghdr: INDEX PATH
>
> Filters: informix.syspaghdr.pg_next != 0
>
> (1) Index Keys: pg_partnum pg_pagenum
> Lower Index Filter:
> informix.syspaghdr.pg_partnum = 1048576 *
> informix.sysdbstab.dbsnum + 1
>
> NESTED LOOP JOIN
>
>
> QUERY:
> ------
> select b.dbsname database, b.tabname table
> from systabnames b, pp where partn = partnum
>
> Estimated Cost: 10
> Estimated # of Rows Returned: 10
>
> 1) informix.pp: SEQUENTIAL SCAN (Serial, fragments:
> ALL)
>
> 2) informix.b: INDEX PATH
>
> (1) Index Keys: partnum
> Lower Index Filter: informix.b.partnum =
> informix.pp.partn
> NESTED LOOP JOIN
>
> 4. Here is the explain output where it is sleeping
> forever
>
>
> QUERY:
> ------
> select pg_partnum + pg_pagenum - 1 partn
> from syspaghdr, sysdbspaces a
> where pg_partnum = 1048576 * a.dbsnum + 1
> and pg_next != 0
> into temp pp with no log
>
> Estimated Cost: 30
> Estimated # of Rows Returned: 4
>
> 1) informix.syspaghdr: SEQUENTIAL SCAN
>
> Filters: informix.syspaghdr.pg_next != 0
>
> 2) informix.sysdbstab: INDEX PATH
>
> Filters: informix.syspaghdr.pg_partnum = 1048576 *
>
> informix.sysdbstab.dbsnum + 1
>
> (1) Index Keys: dbsnum (Key-Only)
> Lower Index Filter: informix.sysdbstab.dbsnum > 0
> NESTED LOOP JOIN
>
> Any ideas what I should be doing to make it run fast.

Observation 1: the sequential scan is on the temporary table pp.
Observation 2: you can run update statistics on temporary tables.
Observation 3: you can create indexes on temporary tables.

Consequently, you could modify that query to run update statistics on the
temp table and/or add an index on it (before running update statistics).

The query might still take a long time.

You can also detect outstanding IPAs by looking at the information from
'oncheck -pT'. Be a bit wary of that (you probably don't want to run it
across all databases at once, for example), but it can be done.

The script attached below (which went via a PC so it probably has
extraneous ^M characters at the ends of the lines) is a pure Perl script
that analyzes the text output from 'oncheck -pT' looking for outstanding
in-place alters. I'm submitting it to the IIUG Software Archive too.
There's not much rocket science in it - you simply have to know what to
look for and how to deduce whether there are any outstanding IPAs. It is
does know about IDS 9.50 (but there's always a chance something might
change between now and the GA date) - it has also been tested on (selected
versions of) 9.40, 9.30, and 7.31. It has been tested with Perl 5.5.3,
5.6.1, 5.8.0 and 5.8.5. There's an outside chance that one of the two
modules it uses (File::Basename, Getopt::Std) is not available on the
vanilla install of Perl, but I think they are standard core modules.




I apologize if this gets butchered on the way to the mailing lists.

Incidentally, to remove outstanding IPAs, it is necessary only to ensure
that one row on each page is updated. It is also a good idea, in general,
to limit the amount of work in an individual transaction. So, if you're
going to remove the IPA with a dummy update, you probably have to update
all rows (because it isn't easy to work out how to fix things so each page
is updated once), but you should look at whether you can exploit any range
partitioning on your fragmented tables (round robin is bad for this). The
report above produces information about each fragment in turn - you should
aim to exploit that if possible.

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"

Messages In This Thread

• Query to find in-place alter pending tables running forever
  Vineet Mehrotra -- Wednesday, 3 November 2004, at 11:53 a.m.
  • Re: Query to find in-place alter pending tables running forever (Detecting
    Jonathan Leffler -- Wednesday, 3 November 2004, at 3:30 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.