HI ALL,

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.

Regards,

Vineet




__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com


sending to informix-list

select
{+ ORDERED }
pg_partnum + pg_pagenum - 1 partn
from
sysdbspaces a,
syspaghdr
where pg_partnum = 1048576 * a.dbsnum + 1
and pg_next != 0
into temp pp with no log

otherwise you will read each and every page in your instance and this
may give you grey hair whenever it is finished dependant on
how big your instance is...

See you

Superboer.


Vineet Mehrotra <vin_us@yahoo.com> wrote in message news:<cmb8jh$kio$1@news.xmission.com>...
> HI ALL,
>
> 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.
>
> Regards,
>
> Vineet
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Check out the new Yahoo! Front Page.
> www.yahoo.com
>
>
> sending to informix-list

superboer wrote:
> select
> {+ ORDERED }
> pg_partnum + pg_pagenum - 1 partn
> from
> sysdbspaces a,
> syspaghdr
> where pg_partnum = 1048576 * a.dbsnum + 1
> and pg_next != 0
> into temp pp with no log
>
> otherwise you will read each and every page in your instance and this
> may give you grey hair whenever it is finished dependant on
> how big your instance is...

Without wishing in any way to detract from what Superboer wrote...

Vineet asked this question during the outage at the IIUG, and I
responded on one of the IIUG mailing lists with the attached Perl script.

The poor performing query was doing a sequential scan on the temp
table created by the query - and that it is conceivable that either an
index or an update statistics (or both) on the temp table would
improve performance.

The output of 'oncheck -pT' contains the information about IPAs (in
amongst a lot of other information). The attached Perl script (which
has been tested with Perl 5.5.3, 5.6.1, 5.8.5 on output from IDS 7.31,
9.30, 9.40 and 9.50 - fragmented and non-fragmented tables) diagnoses
partitions (fragments) of tables with outstanding IPAs quite handily.
(Vineet expressed satisfaction with it.)

I sent the message to software@iiug.org for inclusion in the IIUG
Software Archive, but it has not made it there yet -- I suspect the
team is still a bit busy recovering from the carnage.

> Vineet Mehrotra <vin_us@yahoo.com> wrote:
>> 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
>>
>> [...snippage...]
>>sending to informix-list

The original attachment contained DOS line endings; this version has
Unix line endings.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

#!/usr/bin/perl -w
#
# @(#)$Id: ipa.info.pl,v 1.10 2004/10/21 19:46:44 jleffler Exp $
#
# Extract outstanding IPA information from 'oncheck -pT' output
#
# Usage:
# ipa.info [-V][-a][-l][-d N] [file...]
# By default, only list only table fragments with outstanding
# IPAs (databasewner.table, with partition and dbspace
# information if available). Input files should be the output
# from 'oncheck -pT'.
# -a - list all fragments and whether there is an outstanding IPA.
# -l - list all IPA version records
# -d N - set debug to level N (0, 1, 2 are relevant). Level 0 is
# default. Level 1 lists all IPA version records and implies
# '-a'. Level 2 tracks the input data too.
# -V - print version information and exit.
#
# Based on sample output from IDS 9.50.UC1, 9.40.UC4, 7.31.UD5, 9.30.UC3.
# Examples included non-fragmented tables, fragmented tables, and tables
# partitioned (fragmented) with multiple fragments in a single dbspace
# (9.50 feature), with and without outstanding IPAs. Some of the
# fragmented tables only had IPAs outstanding on some fragments.
#
# Tested with Perl 5.005_03, 5.6.1, 5.8.0, 5.8.5.

use strict;
use Getopt::Std;
use File::Basename;

my %opts;

getopts('ald:V', \%opts);

if (defined $opts{V})
{
my $VERSION = sprintf("%d.%02d", q$Revision: 1.10 $ =~ /(\d+)\.(\d+)/);
my $arg0 = basename $0;
print "$arg0 version $VERSION\n";
exit 0;
}

my $aflag = (defined $opts{a}) ? 1 : 0;
my $lflag = (defined $opts{l}) ? 1 : 0;
my $debug = (defined $opts{d}) ? $opts{d} : 0;
sub debug { return $debug; }

# Read and discard a line - with optional trace.
sub read_discard
{
my($n) = @_;
$_ = <>;
chomp;
print "XX discard $n: $_\n" if debug > 1;
}

# Array or hash for versions? Advantages and disadvantages to both.
my($dbase, $owner, $table, $partition, $dbspace, %versions);
my $stage = 0;

