Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help CBO ANOMALY!!! - BUG WITH HASH_AREA_SIZE? :)

Re: Help CBO ANOMALY!!! - BUG WITH HASH_AREA_SIZE? :)

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Tue, 6 Jun 2006 06:46:54 -0700 (PDT)
Message-ID: <20060606134654.43261.qmail@web33215.mail.mud.yahoo.com>


Stephane - the issue I had was not that it choose hash join but that setting the hash_area_size to a "smaller" size in the session worked better than the parameters on the database - I don't understand that at all.    

  You are absolutely right on the nested loops. I suggested they use the USE_NL hint - I did that and it worked with nested loops and was the most efficient plan by far - Thanks!    

  Still, I would love to know what is going on with the session setting versus database setting on hash_area_size.    

  :)

Stephane Faroult <sfaroult_at_roughsea.com> wrote:   Paula,

If I understand well, the CBO chooses hash-joins (powerfully enticed by a meg hash_area_size) where nested loops would be more appropriate? Can you rewrite your query? IMHO in-line views such as select * from ... where condition are a poor use of inline view, and I suspect that they are responsible for leading the CBO astray. Since your query returns few rows, you can suspect that in the final phases nested loops is what you want. I presume that the condition on mv_appointing_entity is reasonably selective by itself. You should aim for an unavoidable full scan of this table, then nested loops against the others. Since all the columns in the select list come from mv_appointing_entity, I would rather have a few correlated subqueries (i.e. AND EXISTS) against the other ones. Now, if this canned software I would head towards outlines, even if I hate them ...

HTH Stephane Faroult

Paula Stankus wrote:
> Version OS: 2.8
>
> Version Oracle:
>
> SQL> select version from v$instance;
> VERSION
> -----------------
> 8.1.7.0.0
>
>
> Problem:
>
> I have a query that uses a number of hash joins. It takes over a
> minute to run each time. I modify my session and explicitly set
> hash_area_size to 3,500,000 it runs in 2 seconds. When I look at the
> parameters for the session hash_area_size is set to:
>
> hash_area_size integer 8192000
> The query is:
>
> 1 SELECT ae.ae_type, ae.appointing_entity_id, ae.person_last_nm,
> 2 ae.person_first_nm, ae.person_middle_nm, ae.person_suffix,
> 3 ae.firm_nm, ae.company_nm, ae.group_name,
> 4 ae.appointing_entity_num
> 5 FROM mydoi.mv_licensee mle
> 6 ,
> 7 mydoi.mv_appointing_entity ae,
> 8 (SELECT *
> 9 FROM mydoi.mv_address
> 10 WHERE addr_type_id = 2) addss,
> 11 (SELECT *
> 12 FROM mydoi.mv_appointment
> 13 WHERE appt_status_id IN ('23', '24', '25', '64', '65')) appt
> 14 WHERE ( ae.company_nm LIKE UPPER ('%STATE FARM%')
> 15 OR ae.person_last_nm LIKE UPPER ('%STATE FARM%')
> 16 OR ae.person_first_nm LIKE UPPER ('%STATE FARM%')
> 17 OR ae.person_middle_nm LIKE UPPER ('%STATE FARM%')
> 18 OR ae.firm_nm LIKE UPPER ('%STATE FARM%')
> 19 )
> 20 AND addss.addr_county_code IN ('34')
> 21 AND mle.licensee_id = appt.licensee_id
> 22 AND mle.licensee_type = addss.link_table_cd
> 23 AND mle.licensee_type_id = addss.addr_linked_to_id
> 24 AND ae.appointing_entity_id = appt.appointing_entity_id
> 25 ORDER BY DECODE (ae.ae_type,
> 26 'P', ae.person_last_nm || ae.person_first_nm,
> 27 'F', ae.firm_nm,
> 28 'C', ae.company_nm,
> 29 'G', ae.group_name
> 30* )
> 31 /
>
>
> Here is the stats and time from the one with hash_area_size explicitly
> set:
>
> SQL> set timing on;
> SQL> set autotrace on;
> SQL> alter session set hash_area_size=3500000;
> Session altered.
> Elapsed: 00:00:00.00
> 12 rows selected.
> Elapsed: 00:00:02.15
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18696 Card=1033 Byte
> s=73343)
> 1 0 SORT (ORDER BY) (Cost=18696 Card=1033 Bytes=73343)
> 2 1 HASH JOIN (Cost=18666 Card=1033 Bytes=73343)
> 3 2 NESTED LOOPS (Cost=18606 Card=1033 Bytes=29957)
> 4 3 HASH JOIN (Cost=346 Card=830 Bytes=14940)
> 5 4 INDEX (RANGE SCAN) OF 'MV_ADDRESS_TEMP' (UNIQUE) (
> Cost=22 Card=2470 Bytes=22230)
> 6 4 INDEX (FAST FULL SCAN) OF 'IDX_LICENSEE_TYPE_ID' (
> NON-UNIQUE) (Cost=309 Card=770352 Bytes=6933168)
> 7 3 TABLE ACCESS (BY INDEX ROWID) OF 'MV_APPOINTMENT' (C
> ost=22 Card=949740 Bytes=10447140)
> 8 7 INDEX (RANGE SCAN) OF 'IDX_APPT_FK_IDS' (NON-UNIQU
> E) (Cost=4 Card=949740)
> 9 2 TABLE ACCESS (FULL) OF 'MV_APPOINTING_ENTITY' (Cost=59
> Card=8785 Bytes=368970)
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 102 db block gets
> 8033 consistent gets
> 1706 physical reads
> 0 redo size
> 1587 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 21 sorts (memory)
> 0 sorts (disk)
> 12 rows processed
> SQL> spool off;
>
>
> The query where hash_area_size is NOT MODIFIED:
>
> SQL> set timing on;
> 12 rows selected.
> Elapsed: 00:01:48.28
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17772 Card=1027 Byte
> s=72917)
> 1 0 SORT (ORDER BY) (Cost=17772 Card=1027 Bytes=72917)
> 2 1 HASH JOIN (Cost=17742 Card=1027 Bytes=72917)
> 3 2 HASH JOIN (Cost=17682 Card=1027 Bytes=29783)
> 4 3 INDEX (RANGE SCAN) OF 'MV_ADDRESS_TEMP' (UNIQUE) (Co
> st=22 Card=2470 Bytes=22230)
> 5 3 HASH JOIN (Cost=17650 Card=949740 Bytes=18994800)
> 6 5 INDEX (FAST FULL SCAN) OF 'IDX_LICENSEE_TYPE_ID' (
> NON-UNIQUE) (Cost=309 Card=770352 Bytes=6933168)
> 7 5 TABLE ACCESS (FULL) OF 'MV_APPOINTMENT' (Cost=1612
> 5 Card=949740 Bytes=10447140)
> 8 2 TABLE ACCESS (FULL) OF 'MV_APPOINTING_ENTITY' (Cost=59
> Card=8785 Bytes=368970)
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 9161 db block gets
> 270675 consistent gets
> 278926 physical reads
> 0 redo size
> 1587 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 12 rows processed
> SQL> spool off;
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>



Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 06 2006 - 08:46:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US