Home » RDBMS Server » Performance Tuning » Query is taking more time to execute (Oracle 10G, Actuate reports)
Query is taking more time to execute [message #327431] Mon, 16 June 2008 07:02 Go to next message
Messages: 5
Registered: May 2008
Junior Member

I heve a cursor with a select statement as shown below.It is taking very long execution time due to which I am not able to generate report.I use actuate reporting tool.What could be the potential solutions for this.Can any one please advise on this

Select ********,********,*********.................
From ar_invoices ari, ari_items arii, packing_lists pal,
pal_cases palcs, palcs_items palcsi, purchase_orders po,
sales_orders sao, sao_items saoi, job_orders jo,
osch_items oschi, po_items poi
Where sao.org_id = saoi.sao_org_id
And sao.id = saoi.sao_id
And saoi.sao_org_id = poi.po_org_id
And saoi.sao_id = poi.trans_id_1
And saoi.id = poi.trans_id_2
And saoi.sao_org_id = palcsi.palcs_pal_org_id(+)
And saoi.sao_id = palcsi.trans_id_3(+)
And saoi.id = palcsi.trans_id_4(+)
And palcsi.palcs_pal_org_id = palcs.pal_org_id(+)
And palcsi.palcs_pal_id = palcs.pal_id(+)
And palcsi.palcs_id = palcs.id(+)
And palcs.pal_org_id = pal.org_id(+)
And palcs.pal_id = pal.id(+)
And palcsi.palcs_pal_org_id = arii.ari_org_id(+)
And palcsi.palcs_pal_id = arii.trans_id_1(+)
And palcsi.id = arii.trans_id_2(+)
And palcsi.palcs_id = arii.trans_id_3(+)
And arii.ari_org_id = ari.org_id(+)
And arii.ari_id = ari.id(+)
And pal.org_id = jo.org_id(+)
And pal.trans_id_1 = jo.id(+)
And sao.org_id = p_org_id
And nvl(sao.orggp_id
,'x') = nvl(p_orggp_id
And nvl(po.orgp_id_4
,'x') = nvl(p_factory
And nvl(sao.orgp_id
,'x') = nvl(p_customer
And nvl(sao.orgreg_id
,'x') = nvl(p_region
And poi.po_org_id = po.org_id
And poi.po_id = po.id
And saoi.sao_org_id = oschi.osch_org_id(+)
And saoi.sao_id = oschi.trans_id_3(+)
And saoi.id = oschi.trans_id_4(+)
And saoi.status <> 'CANCELLED'
And poi.status <> 'CANCELLED'
(saoi.sao_org_id, saoi.sao_id, saoi.id) Not In
(Select saoi.sao_org_id, saoi.sao_id, saoi.id
From palcs_items palcsi, ari_items arii,
ar_invoices ari, sao_items saoi
Where saoi.sao_org_id = palcsi.palcs_pal_org_id
And saoi.sao_id = palcsi.trans_id_3
And saoi.id = palcsi.trans_id_4
And palcsi.palcs_pal_org_id = arii.ari_org_id
And palcsi.palcs_pal_id = arii.trans_id_1
And palcsi.id = arii.trans_id_2
And palcsi.palcs_id = arii.trans_id_3
And arii.ari_org_id = ari.org_id
And arii.ari_id = ari.id
And nvl(ari.bl_confirmed
,'N') = 'Y')
And (saoi.customer_request_date Is Null Or
(trunc(saoi.customer_request_date) Between
,trunc(saoi.customer_request_date)) And
And (saoi.factory_due_date Is Null Or
(trunc(saoi.factory_due_date) Between
,trunc(saoi.factory_due_date)) And
And (sao.received_date Is Null Or
(trunc(sao.received_date) Between
,trunc(sao.received_date)) And

[Updated on: Mon, 16 June 2008 07:06] by Moderator

Report message to a moderator

Re: Query is taking more time to execute [message #327435 is a reply to message #327431] Mon, 16 June 2008 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64455
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 20 May 2008 14:13
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.


Re: Query is taking more time to execute [message #330279 is a reply to message #327431] Sat, 28 June 2008 15:08 Go to previous messageGo to next message
Messages: 118
Registered: June 2008
Senior Member
how meny items in cursor?
if tere >100 then do not use cursor.
cursors generate nested loops all the time, and optimiser runs query by the indexes...
it is fast if you need select by one patameter from starting table.
but better do not use cursors, and optmiser will use hash joins in meny joins, so it will be faster
Re: Query is taking more time to execute [message #330369 is a reply to message #330279] Sun, 29 June 2008 23:23 Go to previous message
Messages: 5
Registered: May 2008
Junior Member

As I am new to query tuning,If u dont mind,can you please make it more clear with regards to my cursor.What are the things which results in bad performance and where should i modify.
Previous Topic: to make fast retrival
Next Topic: increase of sessions
Goto Forum:

Current Time: Fri Feb 24 00:04:17 CST 2017

Total time taken to generate the page: 0.13247 seconds