Re: Optimizer Mode

Mark C. Stock wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1077300834.922938_at_yasure...
> | Last night while teaching DBMS_APPLICATION_INFO I found something
> | in v$sqlarea that is undocumented at tahiti.oracle.com (9.2.0.4)
> |
> | SQL> select distinct optimizer_mode from v$sqlarea;
> |
> | OPTIMIZER_MODE
> | -------------------------
> | CHOOSE
> | MULTIPLE CHILDREN PRESENT
> | NONE
> |
> |
> | Can anyone explain what the MULTIPLE CHILDREN PRESENT optimizer
> | mode is?
> |
> | Thanks.
> |
> | --
> | Daniel Morgan
> | http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> | http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> | damorgan_at_x.washington.edu
> | (replace 'x' with a 'u' to reply)
> |
>
> could it apply to the students?
>
> seriously, this is referencing 'multiple child cursors' , not an actual
> mode -- look in v$sql for the actual statements and the actual mode
>
> ;-{ mcs

I did and this is what I see:

select distinct sql_text from v$sqlarea
where optimizer_mode LIKE 'MULTI%';

Among the results:

select value$ from sys.props$ where name = :1

Of course there is also this:

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.pct
free$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,i.dblkkey,i.
clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.insta
nces,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n vl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,spare2,spare6, de code(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) from ind$ i, (selec
t enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#, min(t
o_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group
  by enabled) c where i.obj#=c.enabled(+) and i.bo#=:1

too.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)