Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problems Solaris Vs Windows

RE: Performance Problems Solaris Vs Windows

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Wed, 11 Jun 2003 12:23:44 -0700
Message-ID: <F001.005AF72F.20030611115948@fatcity.com>

Do you have anything set in the
configuration that would (hints, multiblock read count...) that would favor a different execution path?

  <FONT
  face=Tahoma>-----Original Message-----From: Ishwar Tewari   [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 11, 2003 3:00   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   Performance Problems Solaris Vs Windows   Do u analyze both sets of tables on the   different platforms at the same regular intervals?       

    <FONT
    face=Tahoma>-----Original Message-----From: [EMAIL PROTECTED]     [mailto:[EMAIL PROTECTED]On Behalf Of Munish     BajajSent: Wednesday, June 11, 2003 12:35 PMTo:     Multiple recipients of list ORACLE-LSubject: 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     

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>

      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step16">16
<IMG height=16 alt=border=0
src="" width=16 align=top> SELECT STATEMENT

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step15">15
<IMG height=16 alt=""
src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (AGGREGATE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step14">14
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> NESTED

    LOOPS
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step12">12
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> NESTED

    LOOPS
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step10">10
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> MERGE JOIN (CARTESIAN)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step4">4
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top>
<FONT

    size=3>VIEW
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step3">3
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (UNIQUE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step2">2
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) <A
    name=planStep1>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step1">1
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) <A
    name=planStep9>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step9">9
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT

    (JOIN)
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step8">8
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top>
<FONT

    size=3>VIEW
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step7">7
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (UNIQUE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step6">6
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) <A
    name=planStep5>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step5">5
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) <A
    name=planStep11>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step11">11
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (UNIQUE SCAN), AST_PK (GMASTER)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step13">13
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER)

    Explain Plan on Windows     

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>

      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step15">15
<IMG height=16 alt=border=0
src="" width=16 align=top> SELECT STATEMENT

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step14">14
<IMG height=16 alt=""
src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (AGGREGATE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step13">13
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> NESTED

    LOOPS
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step11">11
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> HASH

    JOIN
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step4">4
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top>
<FONT

    size=3>VIEW
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step3">3
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (UNIQUE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step2">2
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) <A
    name=planStep1>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step1">1
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) <A
    name=planStep10>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step10">10
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> NESTED

    LOOPS
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step8">8
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top>
<FONT

    size=3>VIEW
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step7">7
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> SORT (UNIQUE)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step6">6
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) <A
    name=planStep5>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step5">5
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) <A
    name=planStep9>
    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step9">9
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> INDEX (UNIQUE SCAN), AST_PK (QUARKDMS)

    <TABLE cellSpacing=0 cols=3 cellPadding=0 width=634 bgColor=#f7f7e7 border=0>
      
      

<A
href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step12">12
<IMG height="100%" alt=""
src="" width=16 border=0><IMG height="100%" alt="" src="" width=16 border=0><IMG height=16 alt="" src="" width=16 align=top border=0><IMG height=16 alt=border=0 src="" width=16 align=top> 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
    <SPAN
    class=013280715-11062003>Munish
Bajaj Received on Wed Jun 11 2003 - 14:23:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US