Re: optimizer parameters

From: Jonathan Lewis <>
Date: Wed, 27 Apr 2011 17:17:14 +0100
Message-ID: <D7EF4205CD5B43028B9A6325E3E0F253_at_Primary>

  • Original Message -----
    ]From: "Martin Brown" <>
    ]To: <>; <>; <>
    ]Cc: <>
    ]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.


Jonathan Lewis

Received on Wed Apr 27 2011 - 11:17:14 CDT

Original text of this message