Home » RDBMS Server » Performance Tuning » How to perforamnce this query? Help
How to perforamnce this query? Help [message #324912] |
Wed, 04 June 2008 08:40  |
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 #324922 is a reply to message #324912] |
Wed, 04 June 2008 08:59   |
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   |
 |
BlackSwan
Messages: 26766 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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:04:56 CST 2025
|