USE_HASH hints

[girly1972]

Hi,

I'm creating a huge report that is using tons of nested loops.

Question:
1. I'm trying to figure out if I need to use the hint USE_HASH for each table?

2.How will I know which tables to use for the use_hash?

I have the explain plan if needed.
Plan hash value: 2454355945

SELECT/*+ use_hash(a) use_hash(p) use_hash(t)*/ DISTINCT
p.x1||'^'||
p.x2||'^'||
p.x3||'^'||
p.x4||'^'||
m.x5||'^'||
t.x6||'^'||
t.x7||'^'||
t.x8||'^'||
tm.x9||'^'||
a.x10||'^'||
o1.x11||'^'||
o2.x12||'^'||
nvl(c1.class_code,' ')||'^'||
nvl(c2.class_code,' ')||'^'||
nvl(c3.class_code,' ')||'^'||
nvl(c4.class_code,' ')||'^'||
nvl(c5.class_code,' ')||'^'||
nvl(c6.class_code,' ')||'^'||
nvl(c7.class_code,' ')||'^'||
nvl(c8.class_code,' ')||'^'||
c.x13||'^'||
a.x14||'^'||
a.x15||'^'||
a.attribute10||'^'||
i.x16||'^'||
e.x17||'^'||
e.x18||'^'||
e.x19||'^'||
e.x20||'^'||
ct.x21||'^'||
ct.x22||'^'||
ct.x23||'^'||
ct.x24||'^'||
e.x25||'^'||
e.x26||'^'||
e.x27||'^'||
f.x28||'^'||
b.x29||'^'||
b.x30||'^'||
b.x31||'^'||
b.x32,
3
FROM apps.gm_award_all a
JOIN apps.pa_project_all p
ON p.project_type = 'Program'
AND p.SEGMENT1 BETWEEN NVL('##5', p.segment1) AND NVL('##6', p.segment1)
JOIN apps.pa_task t
ON t.project_id = p.PROJECT_ID
JOIN apps.hr_all_organization_units o1
ON o1.ORGANIZATION_ID = p.CARRYING_OUT_ORGANIZATION_ID
JOIN apps.pa_segment_value_lookups d1
ON d1.SEGMENT_VALUE_LOOKUP = o1.NAME
AND d1.segment_value BETWEEN NVL('##3',d1.segment_value)
AND NVL ('##4',d1.segment_value)
JOIN apps.hr_all_organization_units o2
ON o2.ORGANIZATION_ID = t.CARRYING_OUT_ORGANIZATION_ID
LEFT OUTER
JOIN apps.ra_customers c
ON c.CUSTOMER_ID = a.FUNDING_SOURCE_ID
LEFT OUTER

[skhanal]

What version of Oracle are you using?. Instead of trying to manipulate query plan with hints, run sql advisor and see what kind of recommendation it generates and try them.

[girly1972]

I'm using oracle database version 10.2.0.3. I am also using Toad, is this sql advisor in Toad as well? Where can I find this sql advisor?


Girly1972

[girly1972]

So I guess no one has any idea on how to use hints?



girly1972