One option you could pursue is:
- set optimizer_mode back to choose (as you've done)
- create a logon trigger (for everyone) which set
optimizer_GOAL to first rows - so your dictionary runs
as choose (ie rule), but your sessions run as
first_rows (which I presume is your preference).
For those queries like the one below which are
'session-launched against dictionary objects' (for
lack of a better term), you could
i) run them with the RULE hint manually and catch a
stored outline
ii) use that stored outline to override the first_rows
issues for the original sql
hth
connor
- "Reardon, Bruce (CALBBAY)"
<Bruce.Reardon_at_comalco.riotinto.com.au> wrote: >
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
> (see
>
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
> ase_id=FOR&p_id=279251.999 )
> 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
>
> --- Cherie_Machler_at_gelco.com 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)"
>
> > Subject: Synonyms can be VERY bad for
> performance
>
> > 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
>
=== message truncated ===
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: hamcdc_at_yahoo.co.uk
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 Fri Nov 02 2001 - 03:14:46 CST
