Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with 10046 level 12 trace

RE: Help with 10046 level 12 trace

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 22 Aug 2002 14:05:32 -0800
Message-ID: <F001.004BDA4A.20020822140532@fatcity.com> 





One option: Don't use tkprof. It completely ignores all the non-level-1
data that you've taken the trouble to collect. Gzip or winzip your raw
trace data, and then upload the name.trc.gz file to
www.hotsos.com/products/profiler/fman.html. Let me know when it's up,
and I'll take a look at the file for you.




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com



Upcoming events:


- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu


- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark





-----Original Message-----


Ghosalkar


Sent: Thursday, August 22, 2002 3:41 PM


To: Multiple recipients of list ORACLE-L



Hello All,



One our lead developers has just written a Delphi application to process
some printing jobs. The application would every other minute query a job
table and generate a report to be printed to specific network printers.



I ran a 10046 level 12 trace using dbms_system.set_ev.



I would be very glad if experts can help me understand how to interpret
the trace and tkprof output. Also pls let me know if ull find anything
which points to or would lead to performance problems.



I am thinking of setting session_cached_cursors to a non zero value.



thanks


Mandar



Tkprofed trace file




TKPROF: Release 7.1.6.2.0 - Production on Thu Aug 22 12:04:09 2002



Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.



Trace file: ora_22248.trc


Sort options: default






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  


       WSJ_JOB_ID JOB,   
       WSJ_PWS_ID PWS, 
       pws_version_number VER,  
       WSJ_PRINTER_NAME "PRINTER", 
       wsj_als_allocation_group ALS, 
       SUM(ORL_CUTBACK_QUANTITY * ORL_PO_PRICE) TOT_PRICE, 
       LOC_LOCATION_CODE "LOC", 
       CST_CST_NO,  
       CST_NAME, CST_STORE_TYPE MS, 
       CST_INTERNATIONAL_FLAG INTL, 
       ADR_CITY CITY, ADR_STATE_CODE ST, 
       ORH_PO_NO PO, 
       SLR_FIELD_NAME SLR, 
       CDP_NO DEPT, 
       ORMK_MARKET MKT, 
       ORH_LOG_NUMBER LOG, 
       ORH_DIV_NO "DIV", 
       PWS.CREATED_BY "BY", 
       PWS.CREATION_DATE "CREATED", 
       PWS.LAST_UPDATED_BY "UP_BY", 
       PWS.LAST_UPDATE_DATE "UP_DATE", 
       RV_MEANING,  
       LBT_DESCRIPTION "LABEL" 




FROM worksheet_jobs, PULL_WORKSHEETS PWS, Allocation_sessions, location,


     ORDER_LINES,      STYLE_SIZE,style_master, 
     ORDER_HEADERS, CUSTOMER_MASTER,  
     CUSTOMER_SELLER_ASGT, 
     CUSTOMER_ADDRESS_ASGT, SELLER, CUSTOMER_DEPARTMENT, 
     ADDRESS, ORDER_MARKETS, 
     cg_ref_codes, label_type 




WHERE 1=1 


  and ORH_LBT_ID = LBT_ID (+) 
  and ORH_SPECIAL_ORDER = RV_LOW_VALUE (+) 
  AND CSL_EMP_ID = SLR_EMP_ID(+) 
  AND CSL_ORMK_ID = ORMK_ID (+) 
  AND CAA_STA_STATUS = 'CAA01' 
  AND CAA_ADDRESS_TYPE = 'CO' 
  AND CAA_ADR_ID = ADR_ID 
  AND CAA_CST_ID = CST_ID 
  AND ORH_CSL_ID = CSL_ID 
  AND ORH_CDP_ID = CDP_ID (+) 
  AND ORH_CST_ID = CST_ID 
  and ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
  AND ORL_PWS_ID = PWS_ID 
  AND ALS_LOC_ID = LOC_ID 
  and wsj_als_allocation_group = als_allocation_group 


  and pws_id = wsj_pws_id 


  and wsj_status = 'IN QUEUE' 

  group by       
       WSJ_JOB_ID, 
       WSJ_PWS_ID, 
       pws_version_number,  
       WSJ_PRINTER_NAME, 
       wsj_als_allocation_group, 
       LOC_LOCATION_CODE, 
       CST_CST_NO,  
       CST_NAME, CST_STORE_TYPE , 
       CST_INTERNATIONAL_FLAG, 
       ADR_CITY, ADR_STATE_CODE, 
       ORH_PO_NO, 
       SLR_FIELD_NAME, 
       CDP_NO, 
       ORMK_MARKET, 
       ORH_LOG_NUMBER, 
       ORH_DIV_NO, 
       PWS.CREATED_BY, 
       PWS.CREATION_DATE, 
       PWS.LAST_UPDATED_BY, 
       PWS.LAST_UPDATE_DATE, 
       RV_MEANING,  
       LBT_DESCRIPTION 




order by WSJ_PWS_ID, wsj_job_id desc



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse       12      0.05       0.00          0          0          0
0


Execute      6      0.00       0.00          0          0          0
0


Fetch       11      0.83       0.80          0      20484         12
260


