Thursday, October 25, 2007

Hints "stronger" than db parameters?

Actually I've tested only one parameter so the plural in title is questionable generalization :)

Recently battling with ORA-04030: out of process memory errors I found at least for me quite interesting fact.
USE_HASH hint is more powerful than parameter _hash_join_enabled = false (for 10g) or hash_join_enabled = false (for 9i).
I was quite suprised because without this test case I'd thought completely opposite. Of course neither hash_join_enabled nor _hash_join_enabled should be often altered to different value than default "true", but in case of "false" and your code having hints use_hash, you'll have hash joins regardless of above mentioned db parameter setting. It might be quite nasty surprise in case you have eliminated hash joins because of possible bugs, for example.
I've tested this behaviour for 10.2.0.1.0 SE and 10.2.0.1.0 EE on Windows as well as 10.2.0.3.0 SE on AIX and 9.2.0.7.0 EE on windows, all had the same behaviour.
Here is my test case and 10046 event trace execution plan results on 10.2.0.1.0 SE/Windows (all other tests were similar):


SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table a as select rownum rn, object_name
2 from all_objects;

Table created.

SQL> alter session set events '10046 trace name context forever, level 1';

Session altered.

SQL> select count(*) from (
2 select *
3 from a a1, a a2
4 where a1.rn = a2.rn);

COUNT(*)
----------
60141

Execution plan:
STAT #24 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=73 pw=0 time=212727 us)'
STAT #24 id=2 cnt=60141 pid=1 pos=1 obj=0 op='HASH JOIN (cr=556 pr=73 pw=0 time=406508 us)'
STAT #24 id=3 cnt=60141 pid=2 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=73 pw=0 time=61652 us)'
STAT #24 id=4 cnt=60141 pid=2 pos=2 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60192 us)'


SQL> alter session set "_hash_join_enabled" = false;

Session altered.

SQL> select count(*) cnt from (
2 select *
3 from a a1, a a2
4 where a1.rn = a2.rn);

CNT
----------
60141

Execution plan:
STAT #12 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=0 pw=0 time=374302 us)'
STAT #12 id=2 cnt=60141 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=556 pr=0 pw=0 time=470826 us)'
STAT #12 id=3 cnt=60141 pid=2 pos=1 obj=0 op='SORT JOIN (cr=278 pr=0 pw=0 time=88667 us)'
STAT #12 id=4 cnt=60141 pid=3 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=44 us)'
STAT #12 id=5 cnt=60141 pid=2 pos=2 obj=0 op='SORT JOIN (cr=278 pr=0 pw=0 time=231092 us)'
STAT #12 id=6 cnt=60141 pid=5 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60174 us)'

SQL> select count(*) cnt from (
2 select /*+ full(a1) full(a2) use_hash(a1 a2)*/ *
3 from a a1, a a2
4 where a1.rn = a2.rn);

CNT
----------
60141

Execution plan:
STAT #24 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=556 pr=73 pw=0 time=214116 us)'
STAT #24 id=2 cnt=60141 pid=1 pos=1 obj=0 op='HASH JOIN (cr=556 pr=73 pw=0 time=399462 us)'
STAT #24 id=3 cnt=60141 pid=2 pos=1 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=73 pw=0 time=60803 us)'
STAT #24 id=4 cnt=60141 pid=2 pos=2 obj=176936 op='TABLE ACCESS FULL A (cr=278 pr=0 pw=0 time=60188 us)'

1 comment:

Brandon Allen said...

Hi Gints, I just came across the same behavior in 8.1.7.3 on HPUX. I guess it's always been this way and we just hadn't noticed it until recently.

Regards,
Brandon Allen