By Franck Pachot
.
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.