@trace

These scripts will quickly generate trace from sqlplus. Just add @trace as first part, your sql  as second part and @trace_end as final part. This examples show a full table scan if you omit the pay_from_customer: @trace SELECT acr.pay_from_customer, acr.receipt_date, acr.receipt_number FROM ar_cash_receipts_all acr WHERE 1=1 --and acr.PAY_FROM_CUSTOMER = 999 and acr.CUSTOMER_SITE_USE_ID = 999 AND acr.status='APP' AND acr.reversal_date IS NULL ; @trace_end . trace.sql by xls4oracle.com - copyright 2009 Thomas Lundqvist Consulting ----------------------------------------------------------------------- trace initialized execute sql and complete with @trace_end created tracefile trace\20091018115531.txt SQL> SELECT 2 acr.pay_from_customer, 3 acr.receipt_date, 4 acr.receipt_number 5 FROM ar_cash_receipts_all acr 6 WHERE 1=1 7 --and acr.PAY_FROM_CUSTOMER = 999 8 and acr.CUSTOMER_SITE_USE_ID = 999 9 AND acr.status='APP' 10 AND acr.reversal_date IS NULL ; Elapsed: 00:00:10.04 . Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2853 Card=10 Bytes=360) 1 0 TABLE ACCESS (FULL) OF 'AR_CASH_RECEIPTS_ALL' (TABLE) (Cost=2853 Card=10 Bytes=360) . Statistics ---------------------------------------------------------- 1200 recursive calls 0 db block gets 10753 consistent gets 10414 physical reads 0 redo size 223 bytes sent via SQL*Net to client 230 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 0 rows processed . And if you include the pay_from_customer: @trace SELECT acr.pay_from_customer, acr.receipt_date, acr.receipt_number FROM ar_cash_receipts_all acr WHERE 1=1 and acr.PAY_FROM_CUSTOMER = 999 and acr.CUSTOMER_SITE_USE_ID = 999 AND acr.status='APP' AND acr.reversal_date IS NULL ; @trace_end . trace.sql by xls4oracle.com - copyright 2009 Thomas Lundqvist Consulting ----------------------------------------------------------------------- trace initialized execute sql and complete with @trace_end created tracefile trace\20091018115744.txt SQL> SELECT 2 acr.pay_from_customer, 3 acr.receipt_date, 4 acr.receipt_number 5 FROM ar_cash_receipts_all acr 6 WHERE 1=1 7 and acr.PAY_FROM_CUSTOMER = 999 8 and acr.CUSTOMER_SITE_USE_ID = 999 9 AND acr.status='APP' 10 AND acr.reversal_date IS NULL ; Elapsed: 00:00:00.02 . Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=1 Bytes=36) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AR_CASH_RECEIPTS_ALL' (TABLE) (Cost=26 Card=1 Bytes=36) 2 1 INDEX (RANGE SCAN) OF 'AR_CASH_RECEIPTS_N2' (INDEX) (Cost=3 Card=24) . Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 5 consistent gets 3 physical reads 0 redo size 231 bytes sent via SQL*Net to client 230 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed . SQL> @trace_end Pls. note that the trace is with traceonly – that means no data is retrieved from the database and you dont need any valid keys to validate your sql – just use dummy keys like 999 in this example!!!   @trace (0 downloads)   

Leave a Reply