------- ------  -------- ---------- ---------- ---------- ----------




total       29      0.88       0.80          0      20484         12
260



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (GROUP BY)
      0    FILTER
      0     MERGE JOIN (OUTER)
      0      SORT (JOIN)
      0       NESTED LOOPS
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          NESTED LOOPS
      0           NESTED LOOPS
      0            NESTED LOOPS (OUTER)
      0             NESTED LOOPS (OUTER)
      0              NESTED LOOPS
      0               NESTED LOOPS (OUTER)
      0                NESTED LOOPS (OUTER)
      0                 NESTED LOOPS
      0                  NESTED LOOPS
      0                   NESTED LOOPS
      0                    NESTED LOOPS
      0                     NESTED LOOPS
      0                      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'WORKSHEET_JOBS'

      0                       INDEX (RANGE SCAN) OF 'WSJ_I5'
(NON-UNIQUE)
      0                      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ALLOCATION_SESSIONS'

      0                       INDEX   HINT: ANALYZED (UNIQUE SCAN) OF


'ALS_PK' (UNIQUE)

      0                     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'LOCATION'

      0                      INDEX   HINT: ANALYZED (UNIQUE SCAN) OF


'LOC_PK' (UNIQUE)

      0                    TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'PULL_WORKSHEETS'

      0                     INDEX   HINT: ANALYZED (UNIQUE SCAN) OF


'PWS_PK' (UNIQUE)

      0                   TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_LINES'

      0                    INDEX   HINT: ANALYZED (RANGE SCAN) OF


'ORL_I5' (NON-UNIQUE)

      0                  TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_HEADERS'

      0                   INDEX   HINT: ANALYZED (UNIQUE SCAN) OF


'ORH_PK' (UNIQUE)

      0                 TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'LABEL_TYPE'

      0                  INDEX   HINT: ANALYZED (UNIQUE SCAN) OF


'LBT_PK' (UNIQUE)

      0                TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_DEPARTMENT'

      0                 INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CDP_PK'
(UNIQUE)
      0               TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_SELLER_ASGT'

      0                INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CSL_PK'
(UNIQUE)
      0              TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_MARKETS'

      0               INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORMK_PK'
(UNIQUE)
      0             TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'SELLER'
      0              INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'SLR_PK'
(UNIQUE)
      0            TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_MASTER'

      0             INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CST_PK'
(UNIQUE)
      0           TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'STM_STYLE_MASTER'

      0            INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'STM_PK'
(UNIQUE)
      0          INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'SIZ_PK'
(UNIQUE)
      0         TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_ADDRESS_ASGT'

      0          INDEX   HINT: ANALYZED (RANGE SCAN) OF 'I_CAA_1'
(NON-UNIQUE)
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ADDRESS'
      0         INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ADR_PK'
(UNIQUE)
      0      SORT (JOIN)
      0       TABLE ACCESS   HINT: ANALYZED (FULL) OF 'CG_REF_CODES'

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







SELECT DISTINCT 


     ORL_ID ORL, 
     ORL_PWS_ID PWS, 
     ORL_START_DATE Start_DT, 
     ORL_COMPLETION_DATE COMPL_DT, 
     ORL_IN_STORE_DATE IN_STORE, 
     ORL_LINE_NUMBER LN, 
     ORL_LINE_SUB_NUMBER SUB, 
     ORL_DIV_NO "Div", 
     ORL_SHIPPED_STYLE_NO STYLE, 
     ORL_CUSTOMER_STYLE_NO cST_STYLE, 
     ORL_NRMA_CODE NRMA, 
     ORL_COLOR_TEXT COLOR, 
     ORL_QUANTITY QTY, 
     ORL_CUTBACK_QUANTITY CBACK, 
     ORH_LOG_NUMBER LOG, 
     CLP_DESCRIPTION COLOR_TXT, 
     SIZ_DESCRIPTION "SIZES", 
     nvl(orl_case_bundles,0) bundles, 
     nvl(orl_bundle_quantity,0) bundle_qty 
 FROM ORDER_HEADERS, STYLE_SIZE, ORDER_LINES, 
           COLOR_PATTERN, STYLE_MASTER 
WHERE ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
      AND ORL_NRMA_CODE = CLP_NRMA_CODE 
      AND ORL_PWS_ID = :PWS

call     count       cpu    elapsed       disk      query    current


rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      248      0.70       0.71          0          0          0
0


Execute    242      0.21       0.23          0          0          0
0


Fetch      242      0.29       0.23          0       5195          0
393


------- ------  -------- ---------- ---------- ---------- ----------




total      732      1.20       1.17          0       5195          0
393



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (UNIQUE)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ORDER_LINES'
      0         INDEX   HINT: ANALYZED (RANGE SCAN) OF 'ORL_I5'
(NON-UNIQUE)
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'STM_STYLE_MASTER'

      0         INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'STM_PK'
(UNIQUE)
      0       TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'COLOR_PATTERN'

      0        INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'I_CLP_3'
