Re: OCP - Performance Tuning

Thanks Jonathan...

But this illustrates also another problem with OCP. If this was a proctored essay-style exam then one could at least show one's reasoning to arrive at a conclusion, even if essentially the answer was incorrect. With what you have posted below you make a clear argument for your points. Good !

In OCP you have no such chance to answer such an 'it depends' question.

Should it be the case that the questions at least have a definite one right answer (for radio button questions).

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b181rj$m9g$1$8302bc10_at_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 - 04:23:24 CST