RE: Help CBO ANOMALY!!! - BUG WITH HASH_AREA_SIZE? :)
- From: Paula Stankus <paulastankus@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 5 Jun 2006 13:15:38 -0700 (PDT)
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
Other related posts:
