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

Folks

My replies embedded below prefixed by "=>"

Cheers

-----Original Message-----
From: Mladen Gogala [mailto:mgogala_at_verizon.net]
> Folks

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

Ans Index it properly and compute histograms for the columns involved.

=> How to know if an index is a Candidate for "compute histograms", any guidelines?

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?

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

=> Oracle ver 10.2.0.2 on 2-Node RAC.

=> EAB Table partitions' Statistics were taken at estimate_percent=>30. (This is because the Table Size is Large i.e. 40 GB)

=> EAB's BOTH indexes computed at 100 %

=> GAM Table partitions' Statistics computed at 100 %

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"

Ans 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.

=> Qs Some sample Commands to make index cheap & Massage Clustering factor please?

> 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

>

.. .

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