Basically virtually all of the ALL_ and DBA_ etc
objects in the data dictionary are views - some very
complex. When you use optimizer_mode = first_rows,
you are now using the CBO on the data dictionary -
since the dict is "optimized" for RBO, you can get
some occasional anomalies when accessing dictionary
objects as part of an app.
hth
connor
--- [EMAIL PROTECTED] wrote: >
> Bruce,
>
> Can you expand further on the following statement?
> We use a lot of
> synonyms (not in forms but in SQL).
>
> This led to the above query using around 1000 times
> more consistent gets
> than it needed to (due to "bad" execution plan).
>
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information System
>
>
>
>
>
>
> "Reardon, Bruce (CALBBAY)"
>
>
> <[EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> to.com.au>
> cc:
>
> Sent by: [EMAIL PROTECTED]
> Subject: Synonyms can be VERY bad for
> performance
>
>
>
>
>
>
> 10/30/01 10:35 PM
>
>
> Please respond to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> For your information and comment.
>
> We have just had a situation where the use of
> synonyms in our Forms
> application was very bad for performance.
>
> In particular, opening a form was taking around 11
> seconds, and 9.3 seconds
> of that was spent in translating the synonyms.
> A section of the tkprof output is shown below.
>
> select OBJ.OBJECT_TYPE ,OBJ.OBJECT_NAME ,OBJ.OWNER
> into :b0,:b1,:b2
> from
> ALL_SYNONYMS SYN ,ALL_OBJECTS OBJ where
> ((((SYN.SYNONYM_NAME=:b1 and
> SYN.OWNER=:b2) and SYN.TABLE_NAME=OBJ.OBJECT_NAME)
> and SYN.TABLE_OWNER=
> OBJ.OWNER) and OBJ.OBJECT_TYPE in
> ('TABLE','VIEW','SYNONYM'))
>
>
> call count cpu elapsed disk
> query current
> rows
> ------- ------ -------- ---------- ----------
> ---------- ----------
> ----------
> Parse 0 0.00 0.00 0
> 0 0
> 0
> Execute 11 0.03 0.03 0
> 0 0
> 0
> Fetch 11 9.26 9.27 0
> 427438 55
> 11
> ------- ------ -------- ---------- ----------
> ---------- ----------
> ----------
> total 22 9.29 9.30 0
> 427438 55
> 11
>
>
> System Details: Oracle 8.1.7.1.4, NT 4 Sp6a, quad
> processor server,
> optimizer_mode = first_rows, JVM installed
> The JVM install created 10300 objects
> with an object_type like
> '%JAVA%' and around 9600 synonyms.
>
> It was the optimizer_mode = first_rows (combined
> with all the synonyms from
> the JVM install) that was the real problem.
>
> Because we were in first_rows, queries against the
> data dictionary were
> optimized in first_rows mode rather than rule.
> This was despite us not having any
> statistics on system or sys
> objects.
>
> This led to the above query using around 1000 times
> more consistent gets
> than it needed to (due to "bad" execution plan).
>
>
> We found 2 ways to get around this:
> Get rid of the synonyms and use "alter
> session set
> current_schema"
> in a logon trigger, OR
> Change the optimizer_mode to choose.
>
> Our central development team decided to initially go
> with altering the
> optimizer_mode to choose.
> This improved the form opening time to
> approx 4 secs but 10% of
> this
> (0.44) seconds is still spent on translating
> synonyms.
>
> To me, this just goes to show that synonyms can be
> bad for performance as
> well as being bad for scalability.
>
> Regards,
> Bruce Reardon
> mailto:[EMAIL PROTECTED]
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> 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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> 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).
=====
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
____________________________________________________________
Nokia Game is on again.
Go to http://uk.yahoo.com/nokiagame/ and join the new
all media adventure before November 3rd.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