(UNIQUE)
      0      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'STYLE_SIZE'
      0       INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'SIZ_PK' (UNIQUE)
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ORDER_HEADERS'
      0      INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORH_PK' (UNIQUE)

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







SELECT 


       orl_id orl, 
       COUNT (DISTINCT(CST_NO || CAA_SHIPTO_NO || MKF_SUFFIX_NO ||
MKF_LOCATION))  NUM, 
       RPAD (SDI_CUTBACK_QUANTITY, 4 , ' ') QTY 
FROM ORDER_LINES, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE CST_ID = CAA_CST_ID 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY orl_id,SDI_CUTBACK_QUANTITY



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      399      0.37       0.80          0          0          0
0


Execute    393      0.33       0.31          0          0          0
0


Fetch      393      1.90       1.72          0      55461          0
514


------- ------  -------- ---------- ---------- ---------- ----------




total     1185      2.60       2.83          0      55461          0
514



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (GROUP BY)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      NESTED LOOPS (OUTER)
      0       NESTED LOOPS
      0        INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORL_PK' (UNIQUE)
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'STORE_DISTRIBUTIONS'

      0         INDEX   HINT: ANALYZED (RANGE SCAN) OF 'SDI_FK1'
(NON-UNIQUE)
      0       TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'MARK_FOR'
      0        INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'MKF_PK' (UNIQUE)
      0      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_ADDRESS_ASGT'

      0       INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CAA_PK' (UNIQUE)
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_MASTER'

      0      INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CST_PK' (UNIQUE)

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







SELECT 



  DISTINCT 


      orl_pws_id pws, 
      orl_id ORL, 
      CST_NO,  
      CAA_SHIPTO_NO STORE_NO,  
      MKF_SUFFIX_NO "SUFFIX",  
      MKF_LOCATION "MKF_LOC", 
      sdi_quantity qty, 
      sdi_cutback_quantity cback, 
      sdi_ratio_exception_flag rflag, 
      sdi_ratio_cutback_flag rcflag       
FROM  ORDER_LINES, ORDER_HEADERS, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE 1=1 
      AND CST_ID = CAA_CST_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ORH_ID = ORH_ID 
      AND ORL_ID = :ORL

call     count       cpu    elapsed       disk      query    current


rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      398      0.71       0.77          0          0          0
0


Execute    392      0.29       0.57          0          0          0
0


Fetch      529      2.04       1.93          0      56620          0
4901


------- ------  -------- ---------- ---------- ---------- ----------




total     1319      3.04       3.27          0      56620          0
4901



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (UNIQUE)
      0    FILTER
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS (OUTER)
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_LINES'

      0           INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORL_PK'
(UNIQUE)
      0          INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORH_PK'
(UNIQUE)
      0         TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'STORE_DISTRIBUTIONS'

      0          INDEX   HINT: ANALYZED (RANGE SCAN) OF 'SDI_FK1'
(NON-UNIQUE)
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'MARK_FOR'
      0         INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'MKF_PK'
(UNIQUE)
      0       TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_ADDRESS_ASGT'

      0        INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CAA_PK' (UNIQUE)
      0      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'CUSTOMER_MASTER'

      0       INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'CST_PK' (UNIQUE)

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







SELECT orl_pws_id PWS, 

         orl_id ORL, 
         GSZ_SIZE_CODE "SIZES", 
               LPAD( SUM ( OUL_CUTBACK_QUANTITY ), 5, ' ') qty, 
               GSZ_POSITION_NUMBER IDX 
FROM GARMENT_SIZE,  
           UPC_XREF_DETAIL, 
           ORDER_UPC_LINES, 
           STORE_DISTRIBUTIONS, 
           ORDER_LINES 
WHERE GSZ_ID = UXD_GSZ_ID 
      AND UXD_ID = OUL_UXD_ID 
      AND OUL_SDI_ID = SDI_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY ORL_PWS_ID, ORL_ID,GSZ_SIZE_CODE, GSZ_POSITION_NUMBER 

ORDER BY GSZ_POSITION_NUMBER


call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      399      0.85       0.88          0          0          0
0


Execute    392      0.29       0.23          0          0          0
0


Fetch      392      4.73       4.86          0     163822          0
1611


------- ------  -------- ---------- ---------- ---------- ----------




total     1183      5.87       5.97          0     163822          0
1611



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (GROUP BY)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ORDER_LINES'
      0         INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORL_PK'
(UNIQUE)
      0        TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'STORE_DISTRIBUTIONS'

      0         INDEX   HINT: ANALYZED (RANGE SCAN) OF 'SDI_FK1'
(NON-UNIQUE)
      0       TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_UPC_LINES'

      0        INDEX   HINT: ANALYZED (RANGE SCAN) OF 'OUL_FK1'
(NON-UNIQUE)
      0      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'UPC_XREF_DETAIL'

      0       INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'UXD_PK' (UNIQUE)
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'GARMENT_SIZE'
      0      INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'GSZ_PK' (UNIQUE)

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







SELECT DISTINCT OHC_COMMENTS 



FROM  


   ORDER_HEADER_COMMENTS, 
   ORDER_HEADERS, 
   ORDER_LINES 




