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: Manoj Kumar Jha <jmanoj_at_delhi.tcs.co.in>
Date: Thu, 12 Jun 2003 01:45:44 -0700
Message-ID: <F001.005AFE23.20030612012420@fatcity.com>

Your query is not written properly....
 
Try this one..
and try to use count(a.column_name) or count(b.column_name) instead of count(*)  whatever is ur requirement.
 
SELECT COUNT(*)    FROM mam_assets a,
mam_asset_attr_domain_values dmv65549    WHERE a."ID" = dmv65549.asset_id      AND dmv3.domain_value_id = 71      AND dmv3.asset_attribute_xid =
3      AND dmv3.domain_xid = 7;

 
 
 

<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

  To: <A [EMAIL PROTECTED]
  href="mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L   

  Sent: Thursday, June 12, 2003 11:44
  AM
  Subject: RE: Performance Problems Solaris   Vs Windows   

  Both the Tables have been analyzed. I'm   trying to find the difference between init parameters.   <FONT face=Arial
  size=2> 
  Regards
  Munish Bajaj   

    <FONT
    face=Tahoma>-----Original Message-----From: Ishwar Tewari     [mailto:[EMAIL PROTECTED]Sent: Thursday, June 12, 2003     00:30To: 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)
<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)
<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 Thu Jun 12 2003 - 03:45:44 CDT

Original text of this message

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