| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problems Solaris Vs Windows
When I specify the STAR Hint in the Query
the Query runs OK.
Regards Munish
Bajaj -----Original
Message-----From: Munish Bajaj
[mailto:[EMAIL PROTECTED]Sent: Thursday, June 12, 2003
11:44To: Multiple recipients of list ORACLE-LSubject: 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>
Regards <FONT
face=Arial>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
 |
 |