On Mon, Oct 8, 2012 at 9:57 AM, Chris Dunscombe <cdunscombe@xxxxxxxxx>wrote: > Hi, > We've got a large RAC database where we've been using SPBs for about 6 > months. The SPBs were generated in the perf test environment and then > migrated across. There are still plenty of SQL statements in live that > aren't using SPBs. Now people are asking about setting > > OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE > > in production, we've run with setting in perf test with full volume > testing and not seen any obvious bad side effects. My natural reaction is > to be cautious and say no. > > Am I being sensible, overly cautious or what? Anybody's experiences in > this area would be most welcome. > > Version 11.2.0.3 on RHEL 5.6 > > Hi, There is couple of gotchas : - SPB is consuming space in SYSAUX - you need good evolve and check procedure - automatic evolve procedure could activate a non-optimal plan and you need to procedure to capture that easily and fix it - bugs like after dropping index Oracle is always using FTS even if other indexes are available But number one is WHY do you want to use plan stability feature ? If all your SQL are optimized by CBO in wrong way it is very likely that your statistics and/or system parameters are not describing your data in correct way and CBO can't produce proper plans. Did you reviewed problem from that perspective ? Of course it is possible that your data / application need forced plan stability but in my opinion it should be used in limited scope only for queries that misbehave due to strange/unknown circumstances. regards, -- Marcin Przepiorowski http://oracleprof.blogspot.com -- //www.freelists.org/webpage/oracle-l