Query to find in-place alter pending tables running forever

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

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!"