FTS in SQL Query ... Advice please?
- From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Mar 2007 13:12:17 +0530
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***
Other related posts:
