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

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

RE: Help with 10046 level 12 trace

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 22 Aug 2002 14:14:15 -0800
Message-ID: <F001.004BDA78.20020822141415@fatcity.com>


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.

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
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).
Received on Thu Aug 22 2002 - 17:14:15 CDT

Original text of this message

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