A Small Addition :- SST Table only has 50 Records with index on (set_id,sol_id)
Qs. Under the Column "count" , Is a SMALL Value of "Execute" ( = 2) Versus a Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for performance ? Qs. What does the above mean to indicate ? I am Quite basic in tkprof evaluation . I have Generally Seen "count-Execute" EQUAL to "count-Fetch" , for Other Traces Thanks so much > -----Original Message----- > From: Bill Zakrzewski [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, October 02, 2001 1:59 AM > To: LazyDBA.com Discussion; VIVEK_SHARMA > Subject: Re: tkprof Trace Output Evaluation > > I quickly looked at your query and one improvement you can make is to > remove > the > > "WHERE B.sol_id > in ( > SELECT sol_id from SST WHERE set_id = :1 ) " > > and replace it with > > WHERE exists (select 'x' from SST where sol_id=B.sol_id > and set_id = :1) > > > ----- Original Message ----- > From: "VIVEK_SHARMA" <[EMAIL PROTECTED]> > To: "LazyDBA.com Discussion" <[EMAIL PROTECTED]> > Sent: Monday, October 01, 2001 4:18 PM > Subject: tkprof Trace Output Evaluation > > > > > 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) > > ********************************************************************** > ** > ******** > > > > -------- > Oracle documentation is here: > http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > To unsubscribe: send a blank email to > [EMAIL PROTECTED] > To subscribe: send a blank email to [EMAIL PROTECTED] > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > Tell yer mates about http://www.farAwayJobs.com > By using this list you agree to these > terms:http://www.lazydba.com/legal.html > > > -- 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).
