Re: OCP - Performance Tuning

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 29 Jan 2003 08:02:36 -0000
Message-ID: <b181rj$m9g$1$8302bc10@news.demon.co.uk>

And the solution is ......

I believe the correct response is B,
although the right answers are C and D.

Notes:

The exam is probably for Oracle 8, since you wouldn't use first_rows in Oracle 9 - it exists only for backward compatibility. So we have to assume that the parameter
pga_aggregate_target is not available to disqualify option (c) about sort parameters.

Historically, the manuals have always
suggested that using FIRST_ROWS
encourages the use of indexes and
discourages the appearance of hash
and merge joins. This is why I think
that the pavlovian response is supposed
to be B.

It is very easy to produce an example
where changing the value of sort_area_size causes a change in the execution path
between merge join and nested loop join.

It is very easy to produce an example
where changing the number of rows
(without changing the number of blocks)
in one of the tables causes a change in
the execution path between merge join
and nested loop join.

Helpful Hints for OCP (1) - if you are asked to select one option, but you know that two of the options are correct, the expected response might be one of the incorrect options.

By the way, Richard, I have to disagree with your comments that this is just one question, and you are allowed to fail 30% and still pass. This question happens to be about a fundamental understanding of the optimizer which everyone OUGHT to be able to argue if they are going to claim they have a basic understanding of Oracle performance. On the other hand, there are trivial and irrelevant questions in the exam which add no real value but which could be memorised from the course notes - thus allowing an incompetent to fail the truly relevant material and still get the qualification.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Jonathan Lewis wrote in message ...


>

>

>I've just been sent the following question from an OCP test,

>and I can't decide whether this is a subtle trap, or a naive

>interpretation of the manuals on the part of the examiner.

>

>I haven't been told whether this is a 'single answer only'

>question, or 'perm any N'.  But I assume from the grammar

>that precisely one answer is supposed to be correct.

>

>If you were taking the exam, which one would you put ?

>

>(NB - I didn't say "which one is correct?").

>

>4. The cost-based optimizer can choose between a nested

>    loops join and a sort merge join   operation. All tables are

>    analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS.

>    Which  execution plan will be the result?

>

>a. The sort-merge join.

>b. The nested loops join.

>c. This depends on some sort parameter values.

>d. This depends on the number of rows in each table.

>

>

>

>--

>Regards

>

>Jonathan Lewis

>http://www.jlcomp.demon.co.uk

>

>Coming soon a new one-day tutorial:

>Cost Based Optimisation

>(see http://www.jlcomp.demon.co.uk/tutorial.html )

>

>____UK_______March

>____USA_(FL)_May

>

>

>Next Seminar dates:

>(see http://www.jlcomp.demon.co.uk/seminar.html )

>

>____USA_(CA, TX)_August

>

>

>The Co-operative Oracle Users' FAQ

>http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

>

>

>

Received on Wed Jan 29 2003 - 02:02:36 CST