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

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/

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