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).
