Oracle query transformation deactivation

Sometimes I'm going nuts over different query execution plans in development, integration tests, and productive systems. Apart from the usual analysis I run, I just want to know:

Can some of the query optimiser's transformation operations be deactivated on a system level (just as they can be deactivated on a per-query level using hints)?

In this case, I'd expect a UNION ALL PUSHED PREDICATE operation for a query looking roughly like this:

SELECT ...
FROM (SELECT ... FROM A
      UNION ALL
      SELECT ... FROM B)
WHERE X = :B1

A and B are views, both selecting from the same tables containing X, where X is the primary key. It is important that the selection for X is pushied into both views A and B before fetching all of A and B's data. And it's also possible, because no complex transformations are required.

So apart from deactivated indexes, bad statistics, bind variable peeking issue, and all the other usual suspects, 开发者_StackOverflowis there a possibility that the whole Oracle instance just can't do one or two transformations because they're switched off?

---

Yes. Various and sundry initialization parameters control query transformation and optimization, and a significant number of them aren't documented.

The following query shows all the undocumented parameters, at least for 10g:

SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value"
  FROM x$ksppi a,
       inner join x$ksppc b
          on a.indx = b.indx
       inner join x$ksppsv c
          on a.indx = c.indx
 WHERE a.ksppinm LIKE '/_%' escape '/'
/

Similarly, setting event 10053 will generate an optimization trace file, which will show what parameters (documented or otherwise) affected the generation of the query plan.

---

If you want to have stable execution plans across different instances, you be able to achieve this with exporting statistics on the reference system and importing them into the others.

Examples can be found in the manual and here

You might also want to lock the statistics in the target environments after the import so that they are not changed.

---

There are a number of database initialization parameters that can enable or disable various optimizer options and different query transformations. So if you have different initialization parameters set in different environments, you can definitely end up in a situation where one environment can do a particular transform and another cannot despite having identical data structures and statistics.

In the case of this particular query, my mind goes immediately to the OPTIMIZER_SECURE_VIEW_MERGING parameter. That definitely has the potential to cause problems for this particular type of construct.