One option you could pursue is: a) set optimizer_mode back to choose (as you've done) b) 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)" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > > -----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 > > --- [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)" > > > 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: [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).