Re: 7.x to 9.x Performance issue in the extreme
tlowrie@munis.com (Tom Lowrie) wrote in message news:<e91c92f1.0310020736.6b8acebe@posting.google.com>...
> Greetings,
>
> The problem: I run an identical program on Server A and Server B. On
> Server A the program runs in 12 seconds. On Server B it takes 1.5
> hours.
>
> Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7
>
> Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2
>
> The only difference in the hardware on these 2 servers is Server A has
> 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are
> identical.
>
> Both servers use the 4J's "front end", installed and compiled on each
> server in accordance with the 4J's installation guidelines.
>
> Both servers are dedicated to running the IDS and the Application
> software only. The application software is identical on both servers.
> The application software resides on the same server as the IDS. Both
> servers are pretty much running at the same load level ? not stressed
> at all. Both appear to have plenty of available memory and plenty of
> available CPU time. There is no I/O issue on either server. In short,
> plenty of hardware for the task at hand.
>
> The data in the DB is identical on both servers. The program that
> generates the update statistics SQL is identical on both servers. The
> indexes are identical on both servers. The total size of the database
> after import is less than 2.5 Gig. The tables being accessed for this
> report are all under 120 Meg.
>
> The onconfig files are identical on both servers except for:
>
> Variable: Server A: Sever B:
> SHMVIRTSIZE 786432 524288
> SHMADD 262144 393216
> STACKSIZE 32 64>
> The program is a YTD Budget Report that takes a few parameters then
> goes out to the database to collect the data and generate the report.
> Nothing fancy. Nothing spectacular. Just your normal, everyday
> accounting type report.
>
> The speed on system B in all other areas is excellent. Normal "Finds"
> against the largest tables, moving between screens,
> adding/deleting/changing field data, etc all runs fine.
>
> Given this info, where do I look for the problem?
Thanks to all of you who have extended a response. The general
consensus seems to be "update statistics". Here is some
additional, more specific information on my problem:
The entire database was dbexported, dropped, dbimported last
Monday night. This should take care of any "drop distribution"
issues. We have an update statistics program that we use to
generate an update statistics sql. It was used and the resulting
update statistics sql was run against the newly imported database.Here are the update statistic commands used on the tables
accessed by the glytdbud program:
update statistics medium for table glmaster distributions only;
update statistics high for table glmaster(glma_accttp);
update statistics high for table glmaster(glma_seg1);
update statistics high for table glmaster(glma_obj);
update statistics high for table glmaster(glma_project);
update statistics high for table glmaster(glma_org);
update statistics high for table glmaster(glma_entity);
update statistics low for table glmaster(glma_segx);
update statistics medium for table glfundat distributions only;
update statistics medium for table glmstbal distributions only;
update statistics high for table glmstbal(glmb_org);
update statistics low for table glmstbal(glmb_obj);
update statistics low for table glmstbal(glmb_project);
update statistics low for table glmstbal(glmb_period);
update statistics medium for table glmstbal distributions only;
update statistics high for table glmstbal(glmb_org);
update statistics low for table glmstbal(glmb_obj);
update statistics low for table glmstbal(glmb_project);
update statistics low for table glmstbal(glmb_period);
update statistics medium for table gljehold distributions only;
update statistics high for table gljehold(glje_seg1);
update statistics high for table gljehold(glje_project);
update statistics high for table gljehold(glje_yrpr);
update statistics high for table gljehold(glje_org);
update statistics high for table gljehold(glje_yr);
update statistics low for table gljehold(glje_segrem);
update statistics low for table gljehold(glje_obj);
update statistics low for table gljehold(glje_jnl);
update statistics low for table gljehold(glje_seq);
update statistics low for table gljehold(glje_per);
And now for the dbschemas for the tables listed above:
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC7
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J961997
{ TABLE "munis".glmaster row size = 758 number of columns = 99 index
size = 246 }
create table "munis".glmaster
(
glma_accttp char(1),
glma_seg1 char(4),
glma_segx char(16),
glma_obj char(6),
glma_project char(5),
glma_org char(8),
glma_entity char(1),
glma_desc char(30),
glma_short_desc char(10),
glma_baltp char(1),
glma_stat char(1),
glma_encumb_bud char(1),
glma_budgetary char(1),
glma_ref_org char(8),
glma_ref_obj char(6),
glma_ref_proj char(5),
glma_last_up_date date,
glma_b_sign char(1),
glma_seg2 char(6),
glma_seg3 char(6),
glma_seg4 char(6),
glma_seg5 char(6),
glma_seg6 char(6),
glma_seg7 char(6),
glma_seg8 char(6),
glma_bud_yr smallint,
glma_incep_to_soy decimal(13,2),
glma_bud_ny5 decimal(13,2),
glma_stat_ny5 decimal(7,2),
glma_bud_ny4 decimal(13,2),
glma_stat_ny4 decimal(7,2),
glma_bud_ny3 decimal(13,2),
glma_stat_ny3 decimal(7,2),
glma_bud_ny2 decimal(13,2),
glma_stat_ny2 decimal(7,2),
glma_bud_req1_ny1 decimal(13,2),
glma_bud_req2_ny1 decimal(13,2),
glma_bud_req3_ny1 decimal(13,2),
glma_bud_req4_ny1 decimal(13,2),
glma_bud_req5_ny1 decimal(13,2),
glma_stat1_ny1 decimal(7,2),
glma_stat2_ny1 decimal(7,2),
glma_stat3_ny1 decimal(7,2),
glma_stat4_ny1 decimal(7,2),
glma_stat5_ny1 decimal(7,2),
glma_bud_req1_cy decimal(13,2),
glma_bud_req2_cy decimal(13,2),
glma_bud_req3_cy decimal(13,2),
glma_bud_req4_cy decimal(13,2),
glma_bud_req5_cy decimal(13,2),
glma_orig_bud_cy decimal(13,2),
glma_xfr_bud_incy decimal(13,2),
glma_xfr_bud_outcy decimal(13,2),
glma_cfwd_bud_cy decimal(13,2),
glma_cfwd_bud_xfcy decimal(13,2),
glma_rev_bud_cy decimal(13,2),
glma_memo_bal_cy decimal(13,2),
glma_actual_cy decimal(13,2),
glma_actl_cy_frmly decimal(13,2),
glma_estimate_cy decimal(13,2),
glma_stat_cy decimal(7,2),
glma_encumb_cy decimal(13,2),
glma_encm_cy_frmly decimal(13,2),
glma_req_cy decimal(13,2),
glma_orig_bud_ly1 decimal(13,2),
glma_xf_bud_inly1 decimal(13,2),
glma_xf_bud_outly1 decimal(13,2),
glma_cfwd_bud_ly1 decimal(13,2),
glma_rev_bud_ly1 decimal(13,2),
glma_memo_bal_ly1 decimal(13,2),
glma_actual_ly1 decimal(13,2),
glma_c