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: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Thu, 22 Aug 2002 13:12:03 -0800
Message-ID: <F001.004BD8A3.20020822131203@fatcity.com>


First, you have to change a few numbers: String "TKPROF: Release 7.1.6.2.0" needs to look like "TKPROF: Release 9.2.0.1.0" and then you can use the argument waits=yes to TKPROF command. The other solution is to submit your file (non-tkprofed) to http://www.hotsos.com and they'll analyze it for you.

> -----Original Message-----
> From: Mandar A. Ghosalkar [mailto:mghosalk_at_byer.com]
> Sent: Thursday, August 22, 2002 4:41 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Help with 10046 level 12 trace
>
>
> 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
>
> *** 2002.08.22.11.59.45.177
> *** SESSION ID:(42.277) 2002.08.22.11.59.45.111
> 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 more data from client' ela= 1
> p1=1952673875 p2=1 p3=0
> =====================
> PARSING IN CURSOR #1 len=2282 dep=0 uid=25 oct=3 lid=25
> tim=4220030711 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=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: Gogala, Mladen
  INET: MGogala_at_oxhp.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 - 16:12:03 CDT

Original text of this message

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