tkprof Trace Output Evaluation

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Mon, 01 Oct 2001 13:16:36 -0700
Message-ID: <F001.0039DF24.20011001132041@fatcity.com>

Under the Column "count" , Is a SMALL Value of "Execute" ( = 2) Versus a Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for performance ?

NOTE -
TBA_FLEXI_FIXED_PARM_TBL Contains 600,000 records only 2 indexes exist on TBA_FLEXI_FIXED_PARM_TBL Index 1 = UNIQUE index on acid
Index 2 = Index on sol_id

TBA_GENERAL_ACCT_MAST_TBL Contains about 11,000,000 records

Following is the Detailed Trace :-

---

---

SELECT A.ACID
FROM
 TBA_FLEXI_FIXED_PARM_TBL A, TBA_GENERAL_ACCT_MAST_TBL B WHERE B.sol_id in (
  SELECT sol_id from SST WHERE set_id = :1 ) AND B.ACID = A.ACID AND   ( A.NEXT_AUTO_CR_DATE IS NULL OR A.NEXT_AUTO_CR_DATE <= TO_DATE( :2 ,

  'DD-MM-YYYY HH24:MI:SS') OR (   A.AUTO_CR_FREQ_HLDY_STAT = 'P' AND
  A.NEXT_AUTO_CR_DATE >TO_DATE( :3 , 'DD-MM-YYYY HH24:MI:SS') AND
  A.NEXT_AUTO_CR_DATE <TO_DATE( :4 ,'DD-MM-YYYY HH24:MI:SS') ) )    AND
  A.DEL_FLG != 'Y' AND A.ENTITY_CRE_FLG = 'Y' AND

  A.AUTO_CR_FROM_OPER_ACCT_FLG = 'Y' ORDER BY B.CRNCY_CODE,   B.ACCT_CRNCY_CODE, B.SCHM_CODE, B.FORACID call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.01 0.01 0 0 0 0
Execute 2 0.01 0.01 0 0 0 0
Fetch 2525 78.62 111.15 77441 2538463 11 25248
------- ------ -------- ---------- ---------- ---------- ----------

---

total 2528 78.64 111.17 77441 2538463 11 25248

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (TBAGEN)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      PARTITION RANGE (ALL) PARTITION: START=1 STOP=2
      0       TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                  'FLEXI_FIXED_PARM_TABLE' PARTITION: START=1 STOP=2
      0      TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
                 'GENERAL_ACCT_MAST_TABLE' PARTITION:ROW LOCATION
      0       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                  'IDX_GENERAL_ACCT_MAST_TABLE' (UNIQUE)
      0     VIEW OF 'VW_NSO_1'
      0      SORT (UNIQUE)
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'IDX_SOL_ID_SET_TABLE' (UNIQUE)



************************************************************************

---

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.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 Mon Oct 01 2001 - 15:16:36 CDT