0

Is there a way in Oracle 10G to tell the CBO that it should never consider/ Reject a particular PLAN of Execution (If we know the Plan Hash Value and if we know that the given plan always provokes performance issues).

Likewise, is there a way to tell it a list of Plans that are Good to use?

Guddu
  • 1,588
  • 4
  • 25
  • 53

2 Answers2

1

You can not "reject" particular execution plan. But you can "enforce" specific execution plan but using "Stored Outlines". Simply you let Oracle to create set of hints which will enforce specific exec plan. But this should be considered as last resort solution. These Outlines have several gotchas like

  • The should be regenerated after every patching

  • They may need LOGON TRIGGER to be enforced If you can not embed them into SQL which is hardcoded inside closed source application

Proper indexing, partitioning, dynamic sampling or lock on table stats is generally better solution.

Plus if you do not have a license for AWR you can still use it's predecessor STATSPACK. If you collect STATSPACK stats regularly you can get history of the queries exec plans including average buffer gets and disk reads per execution for every "historical" exec plan.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
0

Use "Hints" to alter the execution plan. There's a lot of information on Hints, but my experience is that, CBO is dynamic whereas, hints, once they are in, the performance will always be the same (which is dangerous if the data changes significantly, as they could degrade performance in the long run)

Here is an example of a hint:

select /*+ index(scott.emp,dept_idx) */ * from emp

Here is where you can learn more about them: http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm

In my experience, I've had situations where Hints have dramatically increased the running time of a complex SQL Query.

Roberto Navarro
  • 948
  • 4
  • 16