Re: plan stability
Date: Mon, 14 Sep 2009 12:47:50 -0700 (PDT)
Message-ID: <46fc65c2-7d5b-49e1-ae29-3ac785bee124_at_e34g2000vbm.googlegroups.com>
On Sep 13, 3:05 pm, DEV <sdp..._at_gmail.com> wrote:
> Oracle 10.2.0.3 EE on RHEL 4
> I have two schemas (say schemaA and schemaB) with the same set of
> table structures but the data could be different by just a little
> bit. The app is working fine on schemaA but is very CPU intensive on
> schemaB, so I want the app to use the same plan as on on schemaA. I
> created an outline on schemaB with dbms_outln.create_outline with the
> hash_value child_number from v$sql_plan. checking the user_outlines
> view the plan is not being used. Any idea why?
> TIA,
> Dev
There are ways to force Oracle to use an index or specifically not use a hinted index using stored outlines. This could be done a couple different ways, including hacking the outlines (substituting the outline hints with the hinted access path with the one which is not hinted), but even better would be to adjust the optimizer parameters at the session level to force the desired access path, and then create the stored outline.
My notes on the topic suggest to check:
Metalink Doc ID 604022.1 "How To Force A Query To Used Index Hint With
Stored Outline"
Metalink Doc ID 726802.1 "Editing Stored Outlines in Oracle10g and
Oracle11g"
Metalink Doc ID 730062.1 "How to Edit a Stored Outline to Use the Plan
from Another Stored Outline"
Metalink Doc ID 144194.1 "Editing Stored Outlines in Oracle9i - an
example"
Metalink Doc ID 728647.1 "How to Transfer Stored Outlines from One
Database to Another (9i and above)"
Metalink Doc ID 5893396.8 "Wrong results possible due to invalid
stored outline"
http://www.jlcomp.demon.co.uk/04_outlines.rtf "Plan Stability in
Oracle 8i/9i"
The book "Troubleshooting Oracle Performance" also contains a section on hacking stored outlines.
I believe that stored outlines are table owner schema specific. You must also set USE_STORED_OUTLINES=TRUE after every bounce of the database, or create a trigger on startup of the database to set that parameter.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Sep 14 2009 - 14:47:50 CDT