WHERE ORL_PWS_ID = :PWS 


and ohc_cmt_code = 'ALLOC' 
AND ORL_ORH_ID = ORH_ID 
and OHC_ORH_ID = ORH_ID

call     count       cpu    elapsed       disk      query    current


rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      248      0.21       0.26          0          0          0
0


Execute    242      0.15       0.22          0          0          0
0


Fetch      242      0.28       0.18          0       3623          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total      732      0.64       0.66          0       3623          0
0



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (UNIQUE)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ORDER_LINES'
      0       INDEX   HINT: ANALYZED (RANGE SCAN) OF 'ORL_I5'
(NON-UNIQUE)
      0      INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'ORH_PK' (UNIQUE)
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_HEADER_COMMENTS'

      0      AND-EQUAL
      0       INDEX   HINT: ANALYZED (RANGE SCAN) OF 'OHC_FK1'
(NON-UNIQUE)
      0       INDEX   HINT: ANALYZED (RANGE SCAN) OF 'OHC_FK2'


(NON-UNIQUE)









SELECT DISTINCT OLC_COMMENTS, 



DECODE ( LENGTH ( ORL_LINE_NUMBER ), 1, 0 || ORL_LINE_NUMBER ||

ORL_LINE_SUB_NUMBER, 


                  ORL_LINE_NUMBER || ORL_LINE_SUB_NUMBER ) LINE_NUMBER 


FROM ORDER_LINE_COMMENTS, ORDER_LINES 



WHERE OLC_ORL_ID = ORL_ID 



and olc_cmt_code = 'ALLOC' 


      AND ORL_PWS_ID = :PWS 



ORDER BY LINE_NUMBER


call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      248      0.28       0.42          0          0          0
0


Execute    242      0.18       0.18          0          0          0
0


Fetch      242      0.15       0.18          0       2084          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total      732      0.61       0.78          0       2084          0
0



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: RULE
      0   SORT (UNIQUE)
      0    NESTED LOOPS
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF 'ORDER_LINES'
      0      INDEX   HINT: ANALYZED (RANGE SCAN) OF 'ORL_I5'
(NON-UNIQUE)
      0     TABLE ACCESS   HINT: ANALYZED (BY ROWID) OF


'ORDER_LINE_COMMENTS'

      0      AND-EQUAL
      0       INDEX   HINT: ANALYZED (RANGE SCAN) OF 'OLC_FK2'
(NON-UNIQUE)
      0       INDEX   HINT: ANALYZED (RANGE SCAN) OF 'OLC_FK1'


(NON-UNIQUE)









Update WORKSHEET_JOBS 


 Set wsj_status = 'IN QUEUE'  


   where wsj_job_id =:JOB



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse      220      0.17       0.17          0          0          0
0


Execute    220      0.21       0.21          0        440        230
220


Fetch        0      0.00       0.00          0          0          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total      440      0.38       0.38          0        440        230
220



Misses in library cache during parse: 0


Optimizer hint: RULE


Parsing user id: 25  (OA)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  UPDATE STATEMENT   HINT: RULE
      1   INDEX   HINT: ANALYZED (UNIQUE SCAN) OF 'WSJ_PK' (UNIQUE)




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







OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS


call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse     2172      3.34       4.01          0          0          0
0


Execute   2129      1.66       1.95          0        440        230
220


Fetch     2051     10.22       9.90          0     307289         12
7679


------- ------  -------- ---------- ---------- ---------- ----------




total     6352     15.22      15.86          0     307729        242
7899



Misses in library cache during parse: 0



 2172  user  SQL statements in session.


    0  internal SQL statements in session.
 2172  SQL statements in session.


    8  statements EXPLAINed in this session.






Trace file: ora_22248.trc


Trace file compatibilty: 7.02.01


Sort options: default



       1  session in tracefile.


    2172  user  SQL statements in trace file.


       0  internal SQL statements in trace file.
    2172  SQL statements in trace file.

       8  unique SQL statements in trace file.
       8  SQL statements EXPLAINed using schema:
           OA.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.




   94515  lines in trace file.






Raw trace file


Dump file /u21/home/oracle/admin/qadb/udump/ora_22248.trc
Oracle7 Server Release 7.3.4.3.1 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.3.1 - Production


ORACLE_HOME = /u21/home/oracle/product/7.3.4

System name:	HP-UX
Node name:	venus
Release:	B.10.20
Version:	E
Machine:	9000/899




Instance name: qadb


Redo thread mounted by this instance: 1


Oracle process number: 35


Unix process pid: 22248, image: oracleqadb




     ORDER_LINES,      STYLE_SIZE,style_master, 
     ORDER_HEADERS, CUSTOMER_MASTER,  
     CUSTOMER_SELLER_ASGT, 
     CUSTOMER_ADDRESS_ASGT, SELLER, CUSTOMER_DEPARTMENT, 
     ADDRESS, ORDER_MARKETS, 
     cg_ref_codes, label_type 




