Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Synonyms can be VERY bad for performance

Re: Synonyms can be VERY bad for performance

From: <Cherie_Machler_at_gelco.com>
Date: Thu, 01 Nov 2001 05:21:05 -0800
Message-ID: <F001.003BA351.20011101051018@fatcity.com>

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)"                                         
                                         
                    <Bruce.Reardon_at_comalco.riotin       To:     Multiple recipients of 
list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    to.com.au>                          cc:                            
                                         
                    Sent by: root_at_fatcity.com           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:bruce.reardon_at_comalco.riotinto.com.au

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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: 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.com -- Author: INET: Cherie_Machler_at_gelco.com 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: 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).
Received on Thu Nov 01 2001 - 07:21:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US