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
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.
Hi Franck, the basic idea of an offline optimization is something I like – but I agree that sql profiles are just a workaround and not a long term solution. For data warehouses I could imagine an additional optimizer mode (or something similar) that would give the CBO more time to create decent plans for long running queries – the standard OLTP strategy to create plans as fast as possible seems to me not to be very fitting in the DWH context. Of course that’s still not a guarantee for better plans…
Strictly speaking, I don’t know really “long term solutions”… Optimizer is constantly changing.
Hi Sayan, by ‘long term solution’ I can think of adding extended statistics, setting table preferences to gather histograms or not, changing the query to use bind variable or not…
Interesting…
I never use the default SQL Profile mechanism – by “default”, I mean via SQL Tuning Advisor and using OPT_ESTIMATE.
In practice, I only ever use SQL Profiles as alternative to a SQL Plan Baseline to apply the outline hints from a specific execution as used by the Oracle-supplied coe_xfr_sql_profile.sql script, part of SQLT.
One of the first things I checked when looking at 12c was whether the query block alias generation seemed to generate the same alias in 12 as 11 and it did seem to.
But new and subtle variations on join methods come along each release which mean that even the outline hints are not immune to being incomplete on upgrade.
Hi Dominic, thanks for you comment. It’s a good idea to make reference to Carlos Sierra coe_xfr_sql_profile.sql I usually prefer to use SQL Patch for that, which is available without option and also in Standard Edition.