WHERE 1=1 


  and ORH_LBT_ID = LBT_ID (+) 
  and ORH_SPECIAL_ORDER = RV_LOW_VALUE (+) 
  AND CSL_EMP_ID = SLR_EMP_ID(+) 
  AND CSL_ORMK_ID = ORMK_ID (+) 
  AND CAA_STA_STATUS = 'CAA01' 
  AND CAA_ADDRESS_TYPE = 'CO' 
  AND CAA_ADR_ID = ADR_ID 
  AND CAA_CST_ID = CST_ID 
  AND ORH_CSL_ID = CSL_ID 
  AND ORH_CDP_ID = CDP_ID (+) 
  AND ORH_CST_ID = CST_ID 
  and ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
  AND ORL_PWS_ID = PWS_ID 
  AND ALS_LOC_ID = LOC_ID 
  and wsj_als_allocation_group = als_allocation_group 


  and pws_id = wsj_pws_id 


  and wsj_status = 'IN QUEUE' 

  group by       
       WSJ_JOB_ID, 
       WSJ_PWS_ID, 
       pws_version_number,  
       WSJ_PRINTER_NAME, 
       wsj_als_allocation_group, 
       LOC_LOCATION_CODE, 
       CST_CST_NO,  
       CST_NAME, CST_STORE_TYPE , 
       CST_INTERNATIONAL_FLAG, 
       ADR_CITY, ADR_STATE_CODE, 
       ORH_PO_NO, 
       SLR_FIELD_NAME, 
       CDP_NO, 
       ORMK_MARKET, 
       ORH_LOG_NUMBER, 
       ORH_DIV_NO, 
       PWS.CREATED_BY, 
       PWS.CREATION_DATE, 
       PWS.LAST_UPDATED_BY, 
       PWS.LAST_UPDATE_DATE, 
       RV_MEANING,  
       LBT_DESCRIPTION 




order by WSJ_PWS_ID, wsj_job_id desc


END OF STMT



PARSE #1:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030711
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1
p3=0


WAIT #1: nam='SQL*Net more data from client' ela= 19 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #1 len=2282 dep=0 uid=25 oct=3 lid=25 tim=4220030731
hv=2584316821 ad='d7629d38'


SELECT  


       WSJ_JOB_ID JOB,   
       WSJ_PWS_ID PWS, 
       pws_version_number VER,  
       WSJ_PRINTER_NAME "PRINTER", 
       wsj_als_allocation_group ALS, 
       SUM(ORL_CUTBACK_QUANTITY * ORL_PO_PRICE) TOT_PRICE, 
       LOC_LOCATION_CODE "LOC", 
       CST_CST_NO,  
       CST_NAME, CST_STORE_TYPE MS, 
       CST_INTERNATIONAL_FLAG INTL, 
       ADR_CITY CITY, ADR_STATE_CODE ST, 
       ORH_PO_NO PO, 
       SLR_FIELD_NAME SLR, 
       CDP_NO DEPT, 
       ORMK_MARKET MKT, 
       ORH_LOG_NUMBER LOG, 
       ORH_DIV_NO "DIV", 
       PWS.CREATED_BY "BY", 
       PWS.CREATION_DATE "CREATED", 
       PWS.LAST_UPDATED_BY "UP_BY", 
       PWS.LAST_UPDATE_DATE "UP_DATE", 
       RV_MEANING,  
       LBT_DESCRIPTION "LABEL" 




FROM worksheet_jobs, PULL_WORKSHEETS PWS, Allocation_sessions, location,


     ORDER_LINES,      STYLE_SIZE,style_master, 
     ORDER_HEADERS, CUSTOMER_MASTER,  
     CUSTOMER_SELLER_ASGT, 
     CUSTOMER_ADDRESS_ASGT, SELLER, CUSTOMER_DEPARTMENT, 
     ADDRESS, ORDER_MARKETS, 
     cg_ref_codes, label_type 




WHERE 1=1 


  and ORH_LBT_ID = LBT_ID (+) 
  and ORH_SPECIAL_ORDER = RV_LOW_VALUE (+) 
  AND CSL_EMP_ID = SLR_EMP_ID(+) 
  AND CSL_ORMK_ID = ORMK_ID (+) 
  AND CAA_STA_STATUS = 'CAA01' 
  AND CAA_ADDRESS_TYPE = 'CO' 
  AND CAA_ADR_ID = ADR_ID 
  AND CAA_CST_ID = CST_ID 
  AND ORH_CSL_ID = CSL_ID 
  AND ORH_CDP_ID = CDP_ID (+) 
  AND ORH_CST_ID = CST_ID 
  and ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
  AND ORL_PWS_ID = PWS_ID 
  AND ALS_LOC_ID = LOC_ID 
  and wsj_als_allocation_group = als_allocation_group 


  and pws_id = wsj_pws_id 


  and wsj_status = 'IN QUEUE' 

  group by       
       WSJ_JOB_ID, 
       WSJ_PWS_ID, 
       pws_version_number,  
       WSJ_PRINTER_NAME, 
       wsj_als_allocation_group, 
       LOC_LOCATION_CODE, 
       CST_CST_NO,  
       CST_NAME, CST_STORE_TYPE , 
       CST_INTERNATIONAL_FLAG, 
       ADR_CITY, ADR_STATE_CODE, 
       ORH_PO_NO, 
       SLR_FIELD_NAME, 
       CDP_NO, 
       ORMK_MARKET, 
       ORH_LOG_NUMBER, 
       ORH_DIV_NO, 
       PWS.CREATED_BY, 
       PWS.CREATION_DATE, 
       PWS.LAST_UPDATED_BY, 
       PWS.LAST_UPDATE_DATE, 
       RV_MEANING,  
       LBT_DESCRIPTION 




