| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Synonyms can be VERY bad for performance
Cherie,
My comment on the consistent gets comparison came from using autotrace.
More specifically, from the tkprof output, the troublesome statement was:
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'))
I created a select only version of this with no bind variables and put the same statement into a .sql file 11 times.
(I put it in 11 times because the tkprof output showed up 11 executes / 11 fetches).
I then logged onto our database as sys and turned autotrace on.
When run in first_rows, it used 40183 consistent gets.
I then did an alter session set optimizer_mode=rule;
When run in rule mode, the script used 44 consistent gets.
Hence my comment on 1000 times more in first_rows.
The tkprof comparison of when the database was in first rows vs the database in choose mode showed an even bigger difference. In first_rows, the fetch call query value was 427438. In choose, the fetch call query value was 220. This is a difference of 2000 times.
The synonym translations were particularly bad for us due to the very high number of synonyms in database (we have 11143 synonyms, around 9600 of these came from installing Java).
So as Connor suggested, in first_rows mode you will encounter some very bad queries against the data dictionary.
An example of 1 which has been fixed by Oracle is catblock.sql - there is an updated version available on Metalink -
see note 122567.1 titled "Poor Performance in Query on
DBA_WAITERS"
Whilst searching for the notes suggested by Anita, I came across a
good forum discussion
This describes how the ODBC driver 8.1.7.4 has been fixed /
improved to use rule hints when accessing the data dictionary.
Before this if you used the Oracle ODBC driver and were in
first_rows mode we had to wait 5 - 10 minutes just to link a table in
Access.
I haven't yet had a chance to download the new ODBC driver
to test this out.
It would be good if the all_objects / all_synonyms views were hinted to give good execution plans regardless of the optimizer_mode you were in.
Cherie - to see if this problem is affecting you and how much I would do a couple of things:
count(*) from dba_synonyms
In first_rows, trace a query using synonyms and run tkprof on it -
see how much time is allocated to synonym translation
Alter to (say) choose, trace the same query using synonyms and run
tkprof on it - see how much time is allocated to synonym translation
If you run the tkprof explain plan as sys you will be able to see the (potentially) different execution plans used during synonym translation.
I hope this helps & I'll be interested to see your results.
Regards,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au
-----Original Message-----
Sent: Friday, 2 November 2001 4:04
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
> 10/30/01 10:35 PM
>
> 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).Received on Thu Nov 01 2001 - 16:27:52 CST
 |
 |