Optimizer Mode

Daniel Morgan

unread,

Feb 20, 2004, 1:14:34 PM2/20/04

to

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
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

Mark C. Stock

unread,

Feb 20, 2004, 1:37:23 PM2/20/04

to

"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1077300834.922938@yasure...

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

to get you started (from the Reference Manual). brackets added ...

V$SQL
This view lists statistics on shared SQL area [[ without the GROUP BY clause
and
contains one row for each child of the original SQL text entered. ]]

V$SQLAREA
This view lists statistics on shared SQL area and contains one row [[ per
SQL string. ]] It
provides statistics on SQL statements that are in memory, parsed, and ready
for
execution.

;-{ mcs

Daniel Morgan

unread,

Feb 20, 2004, 2:08:05 PM2/20/04

to

Mark C. Stock wrote:

> "Daniel Morgan" <damo...@x.washington.edu> wrote in message
> news:1077300834.922938@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
> | damo...@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.

Jonathan Lewis

unread,

Feb 20, 2004, 2:10:48 PM2/20/04

to

Since v$sqlarea is an aggregate of v$sql, there
has to be a mechanism for reporting something
when different child cursors (i.e. the v$sql entries)
are running under different optimizer modes. That's
what you are seeing here.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1077300834.922938@yasure...

Michel Cadot

unread,

Feb 20, 2004, 2:21:11 PM2/20/04

to

"Daniel Morgan" <damo...@x.washington.edu> a écrit dans le message de
news:1077300834.922938@yasure...

See Metalink note 1013747.102
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1013747.102

Regards
Michel Cadot

Jonathan Lewis

unread,

Feb 20, 2004, 5:06:57 PM2/20/04

to

Following up the notes from the other posters
about the metalink note, and your sample SQL,
I've just checked v$sql, and v$sql_shared_cursor
on my system.

Some of the stuff you quoted appears
on my 9.2.0.4 as well, with the flags
set on
sql_type_mismatch
bind_mismatch (different max lengths, perhaps)

A couple appear with one v$sql entry
showing the optimizer_mode as CHOOSE,
and another with "NONE", which I assume
means that heap 6 for the cursos has been flushed
from the shared pool.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Daniel Morgan" <damo...@x.washington.edu> wrote in message

news:1077304042.108905@yasure...

Daniel Morgan

unread,

Feb 21, 2004, 2:27:03 AM2/21/04

to

Daniel Morgan

unread,

Feb 21, 2004, 2:27:44 AM2/21/04

to

Jonathan Lewis wrote:

> Since v$sqlarea is an aggregate of v$sql, there
> has to be a mechanism for reporting something
> when different child cursors (i.e. the v$sql entries)
> are running under different optimizer modes. That's
> what you are seeing here.

Thanks.

Mr. Townsend ... how about some documentation at tahiti?

Daniel Morgan

unread,

Feb 21, 2004, 2:28:26 AM2/21/04

to

Jonathan Lewis wrote:

> Following up the notes from the other posters
> about the metalink note, and your sample SQL,
> I've just checked v$sql, and v$sql_shared_cursor
> on my system.
>
> Some of the stuff you quoted appears
> on my 9.2.0.4 as well, with the flags
> set on
> sql_type_mismatch
> bind_mismatch (different max lengths, perhaps)
>
> A couple appear with one v$sql entry
> showing the optimizer_mode as CHOOSE,
> and another with "NONE", which I assume
> means that heap 6 for the cursos has been flushed
> from the shared pool.

Thanks for the clarification.