Yes, I have seen the "MULTIPLE CHILDS PRESENT" as
well.
Yet still, how do you explain the high ratio of "NONE"s.
Surely you/someone isn't running so any DDLs / ANALYZEs etc
when you query V$SQL and find that 1261 of 2733 are "NONE"s
?
I ran a test where I began a DBMS_STATS.GATHER_SCHEMA_STATS and
immediately noticed a spike in the "NONE"s
which gradually came down [I even did an ALTER SYSTEM FLUSH
SHARED_POOL in-between].
Hemant
Hemant
At 12:59 PM 29-10-03 -0800, you wrote:
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 -----
From: "Hemant K Chitale"
<hkchital@singnet.com.sg>
To: "Multiple recipients of list
ORACLE-L"
<ORACLE-L@fatcity.com>
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:
hkchital@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@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).
>
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@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@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).
Received on Thu Oct 30 2003 - 08:04:26 CST
