Home » RDBMS Server » Performance Tuning » Slow Performance of query
| Slow Performance of query [message #217552] |
Fri, 02 February 2007 14:39  |
deepayan
Messages: 51
Registered: December 2005
|
Member |
|
|
Dear All,
This query is taking unusually long time to execute.
Any Idea regarding its tuning is extremely needed.
SELECT
trx.customer_trx_id,
trxlines.interface_line_attribute1 PROJECT_NUMBER,
items.draft_invoice_num,
hdr.event_type,
EVENTS.Event_Id,
decode(hdr.event_type,'Delivery',billper.delivery_percentage,'Installation',billper.installation_percentage,'Retention',billper.reten tion_percentage,null) EVENT_PERCENT,
hdr.dc_mir_no,
hdr.item_code,
hdr.item_description,
hdr.unit_selling_price,
decode(hdr.tax_code,null,null,hdr.tax_code||' @ '||hdr.tax_rate) TAX_CODE,
det.quantity,
trxlines.extended_amount LINE_AMT,
taxlines.extended_amount TAX,
trxlines.extended_amount + taxlines.extended_amount TOTAL
from
ra_customer_trx_all trx,
ra_customer_trx_lines_all trxlines,
pa_projects_all proj,
pa_draft_invoice_items items,
pa_events events,
tcs_cmc_olpics_det_t det,
tcs_cmc_olpics_hdr_t hdr,
tcs_cmc_proj_bill_percent billper,
ra_customer_trx_lines_all taxlines
where
trxlines.customer_trx_id = trx.customer_trx_id AND
trx.complete_flag = 'Y' AND
taxlines.link_to_cust_trx_line_id = trxlines.customer_trx_line_id and
nvl(trxlines.interface_line_attribute1,'1') = /*nvl(*/proj.segment1/*,'1')*/ AND
trxlines.Set_Of_Books_Id = '85' AND --85
trx.Set_Of_Books_Id = '85' AND -- /*nvl(*/items.project_id/*,1)*/ = /*nvl(*/proj.project_id/*,1)*/ and
/*nvl(*/to_char(items.draft_invoice_num)/*,'1')*/ = nvl(trxlines.interface_line_attribute2,'1') and
/*nvl(*/items.line_num/*,1)*/ = /*nvl(*/trxlines.line_number/*,1)*/ and
nvl(items.event_num,1) = /*nvl(*/events.event_num/*,1)*/ and
/*nvl(*/events.project_id/*,1)*/ = /*nvl(*/items.project_id/*,1)*/ and
/*nvl(*/events.event_id/*,1)*/ = nvl(det.event_id,1) and
/*nvl(*/det.cmc_event_id/*,1)*/ = /*nvl(*/hdr.cmc_event_id/*,1)*/ and
/*nvl(*/hdr.project_number/*,'1')*/ = /*nvl(*/billper.project_number/*,'1')*/
order by
trx.customer_trx_id
explain plan : SELECT STATEMENT, GOAL = CHOOSE Cost=53828 Cardinality=1 Bytes=219
PX COORDINATOR
PX SEND QC (ORDER) Object owner=SYS Object name=:TQ10009 Cost=53828 Cardinality=1 Bytes=219
SORT ORDER BY Cost=53828 Cardinality=1 Bytes=219
PX RECEIVE Cost=2 Cardinality=1 Bytes=13
PX SEND RANGE Object owner=SYS Object name=:TQ10008 Cost=2 Cardinality=1 Bytes=13
TABLE ACCESS BY INDEX ROWID Object owner=TCSCMC Object name=TCS_CMC_PROJ_BILL_PERCENT Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=53827 Cardinality=1 Bytes=219
NESTED LOOPS Cost=53825 Cardinality=1 Bytes=206
HASH JOIN Cost=53824 Cardinality=1 Bytes=98
PX RECEIVE Cost=3 Cardinality=1 Bytes=6
PX SEND HASH Object owner=SYS Object name=:TQ10007 Cost=3 Cardinality=1 Bytes=6
TABLE ACCESS BY INDEX ROWID Object owner=AR Object name=RA_CUSTOMER_TRX_LINES_ALL Cost=3 Cardinality=1 Bytes=6
NESTED LOOPS Cost=53578 Cardinality=1 Bytes=87
NESTED LOOPS Cost=53575 Cardinality=1 Bytes=81
NESTED LOOPS Cost=53572 Cardinality=1 Bytes=67
HASH JOIN Cost=53570 Cardinality=1 Bytes=55
PX RECEIVE Cost=52685 Cardinality=16323 Bytes=669243
PX SEND HASH Object owner=SYS Object name=:TQ10006 Cost=52685 Cardinality=16323 Bytes=669243
HASH JOIN BUFFERED Cost=52685 Cardinality=16323 Bytes=669243
BUFFER SORT
PX RECEIVE Cost=42174 Cardinality=131584 Bytes=3421184
PX SEND HASH Object owner=SYS Object name=:TQ10000 Cost=42174 Cardinality=131584 Bytes=3421184
TABLE ACCESS FULL Object owner=AR Object name=RA_CUSTOMER_TRX_LINES_ALL Cost=42174 Cardinality=131584 Bytes=3421184
PX RECEIVE Cost=8373 Cardinality=1198740 Bytes=17981100
PX SEND HASH Object owner=SYS Object name=:TQ10005 Cost=8373 Cardinality=1198740 Bytes=17981100
VIEW Object owner=PA Object name=index$_join$_004 Cost=8373 Cardinality=1198740 Bytes=17981100
HASH JOIN BUFFERED
PX RECEIVE Cost=3862 Cardinality=1198740 Bytes=17981100
PX SEND HASH Object owner=SYS Object name=:TQ10003 Cost=3862 Cardinality=1198740 Bytes=17981100
PX BLOCK ITERATOR Cost=3862 Cardinality=1198740 Bytes=17981100
INDEX FAST FULL SCAN Object owner=PA Object name=PA_DRAFT_INVOICE_ITEMS_N2 Cost=3862 Cardinality=1198740 Bytes=17981100
PX RECEIVE Cost=4389 Cardinality=1198740 Bytes=17981100
PX SEND HASH Object owner=SYS Object name=:TQ10004 Cost=4389 Cardinality=1198740 Bytes=17981100
PX BLOCK ITERATOR Cost=4389 Cardinality=1198740 Bytes=17981100
INDEX FAST FULL SCAN Object owner=PA Object name=PA_DRAFT_INVOICE_ITEMS_U1 Cost=4389 Cardinality=1198740 Bytes=17981100
BUFFER SORT
PX RECEIVE Cost=719 Cardinality=77500 Bytes=1085000
PX SEND HASH Object owner=SYS Object name=:TQ10001 Cost=719 Cardinality=77500 Bytes=1085000
VIEW Object owner=PA Object name=index$_join$_003 Cost=719 Cardinality=77500 Bytes=1085000
HASH JOIN
INDEX FAST FULL SCAN Object owner=PA Object name=PA_PROJECTS_U1 Cost=210 Cardinality=77500 Bytes=1085000
INDEX FAST FULL SCAN Object owner=PA Object name=PA_PROJECTS_U2 Cost=252 Cardinality=77500 Bytes=1085000
TABLE ACCESS BY INDEX ROWID Object owner=AR Object name=RA_CUSTOMER_TRX_ALL Cost=2 Cardinality=1 Bytes=12
INDEX UNIQUE SCAN Object owner=AR Object name=RA_CUSTOMER_TRX_U1 Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PA Object name=PA_EVENTS Cost=3 Cardinality=1 Bytes=14
INDEX RANGE SCAN Object owner=PA Object name=PA_EVENTS_U1 Cost=2 Cardinality=1
INDEX RANGE SCAN Object owner=AR Object name=RA_CUSTOMER_TRX_LINES_N3 Cost=2 Cardinality=3
BUFFER SORT
PX RECEIVE Cost=242 Cardinality=95687 Bytes=1052557
PX SEND HASH Object owner=SYS Object name=:TQ10002 Cost=242 Cardinality=95687 Bytes=1052557
TABLE ACCESS FULL Object owner=TCSCMC Object name=TCS_CMC_OLPICS_DET_T Cost=242 Cardinality=95687 Bytes=1052557
TABLE ACCESS BY INDEX ROWID Object owner=TCSCMC Object name=TCS_CMC_OLPICS_HDR_T Cost=1 Cardinality=1 Bytes=108
INDEX UNIQUE SCAN Object owner=TCSCMC Object name=TCS_CMC_OLPICS_HDR_T_U1 Cost=0 Cardinality=1
INDEX RANGE SCAN Object owner=TCSCMC Object name=TCS_CMC_PROJ_BILL_PERCENT_N1 Cost=1 Cardinality=1
Im attaching the description of tables & indexes..
Thanks in advance...
-
Attachment: describe.txt
(Size: 50.30KB, Downloaded 1977 times)
[Updated on: Sun, 04 February 2007 03:31] by Moderator Report message to a moderator |
|
|
|
| Re: Slow Performance of query [message #220958 is a reply to message #217552] |
Thu, 22 February 2007 20:17  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Make sure you have indexes on both databases to support the query.
Decode and nval are very expensive and make a query run slow. See if you can restructure the query.
Do you really need all the hints? Oracle usually does a great job of optimising a query if given the chance. Try refreshing your optimizer statistics.
There are a lot of loops that cost alot.
What version?
Do you have the init.ora parameter set for cost basis? First rows?
|
|
|
|
| Re: Slow Performance of query [message #221397 is a reply to message #220958] |
Mon, 26 February 2007 10:01 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
| Quote: | Do you really need all the hints?
|
I don't see any hint...
OP, from what I gather from the query, there are "two parts":
the basic apps tables (trx, trxlines, taxlines, projects, items and events) and then there is a set op custom views (det, hdr, billper).
The only join I find between those two parts is:
events.event_id = nvl(det.event_id,1)
It seems to me that you will need more than that for a join and that you are currently selecting rows unnecessary.
Apart from that, it will be hard to help you without knowing the definition of the custom views.
|
|
|
|
Goto Forum:
Current Time: Thu May 16 02:59:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
