TKPROF: Release 10.1.0.2.0 - Production on Thu Jan 12 19:48:27 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Trace file: tusker_ora_24258.trc
Sort options: fchela  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT NVL(SUM(D.quantity),0)
FROM SL_TXN_INQUIRY_INVOICE M,SL_TXN_INQUIRY_INVOICE_DETAIL D 
WHERE M.invoice_no = D.invoice_no
AND M.cancellation <> '1'
AND (M.invoice_date >= :b4
AND M.invoice_date < :b3)
--AND TO_CHAR(M.invoice_date,'MON') = Wk_tmp_mth
--AND TO_CHAR(M.invoice_date,'YYYY') = Wk_tmp_year
AND D.item_code = :b2
AND M.location_code = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 115869      9.51      12.41          0          0          0           0
Fetch   115869   4015.59    4913.67       1197  278929049          0      115869
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   231739   4025.10    4926.09       1197  278929049          0      115869

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 246  (QA_PURCHASING)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 115869  SORT AGGREGATE (cr=278929049 r=1197 w=0 time=4911844415 us)
  32161   FILTER  (cr=278929049 r=1197 w=0 time=4910237435 us)
  32161    NESTED LOOPS  (cr=278929049 r=1197 w=0 time=4909931984 us)
250339431     INDEX FAST FULL SCAN INDX_SL_TXN_INQUIRY_INVOICE_01 (cr=27808560 r=923 w=0 time=2829483025 us)(object id 155266)
  32161     INDEX RANGE SCAN INDX_SL_TXN_INQ_INV_DETAIL_01 (cr=251120489 r=274 w=0 time=1313937173 us)(object id 153456)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
 115869   SORT (AGGREGATE)
  32161    FILTER
  32161     NESTED LOOPS
250339431      TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                 'SL_TXN_INQUIRY_INVOICE'
  32161      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                 'INDX_SL_TXN_INQ_INV_DETAIL_01' (NON-UNIQUE)

********************************************************************************

SELECT NVL(SUM(ID.reserved_quantity),0)
FROM SL_TXN_INQUIRY_SLIP IM,SL_TXN_INQUIRY_SLIP_DETAIL ID
WHERE IM.inquiry_slip_no = ID.inquiry_slip_no
AND ID.item_code = :b2
AND IM.location_code = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute   8913      0.69       0.59          0          0          0           0
Fetch     8913    157.77     298.37       3062    4270592          0        8913
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    17827    158.46     299.00       3062    4270592          0        8913

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 246  (QA_PURCHASING)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   8913  SORT AGGREGATE (cr=4270592 r=3062 w=0 time=298249208 us)
  34029   NESTED LOOPS  (cr=4270592 r=3062 w=0 time=298012875 us)
  64171    INDEX FAST FULL SCAN INDX_SL_TXN_INQ_SLIP_DETAIL_01 (cr=4135632 r=2276 w=0 time=218967310 us)(object id 149795)
  34029    TABLE ACCESS BY INDEX ROWID SL_TXN_INQUIRY_SLIP (cr=134960 r=786 w=0 time=78684028 us)
  64171     INDEX UNIQUE SCAN PK_SL_TXN_INQUIRY_SLIP (cr=70789 r=129 w=0 time=10620335 us)(object id 147881)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
   8913   SORT (AGGREGATE)
  34029    NESTED LOOPS
  64171     INDEX   MODE: ANALYZED (FAST FULL SCAN) OF 
                'INDX_SL_TXN_INQ_SLIP_DETAIL_01' (NON-UNIQUE)
  34029     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                'SL_TXN_INQUIRY_SLIP'
  64171      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                 'PK_SL_TXN_INQUIRY_SLIP' (UNIQUE)

********************************************************************************