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