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