Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.ision.net!newsfeed2.easynews.net!ision!newsfeed.freenet.de!newsfeed0.kamp.net!newsfeed.kamp.net!213.239.142.2.MISMATCH!feed.xsnews.nl!border-1.ams.xsnews.nl!68.142.88.75.MISMATCH!hwmnpeer01.ams!news.highwinds-media.com!kramikske.telenet-ops.be!nntp.telenet.be!news.skynet.be!195.238.0.222.MISMATCH!newsspl501.isp.belgacom.be!tjb!not-for-mail From: "Matthias Hoys" Newsgroups: comp.databases.oracle.server References: <45ef1abf$0$2940$ba620e4c@news.skynet.be> <45f06628$0$2943$ba620e4c@news.skynet.be> <1173392430.990319.15890@h3g2000cwc.googlegroups.com> <45f32f33$0$2950$ba620e4c@news.skynet.be> <1173626542.709090.31890@8g2000cwh.googlegroups.com> Subject: Re: CBO & different execution plans Date: Sun, 11 Mar 2007 16:26:58 +0100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-RFC2646: Format=Flowed; Original Lines: 103 Message-ID: <45f41fc6$0$2953$ba620e4c@news.skynet.be> Organization: -= Belgacom Usenet Service =- NNTP-Posting-Host: d3a24955.news.skynet.be X-Trace: 1173626822 news.skynet.be 2953 81.244.123.53:1143 X-Complaints-To: usenet-abuse@skynet.be Xref: news.f.de.plusline.net comp.databases.oracle.server:195295 "Charles Hooper" wrote in message news:1173626542.709090.31890@8g2000cwh.googlegroups.com... > On Mar 10, 6:20 pm, "Matthias Hoys" wrote: >> We are not using VPD. But you are right, the public synonyms point to >> different objects, it seems like the application has some built-in >> security >> layer. Still, those extra security tables are very small, so I'm still >> searching why the query runs fast on Oracle 8i and slow on 10g (with >> roughly >> the same CBO settings and statistics). I found out that, if I add the >> HTS_ASSAY_RESULT table to the end of the table list in the FROM part of >> the >> query, the query always executes fast. However, I can't modify the >> application code. And no matter what CBO settings or statistics that I >> use, >> Oracle 10g generates a non-performant execution plan. Only by rewriting >> the >> query, it's executed fine ... So I'm a bit stuck here ... Maybe stored >> outlines could help ? >> >> Matthias > > FROM > HTS_ASSAY_RESULT R, > HTS_ASSAY_RESULT_TYPE ART, > HTS_ASSAY_PROTOCOL AP , > HTS_ASSAY A, > HTS_COMPOUND_LOT CL > WHERE > R.ALT_ASSAY_ID=AP.ALT_ASSAY_ID > AND ART.ASSAY_ID=AP.ASSAY_ID > AND ART.RESULT_TYPE=R.RESULT_TYPE > AND R.SAMPLE_ID = CL.SAMPLE_ID > AND CL.COMPOUND_ID = 2866242 > AND A.ASSAY_ID = AP.ASSAY_ID > AND A.ASSAY_NAME IN ('PKCZ_IE') > ORDER BY > R.EXPERIMENT_ID, > R.RESULT_ID, > R.PARENT_RESULT_ID, > ART.DRILL_ORDER > > In the fast running execution, tables are joined in the following > order: > HTS_ASSAY to HTS_COMPOUND_LOT, which results in 5 rows. Previous > results are then joined to 536 rows from HTS_ASSAY_RESULT, which > results in 536 rows. The results of the previous are then joined with > 48 rows from HTS_ASSAY_PROTOCOL, which produces 48 rows. The previous > results are then joined with 48 rows from HTS_ASSAY_RESULT_TYPE, which > results in 48 rows. > > In the slow running execution: > The public synonymn for HTS_ASSAY_RESULT points to > HTS_SECURE_ASSAY_RESULT view > HTS_SECURE_ASSAY_RESULT view contains a reference to: > HTS_ASSAY > HTS_ASSAY_PROTOCOL > HTS_ASSAY_RESULT > The HTS_ASSAY to HTS_ASSAY_PROTOCOL join results in 95 rows. The > results of this join is then apparently outer joined with the > 2,377,609 rows returned from the HTS_ASSAY_RESULT table. In the last > join operation for the SQL statement, the results of combining the > other tables (50 rows) is hash joined with the results from > HTS_SECURE_ASSAY_RESULT view (2,377,609 rows), which results in 48 > rows being returned by the SQL statement. > > When the tables are joined in the order indicated in the slow running > execution, Oracle does not have any way to constrain the 2,377,609 > rows in the HTS_ASSAY_RESULT table until the view is finally combined > with the row set results of the other tables. My guess is that Oracle > is predicting that the cardinality coming out of the > HTS_SECURE_ASSAY_RESULT view will be very high, so it selects to join > this view last, rather than third as in the fast running execution. > > What you might try to do is execute a 10053 trace at level 1 when > executing the above SQL statement as a user who is not the schema > owner. Then, alter the optimizer_features_enabled parameter for the > session to 8.1.7 and eexcute another 10053 trace at level 1 when > executing the above SQL statement. If the above SQL statement > executes quickly with optimizer_features_enabled set to 8.1.7, examine > the two trace files. The trace files will list all parameters, > including hidden parameters, that were in effect during the execution > of the SQL statement. Compare the parameters between the two runs - > where differences exist, you are seeing the parameters that changed > automatically when optimizer_features_enabled was changed. By > starting a new session and altering that session one parameter at a > time before each execution of the SQL statement, you may be able to > find the parameter that corrects the problem. If you find the > problematic parameter, you could create a logon trigger that sets the > necessary session level parameters when that application module runs. > > If you post a dump of the optimizer parameters, someone may be able to > identify the parameter that is affecting the join order. > > Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc. > Nice tip, thanks, will try this tomorrow.