FTS in SQL Query ... Advice please?

Folks

Qs What Options exist to avoid FTS on GAM in the below SQL Query?

NOTE - GAM Table was RE-Created & RE-Analyzed again about 15 Days After EAB. db_file_multiblock_read_count=16 - should it be reduced to 8?

NOTE - GAM PARTITION BY RANGE ("SOL_ID") - has 13 indexs - Index 1 - Unique index on ACID

EAB PARTITION BY RANGE ("ACID") - has 2 indexes - Index 1 - Unique index
( acid, eod_date ), Index 2 - (end_eod_date,acid,eod_date)

SELECT COUNT(*), NVL(SUM(EAB.TRAN_DATE_BAL),:"SYS_B_0") , GAM.acct_crncy_code

FROM GAM, EAB WHERE GAM.ACID = EAB.ACID AND GAM.del_flg != :"SYS_B_1"

AND GAM.entity_cre_flg = :"SYS_B_2"

AND GAM.acct_prefix = :"SYS_B_3"

AND GAM.acct_num = :"SYS_B_4"

AND GAM.acct_crncy_code = :"SYS_B_5"

AND EAB.eod_date <= TO_DATE(:"SYS_B_6" , :"SYS_B_7" )

AND EAB.end_eod_date >= TO_DATE(:"SYS_B_8" , :"SYS_B_9")

GROUP BY GAM.acct_crncy_code

call count cpu elapsed disk query current rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.01 0.00 0 0 0 0

Fetch 1 231.70 377.55 3009679 3011675 0 0

• ------ -------- ---------- ---------- ---------- ----------

  ---

total 3 231.71 377.56 3009679 3011675 0 0

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: CHOOSE

Parsing user id: 18 (TBAADM)

Rows Row Source Operation

• ---------------------------------------------------

      0 SORT GROUP BY NOSORT (cr=3011675 pr=3009679 pw=0 time=377555388 us)

      0 TABLE ACCESS BY LOCAL INDEX ROWID EOD_ACCT_BAL_TABLE
(cr=3011675 pr=3009679 pw=0 time=377555361 us)

      1 NESTED LOOPS (cr=3011675 pr=3009679 pw=0 time=377555338 us)

      0 PARTITION RANGE ALL PARTITION: 1 16 (cr=3011675 pr=3009679 pw=0 time=377555329 us)

      0 TABLE ACCESS FULL GENERAL_ACCT_MAST_TABLE PARTITION: 1 16
(cr=3011675 pr=3009679 pw=0 time=377555131 us)

      0 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us)

      0 INDEX RANGE SCAN IDX_EOD_ACCT_BAL_TABLE PARTITION: KEY KEY
(cr=0 pr=0 pw=0 time=0 us)(object id 93112)

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l