Performance Problems Solaris Vs Windows

Wed, 11 Jun 2003 08:52:19 -0700

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

<<Blank Bkgrd.gif>>

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.

<<sql.gif>>

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.

<<blueLineL.GIF>>

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.

<<sort.gif>>

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.

<<empty.GIF>>

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.

<<redLineL.GIF>>

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.

<<nestedLoop.gif>>

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.

<<greyLineT.GIF>>

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.

<<greyLine1.GIF>>

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.

<<blackLineT.GIF>>

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.

<<mergeCartesian.gif>>

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.

<<blackLine1.GIF>>

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.

<<blueLineT.GIF>>

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.

<<view.gif>>

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.

<<blueLine1.GIF>>

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.

<<greyLineL.GIF>>

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.

<<tableAccess.gif>>

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.

<<blackLineL.GIF>>

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.

<<index.gif>>

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.

<<sortJoin.gif>>

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.

<<hashJoin.gif>>

Reply via email to