Home » RDBMS Server » Performance Tuning » How tune the following query? (Oracle 9i)
How tune the following query? [message #569235] Tue, 23 October 2012 06:34 Go to next message
kumar_hi21@yahoo.com
Messages: 6
Registered: August 2012
Location: chennai
Junior Member
Dear Experts,

I am very much new to SQL tuning . Here i have shown my and explain plan




SELECT ei.ROWID row_id, 'PROJECT' search_criteria, ei.expenditure_item_id,
          SUBSTR (gcc.segment2, 1, 6) ACCOUNT,
          SUBSTR (gcc.segment5, 1, 10) local_account,
          SUBSTR (gcc.segment1, 1, 3) le, SUBSTR (gcc.segment3, 1, 5) pu,
          SUBSTR (gcc.segment4, 1, 3) dept, ei.expenditure_id, p.project_id,
          p.segment1 project_number, p.NAME project_name, pt.project_type,
          pt.project_type_class_code, ei.task_id, t.task_number,
          NVL (SUBSTR (t.task_number, 1, INSTR (t.task_number, '*', 1) - 1),
               t.task_number
              ) work_stage,
          DECODE (INSTR (t.task_number, '*', 1),
                  0, '',
                  SUBSTR (t.task_number, INSTR (t.task_number, '*', 1) + 1)
                 ) activity_code,
          t.task_name, ei.expenditure_item_date, ei.expenditure_type,
          et.expenditure_category, et.revenue_category_code,
          x.incurred_by_person_id, emp.full_name employee_name,
                            /*DECODE (p.org_id,
                            '103', DECODE (emp.attribute7,
                                           NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                        ),
                            '107', DECODE (emp.attribute7,
                                           NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                          ),
                            '2201', DECODE (emp.attribute7,
                                            NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                           ),
                            emp.employee_number
                           ) employee_number,*/
          DECODE (p.org_id,
                  '103', NVL (DECODE (emp.attribute7,
                                      NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                      emp.attribute7
                                     ),
                              emp.employee_number
                             ),
                  '107', NVL (DECODE (emp.attribute7,
                                      NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                      emp.attribute7
                                     ),
                              emp.employee_number
                             ),
                  '2201', NVL (DECODE (emp.attribute7,
                                       NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                       emp.attribute7
                                      ),
                               emp.employee_number
                              ),
                  emp.employee_number
                 ) employee_number,                  ---- Ghansham 24-Nov-2009
          emp.attribute7 den_number, ei.job_id, j.NAME job_name,
          x.incurred_by_organization_id, ei.override_to_organization_id,
          NVL (ei.override_to_organization_id,
               x.incurred_by_organization_id
              ) expenditure_organization_id,
          o1.NAME expenditure_organization_name, ei.non_labor_resource,
          ei.organization_id nlr_organization_id,
          o2.NAME nlr_organization_name, ei.system_linkage_function,
          c.expenditure_comment,
          ei.transaction_source || '' transaction_source,
          ei.orig_transaction_reference, x.expenditure_group,
          x.expenditure_status_code, x.expenditure_ending_date, cdl.quantity,
          et.unit_of_measure,
          (SELECT l2.meaning
             FROM pa_lookups l2
            WHERE 1 = 1
              AND l2.lookup_type = 'UNIT'
              AND l2.lookup_code = et.unit_of_measure
              AND ROWNUM = 1) unit_of_measure_m,
          DECODE (ei.system_linkage_function,
                  'ST', ei.raw_cost_rate,
                  'OT', ei.raw_cost_rate,
                  ei.raw_cost_rate
                 ) raw_cost_rate,
          ei.cost_distributed_flag, ei.cost_dist_rejection_code,
          ei.labor_cost_multiplier_name,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  cdl.amount
                 ) burdened_cost,
          DECODE (ei.system_linkage_function,
                  'ST', ei.burden_cost_rate,
                  'OT', ei.burden_cost_rate,
                  ei.burden_cost_rate
                 ) burdened_cost_rate,
          ei.cost_burden_distributed_flag, ei.ind_cost_dist_rejection_code,
          ei.cost_ind_compiled_set_id,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', ei.billable_flag,
                  CAST (NULL AS VARCHAR2 (10))
                 ) capitalizable_flag,
          ei.accrued_revenue, ei.accrual_rate, ei.raw_revenue,
          ei.forecast_revenue,
          SIGN (cdl.amount) * ABS (ei.bill_amount) bill_amount, ei.bill_rate,
          ei.bill_rate_multiplier, ei.adjusted_revenue, ei.adjusted_rate,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
                  cdl.billable_flag
                 ) billable_flag,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', ei.revenue_distributed_flag,
                  CAST (NULL AS VARCHAR2 (10))
                 ) grouped_cip_flag,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
                  ei.revenue_distributed_flag
                 ) revenue_distributed_flag,
          ei.bill_hold_flag, ei.rev_dist_rejection_code, ei.bill_job_id,
          ei.bill_job_billing_title, ei.bill_employee_billing_title,
          ei.rev_ind_compiled_set_id, ei.inv_ind_compiled_set_id,
          ei.event_num, ei.event_task_id, ei.adjusted_expenditure_item_id,
          NVL (ei.net_zero_adjustment_flag, 'N') net_zero_adjustment_flag,
          ei.transferred_from_exp_item_id, ei.converted_flag,
          ei.source_expenditure_item_id, ei.last_update_login,
          ei.last_update_date, ei.last_updated_by, ei.creation_date,
          ei.created_by, ei.attribute_category, ei.attribute1, ei.attribute2,
          ei.attribute3, ei.attribute4, ei.attribute5, ei.attribute6,
          ei.attribute7, ei.attribute8, ei.attribute9, ei.attribute10,
          DECODE (ei.org_id,
                  '1131', SUBSTR (ei.attribute8,
                                  (INSTR (ei.attribute8, '[', 1, 5)
                                  ) + 1,
                                  ((  INSTR (ei.attribute8, ']', 1, 5)
                                    - 1
                                    - (INSTR (ei.attribute8, '[', 1, 5))
                                   )
                                  )
                                 ),
                  TO_CHAR (CAST (NULL AS VARCHAR2 (10)))
                 ),
          ei.org_id, ei.org_id expenditure_org_id,
          tr.user_transaction_source || '' user_transaction_source,
          tr.allow_adjustments_flag, cast('' as varchar2(1)) transferred_item_flag,
          cdl.line_num cdl_line_num,
          cdl.transfer_status_code cdl_xfr_status_code,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.amount,
                  (SIGN (cdl.amount) * ABS (ei.raw_cost))
                 ) raw_cost,
          cdl.function_transaction_code cdl_func_txn_code,
          cdl.pa_date + 0 cdl_pa_date, cdl.dr_code_combination_id cdl_dr_ccid,
          cdl.gl_date + 0 cdl_gl_date, cdl.accumulated_flag cdl_accum_flag,
          cdl.reversed_flag cdl_reversed_flag,
          cdl.line_num_reversed cdl_line_num_rev,
          NVL (cdl.system_reference1, 0) cdl_sys_ref1,
          cdl.system_reference2 cdl_sys_ref2,
          cdl.system_reference3 cdl_sys_ref3,
          cdl.cr_code_combination_id cdl_cr_ccid, cdl.line_type cdl_line_type,
          cdl.code_combination_id cdl_ccid, et.attribute8 aorl_flag,
          (SELECT hp.party_name
             FROM pa_project_customers ppc,
                  hz_cust_accounts hca,
                  hz_parties hp
            WHERE ppc.project_id = p.project_id
              AND ppc.customer_bill_split = 100
              AND hca.cust_account_id = ppc.customer_id
              AND hp.party_id = hca.party_id
              AND ROWNUM = 1) client,
          (SELECT hca.account_number
             FROM pa_project_customers ppc,
                  hz_cust_accounts hca
            WHERE ppc.project_id = p.project_id
              AND ppc.customer_bill_split = 100
              AND hca.cust_account_id = ppc.customer_id
              AND ROWNUM = 1) client_number,
          ei.project_id event_project_id,
          SUBSTR (ei.attribute2, 7, 4) office_site,
          SUBSTR (ei.attribute2, 11, 3) hovensa_mrn,
          SUBSTR (ei.attribute2, 14, 4) hovensa_crew,
          SUBSTR (ei.attribute2, 18, 2) hovensa_craft,
          SUBSTR (ei.attribute2, 20, 2) hovensa_class,
          SUBSTR (ei.attribute2, 1, 1) hovensa_shift,
          SUBSTR (emp.attribute6, 3, 8) hovensa_badge_number,
          SUBSTR (emp.attribute6, 1, 2) hovensa_frequency,
          pa_expenditures_utils.getorgtlname (ei.org_id) prvdr_org_name,
          pa_expenditures_utils.getorgtlname (ei.recvr_org_id) recvr_org_name,
          ei.receipt_currency_code, ei.receipt_exchange_rate,
          ei.receipt_currency_amount, ei.denom_currency_code,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.denom_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.denom_raw_cost)
                  )
                 ) denom_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.denom_burdened_cost)
                  )
                 ) denom_burdened_cost,
          ei.acct_exchange_rounding_limit, ei.acct_currency_code,
          ei.acct_rate_type, ei.acct_rate_date, ei.acct_exchange_rate,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.acct_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.acct_raw_cost)
                  )
                 ) acct_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.acct_burdened_cost)
                  )
                 ) acct_burdened_cost,
          ei.project_currency_code, ei.project_rate_type,
          ei.project_rate_date, ei.project_exchange_rate,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.project_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.project_raw_cost)
                  )
                 ) project_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.project_burdened_cost)
                  )
                 ) project_burdened_cost,
          ei.cc_prvdr_organization_id, ei.cc_recvr_organization_id,
          pa_expenditures_utils.getorgtlname
                      (ei.cc_prvdr_organization_id)
                                                   cc_prvdr_organization_name,
          pa_expenditures_utils.getorgtlname
                      (ei.cc_recvr_organization_id)
                                                   cc_recvr_organization_name,
          cc_cross_charge_code, cc_cross_charge_type, cc_rejection_code,
          jeg_get_lookup ('CC_CROSS_CHARGE_CODE',
                          cc_cross_charge_code
                         ) cc_cross_charge_code_m,
          jeg_get_lookup ('CC_CROSS_CHARGE_TYPE',
                          cc_cross_charge_type
                         ) cc_cross_charge_type_m,
          jeg_get_lookup ('CC_REJECTION_CODE',
                          cc_rejection_code
                         ) cc_rejection_code_m,
          fnd_profile.VALUE ('GL_SET_OF_BKS_ID') set_of_books_id,
          (SELECT ass_attribute3
             FROM per_all_assignments_f paa
            WHERE paa.person_id = emp.person_id
              AND ei.expenditure_item_date BETWEEN effective_start_date
                                               AND effective_end_date
              AND ROWNUM = 1) jeg_assigned_pu,
          (SELECT    SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from1,
                             (c1.l_to1 - c1.l_from1) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from2,
                             (c1.l_to2 - c1.l_from2) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from3,
                             (c1.l_to3 - c1.l_from3) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from4,
                             (c1.l_to4 - c1.l_from4) + 1
                            )
             FROM jeg_proj_mask_map c1
            WHERE p.org_id = 3225                                     -- CA OU
              AND c1.project_id = p.project_id
              AND ei.expenditure_item_date BETWEEN c1.start_date
                                               AND NVL (c1.end_date, SYSDATE)
              AND ROWNUM = 1) jeg_exp_mask,
          DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 11)),
                  'JOB SHOPPER', UPPER (SUBSTR (emp.last_name, 1, 4)),
                  DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 21)),
                          'SHIFT DIFFERENTIAL JS', UPPER
                                                       (SUBSTR (emp.last_name,
                                                                1,
                                                                4
                                                               )
                                                       ),
                             INITCAP (SUBSTR (emp.last_name, 1, 2))
                          || SUBSTR (emp.first_name, 1, 1)
                         )
                 ) mailstop,
          jeg_get_ar_inv (p.project_id, ei.expenditure_item_id) ar_inv_num,
          ---- Ghansham 21-JUL-2008 added foe GCC
          jeg_check_service_centre_cc (ei.expenditure_item_id) service_center,
          ---- Ghansham 21-JUL-2008 added foe GCC
          ei.acct_transfer_price       ---- Ghansham 21-JUL-2008 added foe GCC
     FROM pa_projects_all p,
          pa_cost_distribution_lines_all cdl,
          pa_expenditure_items_all ei,
          pa_tasks t,
          pa_project_types_all pt,
          pa_expenditures_all x,
          hr_all_organization_units o1,
          hr_all_organization_units o2,
          per_jobs j,
          pa_expenditure_types et,
          gl_code_combinations gcc,
          pa_expenditure_comments c,
          pa_transaction_sources tr,
          per_all_people_f emp
    WHERE 1 = 1
      AND cdl.project_id = p.project_id
      AND ei.expenditure_item_id = cdl.expenditure_item_id
