Re: OCP - Performance Tuning

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b15h9a$lqq$1$8300dec7_at_news.demon.co.uk>...

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

I just finished 9i OCP Perf Tuning exam a few weeks back. Everything I've read in the preparation materials (Sybex & Oracle Press), none of them were explicit about matching a join algorithm to OPTIMIZER_MODE... they were pretty vague about any specific ways Oracle goes about implementing the FIRST_ROWS mode.

I would probably suggest (D) is the answer (although "blocks" would be better than "rows" in the statement).

But my OCP-sense is also leading me to (B) because in the world of OCP Performance Tuning, there's a linear cause & effect for everything, and OPTIMIZER_MODE=FIRST_ROWS -> NESTED LOOPS seems to be one of those famous "rules of thumb".

I took the 9i SQL Tuning Workshop and IIRC, (B) was what the course notes said would happen (I don't have them nearby to confirm if they danced around it too). But these course notes also said if you separate table & indexes, queries run faster.... yay

Cheers
Stu Received on Tue Jan 28 2003 - 09:31:25 CST