Re: Optimizer Mode

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 20 Feb 2004 22:06:57 +0000 (UTC)
Message-ID: <c160e1$kg5$1@sparta.btinternet.com>

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" <damorgan_at_x.washington.edu> wrote in message
news:1077304042.108905_at_yasure...


> 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

> > |

> > |

Received on Fri Feb 20 2004 - 16:06:57 CST