Re: When would we see optimizer_mode=NONE in V$SQLAREA ?

Hi!

Yep, when SQL is invalidated, it's optimizer mode goes to "none", as far as I've seen. It's the same with PL/SQL stored procs, when their dependencies change, or when the procedure is loaded but not executed due to incorrect parameter number or types, etc, the optimizer_mode remains "none".

Alter, analyze and validate commands have optimizer_mode setting of the session during the operation runs, but as soon they finish, their corresponding SQL areas are invalidated, thus optimizer_mode goes back to none (with an exception of validate index command). Create and drop statements seem not to be cached at all, but that's perfectly reasonable, because a DROP will clear the corresponding object out anyway and one can't really *reuse* a CREATE statement, because corresponding object has to be dropped before, causing dependent library cache structures invalidated.

Btw, in v$sqlarea, there is also one more "option" for optimizer_mode -> "MULTIPLE CHILDS PRESENT" which states that you should go to v$sql to check individual optimizer modes (I prefer v$sql over v$sqlarea anyway due performance reasons and better granularity...)

For conclusion, this is an example of 8.1.7.1 Portal database with a lot of NONE-s:

SQL> select optimizer_mode, count(*) from v$sql group by optimizer_mode;

OPTIMIZER_ COUNT(*)
---------- ----------

CHOOSE           1467
NONE             1261
RULE                5

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 29, 2003 5:04 PM

>
>
> I noticed in an 9.2 instance that a number of entries in V$SQL, V$SQLAREA
> showed up with OPTIMIZER_MODE=NONE [there were others with CHOOSE]
>
> I can understand that it might be NONE if someone has done an ANALYZE or
> DBMS_STATS
> or executed DDL and the SQLs are invalidated.
> But do you normally see a number of entries in V$SQL like that ?
>
> [I had approx 20% of the entries].
>
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> My personal web site is : http://hkchital.tripod.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hemant K Chitale
> INET: hkchital_at_singnet.com.sg
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).