| 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: 8.1.7.4 migration from 32 bit to 64 bit problem
Kathy,
If you're getting a cartesian join in the "bad" plan, check to see if =
the cardinality of
the first row source of the cartesian thinks one row is being returned =
by that row source
operation. At times the optimizer will choose to do a cartesian join =
thinking that it can
save some effort by buffering the 2nd row source into memory (treating =
it similarly to how
it would treat a sort). If the 2nd row source is big, then the problem =
you're seeing
could occur. =20
My guess is that before the section of the plan that is now doing the =
cartesian was being
accomplished with a nested loops or possibly hash join. See if you can =
identify the
portion that matches up for both the old plan and the new plan. If you =
can isolate the
one portion of the plan that is different, you can then focus your =
efforts on the part of
the predicate that is involved. Check to see that all indexes are there =
that were there
before as well. A missing index could cause this shift in plan as well.
I know this may sound a bit vague, but without the plan excerpts and =
query text, it's a
bit difficult to do more than speculate. =20
Hope it helps some.
Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events at http://www.hotsos.com/education/schedule.html
=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Duret, Kathy
Sent: Friday, November 19, 2004 1:33 PM
To: 'Ron.Reidy_at_arraybiopharma.com'; mark.powell_at_eds.com; =
oracle-l_at_freelists.org
Subject: RE: 8.1.7.4 migration from 32 bit to 64 bit problem
The explan plans are very long but it boils down to now the bad explain =
plan is doing a
merge cartesian join.
I have tried various things, including re:
alter session set optimizer_mode=3D'RULE';
=20
alter session set optimizer_index_cost_adj=3D50;
alter session set optimizer_index_caching=3D90;
=20
alter session set optimizer_index_cost_adj=3D1;
alter session set optimizer_index_caching=3D100;
putting ordered and use_nl in all the views,=20 setting the _complex_view parameter and bouncing the database.
INcreasing the SGA.
I am willing to trying anything at this point...
Kathy
-----Original Message-----
From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com]
Sent: Friday, November 19, 2004 3:05 PM
To: kduret_at_starkinvestments.com; mark.powell_at_eds.com; =
oracle-l_at_freelists.org
Subject: RE: 8.1.7.4 migration from 32 bit to 64 bit problem
Can you post both plans?
Maybe someone here can give you an idea.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
Duret, Kathy
Sent: Friday, November 19, 2004 1:48 PM
To: 'mark.powell_at_eds.com'; 'oracle-l_at_freelists.org'
Subject: RE: 8.1.7.4 migration from 32 bit to 64 bit problem
Nothing was changed..... I tried to increase the shared pool on the new =
=3D dev version and
bounced it.... no good.
I am trying various hints in all the views and sub views to no avail. I =
finally got a
descent tech now for my tar....=3D20
Yes I just updated the stats again for the new tech. I am going to give =
=3D him a 10053
trace as well. The explain plan for the new database is very strange =
for this query.
Kathy
-----Original Message-----
From: Powell, Mark D [mailto:mark.powell_at_eds.com]
Sent: Friday, November 19, 2004 2:06 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: 8.1.7.4 migration from 32 bit to 64 bit problem
Sometimes even a small change in plans can result in a large change in =
performance for the
query depending on what the change is.
Were the statistics updated on the new version?
Was the shared pool increased in size to compensate for the additional 4 =
bytes in every
address pointer used. The 64 bit version of 8.1.7 needs about a 20% =
increase in the
shared pool just to run the same load in our experience, but then we =
have a lot of stored
code (pl/sql in the =3D database).
Were any database parameters changed?
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
Jeremiah Wilton
Sent: Friday, November 19, 2004 2:09 PM
To: Oracle L (E-mail)
Subject: RE: 8.1.7.4 migration from 32 bit to 64 bit problem
Sorry if this has been explored, but it sounds like the difference in =
the plans is the
problem. Can you elaborate on WHAT is different about the plans?
Is something else hogging temp?
-- Jeremiah Wilton Independent Oracle Professional Oracle Certified Master Disaster Recovery - Seminars - Technical Interviews = http://www.speakeasy.net/~jwilton On Fri, 19 Nov 2004, Duret, Kathy wrote:Received on Fri Nov 19 2004 - 18:05:31 CST
> two database set up is EXACTLY the same (init.ora, files size etc)
>
> Query using two views in OLD 32 bit runs in 2 seonds and with the =3D
WHOLE
> company running on the database uses less than 1/2 G of temp =3D
tablespaces.
>
> Query ONLY running on new production database get ora-1652 (out of =3D
temp
> space) on 1G of temp space after 81 seconds.
>
> Once again same set up, same data, BUT the explain plans are =3D
different but
> are fairly similiar.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l This transmission contains information solely for intended recipient and = =3D may be privileged, confidential and/or otherwise protect from disclosure. =3D = If you are not the intended recipient, please contact the sender and delete =3D all copies = of this transmission. This message and/or the materials =3D contained herein = are not an offer to sell, or a solicitation of an offer to buy, =3D any securities or other = instruments. The information has been obtained or derived from sources believed by us to = be reliable, but we do not =3D represent that it is accurate or complete. Any opinions = or estimates contained in this information constitute our judgment as of this date = and are subject =3D to change without notice. Any information you share with us will be used = =3D in the operation of our business, and we do not request and do not want any material, = nonpublic information. Absent an express prior written =3D agreement, we are not = agreeing to treat any information confidentially and will use =3D any and all information and = reserve the right to publish or disclose any information you share with us. -- http://www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which = =3D contains information which may be confidential or privileged. The information is = =3D intended=3D20 to be for the use of the individual or entity named above. If you are =3D = not the=3D20 intended recipient, please be aware that any disclosure, copying, =3D = distribution=3D20 or use of the contents of this information is prohibited. Please notify =3D the sender = of the delivery error by replying to this message, or notify us =3D by telephone = (877-633-2436, ext. 0), and then delete it from your system. -- http://www.freelists.org/webpage/oracle-l This transmission contains information solely for intended recipient and = may be privileged, confidential and/or otherwise protect from disclosure. If = you are not the intended recipient, please contact the sender and delete all copies of = this transmission. This message and/or the materials contained herein are not an offer to = sell, or a solicitation of an offer to buy, any securities or other instruments. = The information has been obtained or derived from sources believed by us to be reliable, but = we do not represent that it is accurate or complete. Any opinions or estimates = contained in this information constitute our judgment as of this date and are subject to = change without notice. Any information you share with us will be used in the operation = of our business, and we do not request and do not want any material, nonpublic = information. Absent an express prior written agreement, we are not agreeing to treat any = information confidentially and will use any and all information and reserve the = right to publish or disclose any information you share with us. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
 |
 |