Strange performance problem

Dear all, I have a strange performance problem: I am using IDS 7.31UC6 in Solaris with E3500, CPUx4, 1024MB RAM. There are 6 instances in the machines and my query only used 2 instances as follow: select a.benf_cd from prulife@prulifes4:pol_benf a,prulife@prulifeu4:clm_claims_ty_dtl b where a.pol_no ="000009162058" and a.benf_cd = b.benf_cd and b.claims_ty = " ACC" and a.benf_cd = "PAB"; I ran this query from the instance "prulifeu4" and it processed for about 3 minutes. However, when I ran this query from "prulifes4", it took 3 seconds to finish. There are about 1 million records in the table "pol_benf" and 155 records in the table "clm_claims_ty_dtl". I have monitored the dbspace usage and found that the DBSPACETEMP was used very quickly when I processed this query under "prulifeu4". It seems it was doing some sorting in the temp dbspace but this symptom was not happened under prulifes4. Does anybody got an idea? Thanks in advance. Regards, Samuel Chan p.s. This query was originally processed in IDS 7.23UC4 of AIX 4.2.1 and found it took only 3 seconds to finish when processed in both instances.

Samuel Chan wrote: > > Dear all, > > I have a strange performance problem: > > I am using IDS 7.31UC6 in Solaris with E3500, CPUx4, 1024MB RAM. There are 6 > instances in the machines and my query only used 2 instances as follow: > > select a.benf_cd from prulife@prulifes4:pol_benf a,> prulife@prulifeu4:clm_claims_ty_dtl b > where a.pol_no ="000009162058" and a.benf_cd = b.benf_cd and b.claims_ty > = " > ACC" and a.benf_cd = "PAB"; > > I ran this query from the instance "prulifeu4" and it processed for about 3 > minutes. However, when I ran this query from "prulifes4", it took 3 seconds > to finish. > > There are about 1 million records in the table "pol_benf" and 155 records in > the table "clm_claims_ty_dtl". > > I have monitored the dbspace usage and found that the DBSPACETEMP was used > very quickly when I processed this query under "prulifeu4". It seems it was > doing some sorting in the temp dbspace but this symptom was not happened > under prulifes4. > > Does anybody got an idea? Thanks in advance. Since there are two instances involved the local engine resolves all joins. Thus if you execute on prulifes4 it pulls over all of the rows from the clm_clains_ty_dtl table that satisfy the filter condition (claims_ty = "ACC" very few rows likely) into a temp table (activity in DBSPACETEMP but not much) then joins the temp table to pol_benf likely using an index. If you run on prulifeu4 that engine must similarly pull in all of the rows satisfying the filter condition (pol_no = "000009162058" and benf_cd = "PAB" ) from the pol_benf table, which is likely more rows than above, into a temp table (more activity in DBTEMPSPACE) and join to the other table using any indexes. If your databases are really that dispursed across multiple machines you should seriously consider XPS which handles joins across co-servers in a more efficient manner. Art S. Kagel