Home » RDBMS Server » Performance Tuning » Hints USE_HASH (merged)
Hints USE_HASH (merged) [message #395193] Tue, 31 March 2009 10:39 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
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








Anne
Re: Hints USE_HASH [message #395397 is a reply to message #395193] Wed, 01 April 2009 05:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm guessing that all of those ##n values are replaced with either other values or nulls.

How do you generally use those values? Are they normally small ranges returning a small number of rows? Or do you frequently use NULLs so that all rows re selected? Use Nested loops for the former, hash joins for the latter. However, if you are using Nested Loops, you need to make sure that the driving table is the one with the selective where clauses.

Ross Leishman
Re: Hints USE_HASH [message #395426 is a reply to message #395397] Wed, 01 April 2009 07:37 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Yes, they are replaced with values. It's a sql plus report, so therefore a user would be prompted to put in a value.

Normally the report is ran with a high range of values, so it will return tons of rows.But sometimes it's null so that all rows are selected. The explain plan shows nested loops through out this report.

Can you explain this better:

you need to make sure that the driving table is the one with the selective where clauses.



Are you saying the driving tables should be used in the hint?

Should I separate each table in the hint as follows:


/*+ use_hash(a) use_hash(p) use_hash(t)*/  


or should it be:


/*+ use_hash(a,p) use_hash(t) */  
Re: Hints USE_HASH [message #395467 is a reply to message #395426] Wed, 01 April 2009 09:18 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Here is the entire query. Also attached explain plan.


SELECT 'Project report ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8',
       0 sort_col   
  FROM DUAL
 UNION
SELECT '##err_message', 1
  FROM DUAL
 UNION
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^'||
       'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'||
       'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'||
       'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'||
       'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'||
       'Program Income^Total Invoiced^Total Revenue^Total Funding^'||
       'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget',
       2      
  FROM DUAL
 UNION
SELECT DISTINCT
       p.segment1||'^'||
       p.START_DATE||'^'||
       p.COMPLETION_DATE||'^'||
       p.PROJECT_STATUS_CODE||'^'||
       m.FULL_NAME||'^'||
       --ra.FULL_NAME||'^'||
       t.task_number||'^'||
       t.START_DATE||'^'||
       t.COMPLETION_DATE||'^'||
       tm.full_name||'^'||
       a.AWARD_NUMBER||'^'||
       rtrim(translate(a.AWARD_SHORT_NAME,chr(10),' '))||'^'||
       o1.name||'^'||
       o2.name||'^'||
       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.CUSTOMER_NAME||'^'||
       a.type||'^'||
       a.Award_Purpose_code||'^'||
       a.attribute10||'^'||
       i.IND_RATE_SCH_NAME||'^'||
       e.total_exp||'^'||
       e.dir_exp||'^'||
       e.ind_exp||'^'||
       e.cs_exp||'^'||
       ct.tot_commit||'^'||
       ct.dir_commit||'^'||
       ct.ind_commit||'^'||
       ct.cs_commit||'^'||
       e.program_income||'^'||
       e.billed||'^'||
       e.rev||'^'||
       f.funding||'^'||
       b.budget_total||'^'||
       b.direct_budget||'^'||
       b.indirect_budget||'^'||
       b.cs_budget,
      3
  FROM apps.gms_awards_all a
  JOIN apps.pa_projects_all p
    ON p.project_type = 'Sponsored' 
   AND p.SEGMENT1 BETWEEN NVL('##5', p.segment1) AND NVL('##6', p.segment1)
  JOIN apps.pa_tasks 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
--Breakdown of Budget totals
  JOIN (SELECT ag.award_id, gra.project_id, gra.task_id,
               gra.budget_version_id,
               SUM (NVL(bl.burdened_cost,0)) budget_total,
               SUM (CASE SUBSTR (r.NAME, 1, 10)
                      WHEN 'Award Reve' THEN NVL (bl.burdened_cost, 0)
                      WHEN 'F & A Cost' THEN NVL (bl.burdened_cost, 0)
                      ELSE 0
                    END) indirect_budget,
               SUM (CASE SUBSTR (r.NAME, 1, 10)
                      WHEN 'Award Reve' THEN 0
                      WHEN 'F & A Cost' THEN 0
                      WHEN 'Cost Share' THEN 0
                      ELSE NVL (bl.burdened_cost, 0)
                    END) direct_budget,
               SUM (CASE SUBSTR (r.NAME, 1, 10)
                      WHEN 'Cost Share' THEN NVL (bl.burdened_cost, 0)
                      ELSE 0
                    END) cs_budget
          FROM apps.gms_resource_assignments gra
          JOIN apps.gms_budget_versions bv
            ON bv.project_id = gra.project_id
           AND bv.budget_version_id = gra.budget_version_id
          JOIN apps.gms_budget_lines bl
            ON bl.resource_assignment_id = gra.resource_assignment_id
          JOIN apps.pa_resource_list_members rlm
            ON rlm.resource_list_member_id = gra.resource_list_member_id
          JOIN apps.pa_resources r
            ON r.resource_id = rlm.resource_id
          JOIN apps.gms_awards_all ag
            ON ag.award_id = bv.award_id 
         WHERE gra.budget_version_id IN
              (SELECT MAX (bv.budget_version_id)
                 FROM gms.gms_budget_versions bv
                WHERE bv.project_id = gra.project_id)
         GROUP BY ag.award_id,
                  gra.project_id,
                  gra.task_id,
                  gra.budget_version_id) b
    ON b.project_id = t.project_id
   AND b.task_id = t.task_id
   AND b.award_id = a.award_id
  LEFT OUTER
--Breakdown of tot commitments
  JOIN (SELECT gma.award_id,
               pct.project_id,
               pct.TASK_ID,
               SUM (NVL(pct.tot_cmt_burdened_cost,0)) tot_commit,
               SUM (CASE pct.expenditure_category
                      WHEN 'F & A Costs' THEN 0
                      WHEN 'Cost Share' THEN 0
                      ELSE NVL(pct.tot_cmt_burdened_cost, 0)
                    END) dir_commit,
               SUM (CASE pct.expenditure_category
                      WHEN 'F & A Costs' THEN NVL(pct.tot_cmt_burdened_cost, 0)
                      ELSE 0
                    END) ind_commit,
               SUM (CASE pct.expenditure_category
                      WHEN 'Cost Share' THEN NVL(pct.tot_cmt_burdened_cost, 0)
                      ELSE 0
                    END) cs_commit
          FROM apps.pa_commitment_txns pct
          JOIN apps.gms_awards_basic_v gmb
            ON gmb.project_id = pct.project_id
           AND gmb.task_id = pct.task_id
          JOIN apps.gms_awards_all gma
            ON gma.award_id = gmb.award_id
         GROUP BY gma.award_id,
                  pct.project_id,
                  pct.TASK_ID) ct
    ON ct.project_id = t.project_id
   AND ct.task_id = t.task_id
   AND ct.award_id = a.award_id
  LEFT OUTER
--Breakdown for tot expenses
  JOIN (SELECT gmsa.award_id,
               gmsac.project_id,
               gmsac.task_id,
               SUM (CASE SUBSTR (gmsac.expenditure_type, 1, 3)
                      WHEN 'IDC' THEN 0
                      ELSE NVL (GMSAC.BURDENED_COST, 0)
                    END)
               + SUM (CASE SUBSTR (et.expenditure_category, 1, 11)
                        WHEN 'F & A Costs' THEN NVL (GMSAC.BURDENED_COST, 0)
                        ELSE 0
                      END)
               + SUM (CASE SUBSTR (et.expenditure_category, 1, 20)
                        WHEN 'Cost Share' THEN NVL (GMSAC.BURDENED_COST, 0)
                        WHEN 'Cost Share-Direct' THEN NVL (GMSAC.BURDENED_COST, 0)
                        WHEN 'Cost Share-Indirect' THEN NVL (GMSAC.BURDENED_COST, 0)
                        WHEN 'Research Enhancement' THEN NVL (GMSAC.BURDENED_COST, 0)
                        ELSE 0
                      END)
               + SUM (CASE SUBSTR (et.expenditure_category, 1, 16)
                        WHEN 'Program Income' THEN NVL (GMSAC.BURDENED_COST, 0)
                        WHEN 'Program - Income' THEN NVL (GMSAC.BURDENED_COST, 0)
                        ELSE 0
                      END) TOTAL_EXP,
               SUM (CASE substr (gmsac.expenditure_type, 1, 3)
                      WHEN 'IDC' THEN 0
                      ELSE NVL (GMSAC.BURDENED_COST, 0)
                    END) DIR_EXP,
               SUM (CASE SUBSTR (et.expenditure_category, 1, 11)
                      WHEN 'F & A Costs' THEN NVL (GMSAC.BURDENED_COST, 0)
                      ELSE 0
                    END) IND_EXP,
               SUM (CASE SUBSTR(et.expenditure_category, 1, 20)
                      WHEN 'Cost Share' THEN NVL (GMSAC.BURDENED_COST, 0) 
                      WHEN 'Cost Share-Direct' THEN NVL (GMSAC.BURDENED_COST, 0)
                      WHEN 'Cost Share-Indirect' THEN NVL (GMSAC.BURDENED_COST, 0)
                      WHEN 'Research Enhancement' THEN NVL (GMSAC.BURDENED_COST, 0)
                      ELSE 0
                    END) CS_EXP,
               SUM(CASE SUBSTR (et.expenditure_category, 1, 16)
                     WHEN 'Program Income' THEN NVL(GMSAC.BURDENED_COST,0)
                     WHEN 'Program - Income' THEN NVL(GMSAC.BURDENED_COST, 0)
                     ELSE 0
                   END) PROGRAM_INCOME,
               SUM (NVL (gmsac.BILLED_AMOUNT, 0)) billed,
               SUM (NVL (gmsac.REVENUE_AMOUNT, 0)) rev
          FROM tams.tams_gms_status_actuals gmsac
          JOIN apps.gms_awards_all gmsa
            ON gmsa.AWARD_ID = gmsac.award_id
          LEFT OUTER
          JOIN apps.pa_expenditure_types et
            ON et.EXPENDITURE_TYPE = gmsac.expenditure_type
          LEFT OUTER
          JOIN apps.PA_COST_DISTRIBUTION_LINES_ALL cdl
            ON cdl.EXPENDITURE_ITEM_ID = gmsac.expenditure_item_id
           AND cdl.project_id = gmsac.project_id
           AND cdl.task_id = gmsac.task_id
           AND cdl.line_num = '1'
          LEFT OUTER
          JOIN apps.gl_code_combinations gcc
            ON gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
         WHERE gmsac.pa_date BETWEEN TO_DATE (SUBSTR ('##1', 1, 10), 'YYYY/MM/DD')
                                 AND TO_DATE (SUBSTR ('##2', 1, 10), 'YYYY/MM/DD')
           AND gcc.segment3 BETWEEN NVL('##7', gcc.segment3)
                                AND NVL('##8', gcc.segment3)
         GROUP BY gmsa.award_id,
                  gmsac.project_id,
                  gmsac.task_id) e
    ON e.project_id = t.project_id
   AND e.task_id = t.task_id
   AND e.award_id = a.award_id
  LEFT OUTER
-- Breakdown of funding amounts  
  JOIN (SELECT gi.award_id,
               project_id,
               pfs.task_id,
               SUM (pfs.FUNDING_AMOUNT) funding
          FROM apps.gms_project_fundings pfs
          JOIN apps.gms_installments gi
            ON gi.installment_id = pfs.installment_id
         WHERE pfs.installment_id IN
              (SELECT MAX (g.INSTALLMENT_ID)
                 FROM apps.gms_installments g
                 JOIN apps.gms_awards_all ga
                   ON ga.award_id = g.award_id
                WHERE g.installment_id = pfs.installment_id)
         GROUP BY gi.award_id,
                  pfs.project_id,
                  pfs.task_id) f
    ON f.project_id = t.project_id
   AND f.task_id = t.task_id 
   AND f.award_id = a.award_id
  LEFT OUTER
  JOIN apps.PA_PROJECT_PARTIES pp1
    ON pp1.PROJECT_ID = p.PROJECT_ID
   AND pp1.PROJECT_ROLE_ID = 1  
   AND pp1.start_date_active <= sysdate
   AND NVL (pp1.END_DATE_ACTIVE, sysdate) >= sysdate
  LEFT OUTER
  JOIN apps.per_people_f m
    ON m.PERSON_ID = pp1.RESOURCE_SOURCE_ID
  JOIN apps.PA_PROJECT_PARTIES pp2
    ON pp2.PROJECT_ID = p.PROJECT_ID
   AND pp2.PROJECT_ROLE_ID = 1004 
   AND pp2.start_date_active <= sysdate
   AND NVL (pp2.END_DATE_ACTIVE,sysdate) >= sysdate
  LEFT OUTER
   JOIN apps.gms_personnel gp
    ON gp.award_role ='AM'
   AND gp.award_id = a.award_id
  JOIN apps.per_people_f fp
    ON fp.person_id = gp.person_id  
 JOIN apps.per_people_f tm
    ON tm.PERSON_ID = t.TASK_MANAGER_PERSON_ID
  LEFT OUTER
  JOIN apps.pa_project_classes c1
    ON c1.PROJECT_ID = p.PROJECT_ID
   AND c1.CLASS_CATEGORY = 'Expense Code'
  LEFT OUTER
  JOIN apps.pa_project_classes c2
    ON c2.PROJECT_ID = p.PROJECT_ID
   AND c2.CLASS_CATEGORY = 'OMB A-21'
  LEFT OUTER
  JOIN apps.pa_project_classes c3
    ON c3.PROJECT_ID = p.PROJECT_ID
   AND c3.CLASS_CATEGORY = 'Revenue Line'
  LEFT OUTER
  JOIN apps.pa_project_classes c4
    ON c4.PROJECT_ID = p.PROJECT_ID
   AND c4.CLASS_CATEGORY = 'Burden Rate'
  LEFT OUTER
  JOIN apps.pa_project_classes c5
    ON c5.PROJECT_ID = p.PROJECT_ID
   AND c5.CLASS_CATEGORY = 'Burden Structure'
  LEFT OUTER
  JOIN apps.pa_project_classes c6
    ON c6.PROJECT_ID = p.PROJECT_ID
   AND c6.CLASS_CATEGORY = 'Site'
  LEFT OUTER
  JOIN apps.pa_project_classes c7
    ON c7.PROJECT_ID = p.PROJECT_ID
   AND c7.CLASS_CATEGORY = 'Sponsor'
  LEFT OUTER
  JOIN apps.pa_project_classes c8
    ON c8.PROJECT_ID = p.PROJECT_ID
   AND c8.CLASS_CATEGORY = 'Type'
  LEFT OUTER
  JOIN apps.PA_IND_RATE_SCHEDULES_ALL_BG i
    ON i.IND_RATE_SCH_ID = a.IDC_SCHEDULE_ID
 ORDER BY 2;




Question: How would I utilize the hint use_hash in the report? Any suggestions?



Anne
Re: Hints USE_HASH [message #396444 is a reply to message #395467] Mon, 06 April 2009 13:50 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
So I guess no one has any idea about how to use hints????



Anne
Re: Hints USE_HASH (merged) [message #396445 is a reply to message #395193] Mon, 06 April 2009 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>So I guess no one has any idea about how to use hints????
Hints are (ab)used by those who think they are smarter than CBO.
If you are not smarter than the CBO, then perhaps HINTS are not for you.

You have table & DDL. We don't
You have data & DML. We don't.
You are free to do trail & error to attempt to find the SILVER BULLET (HINT) which will make SQL run faster.


Any application which requires 100+ line SQL should not expect fast response from the database.


Re: Hints USE_HASH (merged) [message #396456 is a reply to message #395193] Mon, 06 April 2009 17:13 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
"Hints are (ab)used by those who think they are smarter than CBO"

Agreed. If you dont know wh you are using them, then you shouldnt be using them.

Although a very simple rule of thumb is that if you are reading more then 25% of a table via nested loops, then a hash join *may* be faster and more efficient in an IO limited subsystem.

The question should be answered why the CBO feels that doing a hash join is sub-optimal compared to an NL join though. This is a matter of tracing the query using SQL*Trace and TK*Prof to find out the choices made.
Re: Hints USE_HASH [message #396501 is a reply to message #396444] Tue, 07 April 2009 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anne Simms wrote on Mon, 06 April 2009 20:50
So I guess no one has any idea about how to use hints????
Anne

Randomly?

Regards
Michel

Re: Hints USE_HASH [message #396669 is a reply to message #396501] Tue, 07 April 2009 09:26 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I just need to understand how the use the USE_HASH in my report. See my prior postings in this same topic.

Thanks


Anne
Re: Hints USE_HASH (merged) [message #396691 is a reply to message #396456] Tue, 07 April 2009 13:20 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
There are a lot of nested loops in the explain plan. That is why I am using the hint.


Anne
Re: Hints USE_HASH (merged) [message #396721 is a reply to message #395193] Tue, 07 April 2009 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I just need to understand how the use the USE_HASH in my report.
With dozens of HINTS to choose from, why are you fixated on USE_HASH?

If USE_HASH will "always" provide better performance, why does Oracle not do USE_HASH in place of nested loops?

If USE_HASH will "always" provide better performance, why have dozens of other HINTS as alternatives?

Are all the table designed to Third Normal Form?
Re: Hints USE_HASH (merged) [message #396792 is a reply to message #395193] Wed, 08 April 2009 01:26 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Repeat:-
Although a very simple rule of thumb is that if you are reading more then 25% of a table via nested loops, then a hash join *may* be faster and more efficient in an IO limited subsystem.

The question should be answered why the CBO feels that doing a hash join is sub-optimal compared to an NL join though. This is a matter of tracing the query using SQL*Trace and TK*Prof to find out the choices made.

In addition to this, look at using the FULL hint along with the USE_HASH hint.

But as part of your tuning, you should find out how big each table is, and how many rows from that table you need to read to achieve the results. We cannot help with that.

That will dictate any hinting required.
Re: Hints USE_HASH (merged) [message #396945 is a reply to message #396721] Wed, 08 April 2009 07:58 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Ok, well please provide a suggestion. I have already attached the explain plan, what suggestions can you give me from that? Let's forget about hints then, what suggestions do you have??



Anne
Re: Hints USE_HASH [message #399767 is a reply to message #395467] Thu, 23 April 2009 22:49 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I don't know if you need this anymore, but I like to do inline views with this syntax. It's more of an aesthetic thing than anything else, but I find it helps break up the queries and the just seem easier for me to deal with. If you hashes turn out to be necessary, you can treat each part as an autonomous unit.
WITH b AS (SELECT ag.award_id
                 ,gra.project_id
                 ,gra.task_id
                 ,gra.budget_version_id
                 ,SUM(nvl(bl.burdened_cost, 0)) budget_total
                 ,SUM(CASE
                          WHEN substr(r.NAME, 1, 10) IN ('Award Reve', 'F & A Cost') THEN nvl(bl.burdened_cost, 0)
                          ELSE 0
                      END) indirect_budget
                 ,SUM(CASE
                          WHEN substr(r.NAME, 1, 10) IN ('Award Reve', 'F & A Cost', 'Cost Share') THEN 0
                          ELSE nvl(bl.burdened_cost, 0)
                      END) direct_budget
                 ,SUM(CASE substr(r.NAME, 1, 10)
                          WHEN 'Cost Share' THEN nvl(bl.burdened_cost, 0)
                          ELSE 0
                      END) cs_budget
           FROM apps.gms_resource_assignments gra
           JOIN apps.gms_budget_versions bv ON bv.project_id = gra.project_id
                                           AND bv.budget_version_id = gra.budget_version_id
           JOIN apps.gms_budget_lines bl ON bl.resource_assignment_id = gra.resource_assignment_id
           JOIN apps.pa_resource_list_members rlm ON rlm.resource_list_member_id = gra.resource_list_member_id
           JOIN apps.pa_resources r ON r.resource_id = rlm.resource_id
           JOIN apps.gms_awards_all ag ON ag.award_id = bv.award_id
           WHERE gra.budget_version_id IN (SELECT MAX(bv.budget_version_id)
                                           FROM gms.gms_budget_versions bv
                                           WHERE bv.project_id = gra.project_id)
           GROUP BY ag.award_id, gra.project_id, gra.task_id, gra.budget_version_id)
   ,ct AS (SELECT /*+ USE_HASH(pct gmb gma)*/ -- You could do this if you think it will work, but the CBO tends to be pretty good at stuff like this
                  gma.award_id
                 ,pct.project_id
                 ,pct.task_id
                 ,SUM(nvl(pct.tot_cmt_burdened_cost, 0)) tot_commit
                 ,SUM(CASE WHEN pct.expenditure_category IN ('F & A Costs', 'Cost Share') THEN 0
                          ELSE nvl(pct.tot_cmt_burdened_cost, 0)
                      END) dir_commit
                 ,SUM(CASE pct.expenditure_category
                          WHEN 'F & A Costs' THEN nvl(pct.tot_cmt_burdened_cost, 0)
                          ELSE 0
                      END) ind_commit
                 ,SUM(CASE pct.expenditure_category
                          WHEN 'Cost Share' THEN nvl(pct.tot_cmt_burdened_cost, 0)
                          ELSE 0
                      END) cs_commit
           FROM apps.pa_commitment_txns pct
           JOIN apps.gms_awards_basic_v gmb ON gmb.project_id = pct.project_id
                                           AND gmb.task_id = pct.task_id
           JOIN apps.gms_awards_all gma ON gma.award_id = gmb.award_id
           GROUP BY gma.award_id, pct.project_id, pct.task_id)
   ,e1 AS (SELECT gmsa.award_id
                 ,gmsac.project_id
                 ,gmsac.task_id
                 ,SUM(CASE
                          WHEN gmsac.expenditure_type LIKE 'IDC%' THEN 0
                          ELSE nvl(gmsac.burdened_cost, 0)
                      END) dir_exp
                 ,SUM(CASE 
                          WHEN et.expenditure_category LIKE 'F & A Costs%' THEN nvl(gmsac.burdened_cost, 0)
                          ELSE 0
                      END) ind_exp
                 ,SUM(CASE
                          WHEN regexp_like(et.expenditure_category, '^Cost Share(-(D|Ind)irect)*$')
                              OR et.expenditure_category = 'Research Enhancement' THEN nvl(gmsac.burdened_cost, 0)
                          ELSE 0
                      END) cs_exp
                 ,SUM(CASE substr(et.expenditure_category, 1, 16)
                          WHEN regexp_like(et.expenditure_category, '^Program (- )*Income$') THEN nvl(gmsac.burdened_cost, 0)
                          ELSE 0
                      END) program_income
                 ,SUM(nvl(gmsac.billed_amount, 0)) billed
                 ,SUM(nvl(gmsac.revenue_amount, 0)) rev
           FROM tams.tams_gms_status_actuals gmsac
           JOIN apps.gms_awards_all gmsa ON gmsa.award_id = gmsac.award_id
           LEFT JOIN apps.pa_expenditure_types et ON et.expenditure_type = gmsac.expenditure_type
           LEFT JOIN apps.pa_cost_distribution_lines_all cdl ON cdl.expenditure_item_id = gmsac.expenditure_item_id
                                                            AND cdl.project_id = gmsac.project_id
                                                            AND cdl.task_id = gmsac.task_id
                                                            AND cdl.line_num = '1'
           LEFT JOIN apps.gl_code_combinations gcc ON gcc.code_combination_id = cdl.dr_code_combination_id
           WHERE gmsac.pa_date BETWEEN to_date(substr('##1', 1, 10), 'YYYY/MM/DD') AND to_date(substr('##2', 1, 10), 'YYYY/MM/DD')
           AND gcc.segment3 BETWEEN nvl('##7', gcc.segment3) AND nvl('##8', gcc.segment3)
           GROUP BY gmsa.award_id, gmsac.project_id, gmsac.task_id)
     ,e AS (SELECT e1.*
                  ,dir_exp + ind_exp + cs_exp + program_income  total_exp
            FROM e1)
     ,f (SELECT gi.award_id
               ,project_id
               ,pfs.task_id
               ,SUM(pfs.funding_amount) funding
         FROM apps.gms_project_fundings pfs
         JOIN apps.gms_installments gi ON gi.installment_id = pfs.installment_id
         WHERE pfs.installment_id IN (SELECT MAX(g.installment_id)
                                      FROM apps.gms_installments g
                                      JOIN apps.gms_awards_all ga ON ga.award_id = g.award_id
                                      WHERE g.installment_id = pfs.installment_id)
         GROUP BY gi.award_id, pfs.project_id, pfs.task_id)
SELECT 'Project report ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8'
      ,0 sort_col
FROM dual
UNION
SELECT '##err_message'
      ,1
FROM dual
UNION
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^' ||
       'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^' ||
       'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^' ||
       'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^' ||
       'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^' ||
       'Program Income^Total Invoiced^Total Revenue^Total Funding^' ||
       'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget'
      ,2
FROM dual
UNION
SELECT /*+ USE_HASH(a p t ol dl o2 c b ct e f ppl)*/ --You can include as many as you will need
       DISTINCT
       p.segment1 || '^' || p.start_date || '^' || p.completion_date || '^' || p.project_status_code || '^' ||
       m.full_name || '^' ||
      --ra.FULL_NAME||'^'||
       t.task_number || '^' || t.start_date || '^' || t.completion_date || '^' || tm.full_name || '^' ||
       a.award_number || '^' || rtrim(translate(a.award_short_name, chr(10), ' ')) || '^' || o1.NAME || '^' ||
       o2.NAME || '^' || 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.customer_name || '^' || a.TYPE || '^' || a.award_purpose_code || '^' || a.attribute10 || '^' ||
       i.ind_rate_sch_name || '^' || e.total_exp || '^' || e.dir_exp || '^' || e.ind_exp || '^' || e.cs_exp || '^' ||
       ct.tot_commit || '^' || ct.dir_commit || '^' || ct.ind_commit || '^' || ct.cs_commit || '^' ||
       e.program_income || '^' || e.billed || '^' || e.rev || '^' || f.funding || '^' || b.budget_total || '^' ||
       b.direct_budget || '^' || b.indirect_budget || '^' || b.cs_budget
      ,3
FROM apps.gms_awards_all a
JOIN apps.pa_projects_all p ON p.project_type = 'Sponsored'
                           AND p.segment1 BETWEEN nvl('##5', p.segment1) AND nvl('##6', p.segment1)
JOIN apps.pa_tasks 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 JOIN b ON b.project_id = t.project_id
           AND b.task_id = t.task_id
           AND b.award_id = a.award_id
LEFT JOIN ct ON ct.project_id = t.project_id
            AND ct.task_id = t.task_id
            AND ct.award_id = a.award_id
LEFT JOIN  e ON e.project_id = t.project_id
            AND e.task_id = t.task_id
            AND e.award_id = a.award_id
LEFT JOIN f ON f.project_id = t.project_id
           AND f.task_id = t.task_id
           AND f.award_id = a.award_id
LEFT JOIN apps.pa_project_parties pp1 ON pp1.project_id = p.project_id
                                           AND pp1.project_role_id = 1
                                           AND pp1.start_date_active <= SYSDATE
                                           AND nvl(pp1.end_date_active, SYSDATE) >= SYSDATE
LEFT JOIN apps.per_people_f m ON m.person_id = pp1.resource_source_id
JOIN apps.pa_project_parties pp2 ON pp2.project_id = p.project_id
                                AND pp2.project_role_id = 1004
                                AND pp2.start_date_active <= SYSDATE
                                AND nvl(pp2.end_date_active, SYSDATE) >= SYSDATE
LEFT JOIN apps.gms_personnel gp ON gp.award_role = 'AM'
                               AND gp.award_id = a.award_id
JOIN apps.per_people_f fp ON fp.person_id = gp.person_id
JOIN apps.per_people_f tm ON tm.person_id = t.task_manager_person_id
LEFT JOIN apps.pa_project_classes c1 ON c1.project_id = p.project_id
                                    AND c1.class_category = 'Expense Code'
LEFT JOIN apps.pa_project_classes c2 ON c2.project_id = p.project_id
                                    AND c2.class_category = 'OMB A-21'
LEFT JOIN apps.pa_project_classes c3 ON c3.project_id = p.project_id
                                    AND c3.class_category = 'Revenue Line'
LEFT JOIN apps.pa_project_classes c4 ON c4.project_id = p.project_id
                                    AND c4.class_category = 'Burden Rate'
LEFT JOIN apps.pa_project_classes c5 ON c5.project_id = p.project_id
                                    AND c5.class_category = 'Burden Structure'
LEFT JOIN apps.pa_project_classes c6 ON c6.project_id = p.project_id
                                    AND c6.class_category = 'Site'
LEFT JOIN apps.pa_project_classes c7 ON c7.project_id = p.project_id
                                    AND c7.class_category = 'Sponsor'
LEFT JOIN apps.pa_project_classes c8 ON c8.project_id = p.project_id
                                    AND c8.class_category = 'Type'
LEFT JOIN apps.pa_ind_rate_schedules_all_bg i ON i.ind_rate_sch_id = a.idc_schedule_id
ORDER BY 2;
Re: Hints USE_HASH [message #400308 is a reply to message #399767] Mon, 27 April 2009 08:28 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Thanks for the suggestion Scott!!!!

Anne
Previous Topic: SQL PERFORMANCE ANALYZER
Next Topic: DBWR writes entire dirty block ?
Goto Forum:
  


Current Time: Thu Dec 08 22:06:16 CST 2016

Total time taken to generate the page: 0.24705 seconds