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
> 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
> 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.)
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.
On Dec 2, 12:30 pm, Rick Denoire <educacion.super...@online.de> wrote:
> 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!
> 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.
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!
Robert Klemme <shortcut...@googlemail.com> wrote: >On 02.12.2007 02:30, Rick Denoire wrote: >> Hello
>> 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
>> 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.)
>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.
>> 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.
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!
Noons <wizofo...@yahoo.com.au> wrote: >> 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.
On Dec 3, 6:41 am, Rick Denoire <educacion.super...@online.de> wrote:
> 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.
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.
> 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!
<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."
> 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. :-)
>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?
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 ).
> 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?
> 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?- Hide quoted text -
> - 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.
> 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
> 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
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.
Ben wrote: > 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?- Hide quoted text -
>> - 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.
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.
On Dec 5, 8:00 am, Ben <benal...@yahoo.com> wrote:
> 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:
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!
> On Dec 5, 8:00 am, Ben <benal...@yahoo.com> wrote:
> > 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:
> 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!
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?
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
On Dec 6, 5:08 am, Ben <benal...@yahoo.com> wrote:
> 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...
