query slow in 9i, but not slow in 8i 2004-03-01 - By Guang Mei
in 9i db:
SQL > alter session set sort_area_size = 200000000;
Session altered.
SQL > select NAME,VALUE from v$parameter where name like '%sort% ';
NAME
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
VALUE
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
nls_sort
sort_area_size
200000000
sort_area_retained_size
9000000
SQL > select NAME,VALUE from v$parameter where name like '%hash% ';
NAME
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
VALUE
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
hash_join_enabled
TRUE
hash_area_size
400000000
SQL > SET AUTOTRACE TRACEONLY EXPLAIN
SQL > 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));
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)
It did not seem to help.
Guang
On Mon, 1 Mar 2004, Wolfgang Breitling wrote:
> At 08:33 PM 3/1/2004, you wrote:
> >When I put hint use_hash in the sql, in 9i the subquery did take the hint
> >and explain plan showed it used "hash join " instead of "merge join ", but
> >the cost was higher (than "merge join "), and "SORT (UNIQUE) " still caused
> >cost to jump 10 times. Therefore using hint "use_hash " didn 't solve my
> >problem.
>
> Never mind the cost. They don 't mean a thing outside the optimizer. Does
> the sql perform better with the hint that causes it to use hash join?
>
> >I can not bounce the 9i db to try all the init parameters now since there
> >are other programs running. But I strongly suspect the "problem " is not
> >due to "hash join " and "merge join " (becuase both showed acceptable cost
> >values in explain plan, and "hash join " has been working for this sql for the
> >past year or so, never failed), but due to some optimizer change(s) that
> >oracle
> >made on 9i about sorting of a large view result set.
> >
> >I have had four cases so far that a sql which ran fast in 8i became
> >painfully slow in 9i, I need either setting some obsolete parameters or
> >re-write sql to have them run fast in 9i. So far I have not had a
> >situation that the some sql automatically runs much faster in 9i than 8i.
> >I have spent quite some time "fixing " these situations and I have not had
> >a complete successful run on our application yet.
> >
> >I have not set "optimizer_features_enable=8.1.7 " yet, but it looks like I
> >might have to.
>
> That should be a very last resort.
>
> >Anyway, here is the workarea_size_policy value in my 9i db:
> >
> >SQL > select NAME,VALUE from v$parameter where name like '%work% ';
> >
> >NAME
> >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> >VALUE
> >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
> >workarea_size_policy
> >AUTO
> >
> >Would this value have some effect on my query?
>
> It could. What is the pga_aggregate_target? I couldn 't quickly find what
> overrides what:
> Does setting sort_area_size or hash_area_size override
> workare_size_policy=auto or vice versa?
> Does anyone know? (Rhetorical question - someone 'll know). I lean towards
> the former.
>
> Run a 10053 event trace when explaining the sql - both in 8i and in 9i. It
> may be too big to post. You can e-mail it to me.
>
> >Guang
> >
> >
> >
> >On Tue, 2 Mar 2004, zhu chao wrote:
> >
> > > hi, guang:
> > > If all other SQL performs well in the new 9i instance, I would consider
> > > using dbms_stats to transfer the statistics from 8i to 9i and check if the
> > > explain plan goes better, if it still does not work, I would use HINT.
> > Since
> > > using use_hash get the good plan, what do you means by saying: sort
> > > opration costs too high? Is it the actual SQL cost more time or just the
> > > explain cost get high? IF only the explain cost high, I won 't care about it
> > > at all.
> > > Do you use workarea_size_policy=auto in 9i?
> > > If more SQLs get bad, I would consider using
> > > optimizer_features_enable=8.1.7 and other optimizer related parameter the
> > > same as 8i.
> > >
> > > Regards
> > > Zhu Chao
> > >
> > > -- -- Original Message -- --
> > > From: "Guang Mei " <gmei@(protected) >
> > > To: "Oracle-L-freelists " <oracle-l@(protected) >
> > > Sent: Tuesday, March 02, 2004 1:09 AM
> > > Subject: query slow in 9i, but not slow in 8iut
> > >
> > >
> > > > 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@(protected)
> > > > put 'unsubscribe ' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://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@(protected)
> > > put 'unsubscribe ' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://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@(protected)
> >put 'unsubscribe ' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe ' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
