[Oracle] DB Optimizer Part VIII - Looking under th...

stefan_koehler · ‎01-05-2014

Introduction

We investigated and talked about some basic cardinality calculations of the Oracle cost based optimizer (CBO) in some of my previous blog posts. However in most cases there is much more happening under the hood, which leads to some issues from time to time as discussed here on SCN for example.

Initially i did not plan to write a blog post about (cost based) query transformations in general as this is a very complex, wide and continuously changing / improving topic, but then i received an e-mail from a client a few weeks ago with a very simple example of it (the later explained example occurred in a SAP ECC environment). Basically said, the client noticed different costs in an execution plan for the same SQL statement on two different database (exact the same version of course), even if all the object / system statistics (which were exported and imported) and database parameter settings were the same.

Let's check the official documentation and white papers first to get an idea of query transformations.

Footnote: Unfortunately the (cost based) query transformation behavior is not officially documented by Oracle in detail. So the linked references and white papers are mostly written by "third party" researchers or Oracle employees, but all of these publishers are a very trustable source of information. If you are interested into this topic you will see that it is really valuable source of information.

Query transformation - What is it and why do we need it?

The following statements are valid for Oracle RDBMS versions Oracle 10g or higher as the "Cost based query transformation" (CBQT) feature was introduced with Oracle 10g R1.

SQL Engine / SQL statement processing

** The screenshots are taken from the documents "Closing The Query Processing Loop in Oracle 11g" and "Query Transformations" **

** For more details check the reference section **

I knowingly do not include the "Optimizer components" graphic from the official Oracle documentation as this one looks like the SQL statement processing / optimization is a "straight forward process", but in reality the optimization engine has a feedback loop to the transformation engine since Oracle 10g. This is illustrated very good in the right graphic.

Let's take another step back and re-check the SQL statement processing in general without considering the interaction between the SQL executing engine and SQL compiler (e.g. cardinality feedback or ACS).

Source: "Closing The Query Processing Loop in Oracle 11g"

Database query processing refers to the process inside a database management system (DBMS) that is responsible for compiling and executing SQL statements. The compilation process (performed by the SQL Compiler) takes as input a SQL statement text (with optional host variables) and produces an execution plan. The execution process (performed by the Execution Engine) takes as input the execution plan and returns the result of the execution. An execution plan contains the detailed steps necessary to execute the SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The processing order and implementation of the operators are decided by the query optimizer, using a combination of query transformations and physical optimization techniques.

The execution plan generated for the SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance; the lower the cost, the better the performance (e.g. response time) of the query is expected to be. The cost model relies on object statistics (e.g. number of rows, number of blocks, and distribution of column values) and system statistics (e.g. IO bandwidth of the storage subsystem).

The left graphic illustrates the lifecycle of a SQL statement inside the SQL compiler. A SQL statement goes through the Parser, Semantic Analysis (SA), and Type-Check(TC) first before reaching the optimizer. The Oracle optimizer performs a combination of logical and physical optimization techniques. The Query Transformer (QT) is responsible for selecting the best combination of transformations (e.g. subquery unnesting and view merging) while the Plan Generator (PG) is responsible for selecting access paths, join methods, and join orders. The QT calls the PG for every candidate set of transformations and retains the one that yields the lowest cost. The PG calls the Cost Estimator (CE) for every alternative access path, join method, and join order and keeps the one that has the lowest cost. The Code Generator (CG) stores the optimizer decisions into a structure called a cursor.

I think this should be enough basic information about the general SQL engine processing. Let's dig a little bit deeper into the logical (= SQL transformation) and physical optimization (= generating execution plans for the each transformed SQL) part.

Basically said the primary goal of a query transformation is to enhance the query performance by generating a semantically equivalent form of a (SQL) statement, which produces the same results, but significantly differs in performance.

Source: "Enhanced Subquery Optimizations in Oracle"

Oracle performs a multitude of query transformations - subquery unnesting, group-by and distinct view merging, common sub-expression elimination, join predicate pushdown, join factorization, conversion of set operators intersect and minus into[anti-] join, OR expansion, star transformation, group-by and distinct placement etc. Query transformation in Oracle can be heuristic or cost based. In cost-based transformation, logical transformation and physical optimization are combined to generate an optimal execution plan.

In Oracle 10g, a general framework for cost-based query transformation and several state space search strategies were introduced. During cost-based transformation, a query is copied, transformed and its cost is calculated using existing cost-based physical optimizer. This process is repeated multiple times applying a new set of transformations; and at the end, one or more transformations are selected and applied to the original query, if it results in an optimal cost. The cost-based transformation framework provides a mechanism for the exploration of the state space generated by applying one or more transformations thus enabling Oracle to select the optimal transformation in an efficient manner. The cost-based transformation framework can handle the complexity produced by the presence of multiple query blocks in a user query and by the interdependence of transformations. The availability of the general framework for cost-based transformation has made it possible for other innovative transformations to be added to the vast repertoire of Oracle's query transformation techniques.

The described process with each of its components is illustrated in the right graphic above. The white paper mentions two kind of query transformations called "heuristic / rule based transformations" and "cost-based transformations (CBQT)", but unfortunately it does not explain these in detail and its interaction. So let's check these two transformations shortly as the final of this chapter, before we dig into the example.

Heuristic-based query transformation

