RE: Statistics in Rule based database

I run DBMS_STATS.GATHER_SCHEMA_STATS of my R11.0.3 [8.1.7] database.

There are a very higher number of customised reports running and the "longer-running" ones are
now flying with a /*+ CHOOSE */ hint !

Definitely collect statistics even if the optimizer_mode is RULE.

Hemant
At 09:16 AM 25-04-03 -0800, you wrote:
>Dennis,
>I know that Oracle tells you to analyze tables for Apps (both 10.7 & 11.0)
>because even though the optimizer is set to rule, some of the delivered
>code may have cost based hints in it. I usually analyze once a week on
>one of my Apps databases & the one week it failed, a lot of processes ran
>much slower. You might check the slow-running queries for hints.
>HTH
>Denise
>
>
>-----Original Message-----
>Sent: Friday, April 25, 2003 11:27 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi All,
>Oracle 8.0.5
>HP 11.0
>Oracle Apps 11.0.3
>What happens if we analyze the database on rule based optimizer?
>I have an Apps database with optimizer init parameter set to 'rule' and has
>some statistics in it ( previous DBA set this up). I have noticed that with
>statistics it seems to be using cost base optimizer because the query plan
>changes during different sampling period. I deleted the statistics from the
>database and the query plan became horrific with full table scans all over
>the place.
>Can anybody explain to me what is going on here?
>
>TIA
>
>Dennis
>
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Meng, Dennis
> INET: Dennis.Meng_at_alcoa.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Agliori, Denise N.
> INET: agliori_at_ppg.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).