This was first published on https://blog.dbi-services.com/oracle-sql-profile-why-multiple-optestimate (2014-11-08)
Republishing here for new followers. The content is related to the the versions available at the publication date

Oracle SQL Profile: why multiple OPT_ESTIMATE?

In a previous blog I’v shared my script to retrieve the OPT_ESTIMATE hints from a SQL Profile. In the example I made, I had two lines for each table:

--- PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak:
/*+
OPT_ESTIMATE(@"SEL$2CBA5DDD", TABLE, "EMPLOYEES"@"SEL$1", SCALE_ROWS=2)
OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "EMPLOYEES"@"SEL$1", SCALE_ROWS=2)
OPT_ESTIMATE(@"SEL$6AE97DF7", TABLE, "DEPARTMENTS"@"SEL$1", SCALE_ROWS=5.185185185)
OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "DEPARTMENTS"@"SEL$1", SCALE_ROWS=5.185185185)
*/

The reason is that when the optimizer do some transformations to the query, then the query block identifiers can change. And when you adjust a cardinality estimation, you must do it for all transformations or you will completely mess up the optimizer choice.

When I do an explain plan which show the query blocks, I have only the SEL$58A6D7F6 one:

SQL> explain plan for
  2  select distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES
  3  using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000 ;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic +alias'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 3041748347
-------------------------------------------------------------------
| Id  | Operation                             | Name              |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |
|   1 |  HASH UNIQUE                          |                   |
|   2 |   NESTED LOOPS SEMI                   |                   |
|   3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |
|   5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |
-------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$58A6D7F6
   3 - SEL$58A6D7F6 / DEPARTMENTS@SEL$1
   4 - SEL$58A6D7F6 / EMPLOYEES@SEL$1
   5 - SEL$58A6D7F6 / EMPLOYEES@SEL$1

In order to confirm that the duplicate OPT_ESTIMATE are coming from different transformations, I’ve generated a 10053 trace and searched for SEL$6AE97DF7:

Registered qb: SEL$6AE97DF7 0x851d8eb8 (DISTINCT PLACEMENT SEL$58A6D7F6; SEL$58A6D7F6; "EMPLOYEES"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$6AE97DF7 nbfros=2 flg=0
    fro(0): flg=0 objn=92595 hint_alias="DEPARTMENTS"@"SEL$1"
    fro(1): flg=1 objn=0 hint_alias="VW_DTP_43B5398E"@"SEL$43B5398E"

that’s the Distinct Placement. let’s try the PLACE_DISTINCT hint:

SQL> explain plan for
  2  select /*+ PLACE_DISTINCT(EMPLOYEES) */ distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES
  3  using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000 ;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic +alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 2901355344

--------------------------------------------------------------------
| Id  | Operation                              | Name              |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   |
|   1 |  HASH UNIQUE                           |                   |
|   2 |   NESTED LOOPS SEMI                    |                   |
|   3 |    TABLE ACCESS FULL                   | DEPARTMENTS       |
|   4 |    VIEW PUSHED PREDICATE               | VW_DTP_43B5398E   |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |
|   6 |      INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |
--------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$6AE97DF7
   3 - SEL$6AE97DF7 / DEPARTMENTS@SEL$1
   4 - SEL$9B757045 / VW_DTP_43B5398E@SEL$43B5398E
   5 - SEL$9B757045 / EMPLOYEES@SEL$1
   6 - SEL$9B757045 / EMPLOYEES@SEL$1

Here is where the

OPT_ESTIMATE(@"SEL$6AE97DF7", TABLE, "DEPARTMENTS"@"SEL$1", SCALE_ROWS=5.185185185)

makes sense. The same cardinality adjustment must be done for each transformation that the optimizer is evaluating.

That observation brings me to the following: what happens to your profiles when you upgrade to a version that brings new optimizer transformations? The optimizer will compare some plans with adjusted cardinalities, compared with some plans with non-adjusted cardinalites. And that is probably not a good idea.

In my opinion, SQL Profiles are just like hints: a short term workaround that must be documented and re-evaluated at each upgrade.

 

5 Comments