Re: OCP Tuning Sample Question

This situation embarassed me a little too. I think there were two different parameters in Oracle7:

OPTIMIZER_MODE for using in parameter file, and

OPTIMIZER_GOAL for ALTER SESSION,

and that's the way they expect a DBA should work.

But in Oracle8 you can definitely set MODE in ALTER SESSION. Where did you find this question, Buck? If in Self Test Software assesment tests for Oracle7 DBA, then no wonder. I don't think the question is relevant for Oracle8. Anyway, I passed the exam already. ;-)

Regards.

Buck Turgidson wrote:

> 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 Tue Jan 25 2000 - 04:46:06 CST