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
---------- ----------
CHOOSE 1467
NONE 1261
RULE 5
Tanel.
----- Original Message -----
From: "Hemant K Chitale" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Wednesday, October 29, 2003 5:04
PM
Subject: When would we see optimizer_mode=NONE in
V$SQLAREA ?
>
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