order by WSJ_PWS_ID, wsj_job_id desc


END OF STMT



PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030731
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #1:


EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030731
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1


p3=0


WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1952673875 p2=2044
p3=0


WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1952673875 p2=2030
p3=0


FETCH #1:c=14,e=14,p=0,cr=3414,cu=2,mis=0,r=25,dep=0,og=3,tim=4220030746
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #2 len=849 dep=0 uid=25 oct=3 lid=25 tim=4220030746
hv=2102935013 ad='d78fcfd0'


SELECT DISTINCT 


     ORL_ID ORL, 
     ORL_PWS_ID PWS, 
     ORL_START_DATE Start_DT, 
     ORL_COMPLETION_DATE COMPL_DT, 
     ORL_IN_STORE_DATE IN_STORE, 
     ORL_LINE_NUMBER LN, 
     ORL_LINE_SUB_NUMBER SUB, 
     ORL_DIV_NO "Div", 
     ORL_SHIPPED_STYLE_NO STYLE, 
     ORL_CUSTOMER_STYLE_NO cST_STYLE, 
     ORL_NRMA_CODE NRMA, 
     ORL_COLOR_TEXT COLOR, 
     ORL_QUANTITY QTY, 
     ORL_CUTBACK_QUANTITY CBACK, 
     ORH_LOG_NUMBER LOG, 
     CLP_DESCRIPTION COLOR_TXT, 
     SIZ_DESCRIPTION "SIZES", 
     nvl(orl_case_bundles,0) bundles, 
     nvl(orl_bundle_quantity,0) bundle_qty 
 FROM ORDER_HEADERS, STYLE_SIZE, ORDER_LINES, 
           COLOR_PATTERN, STYLE_MASTER 
WHERE ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
      AND ORL_NRMA_CODE = CLP_NRMA_CODE 
      AND ORL_PWS_ID = :PWS




END OF STMT



PARSE #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030747
WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #2 len=849 dep=0 uid=25 oct=3 lid=25 tim=4220030747
hv=2102935013 ad='d78fcfd0'


SELECT DISTINCT 


     ORL_ID ORL, 
     ORL_PWS_ID PWS, 
     ORL_START_DATE Start_DT, 
     ORL_COMPLETION_DATE COMPL_DT, 
     ORL_IN_STORE_DATE IN_STORE, 
     ORL_LINE_NUMBER LN, 
     ORL_LINE_SUB_NUMBER SUB, 
     ORL_DIV_NO "Div", 
     ORL_SHIPPED_STYLE_NO STYLE, 
     ORL_CUSTOMER_STYLE_NO cST_STYLE, 
     ORL_NRMA_CODE NRMA, 
     ORL_COLOR_TEXT COLOR, 
     ORL_QUANTITY QTY, 
     ORL_CUTBACK_QUANTITY CBACK, 
     ORH_LOG_NUMBER LOG, 
     CLP_DESCRIPTION COLOR_TXT, 
     SIZ_DESCRIPTION "SIZES", 
     nvl(orl_case_bundles,0) bundles, 
     nvl(orl_bundle_quantity,0) bundle_qty 
 FROM ORDER_HEADERS, STYLE_SIZE, ORDER_LINES, 
           COLOR_PATTERN, STYLE_MASTER 
WHERE ORL_ORH_ID = ORH_ID 
      AND ORL_SHIPPED_STM_ID = STM_ID 
      AND STM_SIZ_ID = SIZ_ID 
      AND ORL_NRMA_CODE = CLP_NRMA_CODE 
      AND ORL_PWS_ID = :PWS




END OF STMT



PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030747
WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #2:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400d01f8 bln=22 avl=04 flg=05


   value=235823

EXEC #2:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030748
WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1


p3=0


WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
FETCH #2:c=0,e=0,p=0,cr=38,cu=0,mis=0,r=3,dep=0,og=3,tim=4220030748
WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #3 len=454 dep=0 uid=25 oct=3 lid=25 tim=4220030749
hv=208411543 ad='d78df038'


SELECT 


       orl_id orl, 
       COUNT (DISTINCT(CST_NO || CAA_SHIPTO_NO || MKF_SUFFIX_NO ||
MKF_LOCATION))  NUM, 
       RPAD (SDI_CUTBACK_QUANTITY, 4 , ' ') QTY 
FROM ORDER_LINES, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE CST_ID = CAA_CST_ID 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY orl_id,SDI_CUTBACK_QUANTITY


END OF STMT



PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030749
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #3 len=454 dep=0 uid=25 oct=3 lid=25 tim=4220030749
hv=208411543 ad='d78df038'