These transformations are applied to incoming SQL statements whenever possible. These transformations always provide equivalent or better query performance, so that Oracle knows that applying these transformations will not degrade performance. Examples for heuristic-based query transformations are: Simple view merging, Subquery “flattening”, Transitive predicate generation, Common subexpression elimination, Join elimination, etc..

More details about each transformation can be found in the references "Cost-Based Query Transformation in Oracle" and "Query Optimization in Oracle Database10g Release 2 White Paper".

Cost-based query transformation

Oracle uses a cost-based approach for several classes of query transformations, that do not work very well with heuristic rules (= insufficient execution plans). Using this approach, the transformed query is compared to the original query, and Oracle’s optimizer then selects the best execution strategy. The scope of optimization is spanned over one or more query blocks and different transformations can be applied on different elements (eg. unnesting of subqueries, predicate pull, etc.). Examples for cost-based query transformations are: Materialized view rewrite, OR-expansion, Star transformation, Join Predicate Pushdown, etc..

More details about each transformation can be found in the references "Cost-Based Query Transformation in Oracle" and "Query Optimization in Oracle Database10g Release 2 White Paper".

The general strategy of the transformation interaction is to apply a transformation to all query blocks of a particular SQL statement before applying the next one (= sequential order), but there are some exceptions to this rule. In first place heuristic-based transformations are applied before cost-based transformations. However it may be possible that additional / other heuristic-based transformations are necessary by evaluating and applying cost-based transformations. This interaction is also illustrated in the right graphic from above (check the arrows closely).

The following graphic illustrates possible interactions (as an incomplete example) between heuristic and cost-based optimization techniques. The mentioned abbreviations like "JE, CNT, etc." are also used in the cost based optimizer trace (event 10053) and explained in its legend.

** The screenshot is taken from the document "Cost Based Query Transformations" - For more details check the reference section **

Real life example of a simple (heuristic-based) transformation

The following demo was run on an Oracle database (11.2.0.3.6) on OEL 6.4.

Creating the demo data



SQL> create table TESTTAB (COL1 NUMBER, COL2 NUMBER,

                                                COL3 VARCHAR2(40), COL4 VARCHAR2(20));

SQL> begin

for i in 1 .. 100000 loop

  insert into TESTTAB values (i,i,'TEST','TEST2');

end loop;

commit;

end;

/

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTTAB');

Execute simple SQL with the previously table definition



SQL> select count(COL4) from TESTTAB;

Footnote: Cost_io and Cost_cpu are the serial execution costs whereas Resp_io and Resp_cpu are the costs for parallel execution (decreased when PX is allowed or hinted)

Change table column definition only (all statistics and the query keeps the same)



SQL> alter table TESTTAB modify COL4 NOT NULL;

Execute simple SQL again with changed column definition



SQL> select count(COL4) from TESTTAB;

Footnote: Cost_io and Cost_cpu are the serial execution costs whereas Resp_io and Resp_cpu are the costs for parallel execution (decreased when PX is allowed or hinted)

The execution plan is still the same of course, but the cost for the same SQL statement with the same set of statistics dropped from 76 to 75. This is not a huge impact of course, but we want to keep it as simple as possible for the purpose of illustration right here.

What does happen in detail? You may already notice that the runtime / execution engine is instructed to run a simple "count(*)" instead of a "count(COL4)" in the second example, if you look closely at the column project information.

We have executed the same user SQL "select count(COL4) ...." in both cases, but the clue is a query transformation called "CNT: COUNT() TO COUNT(*)", that kicks in in the second example. Oracle just needs to count the rows in the second example due to the "NOT NULL" constraint on column COL4 ("COL4"[VARCHAR2,20]" is gone in the column projection as well). This simple counting is cheaper as Oracle does not need to retrieve the value of column COL4 and interpret it. The optimizer is aware of that heuristic-based transformation and transformed the SQL in consequence as the calculated cost of such a query is always lower (due to less CPU work - check costed CPU "Cost_cpu" in the CBO trace from above as reference).

So you may wonder how something like this can happen in a SAP environment? My client faced the exact same situation (missing "NOT NULL" constraint on column) with a SAP standard table, which was extended with a custom column some time ago. SAPnote #1387135 - 11g: DDIC support for ALTER TABLE with DEFAULT/NOT NULL describes this scenario and its root cause in detail. The root cause also fits to the environment of my client as the system was grown and upgraded since Oracle 9i continuously.

Summary

So what is the key point of this quite long blog post aside from the basic introduction into heuristic and cost-based query transformation?

I hope you get the main point, that the final SQL that is executed is not necessarily the same "textual and physical" user provided SQL. Oracle is able to re-write / transform your user SQL in many ways and check the costs after / with each transformation. It is also possible that it seems like the corresponding execution plan does not fit to your user SQL at all at the first moment, but you always need to consider the transformation possibilities of the optimizer by troubleshooting SQL execution plans. For example join elimination is not a common topic in a SAP environment due to the lack of primary key-foreign key constraints, but outer join table elimination may be possible, internal "generated" views (check the internal view names in the references) can be noticed sometimes or star transformation is widely often used in SAP BI environments. Depending on the transformation you are also able to spot the transformation in the outline data section of the execution plan (e.g. check the officially documented hints for query transformations in the references).

If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database (performance) issues.