Re: optimizer parameters
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 27 Apr 2011 17:17:14 +0100
----- Original Message -----
]From: "Martin Brown" <martinfbrown@xxxxxxxxxxx>
]To: <mwf@xxxxxxxx>; <niall.litchfield@xxxxxxxxx>; <eglewis71@xxxxxxxxx>
]Cc: <oracle-l@xxxxxxxxxxxxx>
]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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
