From mbajaj@quark.co.in Wed, 11 Jun 2003 08:52:19 -0700 From: Munish Bajaj Date: Wed, 11 Jun 2003 08:52:19 -0700 Subject: Performance Problems Solaris Vs Windows Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 border=0 SELECT STATEMENT 15 border=0 SORT (AGGREGATE) 14 border=0 NESTED LOOPS 12 border=0 NESTED LOOPS 10 border=0 MERGE JOIN (CARTESIAN) 4 border=0 VIEW 3 border=0 SORT (UNIQUE) 2 border=0 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 border=0 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 border=0 SORT (JOIN) 8 border=0 VIEW 7 border=0 SORT (UNIQUE) 6 border=0 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 border=0 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 border=0 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 border=0 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 border=0 SELECT STATEMENT 14 border=0 SORT (AGGREGATE) 13 border=0 NESTED LOOPS 11 border=0 HASH JOIN 4 border=0 VIEW 3 border=0 SORT (UNIQUE) 2 border=0 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 border=0 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 border=0 NESTED LOOPS 8 border=0 VIEW 7 border=0 SORT (UNIQUE) 6 border=0 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 border=0 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 border=0 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 border=0 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 <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. <>