--AND    ei.expenditure_item_date BETWEEN TO_DATE('01-DEC-2007') AND TO_DATE('14-DEC-2007')
      AND cdl.line_type || '' =
                           DECODE (ei.system_linkage_function,
                                   'VI', 'R',
                                   'D'
                                  )
      AND (   ei.cc_cross_charge_type || '' = 'IC'
           OR ei.org_id = fnd_profile.VALUE ('ORG_ID')
          )
      AND t.task_id = ei.task_id
      AND pt.project_type = p.project_type
      AND pt.org_id = p.org_id
      AND x.expenditure_id = ei.expenditure_id
      AND o1.organization_id =
             NVL (ei.override_to_organization_id,
                  x.incurred_by_organization_id
                 )
      AND ei.organization_id = o2.organization_id(+)
      AND ei.job_id = j.job_id(+)
      AND ei.expenditure_type = et.expenditure_type
      AND et.unit_of_measure IN
                          ('DOLLARS', 'HOURS', 'MILEAGE', 'Plots', 'Samples')
      AND gcc.code_combination_id = cdl.dr_code_combination_id
      AND ei.expenditure_item_id = c.expenditure_item_id(+)
      AND ei.transaction_source = tr.transaction_source(+)
      AND x.incurred_by_person_id = emp.person_id(+)
      AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
      AND emp.employee_number(+) IS NOT NULL;


