Re: performance question
Date: Thu, 13 Nov 2008 10:54:24 -0800 (PST)
On Nov 12, 10:58 pm, DA Morgan <damor..._at_psoug.org> wrote:
> joel garry wrote:
> > I thought the white-space thingy was relaxed for comparing two sql
> > statements - or is that just for stored outlines?
> SELECT name, value
> FROM v$parameter
> WHERE name LIKE '%sharing%';
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
I tried Charles' demo with EXACT, SIMILAR and FORCE, all showed the hard parsing (10.2.0.4). I guess that only applies to literal values, as the docs say.
But that does bring up the point, be sure all init.ora are the same on both instances. I know mine aren't, since I was testing _optim_peek_user_binds (made no difference for my purposes), and other things are different just because. My experience has been if there is this big of a difference between dev and prod, and not lots of other effects, there's probably something wrong with an index. That may just reflect the kinds of mistakes I personally make.
Back to the OP, also see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112 particularly the part about "white space". It's possible structural differences may be enough to change the optimizers decisions. Do the proper observations and follow where they lead, after you verify code and objects are as expected.
-- @home.com is bogus. http://catless.ncl.ac.uk/Risks/25.44.html#subj1Received on Thu Nov 13 2008 - 12:54:24 CST