Re: OCP Tuning Sample Question

I now see the doc says OPTIMIZER_MODE at the instance level, and OPTIMIZER_GOAL at the session level. But OPTIMIZER_MODE seems to work at the session level. As long as I know how to answer the question.....

SQL> alter session set optimizer_mode = choose;

Session altered.

SQL> select * from emp;

28 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=28 Bytes=1176 )    1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=28 Bytes=1176)

Statistics

         38  recursive calls
          3  db block gets
         15  consistent gets
          2  physical reads
          0  redo size
       2379  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         28  rows processed

SQL> alter session set optimizer_mode = RULE;

Session altered.

SQL> select * from emp;

28 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 TABLE ACCESS (FULL) OF 'EMP' Statistics

          0  recursive calls
          3  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       2379  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         28  rows processed

SQL>
SQL>
Dave Grzebien <dgrzebie_at_columbus.rr.com> wrote in message news:388B2EA5.7750B52B_at_columbus.rr.com...
> The answer is D. The reason is that the OPTIMIZER_MODE parameter is used
> at the startup of an instance. Therefore, it is set at the instance
> level. It is true that this can be modified at the session, but that is
> done by the ALTER SESSION statement.
>
> Dave Grzebien
> Expert Technical Consultants, Inc
>
> Buck Turgidson wrote:
>
> > This seems ambiguous to me. I think both C and D are correct, but the
> > question indicates only D. Am I missing something?
> >
> > "At which level is the optimizer mode set if you use the OPTIMIZER_MODE
> > parameter?
> > A-Data
> > B-System
> > C-Session
> > D-instance
> > E-Statement.
>
Received on Sun Jan 23 2000 - 12:42:47 CST