Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE
Rick Denoire
After Upgrading a commercial application from 9.2.0.5 to 10.2.0.3 we
got a disastrous performance, even though the new server was at least
10 times faster (in terms of CPU). The application manager did not
follow test procedures prior to the upgrade, which lead us to a
crisis.
After some investigation I found that just setting the following
parameter resulted in a huge (about 100-fold) performance increase:
OPTIMIZER_SECURE_VIEW_MERGING=FALSE
See for example:
http://www.freelists.org/archives/oracle-l/07-2006/msg00260.html
I did not dare to set any hidden parameters yet; perhaps those would
have boosted performance even more.
Can you confirm that reverting mentioned parameter to the non-default
value (FALSE) improves performance so dramatically?
What is the rationale of such detrimental default value of the
initalization parameter that effectively renders the database
"misconfigured"?
Are there any other deadly preset parameters I should be aware of
after Upgrade to Oracle 10g, according to your experience?
Do you really recommend to let Oracle do things automatically? (SGA,
PGA, db_file_multibloc_read_count, etc.)
Thanks a lot
Rick Denoire
Robert Klemme
I guess with this information given you cannot expect profound help.
What did you do to pinpoint the source of the slowness? I mean, do you
*know* where time is spent? If not, I'd investigate this first before
doing any changes to the system.
Kind regards
robert
Noons
> Can you confirm that reverting mentioned parameter to the non-default
> value (FALSE) improves performance so dramatically?
in some cases it might, depends on what the
views are like in your application.
If it works, then use it!
> What is the rationale of such detrimental default value of the
> initalization parameter that effectively renders the database
> "misconfigured"?
not really. it's very dependent on the type of
views and the application itself. I've seen
this not take any effect whatsoever and I've
seen it make a huge difference. Go figure...
> Are there any other deadly preset parameters I should be aware of
> after Upgrade to Oracle 10g, according to your experience?
_unnest_subquery=true (default)
even in 10.2.0.3 patched, I'm still getting
a lot of complex Peoplesoft queries with
problems with this one, particularly with the
ubiquitous (select max(date_field) ...)
subqueries of this product. Set it to false
and problems vanish.
> Do you really recommend to let Oracle do things automatically? (SGA,
> PGA, db_file_multibloc_read_count, etc.)
if you don't have multiple schemas then it's fine.
If you do, then watch out for tables with the
same name in different schemas and auto sga:
deadly combination!
csn
> After some investigation I found that just setting the following
> parameter resulted in a huge (about 100-fold) performance increase:
> OPTIMIZER_SECURE_VIEW_MERGING=FALSE
If I can improve performance 100-fold, I wouldn't worry about other
things too much but go to the manager and ask for a pay raise instead.
Rick Denoire
I spent two weeks (!) investigating the question.
I required support from the vendor, sent them detailed informations.
I let users do different tests.
I collected and printed performance numbers, execution plans, looked
into waits, used AWR, ADDM, SQL Tuning Advisors, statistics
with/without histograms. I was playing with different init parameters
(mostly making things even worse). I set up a clone database, did more
tests... hey! Let's stop.
As you can see from other replies, you are guessing wrong. (Typical
case when people try to complicate obvious things).
If the software is just misbehaving, all this knowledge is for
nothing. Almost every query was doing FTS of huge tables, main
activity was direct path read. Same application was still installed in
the old server running 9i: Execution plans were completely different.
So what? It could have been something different than direct path
reads, it does not bear any direct logical relationship to the
solution. Based on this "knowledge", you would probably... buy more
disks?? Wrong!
Thanks anyway.
Rick Denoire
Rick Denoire
Yes but wait... Performance was already acceptable prior to the
Upgrade. I did not really improve things, just fixed a problem.
If my salary would depend on improvements of this kind, I would get
rich for sure. There is still room for improvement, absolutely!
Bye
Rick Denoire
Rick Denoire
>> Are there any other deadly preset parameters I should be aware of
>> after Upgrade to Oracle 10g, according to your experience?
>
>_unnest_subquery=true (default)
>even in 10.2.0.3 patched, I'm still getting
>a lot of complex Peoplesoft queries with
>problems with this one, particularly with the
>ubiquitous (select max(date_field) ...)
>subqueries of this product. Set it to false
>and problems vanish.
All right, I will pay sharp attention to that.
(By the way: You can't change such an underscore parameter using the
spfile, right? And then, one has to restart the DB, right?).
I will try it in the test instance first, the main problem being
collecting queries generated by the client in the production instance
in order to build a kind of benchmark to be transferred and run in the
test environment.
>
>> Do you really recommend to let Oracle do things automatically? (SGA,
>> PGA, db_file_multibloc_read_count, etc.)
>
>if you don't have multiple schemas then it's fine.
>If you do, then watch out for tables with the
>same name in different schemas and auto sga:
>deadly combination!
It looks like a bug, I wouldn't otherwise understand.
Would you confirm please?
How does the performance issue manifest in this case?
Is it related to the tables with the same names only?
Does SGA distribution get screwed having a general impact?
Your assumption about having views directly hits my problem.
This application does not allow direct user access to tables, but
access goes through a schema having lots of views based on the tables.
Nice to know that the problem can be rationalized in some extent.
Bye
Rick Denoire
Noons
> All right, I will pay sharp attention to that.
> (By the way: You can't change such an underscore parameter using the
> spfile, right? And then, one has to restart the DB, right?).
alter system set "_unnest_subquery"=false scope=both;
it's dynamic as well. the secret is in the quotes! ;-)
> I will try it in the test instance first, the main problem being
> collecting queries generated by the client in the production instance
> in order to build a kind of benchmark to be transferred and run in the
> test environment.
hang on a tick! Don't go about changing undoc
parameters just because! Do it only if you see
performance with complex subqueries going
crazy. Undocumented parameters should not be
used as a matter of fact, only when needed.
>
> It looks like a bug, I wouldn't otherwise understand.
> Would you confirm please?
Yes, it is a bug. Been there since 9i...
> How does the performance issue manifest in this case?
It doesn't. You just get updates on the wrong table.
> Is it related to the tables with the same names only?
Yes, absolutely.
> Does SGA distribution get screwed having a general impact?
Nope.
> Your assumption about having views directly hits my problem.
> This application does not allow direct user access to tables, but
> access goes through a schema having lots of views based on the tables.
That's fine. Shouldn't be a problem, other than the
parameter you already found out about.
> Nice to know that the problem can be rationalized in some extent.
;-)
short...@googlemail.com
> Hello Robert
>
> I spent two weeks (!) investigating the question.
> I required support from the vendor, sent them detailed informations.
> I let users do different tests.
> I collected and printed performance numbers, execution plans, looked
> into waits, used AWR, ADDM, SQL Tuning Advisors, statistics
> with/without histograms. I was playing with different init parameters
> (mostly making things even worse). I set up a clone database, did more
> tests... hey! Let's stop.
>
> As you can see from other replies, you are guessing wrong. (Typical
> case when people try to complicate obvious things).
What exactly did I guess? While you mention "obvious": all the items
you list above were not obvious from your first posting. All that you
mentioned was that "after some investigation" you found an
undocumented parameter that seemingly fixed the issue. While you are
obviously aware of all this, others are not.
> If the software is just misbehaving, all this knowledge is for
> nothing. Almost every query was doing FTS of huge tables, main
> activity was direct path read. Same application was still installed in
> the old server running 9i: Execution plans were completely different.
> So what? It could have been something different than direct path
> reads, it does not bear any direct logical relationship to the
> solution. Based on this "knowledge", you would probably... buy more
> disks?? Wrong!
No need to get agitated.
Good luck
robert
joel garry
<shortcut...@googlemail.com> wrote:
> On 2 Dez., 20:24, Rick Denoire <educacion.super...@online.de> wrote:
>
> > Hello Robert
>
> > I spent two weeks (!) investigating the question.
> > I required support from the vendor, sent them detailed informations.
> > I let users do different tests.
> > I collected and printed performance numbers, execution plans, looked
> > into waits, used AWR, ADDM, SQL Tuning Advisors, statistics
> > with/without histograms. I was playing with different init parameters
> > (mostly making things even worse). I set up a clone database, did more
> > tests... hey! Let's stop.
>
> > As you can see from other replies, you are guessing wrong. (Typical
> > case when people try to complicate obvious things).
>
> What exactly did I guess? While you mention "obvious": all the items
> you list above were not obvious from your first posting. All that you
> mentioned was that "after some investigation" you found an
> undocumented parameter that seemingly fixed the issue. While you are
> obviously aware of all this, others are not.
You might check, that happens to be documented. The documentation has
words that you can search on for more information, like "view merging"
and "query rewrite."
>
> > If the software is just misbehaving, all this knowledge is for
> > nothing. Almost every query was doing FTS of huge tables, main
> > activity was direct path read. Same application was still installed in
> > the old server running 9i: Execution plans were completely different.
> > So what? It could have been something different than direct path
> > reads, it does not bear any direct logical relationship to the
> > solution. Based on this "knowledge", you would probably... buy more
> > disks?? Wrong!
>
> No need to get agitated.
I think problems that lead to the incorrect database data should lead
to agitation, such as the bug Noons was referring to. Simply spending
money on disk unnecessarily might reasonably lead to a lesser amount
of agitation, though personally I'll use up as much as I can take,
since proper capacity planning seems useless.
jg
--
@home.com is bogus.
"So Alice Cooper, what do you think of Marilyn Manson?"
"Well let's see, wears makeup, has a name like a girl, dresses and
behaves in an offensive manner... sounds like a ripoff, to me."
Robert Klemme
> On Dec 3, 12:01 am, "shortcut...@googlemail.com"
> <shortcut...@googlemail.com> wrote:
>> On 2 Dez., 20:24, Rick Denoire <educacion.super...@online.de> wrote:
>>
>>> Hello Robert
>>> I spent two weeks (!) investigating the question.
>>> I required support from the vendor, sent them detailed informations.
>>> I let users do different tests.
>>> I collected and printed performance numbers, execution plans, looked
>>> into waits, used AWR, ADDM, SQL Tuning Advisors, statistics
>>> with/without histograms. I was playing with different init parameters
>>> (mostly making things even worse). I set up a clone database, did more
>>> tests... hey! Let's stop.
>>> As you can see from other replies, you are guessing wrong. (Typical
>>> case when people try to complicate obvious things).
>> What exactly did I guess? While you mention "obvious": all the items
>> you list above were not obvious from your first posting. All that you
>> mentioned was that "after some investigation" you found an
>> undocumented parameter that seemingly fixed the issue. While you are
>> obviously aware of all this, others are not.
>
> You might check, that happens to be documented. The documentation has
> words that you can search on for more information, like "view merging"
> and "query rewrite."
Um, where exactly is it documented that Rick spent two weeks researching
and what he found other than in his recent posting?
>>> If the software is just misbehaving, all this knowledge is for
>>> nothing. Almost every query was doing FTS of huge tables, main
>>> activity was direct path read. Same application was still installed in
>>> the old server running 9i: Execution plans were completely different.
>>> So what? It could have been something different than direct path
>>> reads, it does not bear any direct logical relationship to the
>>> solution. Based on this "knowledge", you would probably... buy more
>>> disks?? Wrong!
>> No need to get agitated.
>
> I think problems that lead to the incorrect database data should lead
> to agitation, such as the bug Noons was referring to. Simply spending
> money on disk unnecessarily might reasonably lead to a lesser amount
> of agitation, though personally I'll use up as much as I can take,
> since proper capacity planning seems useless.
Agreed. But getting agitated because someone cannot read one's mind
seems a bit over the top. :-)
Cheers
robert
Ben
>If you do, then watch out for tables with the
>same name in different schemas and auto sga:
>deadly combination!
Ok, you got my attention! Please give me a link to more information on
this. Of course I'm using auto sga and we have about 6 schemas in our
database that have tables named the same. I haven't seen any *gulp,
knock on wood* updating of tables in wrong schemas. Is it only related
to certain types of tables?
hpuxrac
Nuno has talked a lot about this both on cdos and in his blog ... not
hard to find those is it?
As I remember it the problems start popping up under load and when the
"owner.table_name" or "owner.view_name" isn't completely specified
( ie the "owner." part ommitted ).
Maybe I don't have a good understanding of it though so check out what
Nuno has written ( isn't it dbas-r-us for his blog or similar ).
Ben
I just spent about 20 minutes trying to sift through the blog, ( it is
hell trying to find anything on a blog unless you know the approximate
timeline ) and did a google search for cdos ( which just gets me debt
consolidation sites ). anyway I could get a link or unabreviated name?
Ben
>
> - Show quoted text -
Sorry, I meant to *ask* if * I could * get a link. working on 4 hours
sleep and a sinus infection that is making me quite groggy.
joel garry
> On Dec 4, 4:00 pm, Ben <benal...@yahoo.com> wrote:
>
>
>
>
>
> > On Dec 4, 2:49 pm, hpuxrac <johnbhur...@sbcglobal.net> wrote:
>
> > > On Dec 4, 2:29 pm, Ben <benal...@yahoo.com> wrote:
>
> > > > >if you don't have multiple schemas then it's fine.
> > > > >If you do, then watch out for tables with the
> > > > >same name in different schemas and auto sga:
> > > > >deadly combination!
>
> > > > Ok, you got my attention! Please give me a link to more information on
> > > > this. Of course I'm using auto sga and we have about 6 schemas in our
> > > > database that have tables named the same. I haven't seen any *gulp,
> > > > knock on wood* updating of tables in wrong schemas. Is it only related
> > > > to certain types of tables?
>
> > > Nuno has talked a lot about this both on cdos and in his blog ... not
> > > hard to find those is it?
>
> > > As I remember it the problems start popping up under load and when the
> > > "owner.table_name" or "owner.view_name" isn't completely specified
> > > ( ie the "owner." part ommitted ).
>
> > > Maybe I don't have a good understanding of it though so check out what
> > > Nuno has written ( isn't it dbas-r-us for his blog or similar ).
>
> > I just spent about 20 minutes trying to sift through the blog, ( it is
> > hell trying to find anything on a blog unless you know the approximate
> > timeline ) and did a google search for cdos ( which just gets me debt
> > consolidation sites ). anyway I could get a link or unabreviated name
>
> sleep and a sinus infection that is making me quite groggy
You can get there by following links found with the following Google
Groups search:
wrong schema author:noons
bug 5458753 refers to similar bugs on other platforms and versions.
jg
--
@home.com is bogus.
"It's government-mandated collusion." - Bert Ely, banking consultant,
on the Bush Administration's effort to help defaulting mortgage
borrowers.
The Boss
Search this groups' archive for a thread called
"here's a good one from dizwell on the recent product launch"
and have a look at Noons post of July 20th.
--
Jeroen
Noons
> I just spent about 20 minutes trying to sift through the blog, ( it is
> hell trying to find anything on a blog unless you know the approximate
> timeline ) and did a google search for cdos ( which just gets me debt
> consolidation sites ). anyway I could get a link or unabreviated name?
Sorry for that, Ben. Took me a while
to come back here: been locked in courses
the last two days.
You can read about someone else's
problems with it as well here:
http://christianbilien.wordpress.com/2007/03/
post of March 14th (just in case I get the usual
interjection insinuating "somehow I'm the only one
with this problem"...)
The Metalink note mentioned there talks about it.
The bug mentioned in someone else's post
also explains what the problem is.
I personally opened two calls with Oracle nearly
three years ago with precisely the same problem,
first with 9i, then with 10g. Which were closed by
Oracle Support as "known problem, fixed in 11g".
Boy, did this cause us a problem!...
Try to explain to google for example that 3.5
million of their clicks just didn't get accounted
for because they got aggregated into another
table. They don't like that sort of explanation...
3(three) years ago! And it was a "known" bug
waaaaaay back then...
In fact, it's been "known" since the auto-sga
started, in 9i early releases.
It is absolutely ludicrous it hasn't yet been fixed
in ALL available releases. Not just 11g
(*IF* it is fixed there...).
And then some folks have the gall to call
me cynical when I point out that this is
inadmissible in a product that:
1- is charged for, not freeware
2- that claims to be "enterprise class"
3- that claims to be "unbreakable"
4- that is pushed as multi-schema,
multi-aplication capable.
As a matter of additional info and to end in
a positive note: read the whole of Christian's
blog, it is well worth someone's time no matter
how much they know about Oracle.
Kevin Closson mentioned it a while
ago and I thank God he did!
Ben
thanks to all for pointing me to the posts. That is amazing to me. We
run JDE and there are probably 3 data schemas that have identical
tables, 3 control data schemas with identical tables, and 3 more meta-
type data schemas with identical tables. Yet, I've yet to hit the
problem. I'd would have to say it is probably because the ERP system
uses fully qualified sql statments.
Do you know if this can happen if you are logged in as one of the
schema owners? Is this limited to a certain platform?
joel garry
>
> http://christianbilien.wordpress.com/2007/03/
One of those links that makes this group worthwhile - LOL that the
post on topic here is filed under Off Topic there. That explain plan
deadlock post is eye-opening too. Thanks!
jg
--
@home.com is bogus.
"Australian hams missing? We have Russell Crowe right here!" - heard
on radio
Noons
> problem. I'd would have to say it is probably because the ERP system
> uses fully qualified sql statments.
Bingo. If they are fully qualified table references
(owner.table_name) then the problem doesn't show up.
> Do you know if this can happen if you are logged in as one of the
> schema owners? Is this limited to a certain platform?
When I got hit, our application had a generic login
and used a "alter session set current_schema" on
the login trigger to give it access to tables of a given
schema. Our sql had no owner.table_name as we
wanted to be able to execute it against a different set
of tables by simply changing the login trigger.
Turned out to be a surprise party...