Using Outline for RULE Based optimizer system

From: Tanmoy <tanmoydc_at_gmail.com>
Date: Sun, 24 Jul 2005 03:26:03 -0400
Message-ID: <cbb60e8605072400261442710c@mail.gmail.com>

Hi,

  I have an oracle HRMS system running on 11o and the database version is 8.1.7. The optimizer mode is RULE.

To tune couple of long running queries i was thinking of using stored outlines. However the queries i am goining to be tuned uses bind variable.

Such cases how to use the outline ?

<Original Query>

select pev.screen_entry_value amount,

         ncr.add_or_subtract add_or_subtract,
         TO_DATE(pev1.screen_entry_value, 'DD-MON-YYYY') effective_date

  from pay_accrual_plans pap,
    pay_net_calculation_rules ncr,
    pay_element_entry_values_f pev,

         pay_element_entry_values_f pev1,     pay_element_entries_f pee

  where  pap.accrual_plan_id = p_plan_id
  and    pee.assignment_id = p_assignment_id
  and    pee.element_entry_id = pev.element_entry_id
  and    pev.input_value_id+0 = ncr.input_value_id
  and    pap.accrual_plan_id = ncr.accrual_plan_id
  and    ncr.input_value_id <> pap.co_input_value_id
  and    ncr.input_value_id <> pap.pto_input_value_id

  and pev1.element_entry_id = pee.element_entry_id   and pev1.input_value_id+0 = ncr.date_input_value_id

  and    ((p_input_value_id is NOT NULL and
           p_input_value_id = ncr.input_value_id) or
           p_input_value_id is NULL);

<Outline Query Should be >

I want to use /*+ CHOOSE */ hint to the above query. How to overcome's the parameter (p_input_value_id ,p_plan_id,p_assignment_id) in this case ?

I also heard that queries having OR condition has some caveats in 8.1.7.

Does the outline query have to be verbatim with the original one?

I did go through metalink, and dbazine artile by Jonathan Lewis. But did'nt find any direct answer.

Is there any step by step guide for outline available anywhere ?

-- 
Thanks,
Tanmoy 

-- "Time is the coin of life. Only you can determine how it will be spent."
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jul 24 2005 - 02:27:56 CDT