Maybe you can try doing this, to be sure every thing has statistics EXEC DBMS_STATS.GATHER_DATABASE_STATS(); ----- Original Message ----- From: "Guang Mei" <gmei@xxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Monday, March 01, 2004 1:22 PM Subject: RE: query slow in 9i, but not slow in 8i > Yes, The statistics is update to date on all the tables (in both 8i and 9i). > I ran > > execute DBMS_STATS.GATHER_TABLE_STATS(<owenr>, <table_name> ,cascade => > TRUE); > > on all the tables in the schema. > > > Guang > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Cachito Reyes > Pacheco > Sent: Monday, March 01, 2004 12:11 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: query slow in 9i, but not slow in 8i > > > If you have statictis up to date with dbms package. > > We had a problem with the new views cbo feature > try to use the /*+ NO_MERGE */ hint, > if this is the same problem we had , this will fix that. > Otherwise no idea. > > ----- Original Message ----- > From: "Guang Mei" <gmei@xxxxxxxxxx> > To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx> > Sent: Monday, March 01, 2004 1:09 PM > Subject: query slow in 9i, but not slow in 8i > > > > Hi: > > > > I have a query which gave two very different explain plan on 8173 and > 9204. > > The two instances (on two separate Sun Solaris boxes) both have the same > > db_file_multiblock_read_count (8), block_size (8k), sort_area_size > > (90000000) and sort_area_retained_size (9000000). I narrowed down the part > > which causeed this: > > > > select distinct accession2, id from ( > > select accession2, > > Identifier.id > > from mt.External_accession, mt.identifier > > where external_accession.SEQTABLEID = identifier.seqtabid and > > identifier.type != 'A' and > > identifier.speciesid in > > (24,31,2,19,18,17,23,21,27,32,20,34,30,22,25,26,28,29) > > ); > > > > > > -- on 9204 (slow): > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118228 Card=8046044 > > Bytes=225289232) > > > > 1 0 SORT (UNIQUE) (Cost=118228 Card=8046044 Bytes=225289232) > > 2 1 MERGE JOIN (Cost=1102 Card=8046044 Bytes=225289232) > > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EXTERNAL_ACCESSION' > > (Cost=826 Card=28898835 Bytes=404583690) > > > > 4 3 INDEX (FULL SCAN) OF 'EXTACC_SEQTABID_INDEX' (NON-UN > > IQUE) (Cost=26 Card=28898835) > > > > 5 2 SORT (JOIN) (Cost=276 Card=51045 Bytes=714630) > > 6 5 TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=96 Card=51 > > 045 Bytes=714630) > > > > > > -- on 8173 (not slow): > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=77324 Card=6490972 B > > ytes=188238188) > > > > 1 0 SORT (UNIQUE) (Cost=77324 Card=6490972 Bytes=188238188) > > 2 1 HASH JOIN (Cost=41347 Card=6490972 Bytes=188238188) > > 3 2 TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=95 Card=3892 > > 9 Bytes=545006) > > > > 4 2 TABLE ACCESS (FULL) OF 'EXTERNAL_ACCESSION' (Cost=4111 > > 4 Card=38102138 Bytes=571532070) > > > > > > It looks like 9i thinks MERGE JOIN is better than HASH JOIN for the > subquery > > (which is fine). The problem is that when I have "select distinct > > accession2, id from ..." from the outside, the "SORT (UNIQUE)" part makes > > the cost 100 times higher in 9204 (from 1102 to 118228) while in 8173 it > > only increases the cost less than two times (from 41347 to 77324). > > > > I tried the a couple of ways in 9i, such as adding a hint /*+ > > USE_HASH(identifier) */ in the subquery. This did results in the subquery > > using hash join instead of merge join, but it did not solve the problem. > The > > cost still went to 100 times higher when I put "select distinct > accession2, > > id from ..." there. I aslo changed the init parameter > > "optimizer_max_permutations" to 80000 for the session but it did not help > > either. > > > > So my question is: > > > > 1. What is the reason that in 9204 the sort opration costs that high while > > it does not in 8173? > > > > 2. Any work around? > > > > TIA. > > > > Guang > > > > > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------