Home » RDBMS Server » Performance Tuning » Hash join hint not being used
Hash join hint not being used [message #191239] Tue, 05 September 2006 05:32 Go to next message
speaker
Messages: 30
Registered: April 2006
Member
Hi pls find my SQL and the explain plan in 9i db

SELECT *
FROM (SELECT DISTINCT /*+ USE_HASH( MI INST ) */
NULL AS ctrack_instance_col_id,
NULL AS ctrack_title_narration,
NULL AS ctrack_performer_list,
NULL AS ctrack_is_default_lid,
mi.lid AS ctrack_title_language,
NULL AS ctrack_prt_narration,
NULL AS ctrack_is_published,
NULL AS ctrack_created_date,
NULL AS ctrack_instance_id,
NULL AS ctrack_genres_list,
NULL AS ctrack_short_title,
NULL AS ctrack_owner_name, NULL AS ctrack_price_desc,
NULL AS ctrack_filestatus, NULL AS ctrack_dtmf_title,
NULL AS ctrack_is_sample, NULL AS ctrack_origin_id,
NULL AS ctrack_position, NULL AS ctrack_prt_list,
NULL AS ctrack_is_avail, NULL AS ctrack_filesize,
NULL AS ctrack_filename, NULL AS ctrack_owner_id,
NULL AS ctrack_encoding, NULL AS ctrack_urlpath,
NULL AS ctrack_uncpath, NULL AS ctrack_length,
NULL AS ctrack_ext_id, NULL AS ctrack_price,
NULL AS ctrack_begin, mi.title AS ctrack_title,
NULL AS ctrack_ml_id, NULL AS ctrack_desc,
NULL AS ctrack_type, NULL AS ctrack_rank,
NULL AS ctrack_end, mi.ID AS ctrack_id,
NULL AS fe_id
FROM MI_FULL_PLAIN MI,INST,PDL, PGROUP
WHERE mi.ID = inst.mi_id
AND inst.ID = pdl.inst_id
AND pdl.pgroup_id = pgroup.ID
AND pdl.is_ready = 'Y'
AND pdl.feglist_id = 7111
AND inst.enctype IN (61)
AND pdl.is_published = 'Y'
AND UPPER (mi.title) LIKE UPPER ('%Narration%')
AND mi.TYPE <> '9'
ORDER BY UPPER (mi.title))
WHERE ROWNUM <= 10

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=489)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=15 Card=1 Bytes=489)
3 2 SORT (ORDER BY STOPKEY) (Cost=15 Card=1 Bytes=57)
4 3 SORT (UNIQUE) (Cost=10 Card=1 Bytes=57)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MI_FULL_PLAIN' (
Cost=2 Card=1 Bytes=25)

6 5 NESTED LOOPS (Cost=5 Card=1 Bytes=57)
7 6 NESTED LOOPS (Cost=3 Card=1 Bytes=32)
8 7 NESTED LOOPS (Cost=2 Card=1 Bytes=20)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'PDL' (Co
st=2 Card=1 Bytes=15)

10 9 INDEX (RANGE SCAN) OF 'IX_PDL_FEGLIST_ID
' (NON-UNIQUE) (Cost=1 Card=1)

11 8 INDEX (UNIQUE SCAN) OF 'PK_PGROUP_ID' (UNI
QUE)

12 7 TABLE ACCESS (BY INDEX ROWID) OF 'INST' (Cos
t=1 Card=1 Bytes=12)

13 12 INDEX (UNIQUE SCAN) OF 'PK_INST_ID' (UNIQU
E)

14 6 INDEX (RANGE SCAN) OF 'IX_MI_FP_ID' (NON-UNIQU
E) (Cost=1 Card=2)




The init.ora parameters are as follows

optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string CHOOSE
hash_area_size integer 10485760
hash_join_enabled boolean TRUE


Why the hint is being ignored.
The same query in 8i uses hash join and with good performance

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS 13 17
COUNT STOPKEY
VIEW 13 6 K 17
SORT ORDER BY STOPKEY 13 871 17
SORT UNIQUE 13 871 12
NESTED LOOPS 13 871 6
HASH JOIN 13 819 6
HASH JOIN 13 624 4
TABLE ACCESS FULL ACRI.INST 69 828 2
TABLE ACCESS FULL ACRI.MI_FULL_PLAIN 12 432 1
TABLE ACCESS FULL ACRI.PDL 122 1 K 1
INDEX UNIQUE SCAN AMI.PK_PGROUP_ID 2 8



Pls help
Re: Hash join hint not being used [message #191257 is a reply to message #191239] Tue, 05 September 2006 07:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You cannot specify two tables in a USE_HASH hint. Specify two hints instead.
/*+ USE_HASH(MI) USE_HASH(INST)*/


Ross Leishman
Re: Hash join hint not being used [message #191274 is a reply to message #191257] Tue, 05 September 2006 07:54 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Why not?

The documentation says he can:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50703

From the example:

The USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join. For example:

SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;
Re: Hash join hint not being used [message #191275 is a reply to message #191239] Tue, 05 September 2006 07:56 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Have You tried using the FULL hint on all three tables?
Maybe both needs to be applied?

And off course - have You gathered statistics on the tables?

Br
Kim
Re: Hash join hint not being used [message #191373 is a reply to message #191275] Wed, 06 September 2006 01:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ah yes, clearly I am going mad. Indulge my madness for a few moments longer then...

You have placed the hint in the wrong place - move it between the SELECT and the DISTINCT.

Ross Leishman
Previous Topic: advantage of append mode ??
Next Topic: ORA-01652 - Temporary Tablespace problems!!
Goto Forum:
  


Current Time: Thu Dec 08 10:15:24 CST 2016

Total time taken to generate the page: 0.17003 seconds