and explain plan


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21685820 Card=447922
          39 Bytes=46539136321)

   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'FND_LOOKUP_VALUES' (Co
          st=4 Card=1 Bytes=57)

   3    2       INDEX (RANGE SCAN) OF 'FND_LOOKUP_VALUES_U1' (UNIQUE)
          (Cost=3 Card=1)

   4    0   COUNT (STOPKEY)
   5    4     NESTED LOOPS (Cost=7 Card=1 Bytes=56)
   6    5       NESTED LOOPS (Cost=5 Card=1 Bytes=24)
   7    6         TABLE ACCESS (BY INDEX ROWID) OF 'PA_PROJECT_CUSTOME
          RS' (Cost=4 Card=1 Bytes=13)

   8    7           INDEX (RANGE SCAN) OF 'PA_PROJECT_CUSTOMERS_U1' (U
          NIQUE) (Cost=3 Card=1)

   9    6         TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS'
          (Cost=1 Card=1 Bytes=11)

  10    9           INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (UNIQ
          UE)

  11    5       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_PARTIES' (Cost=2
          Card=1 Bytes=32)

  12   11         INDEX (UNIQUE SCAN) OF 'HZ_PARTIES_U1' (UNIQUE) (Cos
          t=1 Card=1)

  13    0   COUNT (STOPKEY)
  14   13     NESTED LOOPS (Cost=5 Card=1 Bytes=24)
  15   14       TABLE ACCESS (BY INDEX ROWID) OF 'PA_PROJECT_CUSTOMERS
          ' (Cost=4 Card=1 Bytes=13)

  16   15         INDEX (RANGE SCAN) OF 'PA_PROJECT_CUSTOMERS_U1' (UNI
          QUE) (Cost=3 Card=1)

  17   14       TABLE ACCESS (BY INDEX ROWID) OF 'HZ_CUST_ACCOUNTS' (C
          ost=1 Card=1 Bytes=11)

  18   17         INDEX (UNIQUE SCAN) OF 'HZ_CUST_ACCOUNTS_U1' (UNIQUE
          )

  19    0   COUNT (STOPKEY)
  20   19     TABLE ACCESS (BY INDEX ROWID) OF 'PER_ALL_ASSIGNMENTS_F'
           (Cost=7 Card=1 Bytes=27)

  21   20       INDEX (RANGE SCAN) OF 'PER_ASSIGNMENTS_F_N12' (NON-UNI
          QUE) (Cost=3 Card=4)

  22    0   COUNT (STOPKEY)
  23   22     FILTER
  24   23       TABLE ACCESS (BY INDEX ROWID) OF 'JEG_PROJ_MASK_MAP' (
          Cost=3 Card=1 Bytes=31)

  25   24         INDEX (RANGE SCAN) OF 'JEG_PROJ_MASK_MAP_N1' (NON-UN
          IQUE) (Cost=2 Card=1)

  26    0   HASH JOIN (Cost=21685820 Card=44792239 Bytes=46539136321)
  27   26     TABLE ACCESS (FULL) OF 'HR_ALL_ORGANIZATION_UNITS' (Cost
          =8 Card=2291 Bytes=29783)

  28   26     HASH JOIN (Cost=21666496 Card=44792239 Bytes=45956837214
          )

  29   28       TABLE ACCESS (FULL) OF 'PA_PROJECT_TYPES_ALL' (Cost=2
          Card=138 Bytes=3312)

  30   28       HASH JOIN (Cost=21663798 Card=44792239 Bytes=448818234
          78)

  31   30         TABLE ACCESS (FULL) OF 'PA_PROJECTS_ALL' (Cost=3246
          Card=265730 Bytes=11957850)

  32   30         HASH JOIN (Cost=21005440 Card=44483842 Bytes=4257103
          6794)

  33   32           TABLE ACCESS (FULL) OF 'GL_CODE_COMBINATIONS' (Cos
          t=1773 Card=688770 Bytes=21351870)

  34   32           HASH JOIN (OUTER) (Cost=20373686 Card=44175128 Byt
          es=40906168528)

  35   34             HASH JOIN (OUTER) (Cost=19509061 Card=44175128 B
          ytes=39536739560)

  36   35               HASH JOIN (Cost=18943246 Card=44175128 Bytes=3
          6046904448)

  37   36                 TABLE ACCESS (FULL) OF 'PA_TASKS' (Cost=9738
          2 Card=18021390 Bytes=360427800)

  38   36                 HASH JOIN (Cost=18294927 Card=44166353 Bytes
          =35156416988)

  39   38                   TABLE ACCESS (FULL) OF 'PA_EXPENDITURES_AL
          L' (Cost=84808 Card=28594800 Bytes=1401145200)

  40   38                   HASH JOIN (OUTER) (Cost=17413038 Card=4409
          9473 Bytes=32942306331)

  41   40                     HASH JOIN (OUTER) (Cost=16913198 Card=44
          099473 Bytes=32369013182)

  42   41                       HASH JOIN (OUTER) (Cost=16420291 Card=
          44099473 Bytes=31883918979)

  43   42                         HASH JOIN (Cost=15948611 Card=440994
          73 Bytes=30516835316)

  44   43                           TABLE ACCESS (FULL) OF 'PA_EXPENDI
          TURE_TYPES' (Cost=7 Card=1290 Bytes=81270)

  45   43                           HASH JOIN (Cost=15928472 Card=4409
          9473 Bytes=27738568517)

  46   45                             TABLE ACCESS (FULL) OF 'PA_EXPEN
          DITURE_ITEMS_ALL' (Cost=1680109 Card=51616333 Bytes=26530795
          162)

  47   45                             TABLE ACCESS (FULL) OF 'PA_COST_
          DISTRIBUTION_LINES_ALL' (Cost=4105845 Card=309305110 Bytes=3
          5570087650)

  48   42                         TABLE ACCESS (FULL) OF 'PA_TRANSACTI
          ON_SOURCES' (Cost=3 Card=107 Bytes=3317)

  49   41                       TABLE ACCESS (FULL) OF 'PER_JOBS' (Cos
          t=125 Card=49710 Bytes=546810)

  50   40                     TABLE ACCESS (FULL) OF 'HR_ALL_ORGANIZAT
          ION_UNITS' (Cost=8 Card=2291 Bytes=29783)

  51   35               TABLE ACCESS (FULL) OF 'PER_ALL_PEOPLE_F' (Cos
          t=1770 Card=276740 Bytes=21862460)

  52   34             TABLE ACCESS (FULL) OF 'PA_EXPENDITURE_COMMENTS'
           (Cost=21998 Card=15029290 Bytes=465907990)




