Home » SQL & PL/SQL » SQL & PL/SQL » Perfomance tunning (Unix)
Perfomance tunning [message #417731] Tue, 11 August 2009 05:14 Go to next message
vikasarorait
Messages: 9
Registered: August 2009
Junior Member
I need help to tune this query.

SELECT /*+ leading(qha) use_hash(fuser) use_hash(fuser2) use_hash(qs2) use_hash(qs) use_hash(pcc) use_hash(th) */ :request_id, qha.quote_header_id quote_header_id,
       qha.quote_number quote_number, sa.contract_number contract_number,
       qqla.erp_order_number qtc_so_number, qhs.sales_region sales_region,
       qha.quote_name quote_name, ocav.account_number customer_number,
       ocav.customer_name invoice_to_customer_name, ocav.country country,
       th.theater theater, 'Not Retrieved' end_user_customer_number,
       'Not Retrieved' end_user_customer_name,
       qha.total_quote_price qha_total_quote_price,
       qha.currency_code currency,
       qha.total_quote_price_usd qha_total_quote_price_usd,
       qoc.po_number po_number, qoc.po_amount po_amount,
       qoc.total_quote_amount total_quote_amount,
       qha.shell_quote_flag shell_quote,
       qha.non_standard_quote_flag non_std_quote,
       qha.creation_date quote_create_date,
       qha.last_update_date quote_last_update,
       DECODE (qs.quote_status_id,
               10034, ROUND (qha.last_update_date - qoc.creation_date),
               ROUND (SYSDATE - qha.last_update_date)
              ) age,
       qs.status_code it_status, qs.int_user_desc internal_user_status,
       qs.ext_user_desc external_user_status, qoc.email_address email_address,
       qha.quote_expiration_date quote_expire_update,
       qha.solcat_case_number solcat_case_number,
       fuser.user_name quote_created_by,
       fuser2.user_name quote_last_updated_by,
       qoc.order_submitted_by order_submitted_by,
       qoc.creation_date order_start_create_date,
       qoc.order_ctrl_id order_ctrl_id, qqla.send_to_erp_flag sent_to_erp,
       qqla.ec_submission_date ec_submission_date,
       qoc.exp_comp_sub_date export_compliance_submit_date,
       qs2.int_user_desc export_compliance_status,
       qqla.csm_hold_date csm_hold_date,
       TO_CHAR (qqla.csm_release_date,
                'MM/DD/YYYY HH:MI PM'
               ) csm_hold_release_date,
       qqla.erp_order_booked_date erp_order_booked_date,
       (SELECT MAX (creation_date)
          FROM xxcss_qot_convert_ctrl ctrl
         WHERE ctrl.quote_header_id =
                                   qha.quote_header_id)
                                                       convert_con_start_date,
       (SELECT MAX (convert_ctrl_id)
          FROM xxcss_qot_convert_ctrl ctrl
         WHERE ctrl.quote_header_id = qha.quote_header_id) convert_ctrl_id,
       DECODE (SIGN (TO_NUMBER (qqha.deal_id)),
               -1, NULL,
               0, NULL,
               1, qqha.deal_id
              ) deal_id,
       qqha.ec_order_number ec_order_number,
       qqha.erp_instance dest_erp_instance,
       qqha.business_entity dest_erp_business_entity,
       pcc.color_code color_code, pcc.color_descr color_desc,
       qqha.source_application_name source_application_name,
       qqha.order_source cscc_order_source,
       sa.total_no_of_line_items num_of_lines, NULL status_color_suprt_hstry,
       'N' invoiced_flag, 'N' cmfs_flag
  FROM xxcss_o.xxcss_qot_order_ctrl qoc,
       xxcss_o.xxcss_qot_headers_all qha,
       xxcts_om_cust_addresses_v ocav,
       apps.xxcss_qot_statuses_v qs,
       apps.xxcss_qot_statuses_v qs2,
       fnd_user fuser,
       fnd_user fuser2,
       xxcss_o.xxcss_qot_header_search qhs,
       xxcss_o.xxcss_qot_q2o_headers_all qqha,
       xxcss_o.xxcss_qot_q2o_lines_all qqla,
       xxcss_pbm_country_theater_map th,
       xxcss_qot_summary sa,
       xxcss_pbm_color_coding pcc
 WHERE qoc.order_ctrl_id(+) = qha.order_ctrl_id
   AND qha.quote_header_id = sa.quote_header_id
   AND qs.quote_status_id = qha.quote_status_id
   AND qqha.order_ctrl_id(+) = qoc.order_ctrl_id
   AND qqla.q2o_header_id(+) = qqha.q2o_header_id
   AND qha.created_by = fuser.user_id
   AND qha.invoice_to_party_site_id = ocav.cust_acct_site_id
   AND ocav.country = th.country_code
   AND qha.last_updated_by = fuser2.user_id
   AND qha.quote_header_id = qhs.quote_header_id
   AND qs2.quote_status_id(+) = qha.exp_comp_status_id
   AND qs.status_code = pcc.status(+)
   AND ocav.site_use_code = 'BILL_TO'
   AND th.enable_flag = 'Y'
   AND qha.creation_date BETWEEN TO_DATE ('05-01-2009 00:00:00',
                                          'MM/DD/YYYY HH24:MI:SS'
                                         )
                             AND TO_DATE ('05-31-2009 23:59:59',
                                          'MM/DD/YYYY HH24:MI:SS'
                                         )
   AND th.theater = :theater
   AND qha.quote_status_id = :quote_status

[Updated on: Tue, 11 August 2009 07:09] by Moderator

Report message to a moderator

Re: Perfomance tunning (Vikas) [message #417733 is a reply to message #417731] Tue, 11 August 2009 05:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are a funny guy.
Re: Perfomance tunning (Vikas) [message #417736 is a reply to message #417731] Tue, 11 August 2009 05:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Set the GO_FASTER global parameter to 'SUPER DUPER FASTEST'
Re: Perfomance tunning (Vikas) [message #417743 is a reply to message #417731] Tue, 11 August 2009 06:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
vikasarorait wrote on Tue, 11 August 2009 12:14
I need help to tune this query.

SELECT /*+ leading(qha) use_hash(fuser) use_hash(fuser2) use_hash(qs2) use_hash(qs) use_hash(pcc) use_hash(th) */ ...


From the code, it seems that somebody already started tuning it. Let him proceed so - he is surely more aware about all tables involved (their structure within indexes, relationship, cardinality, selectivity of filter conditions) than anybody in this forum.

The only advice I may give you without any info mentioned above and especially without specifying Oracle database version is: gather statistics on all tables involved in this query. Additionally you may try steps listed in http://www.orafaq.com/wiki/Performance_tuning. Or, better, start studying Performance Tuning Guide book, which is part of Oracle documentation. It is available e.g. online on http://tahiti.oracle.com/.
Re: Perfomance tunning (Vikas) [message #417753 is a reply to message #417731] Tue, 11 August 2009 07:06 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
I have read somewhere that, try not to use more than 4 or 5 tables in a join.Try to split your logic, rather than to implement it in a single query.

Looking at the tables joins, I think you might look at normalization.
Re: Perfomance tunning (Vikas) [message #417754 is a reply to message #417753] Tue, 11 August 2009 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I have read somewhere that, try not to use more than 4 or 5 tables in a join.Try to split your logic, rather than to implement it in a single query.


I would have to disagree strenuously with this advise. If you were using some non-Oracle databases then this might be good advice, but it is a very rare case in Oracle when this is good advice.
Re: Perfomance tunning (Vikas) [message #417755 is a reply to message #417753] Tue, 11 August 2009 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have read somewhere that, try not to use more than 4 or 5 tables in a join.

Somewhere, about something, ages ago, from someone that maybe knows what he was talking about...

Regards
Michel
Re: Perfomance tunning (Vikas) [message #417758 is a reply to message #417753] Tue, 11 August 2009 07:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Neo06 wrote on Tue, 11 August 2009 14:06
I have read somewhere that, try not to use more than 4 or 5 tables in a join.

It is described (with recommendation of using LEADING hint which is already present in the posted query), e.g. here: http://www.orafaq.com/tuningguide/five%20tables.html.
Neo06 wrote on Tue, 11 August 2009 14:06
Try to split your logic, rather than to implement it in a single query.

You would need to store intermediate results somewhere. Not very efficient for a large resultset. I would not go this way.
Re: Perfomance tunning (Vikas) [message #417767 is a reply to message #417758] Tue, 11 August 2009 08:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
┬ęCopyright 2003

I think this page is quite outdated.

Regards
Michel
Previous Topic: SQL Query.
Next Topic: Is it possible to Access Partition Table Using DBLink
Goto Forum:
  


Current Time: Sun Dec 04 00:33:35 CST 2016

Total time taken to generate the page: 0.06673 seconds