Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Different explain plans for a query on SQL Plus and PL/SQL

Re: Different explain plans for a query on SQL Plus and PL/SQL

From: EscVector <Junk_at_webthere.com>
Date: 2 Jan 2007 09:22:45 -0800
Message-ID: <1167758564.934512.76860@k21g2000cwa.googlegroups.com>


> Thanks for the suggestion.
> I agree that a Hint would be hard to maintain. However since there are
> multiple selects in the package, a change in optimizer_index_cost_adj,
> db_file_multiblock_read_count will cause a change in the explain plan
> of other selects within the same package.
>
> > Hint is a bad idea, hard to maintain. Use the OPT settings set at
> > session level to get better plan. Alter your session right before
> > running the query. The values are for you to determine based on the
> > join types/plan you want to see.
> >
> > alter session set.....
> >
> > optimizer_index_caching
> > optimizer_index_cost_adj
> > db_file_multiblock_read_count

Good point, but I would still not hint. I'd create a table that stores the appropriate opt settings and a proc to set them. Then I'd set the values before each call or in this case set it before and set it back after. No change to the sql, no reliance on hint, and no need to recompile in order to fix performance problems. Received on Tue Jan 02 2007 - 11:22:45 CST

Original text of this message

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