Re: optimizer parameters

• Original Message -----
  ]From: "Martin Brown" <martinfbrown_at_hotmail.com>
  ]To: <mwf_at_rsiz.com>; <niall.litchfield_at_gmail.com>; <eglewis71_at_gmail.com>
  ]Cc: <oracle-l_at_freelists.org>
  ]Sent: Wednesday, April 27, 2011 4:29 PM
  ]Subject: RE: optimizer parameters
  ]
  

]
]To me, this is the Oracle topic with the most varied opinions and without a
definative answer. I use a "gather auto" method and monitor with a "list stale" procedure I set up, thinking the "gather auto" would be sufficient to maintain statistics with the least amount of process time. I watch execution plans change as tables *DO NOT* go stale and I'm at a complete loss to figure out why. I used the O_I_C and O_I_C_A briefly after watching the contents of V$BH over a long period of time as Donnie B has recommended. But for my app, they amounted to fly crap in the pepper shaker.
]
] So I'm still trying to figure out why plans change when stats don't.
]

Often it's because the queries start asking for "newer" data, and the old stats say that it doesn't exist - or that there's less of it than there used to be. From 10g Oracle pro-rates cardinalities as your queries move outside the low/high values for a column.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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