Advertisement

12.01.2008 at 09:11AM PST, ID: 23947269 | Points: 500
[x]
Attachment Details

Optimize my Query

Asked by Venkatkp007 in Databases Miscellaneous, Oracle Database

Tags:

Can you please help us on Optimizing this Query i have put the Query in the Code section. We don't have access to the admin capabilities but we keep hearing that the following query is sucking the resources and the utilization rate is very high at all times.

Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
SELECT  
 DISTINCT 
 pdeep_code, pdeep_value, pdeep_uom, pdeep_status_code,
      pdeep_status_desc, pnum_value, permission_type_name, maker_product_name,
      adeep_value, adeep_uom, adeep_status_code, adeep_status_desc,
       char_num_value, interface_id, interface_name, preferred_flag,
       pdeep_lead_time, permission_lead_time, PEC_lead_time, PEC_lead_time_status,
       permission_lead_time_status, onnet_lead_time, onnet_lead_time_status,
        a.permission_deep_char_id, a.permission_maker_char_id,
       a.permission_product_type_id, a.permission_maker_name_id, a.pnum_value
    FROM  vw_pps_withpermission a,
	 psu_product p1, psu_cases a2, 
         psu_ref_subproduct sp
    WHERE a2.subproduct_cd = sp.subproduct_cd (+)
	 AND a2.product_cd = p1.product_cd
	 --ADDED TO TAKE CARE OF FULL SCAN , if you take off this join,cost increases significantly.
	 AND A.CASE_ID=A2.CASE_ID
	 --ADDITION ENDS
      AND p1.product_valid_cd = 1
      AND parent_product_id = p1.parent_product_cd
    
      AND a2.case_valid_cd = 1
	   AND a.case_id = :b2
      AND a.permission_maker_char_id = :b1
     
      AND NOT EXISTS (SELECT 'X'
                      FROM psu_parent_ship_permission_deeps
                      WHERE case_id = a2.case_id
                        AND ship_deep_char_id = a.ship_deep_char_id
                        AND permission_deep_char_id = a.permission_deep_char_id
                        AND permission_maker_char_id = a.permission_maker_char_id
                        AND permission_product_type_id = a.permission_product_type_id
                        AND permission_maker_name_id = a.permission_maker_name_id
                        AND valid_cd = 0)
     
    ORDER BY a.pnum_value ASC
 
Loading Advertisement...
 
[+][-]12.01.2008 at 09:23AM PST, ID: 23070461

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2008 at 09:23AM PST, ID: 23070462

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2008 at 09:53AM PST, ID: 23070695

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]12.01.2008 at 09:56AM PST, ID: 23070719

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2008 at 10:45AM PST, ID: 23071159

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]12.01.2008 at 11:00AM PST, ID: 23071313

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.10.2008 at 11:14PM PST, ID: 23146338

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081119-EE-VQP-49 - Hierarchy / EE_QW_3_20080625