Home » SQL & PL/SQL » SQL & PL/SQL » Need to Tune this query
Need to Tune this query [message #242305] Fri, 01 June 2007 09:41 Go to next message
Messages: 2
Registered: November 2005
Junior Member
Hi All ,
I need to tune this query . It takes forever to run. Could yo please give me some advise to tune it .
select /*+ RULE */
gcc.code_combination_id ,
acc_value.flex_value acc_value,
substr(acc_desc.description,1,50) acc_desc,
channel_value.flex_value channel_value ,
gcc.segment1 segment1 ,
substr(channel_desc.description,1,50) channel_desc ,
product_value.flex_value product_value ,
substr(product_desc.description,1,50) product_desc,
substr(le_desc.description,1,50) le_desc,
gl_code_combinations gcc,
fnd_flex_values_tl acc_desc,
fnd_flex_values acc_value,
fnd_flex_values_tl channel_desc,
fnd_flex_values channel_value,
fnd_flex_values channel_parent,
fnd_flex_values_tl product_desc,
fnd_flex_values product_value,
fnd_flex_values_tl le_desc,
fnd_flex_values le_value,
fnd_flex_value_hierarchies acc_p,
fnd_flex_value_hierarchies cc_p,
fnd_flex_value_hierarchies prod_p,
fnd_flex_value_hierarchies channel_p
gcc.segment2 between acc_p.child_flex_value_low and acc_p.child_flex_value_high
and acc_p.flex_value_set_id=acc_value.flex_value_set_id
and acc_p.parent_flex_value=acc_value.flex_value
and acc_value.flex_value_set_id = 1007615
and acc_value.attribute1 = 'Y'
and acc_value.flex_value_id = acc_desc.flex_value_id
and cc_p.flex_value_set_id = 1007616
and cc_p.parent_flex_value = acc_value.attribute3
and gcc.segment3 between cc_p.child_flex_value_low and cc_p.child_flex_value_high
and channel_parent.flex_value = p_channel
and channel_parent.flex_value = channel_p.parent_flex_value
and channel_parent.flex_value_set_id = channel_value.flex_value_set_id
and channel_value.flex_value_id = channel_desc.flex_value_id
and channel_p.flex_value_set_id=channel_parent.flex_value_set_id
and channel_parent.flex_value_set_id = 1007618
and gcc.segment5 between channel_p.child_flex_value_low and channel_p.child_flex_value_high
and gcc.segment5 = channel_value.flex_value
and product_value.flex_value_set_id = 1007617
and product_value.attribute1 = 'Y' and product_value.flex_value='G220'
and gcc.segment4 between prod_p.child_flex_value_low and prod_p.child_flex_value_high
and prod_p.flex_value_set_id=product_value.flex_value_set_id
and prod_p.parent_flex_value=product_value.flex_value
and product_value.flex_value_id = product_desc.flex_value_id
and le_value.flex_value_set_id = 1007614
and le_value.flex_value = gcc.segment1 and segment1='CAG1100'
and le_value.flex_value_id = le_desc.flex_value_id
and le_desc.language = 'US'
and acc_desc.language = 'US'
and channel_desc.language = 'US'
and product_desc.language = 'US';
Re: Need to Tune this query [message #242306 is a reply to message #242305] Fri, 01 June 2007 09:43 Go to previous messageGo to next message
Messages: 24960
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the posting guidelines as stated in the #1 STICKY post at the top of this forum.

[Updated on: Fri, 01 June 2007 09:44] by Moderator

Report message to a moderator

Re: Need to Tune this query [message #242307 is a reply to message #242305] Fri, 01 June 2007 09:45 Go to previous messageGo to next message
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Step one : Delete the "RULE" Hint
Step two : look at the execution plan of the statement
Step three : Trace the statement and analyze it with tkprof

If you have that information you either know what is wrong, or you can post the execution plan / tkprof output here.
Re: Need to Tune this query [message #242328 is a reply to message #242307] Fri, 01 June 2007 10:43 Go to previous message
Michel Cadot
Messages: 63915
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Step 1b: gather statistics on all relevants objects and dependencies (indexes).

Previous Topic: DUAL TABLE
Next Topic: Data in to single row (take your time I'm in no rush)
Goto Forum:

Current Time: Mon Oct 24 09:56:03 CDT 2016

Total time taken to generate the page: 0.08134 seconds