I've used the stored outline swapping trick (metalink 92202.1) with
great success several times in the past, but I'm having trouble getting
it to work in 10g so I'm hoping one of you have already encountered this
and will be kind enough to share the solution. Here's what I've tried
so far:
Background:
- Oracle 10.2.0.2 running BaanIVc application
- Baan is sending a query with FIRST_ROWS(10) and INDEX hints,
which are forcing the CBO to use indexes and nested loops even though
this query is returning about 350,000 rows and performs much better with
full table scans and sort merge joins
- I can't prevent Baan from sending these hints, it's hardcoded
into their database driver
What I've tried:
- Ran query through Baan with "_optimizer_ignore_hints"=true, and
this resulted in good performance using FTS and SM joins, but this isn't
a good solution for end users so I need a stored outline to enforce this
same plan transparently for them
- Created stored outline via dbms_outln.create_outline while the
good plan was in the shared pool
- Ran query through Baan again w/o setting
"_optimizer_ignore_hints" this time and stored outline was used,
according to v$sql.default_category and dba_outlines.used columns, but
now it was back to doing NL joins and index scans again
- Checked dba_outline_hints and sure enough,
dbms_outln.create_outline stored hints for the bad plan (NL joins)
rather than the good plan (SM joins) that was actually being used
according to v$sql_plan at the time I ran the procedure
- Executed 'alter session set "_optimizer_ignore_hints"=true' and
then 'alter outline my_outline rebuild', but this didn't work either -
the plan still used NL joins.
- Next, I tried the outline swapping trick (ran query manually w/o
hints, then updated ol$, ol$hints and ol$nodes to swap the names between
the Baan generated outline and my outline).
- Now, ol$hints does show the correct hints (full(), use_merge(),
etc.) and v$sql.outline_category and dba_outlines.used do show that the
outline is used when I run the query through Baan, but the actual plan
it uses is still doing NL joins and index scans, as if it's just
ignoring the outline hints even though it says it's using the outline.
Any ideas what I'm missing here with either of the above approaches? My
understanding was that dbms_outln.create_outline would create an outline
based on the plan currently in the shared pool (v$sql_plan), but that
doesn't seem to be the case based on my observations above - maybe it's
just because I have the hidden parameter set to ignore hints? Does the
swapping trick not work anymore in 10g due to the addition of ol$nodes?
Thanks in advance for any advice!
Brandon
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 22 2007 - 16:06:35 CDT
