Home » SQL & PL/SQL » SQL & PL/SQL » sql trace (TOAD 8.6)
sql trace [message #435749] Thu, 17 December 2009 13:27 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
First of all my apologies if i'm posting this in the wrong forum, but i couldnt find any for TOAD.
How can i access the sql trace(tkprof) file on a server?. Ihave a query that takes 1second to execute , but when i added a CASE statement its taking 11 minutes and thats the reason i want to run the trace and access the output file to find what wrong.

ORIGINAL SQL QUERY:

SELECT   decode(t1.ps_qty,0,0,(t1.ORIGINAL_AMOUNT/t1.PS_QTY)) unit_cost_1,
   SUM (t2.amount) po_payments,T2.INVOICE_DT, to_char(add_months(t2.invoice_dt,6),'YYYY') INVOICE_DATE_FY, t2.po_no,
         SUM (t1.ps_qty) ps_qty, t1.amount AS encumbered_amount, t1.bud_ref,
         t1.contract_info,t1.original_amount,t1.CLASS district,
		 0 used_amount,0 STOCK_ID
        
     FROM dwtarget.dw_ps_all_payments_fact t2, dwtarget.dw_ps_ap_po_rq_fact t1
     WHERE t2.po_no = t1.ps_no
     AND t2.line_no = t1.ps_line
     AND t1.OBJECT IN ('544020', '544095')
	 GROUP BY t2.po_no,
         t2.invoice_dt,
         t1.amount,
         t1.bud_ref,
         t1.contract_info,
         t1.original_amount,
		 t1.CLASS,
decode(t1.ps_qty,0,0,(t1.ORIGINAL_AMOUNT/t1.PS_QTY))
 
 
UNION ALL
		
SELECT  0 UNIT_COST_1,0 PO_PAYMENTS,NULL INVOICE_DT,NULL INVOICE_DATE_FY,'WMS MATERIAL' PO_NO,0 PS_QTY,0 ENCUMBERED_AMOUNT,TO_CHAR (ADD_MONTHS (t1.date_work, 6), 'YYYY') BUD_REF, NULL CONTRACT_INFO,NULL ORIGINAL_AMOUNT,t2.owner_cat DISTRICT,SUM(t1.amount) used_amount,
         t2.stock_id
              FROM dwtarget.dw_wms_wo_mat_dc t1,
             (SELECT   owner_cat, stock_id
              FROM dwtarget.dw_wms_mat_all
              WHERE master_code_id IN ('8664','8665')
               AND dwtarget.dw_wms_mat_all.unit_id = '724'
              GROUP BY owner_cat, stock_id) t2
              WHERE t1.stock_id = t2.stock_id AND t1.unit_id = '724'
              GROUP BY TO_CHAR (ADD_MONTHS (t1.date_work, 6), 'YYYY'),
         t2.owner_cat,
         t2.stock_id


SQL QUERY WITH A "CASE" STATEMENT IN IT:


SELECT   decode(t1.ps_qty,0,0,(t1.ORIGINAL_AMOUNT/t1.PS_QTY)) unit_cost_1,
   SUM (t2.amount) po_payments,T2.INVOICE_DT, to_char(add_months(t2.invoice_dt,6),'YYYY') INVOICE_DATE_FY, t2.po_no,
         SUM (t1.ps_qty) ps_qty, t1.amount AS encumbered_amount, t1.bud_ref,
         t1.contract_info,t1.original_amount,t1.CLASS district,
		 CASE WHEN ROW_NUMBER () OVER (PARTITION BY t1.ps_no ORDER BY t1.CLASS) =1 THEN t1.amount end  YTD_PO_TOTALS ,0 used_amount,0 STOCK_ID
        
     FROM dwtarget.dw_ps_all_payments_fact t2, dwtarget.dw_ps_ap_po_rq_fact t1
     WHERE t2.po_no = t1.ps_no
     AND t2.line_no = t1.ps_line
     AND t1.OBJECT IN ('544020', '544095')
	 GROUP BY t2.po_no,
         t2.invoice_dt,
         t1.amount,
         t1.bud_ref,
         t1.contract_info,
         t1.original_amount,
		 t1.CLASS,
decode(t1.ps_qty,0,0,(t1.ORIGINAL_AMOUNT/t1.PS_QTY)),
t1.ps_no
 
UNION ALL
		
SELECT  0 UNIT_COST_1,0 PO_PAYMENTS,NULL INVOICE_DT,NULL INVOICE_DATE_FY,'WMS MATERIAL' PO_NO,0 PS_QTY,0 ENCUMBERED_AMOUNT,TO_CHAR (ADD_MONTHS (t1.date_work, 6), 'YYYY') BUD_REF, NULL CONTRACT_INFO,NULL ORIGINAL_AMOUNT,t2.owner_cat DISTRICT,null ytd_po_totals,SUM(t1.amount) used_amount,
         t2.stock_id
              FROM dwtarget.dw_wms_wo_mat_dc t1,
             (SELECT   owner_cat, stock_id
              FROM dwtarget.dw_wms_mat_all
              WHERE master_code_id IN ('8664','8665')
               AND dwtarget.dw_wms_mat_all.unit_id = '724'
              GROUP BY owner_cat, stock_id) t2
              WHERE t1.stock_id = t2.stock_id AND t1.unit_id = '724'
              GROUP BY TO_CHAR (ADD_MONTHS (t1.date_work, 6), 'YYYY'),
         t2.owner_cat,
         t2.stock_id
Any help is greatly appreciated.

Thanks




Pages: 1 Back to Thread List
Threads: [ Previous | Next ]


Thanks
Re: sql trace [message #435751 is a reply to message #435749] Thu, 17 December 2009 13:43 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
While at the MAIN page of this forum try doing SEARCH on this page (Ctrl-F) for "TOAD"


EXPLAIN PLAN <"slow" SQL statement>
SELECT * FROM table dbms_xplan.display;

using sqlplus do the following below
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- run SQL now
post FORMATTED results back here

[Updated on: Thu, 17 December 2009 13:55]

Report message to a moderator

Re: sql trace [message #435778 is a reply to message #435749] Thu, 17 December 2009 20:40 Go to previous messageGo to next message
wvu1999
Messages: 38
Registered: April 2007
Member

Once the trace file is created, you can view it in Toad.

Toad has 2 different screens for traces:
TkProf interface
Trace File Browser

Each requires you FTP the file down from the server.

If you own Spotlight On Oracle (included in the Toad DBA Suite), then you can read trace files directly off the server Smile
Re: sql trace [message #435834 is a reply to message #435749] Fri, 18 December 2009 05:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you say that the query takes 1 second to execute, is that a second to return all the rows, or just to get the first set of rows back.

If the rows are spread out across the table, then the ROW_NUMBER() statement may well need all of the rows to be fetched before it can return any of the details
Previous Topic: group by in different table
Next Topic: Please resolve below SQL Query
Goto Forum:
  


Current Time: Sat Sep 24 23:09:43 CDT 2016

Total time taken to generate the page: 0.07358 seconds