SELECT 


       orl_id orl, 
       COUNT (DISTINCT(CST_NO || CAA_SHIPTO_NO || MKF_SUFFIX_NO ||
MKF_LOCATION))  NUM, 
       RPAD (SDI_CUTBACK_QUANTITY, 4 , ' ') QTY 
FROM ORDER_LINES, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE CST_ID = CAA_CST_ID 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY orl_id,SDI_CUTBACK_QUANTITY


END OF STMT



PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030749
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #3:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400b7220 bln=22 avl=05 flg=05


   value=11219562

EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030749
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1


p3=0


WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=14,cu=0,mis=0,r=1,dep=0,og=3,tim=4220030750
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #4 len=627 dep=0 uid=25 oct=3 lid=25 tim=4220030750
hv=3456517591 ad='d78bd850'


SELECT 



  DISTINCT 


      orl_pws_id pws, 
      orl_id ORL, 
      CST_NO,  
      CAA_SHIPTO_NO STORE_NO,  
      MKF_SUFFIX_NO "SUFFIX",  
      MKF_LOCATION "MKF_LOC", 
      sdi_quantity qty, 
      sdi_cutback_quantity cback, 
      sdi_ratio_exception_flag rflag, 
      sdi_ratio_cutback_flag rcflag       
FROM  ORDER_LINES, ORDER_HEADERS, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE 1=1 
      AND CST_ID = CAA_CST_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ORH_ID = ORH_ID 
      AND ORL_ID = :ORL




END OF STMT



PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030750
WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #4 len=627 dep=0 uid=25 oct=3 lid=25 tim=4220030751
hv=3456517591 ad='d78bd850'


SELECT 



  DISTINCT 


      orl_pws_id pws, 
      orl_id ORL, 
      CST_NO,  
      CAA_SHIPTO_NO STORE_NO,  
      MKF_SUFFIX_NO "SUFFIX",  
      MKF_LOCATION "MKF_LOC", 
      sdi_quantity qty, 
      sdi_cutback_quantity cback, 
      sdi_ratio_exception_flag rflag, 
      sdi_ratio_cutback_flag rcflag       
FROM  ORDER_LINES, ORDER_HEADERS, STORE_DISTRIBUTIONS, 
      CUSTOMER_ADDRESS_ASGT, MARK_FOR, CUSTOMER_MASTER 
WHERE 1=1 
      AND CST_ID = CAA_CST_ID 
      AND SDI_MKF_ID = MKF_ID (+) 
      AND SDI_SHIPTO_CAA_ID = CAA_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ORH_ID = ORH_ID 
      AND ORL_ID = :ORL




END OF STMT



PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030751
WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #4:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400bf920 bln=22 avl=05 flg=05


   value=11219562

EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030751
WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1


p3=0


WAIT #4: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
FETCH #4:c=0,e=0,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=3,tim=4220030751
WAIT #4: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1
p3=0


WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #5 len=557 dep=0 uid=25 oct=3 lid=25 tim=4220030752
hv=3034731954 ad='d789f528'


SELECT orl_pws_id PWS, 

         orl_id ORL, 
         GSZ_SIZE_CODE "SIZES", 
               LPAD( SUM ( OUL_CUTBACK_QUANTITY ), 5, ' ') qty, 
               GSZ_POSITION_NUMBER IDX 
FROM GARMENT_SIZE,  
           UPC_XREF_DETAIL, 
           ORDER_UPC_LINES, 
           STORE_DISTRIBUTIONS, 
           ORDER_LINES 
WHERE GSZ_ID = UXD_GSZ_ID 
      AND UXD_ID = OUL_UXD_ID 
      AND OUL_SDI_ID = SDI_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY ORL_PWS_ID, ORL_ID,GSZ_SIZE_CODE, GSZ_POSITION_NUMBER 

ORDER BY GSZ_POSITION_NUMBER



END OF STMT



PARSE #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030752
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #5 len=557 dep=0 uid=25 oct=3 lid=25 tim=4220030752
hv=3034731954 ad='d789f528'


SELECT orl_pws_id PWS, 

         orl_id ORL, 
         GSZ_SIZE_CODE "SIZES", 
               LPAD( SUM ( OUL_CUTBACK_QUANTITY ), 5, ' ') qty, 
               GSZ_POSITION_NUMBER IDX 
FROM GARMENT_SIZE,  
           UPC_XREF_DETAIL, 
           ORDER_UPC_LINES, 
           STORE_DISTRIBUTIONS, 
           ORDER_LINES 
WHERE GSZ_ID = UXD_GSZ_ID 
      AND UXD_ID = OUL_UXD_ID 
      AND OUL_SDI_ID = SDI_ID 
      AND SDI_ORL_ID = ORL_ID 
      AND ORL_ID = :ORL 




GROUP BY ORL_PWS_ID, ORL_ID,GSZ_SIZE_CODE, GSZ_POSITION_NUMBER 

ORDER BY GSZ_POSITION_NUMBER



END OF STMT



PARSE #5:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030752
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #5:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400b7220 bln=22 avl=05 flg=05


   value=11219562

EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030753
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1


