RE: Performance Problems Solaris Vs Windows

[Paula_Stankus]

Do you have anything set in the configuration that would (hints, multiblock read count...) that would favor a different execution path? -----Original Message----- From: Ishwar Tewari [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problems Solaris Vs Windows Do u analyze both sets of tables on the different platforms at the same regular intervals?   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish Bajaj Sent: Wednesday, June 11, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Subject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*)     FROM mam_assets a, mam_asset_attr_domain_values dmv65549     WHERE a."ID" = dmv65549.asset_id       AND a."ID" IN (SELECT dmv3.asset_id                          FROM mam_asset_attr_domain_values dmv3                          WHERE dmv3.domain_value_id = 71                            AND dmv3.asset_attribute_xid = 3                            AND dmv3.domain_xid = 7)       AND a."ID" IN (SELECT dmv3.asset_id                          FROM mam_asset_attr_domain_values dmv3                          WHERE dmv3.domain_value_id = 71                            AND dmv3.asset_attribute_xid = 3                            AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj