Re: FTS in SQL Query ... Advice please?

On 03/05/2007 02:42:17 AM, VIVEK_SHARMA wrote:
>
> Folks
>
>
>
> Qs What Options exist to avoid FTS on GAM in the below SQL Query?

Index it properly and compute histograms for the columns involved.

>
>
>
> 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?

Which version of the database? Did you compute system statistics?

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

That means a whole lot to me.

>
> 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"

The next 3 conditions look like a good candidate to have a composite index. Also, compute the columns histograms for all columns. Shake rattle and roll the statistics to make the index cheap. Massage the clustering factor for the index.

>
> 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)

3M physical reads? I believe I know why you would want to avoid FTS on that table.

>
> 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)
>

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l