This process can take more than 6 hours,How can i reduce the running time ...
Kindly advice me...

Thanks in advance....
Re: How tune the following query? [message #569237 is a reply to message #569235] Tue, 23 October 2012 07:09 Go to previous messageGo to next message
BlackSwan
Messages: 22711
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
Re: How tune the following query? [message #569575 is a reply to message #569235] Mon, 29 October 2012 22:28 Go to previous messageGo to next message
Kevin Meade
Messages: 1936
Registered: December 1999
Location: Connecticut USA
Senior Member
SQLTRACE and TKPROF may be more correct, but they are also a pain in the neck for many of us to generate.

What version of Oracle are you running? I assume 9/10/11? A simpler more practical approach is to use DBMS_XPLAN to generate your plan. You need more than just the query executpion plan output. You also need the other plan data, in particular the PREDICATE INFORMATION section.

Tuning this query will take several hours to a couple of days. Are you willing to invest this time? Here are some things you can do if you are serious:

) create a query diagram so you can see how the tables are joined visually (1/2 hour to 3 hours depends upon if you use pen an paper or some drawing tool)
) create/run COUNT QUERIES and FILTER QUERIES to determine drivng table and join order based on intial row filtering and compare to current plan order (one hour + runtime)
) create/run RECONSTRUCTION QUERIES to validate cardinality of various steps (three to four hours maybe more if this is your first time + runtime)
) check predicate information for FILTER PREDICATE LISTS that show inefficient fetching or joining and determine if it is because of problem's nature or wasted effort because of physical data model
) consider if NESTED LOOPS JOIN is being done where HASH JOIN should be done
) detemermine optimum set of indexes (indexes may not be the right solution for all steps) using query diagram/driving table/join order/and other info learned
) apply what you have learned and try again (check the plan, check the runtime stats)

