| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with 10046 level 12 trace
Mladen,
Oracle has made a public statement strongly discouraging the use of Oracle Trace, saying that it is now deprecated in favor of 10046 data. My understanding is that Oracle Trace (like the RBO) won't exist in 10i. My hope is that Oracle10i will contain a well-defined API to 10046 data.
Looking for your trace files needn't be a bother to you. That's why we wrote Sparky; it puts a nice GUI in front of starting and stopping the 10046 event, and it finds and fetches the right file for you from the appropriate %_dump_dest directory. It's free at www.hotsos.com/products/sparky.
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-----
Mladen
Sent: Friday, August 23, 2002 1:13 PM
To: Multiple recipients of list ORACLE-L
TKPROF in 9i does ignore some useful data, but that's not the
worst part. The worst part is that I cannot get a useful summary
like the one provided by your profiler. Oh, well, TKPROF in 10i
may actually do what I want it to do.
Tkprof improvements are not, however, on the top of my wish list. The
real thing that I'd like to see is ORACLE_TRACE. So far, it has been
dangerous. I've tried it on my 8.1.7 instances and it BRINGS THEM
DOWN!!!!
There is a critical bug which will bring down your instance if
ORACLE_TRACE_ENABLED is set to TRUE. Looking for *.trc files in
USER_DUMP_DEST is a painstakingly slow process and Oracle Trace Manager
from OEM is just what I need, provided I can get all the information
as from 10046 and in a nice, GUI way.
Mladen Gogala
Oracle DBA
Phone: (203) 459-6855
Email: mgogala_at_oxhp.com
> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Thursday, August 22, 2002 6:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Help with 10046 level 12 trace
>
>
> I'll second that motion. Oddly, though, it's even worse than
> that: This
> code is actually parsing MORE times than it's executing! ("Parse.
> Thanks, but nevermind. Okay, now parse the same thing again. Okay, now
> execute...")
>
> I pulled the trigger pretty quickly a minute ago when I suggested that
> Mandar should upload the file to our site. There's a lot you can learn
> from tkprof output alone. However, amplifying Mladen's point a little
> bit, you're losing a lot of data by viewing raw trace files
> with tkprof.
> For example, are so many parse calls causing latch contention to be a
> significant component of response time? With tkprof output,
> we can only
> guess.
>
> <ad>Even tkprof 9i output ignores significant amounts of useful
> data.</ad>
>
> We'll be glad to look at the raw trace file. Just let me know if you'd
> like us to.
>
>
> 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-----
> Douglass
> Sent: Thursday, August 22, 2002 4:20 PM
> To: Multiple recipients of list ORACLE-L
>
> Well, I'm sure a guru here will correct me if I am wrong, but at first
> blush it looks like your developer is not only parsing, executing and
> fetching in a loop, but is also parsing a few extra times
> just for fun.
> I'm pretty sure you will find that the bulk of your
> performance problem
> lies there.
>
> == the bad way ==
> loop
> parse
> execute
> fetch
> close
> end loop
>
> == the good way ==
> parse
> execute
> loop
> fetch
> end loop
> close
> --
> Philip Douglass
> Internet Networking Group
> Database Administrator
> SIRS Mandarin, Inc.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, August 22, 2002 4:41 PM
>
>
> 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: Philip Douglass
> INET: philipd_at_sirs.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).
>
-- 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). -- 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 Fri Aug 23 2002 - 14:48:49 CDT
 |
 |