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

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

Help with 10046 level 12 trace

From: Mandar A. Ghosalkar <mghosalk_at_byer.com>
Date: Thu, 22 Aug 2002 12:41:18 -0800
Message-ID: <F001.004BD73C.20020822124118@fatcity.com> 





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












Original text of this message













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