while (<>)
{
chomp;
if (/^TBLspace Report for (\w+)\w+)\.(\w+)/)
{
$stage = 1;
print "== retain 1: $_\n" if debug > 1;
$dbase = $1;
$owner = $2;
$table = $3;
$partition = "";
$dbspace = "";
%versions = (); # Necessary for non-fragmented tables.
}
elsif (/Table fragment( partition (\w+))? in DBspace (\w+)/)
{
# 9.50 - Table fragment partition p0 in DBspace rootdbs
# 9.40 - Table fragment in DBspace rootdbs
print "== retain 2: $_\n" if debug > 1;
$partition = $2;
$dbspace = $3;
$stage = 2;
%versions = (); # Necessary for fragmented tables.
}
elsif (/Home Data Page Version Summary/ && $stage > 0)
{
print "== retain 3: $_\n" if debug > 1;
read_discard(1); # Blank line
read_discard(2); # Version/Count header
read_discard(3); # Blank line
my($pages, $sum, $min, $max) = (0, 0, undef, 0);
while (<>)
{
chomp;
last if (/^\s*$/);
last unless m/(\d+)\s+(\((oldest|current)\)\s+)?(\d+)/;
print "== retain 4: $_\n" if debug > 1;
$pages = $4;
$versions{$1} = $pages;
$sum += $pages;
$min = $1 unless defined $min;
$max = $1;
#printf "i = %d, v = %d, s = %d, p = %d\n", $i, $versions{$i}, $sum, $pages;
}
print "XX discard 4: $_\n" if debug > 1;
$stage = 0;
next unless defined $min;
if ($aflag || $sum != $pages || debug > 0)
{
my($dbinfo) = "";
$dbinfo .= " - partition $partition" if ($partition);
$dbinfo .= " - dbspace $dbspace" if ($dbspace);
my $ipainfo = "";
$ipainfo = " -" . (($sum == $pages) ? " No" : "" ) . " Outstanding IPA"
if ($aflag || $lflag || debug);
printf "$dbase:$owner.$table$dbinfo$ipainfo\n";
}
if (($lflag && $sum != $pages) || debug > 0)
{
foreach my $v ($min..$max)
{
printf "%15s version $v: $versions{$v} pages\n", " ";
}
}
}
else
{
print "XX discard 5: $_\n" if debug > 1;
}
}

exit 0

__END__

=pod

=head1 NAME

ipa.info - print information about outstanding in-place alters (IPA).

=head1 SYNOPSIS

ipa.info [-a][-l][-d N] [file ...]

=head1 DESCRIPTION

The ipa.info script analyzes the output from 'oncheck -pT'
and reveals which tables have outstanding in-place alters
(henceforth, IPAs) remaining.

When the table is fragmented, it identifies the dbspace in
which the fragment with outstanding IPAs is located.
When the table is partitioned (fragmented) with several
partitions in a single dbspace (new feature in IDS 9.50),
the partition name is identified too.
Using this information plus the fragmentation scheme (when
it is not ROUND ROBIN) can make the process of removing
outstanding IPAs more efficient.

By default, it simply lists those fragments (tables)
identified in its input that have outstanding IPAs.
Specifying '-a' means that each table analyzed is listed,
along with a verdict ("Outstanding IPA" or "No Outstanding
IPA").
Specifying '-l' means that when a table is listed with
outstanding IPAs, the details of the versions are printed.

Specifying '-d 0' is the default debugging level (no extra
information).
Specifying '-d 1' yields the verdict and version information
for each table.
Specifying '-d 2' shows how each input line is processed.

=head1 AUTHOR

Jonathan Leffler (C<jleffler@us.ibm.com>).

=cut

On Tue, 09 Nov 2004 07:32:27 GMT, Jonathan Leffler
<jleffler@earthlink.net> wrote:

>superboer wrote:
>> select
>> {+ ORDERED }
>> pg_partnum + pg_pagenum - 1 partn
>> from
>> sysdbspaces a,
>> syspaghdr
>> where pg_partnum = 1048576 * a.dbsnum + 1
>> and pg_next != 0
>> into temp pp with no log
>>
>> otherwise you will read each and every page in your instance and this
>> may give you grey hair whenever it is finished dependant on
>> how big your instance is...
>
>Without wishing in any way to detract from what Superboer wrote...
>
>Vineet asked this question during the outage at the IIUG, and I
>responded on one of the IIUG mailing lists with the attached Perl script.
>
>The poor performing query was doing a sequential scan on the temp
>table created by the query - and that it is conceivable that either an
>index or an update statistics (or both) on the temp table would
>improve performance.
>
>The output of 'oncheck -pT' contains the information about IPAs (in
>amongst a lot of other information). The attached Perl script (which
>has been tested with Perl 5.5.3, 5.6.1, 5.8.5 on output from IDS 7.31,
>9.30, 9.40 and 9.50 - fragmented and non-fragmented tables) diagnoses
>partitions (fragments) of tables with outstanding IPAs quite handily.
> (Vineet expressed satisfaction with it.)
>
>I sent the message to software@iiug.org for inclusion in the IIUG
>Software Archive, but it has not made it there yet -- I suspect the
>team is still a bit busy recovering from the carnage.
>

And many thanks for the volunteer effort . . . sometimes it's easy to
forget . . .

JWC

Great contribution indeed, as always.