RE: Help CBO ANOMALY!!! - BUG WITH HASH_AREA

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Mon, 5 Jun 2006 13:15:38 -0700 (PDT)
Message-ID: <20060605201538.13101.qmail@web33206.mail.mud.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 05 2006 - 15:15:38 CDT

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