Once you know the plan you want, make Oracle give it to you using / the Basics (constraints,datatypes,design) / Indexes / Statistics / Rewrites / <only if necessary> Hints and Plan Stability.

Divide and Conquer. You need to figure out where your time is going in this query so you know what to tune.

Good luck, Kevin

[Updated on: Mon, 29 October 2012 22:30]

Report message to a moderator

Re: How tune the following query? [message #569576 is a reply to message #569575] Mon, 29 October 2012 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 22711
Registered: January 2009
Senior Member
below is even more simple, IMO

SQL> set autotrace trace explain
SQL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
Re: How tune the following query? [message #569578 is a reply to message #569576] Mon, 29 October 2012 23:05 Go to previous message
Kevin Meade
Messages: 1936
Registered: December 1999
Location: Connecticut USA
Senior Member
Plan is only half useful without predicate information. Stole this from TOM. Was the first one I saw that had predicate info.

ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual where dummy in ( 'X', 'Y' );
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DUAL"."DUMMY"='X' OR "DUAL"."DUMMY"='Y')
 
Note: rule based optimization
 
14 rows selected.
Previous Topic: How Oracle optimizer choose joins (hash, Merge & nested loop join)
Next Topic: [11g]Direct-path insert / temp usage / PGA
Goto Forum:
  


Current Time: Wed Aug 27 20:16:44 CDT 2014

Total time taken to generate the page: 0.09804 seconds