Prev: BULK UPDATE with FORALL
Next: What is the difference between this two SQL?
From: Rick Denoire on 2 Dec 2007 14:28
csn <csn233(a)gmail.com> wrote:

>
>> 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!

Bye

Rick Denoire
From: Rick Denoire on 2 Dec 2007 14:41
Noons <wizofoz2k(a)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.

Bye

Rick Denoire
From: Noons on 2 Dec 2007 19:05
On Dec 3, 6:41 am, Rick Denoire <educacion.super...(a)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.

;-)
From: shortcutter on 3 Dec 2007 03:01
On 2 Dez., 20:24, Rick Denoire <educacion.super...(a)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!

No need to get agitated.

Good luck

robert
From: joel garry on 3 Dec 2007 18:05
On Dec 3, 12:01 am, "shortcut...(a)googlemail.com"
<shortcut...(a)googlemail.com> wrote:
> On 2 Dez., 20:24, Rick Denoire <educacion.super...(a)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."
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: BULK UPDATE with FORALL
Next: What is the difference between this two SQL?