Home » RDBMS Server » Performance Tuning » Slow Performance of query
Slow Performance of query [message #217552] Fri, 02 February 2007 14:39 Go to next message
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 588 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 Go to previous messageGo to next message
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 Go to previous message
skooman
Messages: 912
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.
Previous Topic: performance enhancement
Next Topic: how to measure the Performance improvement
Goto Forum:
  


Current Time: Thu Dec 08 08:43:53 CST 2016

Total time taken to generate the page: 0.05460 seconds