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: gmei <gmei_at_incyte.com>
Date: Wed, 11 Jun 2003 09:29:58 -0700
Message-ID: <F001.005AF3D9.20030611091557@fatcity.com>

Have you run  "analyze table
...compute statitics;" on these tables on Solaris?  
Guang
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px">   <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 bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step16">16

<IMG align=top alt=border=0
height=16 src="" width=16> SELECT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step15">15

<IMG align=top alt="" border=0
height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step14">14

<IMG alt="" border=0
height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> NESTED

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step12">12

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> NESTED

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step10">10

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> MERGE JOIN

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step4">4

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> VIEW<A

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step3">3

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step2">2

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) <A
  name=planStep1>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step1">1

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) <A
  name=planStep9>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step9">9

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step8">8

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> VIEW<A

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step7">7

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step6">6

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) <A
  name=planStep5>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step5">5

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) <A
  name=planStep11>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step11">11

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (UNIQUE SCAN), AST_PK (GMASTER)

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Solaris%20Plan.html#step13">13

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER)

  Explain Plan on Windows   

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step15">15

<IMG align=top alt=border=0
height=16 src="" width=16> SELECT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step14">14

<IMG align=top alt="" border=0
height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step13">13

<IMG alt="" border=0
height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> NESTED

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step11">11

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> HASH

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step4">4

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> VIEW<A

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step3">3

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step2">2

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) <A
  name=planStep1>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step1">1

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) <A
  name=planStep10>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step10">10

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> NESTED

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step8">8

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> VIEW<A

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step7">7

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> SORT

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step6">6

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) <A
  name=planStep5>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step5">5

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) <A
  name=planStep9>
  <TABLE bgColor=#f7f7e7 border=0 cellPadding=0 cellSpacing=0 cols=3     width=634>     

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step9">9

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> INDEX (UNIQUE SCAN), AST_PK (QUARKDMS)

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

<A

        href="file:///C:/Documents%20and%20Settings/mbajaj/My%20Documents/Windows%20Plan.html#step12">12

<IMG alt="" border=0
height="100%" src="" width=16><IMG alt="" border=0 height="100%" src="" width=16><IMG align=top alt="" border=0 height=16 src="" width=16><IMG align=top alt=border=0 height=16 src="" width=16> 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 Received on Wed Jun 11 2003 - 11:29:58 CDT

Original text of this message

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