Home » RDBMS Server » Performance Tuning » How to perforamnce this query? Help
icon5.gif  How to perforamnce this query? Help [message #324912] Wed, 04 June 2008 08:40 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi all,

This is the first time exp to handling the tuning issue. please help me out to solve this problem.When i performance this given query thru explain plan. I found table access full(uimsmgr.uabscon) in explain plan..It will shows the red color letter that word.But i found one thing after i further changing the script becasue this stament has to been placed in the order by clause NVL(ucnworg_oper_distr_code,'00A00'), I herewith attached the script.

Whn i execute this query, it will take more hrs, so they need to change it and we do to reduce the timing for performance that query.
But i comment this stmt NVL(ucnworg_oper_distr_code,'00A00'), in a sql query, it will take to execute the less timing.

So please help me out to resolve this problem, or please rewrite the query and send it to me..


--explain plan for
SELECT 
          NVL(worg1.ucnworg_oper_distr_code,'00A00'), 
          substr(NVL(worg1.ucnworg_engineer_sector_id,'A0000'),4,2),
          scon1.uabscon_cust_code,
          scon1.uabscon_prem_code,
          serv1.ucrserv_next_visit_date,
          scon1.uabscon_end_date,
          scon1.uabscon_srvc_code,
          scon1.uabscon_status_ind,
          svco1.ucbsvco_requested_date,
          NVL(utvflue_desc,' '),
          svco1.ucbsvco_code, 
          decode(ucbsvco_code,NULL,0,1),
          serv1.ucrserv_styp_code, 
          serv1.ucrserv_srvc_code 
   FROM 
    /* HD069092 - Modified the order of the table to optimise the query */
          uimsmgr.ucrserv serv1, 
          uimsmgr.utvflue flue1, ----count(*) =5
          uimsmgr.utvsrvc srvc1, --- count(*) =448
          uimsmgr.utrjapp japp1, -- count(*) =2024
          uimsmgr.ucbsvco svco1,
          uimsmgr.uabscon scon1, 
          uimsmgr.ucnworg worg1 -- count(*) = 9709
   WHERE 
          worg1.ucnworg_postcode_sector_id (+) = scon1.uabscon_postcode_sector_id 
   AND    nvl(worg1.ucnworg_patch_type,'S') = 'S' 
   AND    svco1.ucbsvco_cust_code (+) = scon1.uabscon_cust_code 
   AND    svco1.ucbsvco_prem_code (+) = scon1.uabscon_prem_code 
   AND    svco1.ucbsvco_stus_code (+) = 'O' 
   AND    serv1.ucrserv_scon_number = scon1.uabscon_number 
   AND    serv1.ucrserv_status_ind = 'A'
   AND    scon1.uabscon_bulk_ind != 'Y' 
   AND    scon1.uabscon_status_ind != 'P'
   AND    scon1.uabscon_status_ind != 'C' 
   AND    srvc1.utvsrvc_code = serv1.ucrserv_srvc_code 
    AND    japp1.utrjapp_styp_code = serv1.ucrserv_styp_code 
   AND    japp1.utrjapp_srvc_code = serv1.ucrserv_srvc_code 
   AND    NVL(japp1.utrjapp_visit_months, 0) > 0  
   AND    flue1.utvflue_type (+) = serv1.ucrserv_flue_type_ind 
 ORDER BY 
 NVL(worg1.ucnworg_oper_distr_code,'00A00', 
 scon1.uabscon_cust_code,scon1.uabscon_prem_code 

---This NVL stmt only i got the bad performance shown by explain plan

[Updated on: Wed, 04 June 2008 08:54] by Moderator

Report message to a moderator

Re: How to perforamnce this query? Help [message #324919 is a reply to message #324912] Wed, 04 June 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to perforamnce this query?

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: How to perforamnce this query? Help [message #324922 is a reply to message #324912] Wed, 04 June 2008 08:59 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi michel,

Thank u for your suggestion. But this is high priority issue. i have to complete today or tomo itself. If u dont nind, please rewrite taht query and help me out from this problem

Next time i will sure to read all those documents wht u send before. please understand me
Re: How to perforamnce this query? Help [message #324926 is a reply to message #324912] Wed, 04 June 2008 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
The lack of planning on your part, does NOT constitute an emergency on our part.

I strongly suspect you'll be very disappointed with the results from here over the next 48 hours.

By the way, 'u' is not a member of this forum.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You have the tables & we do not.
You have the data & we do not.

Do you honestly expect folks to tune a complex SQL statement just by looking at it? Get a clue.

Without many more details, You're On Your Own (YOYO)!

[Updated on: Wed, 04 June 2008 10:18] by Moderator

Report message to a moderator

Re: How to perforamnce this query? Help [message #325149 is a reply to message #324912] Thu, 05 June 2008 02:55 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
At a minimum, we're going to need details of the indexes that are on the tables, and the explain_plan for the current query.

Without more information, we cannot help.
Previous Topic: How to improve this query
Next Topic: Please help in tuning
Goto Forum:
  


Current Time: Wed Dec 07 02:48:40 CST 2016

Total time taken to generate the page: 0.16869 seconds