p3=0


WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
FETCH #5:c=0,e=0,p=0,cr=48,cu=0,mis=0,r=4,dep=0,og=3,tim=4220030753
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #6 len=200 dep=0 uid=25 oct=3 lid=25 tim=4220030753
hv=204020377 ad='d6757ba0'


SELECT DISTINCT OHC_COMMENTS 



FROM  


   ORDER_HEADER_COMMENTS, 
   ORDER_HEADERS, 
   ORDER_LINES 




WHERE ORL_PWS_ID = :PWS 


and ohc_cmt_code = 'ALLOC' 
AND ORL_ORH_ID = ORH_ID 
and OHC_ORH_ID = ORH_ID




END OF STMT



PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030753
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #6 len=200 dep=0 uid=25 oct=3 lid=25 tim=4220030754
hv=204020377 ad='d6757ba0'


SELECT DISTINCT OHC_COMMENTS 



FROM  


   ORDER_HEADER_COMMENTS, 
   ORDER_HEADERS, 
   ORDER_LINES 




WHERE ORL_PWS_ID = :PWS 


and ohc_cmt_code = 'ALLOC' 
AND ORL_ORH_ID = ORH_ID 
and OHC_ORH_ID = ORH_ID




END OF STMT



PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030754
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #6:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400d01f8 bln=22 avl=04 flg=05


   value=235823

EXEC #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030754
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1


p3=0


FETCH #6:c=0,e=0,p=0,cr=26,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030754
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #7 len=332 dep=0 uid=25 oct=3 lid=25 tim=4220030755
hv=506450568 ad='d674f5b0'


SELECT DISTINCT OLC_COMMENTS, 



DECODE ( LENGTH ( ORL_LINE_NUMBER ), 1, 0 || ORL_LINE_NUMBER ||

ORL_LINE_SUB_NUMBER, 


                  ORL_LINE_NUMBER || ORL_LINE_SUB_NUMBER ) LINE_NUMBER 


FROM ORDER_LINE_COMMENTS, ORDER_LINES 



WHERE OLC_ORL_ID = ORL_ID 



and olc_cmt_code = 'ALLOC' 


      AND ORL_PWS_ID = :PWS 



ORDER BY LINE_NUMBER



END OF STMT



PARSE #7:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030755
WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0




PARSING IN CURSOR #7 len=332 dep=0 uid=25 oct=3 lid=25 tim=4220030755
hv=506450568 ad='d674f5b0'


SELECT DISTINCT OLC_COMMENTS, 



DECODE ( LENGTH ( ORL_LINE_NUMBER ), 1, 0 || ORL_LINE_NUMBER ||

ORL_LINE_SUB_NUMBER, 


                  ORL_LINE_NUMBER || ORL_LINE_SUB_NUMBER ) LINE_NUMBER 


FROM ORDER_LINE_COMMENTS, ORDER_LINES 



WHERE OLC_ORL_ID = ORL_ID 



and olc_cmt_code = 'ALLOC' 


      AND ORL_PWS_ID = :PWS 



ORDER BY LINE_NUMBER



END OF STMT



PARSE #7:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030755
WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1
p3=0


BINDS #7:



 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01
   bfp=400b0e88 bln=22 avl=04 flg=05


   value=235823

EXEC #7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030755
WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 0 p1=1952673875 p2=1


p3=0


FETCH #7:c=0,e=0,p=0,cr=14,cu=0,mis=0,r=0,dep=0,og=3,tim=4220030755
WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1952673875 p2=1 p3=0
WAIT #7: nam='SQL*Net message from client' ela= 1 p1=1952673875 p2=1
p3=0

STAT #3 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT GROUP BY '
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=4 cnt=0 pid=3 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=5 cnt=1 pid=4 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=6 cnt=1 pid=5 pos=1 obj=30657 op='INDEX UNIQUE SCAN '
STAT #3 id=7 cnt=1 pid=5 pos=2 obj=27804 op='TABLE ACCESS BY ROWID


STORE_DISTRIBUTIONS '



STAT #3 id=8 cnt=2 pid=7 pos=1 obj=30780 op='INDEX RANGE SCAN '
STAT #3 id=9 cnt=0 pid=4 pos=2 obj=20465 op='TABLE ACCESS BY ROWID
MARK_FOR '



STAT #3 id=10 cnt=1 pid=9 pos=1 obj=20466 op='INDEX UNIQUE SCAN '
STAT #3 id=11 cnt=1 pid=3 pos=2 obj=20367 op='TABLE ACCESS BY ROWID
CUSTOMER_ADDRESS_ASGT '



STAT #3 id=12 cnt=1 pid=11 pos=1 obj=20387 op='INDEX UNIQUE SCAN '
STAT #3 id=13 cnt=1 pid=2 pos=2 obj=25977 op='TABLE ACCESS BY ROWID
CUSTOMER_MASTER '



STAT #3 id=14 cnt=1 pid=13 pos=1 obj=25980 op='INDEX UNIQUE SCAN '



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mandar A. Ghosalkar
  INET: mghosalk_at_byer.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Thu Aug 22 2002 - 17:05:32 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US