Re: OCP Tuning Sample Question

Hello Buck,

From chapter 8 Optimization Modes and Hints in Oracle8 Tuning:

To use cost-based optimization for a statement, first collect statistics for the tables accessed by the statement. Then enable cost-based optimization in one of these ways:

Make sure the OPTIMIZER_MODE initialization parameter is set to its default value of CHOOSE.
To enable cost-based optimization for your session only, issue an ALTER SESSION . . . OPTIMIZER_MODE statement with the ALL_ROWS or FIRST_ROWS option.
To enable cost-based optimization for an individual SQL statement, use any hint other than RULE

So that would be : Instance, Session and Statement level

Buck Turgidson <jcmanNOSPAM_at_worldnet.att.net> wrote in message news:86fia3$m2n$1_at_bgtnsc03.worldnet.att.net...
> 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 - 14:32:19 CST