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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
