Oracle SQL Profiles: Check what they do before accepting them blindly

By Franck Pachot

.
People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer calls me, they expect a bit more than running an advisor and implement the result. I’m there to fix the root cause, not just workaround some missing stats. And this is why I use it very rarely.

However when I have a big query, with a plan that covers several pages, it takes a lot of time to find what is wrong. The method is often based on comparing the estimated cardinalities with the actual ones. But If I have access to the Tuning Pack, then the SQL Tuning Advisor can help to find very quickly where the estimations are going wrong.

The SQL Tuning Advisor proposes a SQL Profile to adjust the estimations. Then I just have to check the biggest adjustment and I can focus where the estimations are wrong. However, that information is not exposed. The SQL Tuning Advisor report shows the new plan, but not the ways it gets to it.

The goal of this post is to give you the query I use to show exactly what the profile will implement when you accept it.

Even if you’re going to accept the profile, It’s a good idea to check it before. It will help to choose if you need to enable ‘force matching’ or not. And if it is a good fix or if there are more sustainable ways to achieve the same.

You probably know that a profile implements the estimation adjustment with the OPT_ESTIMATE hints which adjust it with a ‘scale_rows’ factor that can apply to tables, index selectivity or joins. They is very well explained on the Pythian blog

So, let’s take an exemple. My favorite query to show bad estimations on the HR schema is:

alter session set current_schema=HR;
select distinct DEPARTMENT_NAME
 from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID)
 where DEPARTMENT_NAME like '%ing' and SALARY>20000;

For the simple reason that I know that lot of the department names are ending with ‘ing’ (Marketing, Purchasing,…) but the CBO doesn’t know that. And I can’t give that information through column statistics or histograms:

SQL> select * from table( dbms_xplan.display_cursor(format=>'rowstats last ') );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  4fz1vtn0w8aak, child number 0
-------------------------------------
select distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES
using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000

Plan hash value: 3041748347

----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |      0 |
|   1 |  HASH UNIQUE                          |                   |      1 |      1 |      0 |
|   2 |   NESTED LOOPS SEMI                   |                   |      1 |      1 |      0 |
|*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |      1 |      1 |      7 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |      7 |      1 |      0 |
|*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |      7 |     10 |     55 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')
   4 - filter("EMPLOYEES"."SALARY">20000)
   5 - access("DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID")

Note
-----
   - this is an adaptive plan

Look at E-Rows and A-Rows: I have 7 departments ending with ‘ing’ but the optimizer thinks that there is only one. In 11g you need a profile to help the optimizer or you’re going into an expensive nested loop. This example has few rows, but imagine the consequence when a nested loop is choosen but must execute on millon of rows. In 12c – and if you are in Enterprise Edition – the adaptive plan will help to avoid that situation. As soon as a threshold is reached another plan will be executed.

But even with adaptive plan, there may be a better plan that is possible only with accurate estimations. Let’s see what the SQL Tuning Advisor will find.

Running SQL Tuning Advisor

I create and execute the tuning task:

begin
 dbms_output.put_line('task id: '||
 dbms_sqltune.create_tuning_task(
  task_name=>'dbiInSite',
  description=>'dbi InSite workshop Oracle Tuning',
  scope=>dbms_sqltune.scope_comprehensive,
  time_limit=>30,
  sql_id=>'4fz1vtn0w8aak'
  )
 );
 dbms_sqltune.execute_tuning_task('dbiInSite');
end;
/

And show the report:

SQL> set long 1000000 longc 1000000
SQL> select dbms_sqltune.report_tuning_task('dbiInSite') FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DBIINSITE')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : dbiInSite
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : 11/08/2014 00:03:22
Completed at       : 11/08/2014 00:03:23

-------------------------------------------------------------------------------
Schema Name: HR
SQL ID     : 4fz1vtn0w8aak
SQL Text   : select distinct DEPARTMENT_NAME
              from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID)
              where DEPARTMENT_NAME like '%ing' and SALARY>20000

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 33.67%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'dbiInSite',
            task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .005964           .000177      97.03 %
  CPU Time (s):                 .005999             .0002      96.66 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       13                 9      30.76 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     0                 0
  Fetches:                            0                 0
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3041748347

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| 
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |     1 |    23 |     7  (15)| 
|   1 |  HASH UNIQUE                          |                   |     1 |    23 |     7  (15)| 
|   2 |   NESTED LOOPS SEMI                   |                   |     1 |    23 |     6   (0)| 
|*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |     7 |   112 |     3   (0)| 
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     1 |     7 |     1   (0)| 
|*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')
   4 - filter("EMPLOYEES"."SALARY">20000)
   5 - access("DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID")

2- Using SQL Profile
--------------------
Plan hash value: 2473492969

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    23 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE                  |             |     1 |    23 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |     1 |    23 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |             |     1 |    23 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | EMPLOYEES   |     1 |     7 |     3   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("EMPLOYEES"."SALARY">20000)
   5 - access("DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID")
   6 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')

-------------------------------------------------------------------------------

So even if I have adaptive plan, a better plan is possible. When the optimizer know that there are more rows from DEPARTMENTS than EMPLOYEES, it’s better to start with EMPLOYEES, for nested loop as well as for hash join.

Showing the OPT_ESTIMATE hints

Then I don’t want to accept the profile yet, but want to see all those OPT_ESTIMATE hints that has been determined by the SQL Tuning Advisor and that will be added to the query when the profile is accepted. Here my script. Note that this script is for 11g and 12c. In 10g the information was stored elsewhere. You can go to Jonathan Levis post for the 10g query.

set serveroutput on echo off
declare
  -- input variables
  input_task_owner dba_advisor_tasks.owner%type:='SYS';
  input_task_name dba_advisor_tasks.task_name%type:='dbiInSite';
  input_show_outline boolean:=false;
  -- local variables
  task_id  dba_advisor_tasks.task_id%type;
  outline_data xmltype;
  benefit number;
begin
  for o in ( select * from dba_advisor_objects where owner=input_task_owner and task_name=input_task_name and type='SQL')
  loop
          -- get the profile hints (opt_estimate)
          dbms_output.put_line('--- PROFILE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');
          dbms_output.put_line('/*+');
          for r in (
            select hint,benefit from (
             select case when attr5 like 'OPT_ESTIMATE%' then cast(attr5 as varchar2(4000)) when attr1 like 'OPT_ESTIMATE%' then attr1 end hint,benefit
             from dba_advisor_recommendations t join dba_advisor_rationale r using (task_id,rec_id)
             where t.owner=o.owner and t.task_name = o.task_name and r.object_id=o.object_id and t.type='SQL PROFILE'
             --and r.message='This attribute adjusts optimizer estimates.'
            ) order by to_number(regexp_replace(hint,'^.*=([0-9.]+)[^0-9].*$','1'))
          ) loop
           dbms_output.put_line('   '||r.hint); benefit:=to_number(r.benefit)/100;
          end loop;
          dbms_output.put_line('*/');
          -- get the outline hints
          begin
          select outline_data into outline_data from (
              select case when other_xml is not null then extract(xmltype(other_xml),'/*/outline_data/hint') end outline_data
              from dba_advisor_tasks t join dba_sqltune_plans p using (task_id)
              where t.owner=o.owner and t.task_name = o.task_name and p.object_id=o.object_id  and t.advisor_name='SQL Tuning Advisor' --11gonly-- and execution_type='TUNE SQL'
              and p.attribute='Using SQL profile'
          ) where outline_data is not null;
          exception when no_data_found then null;
          end;
          exit when not input_show_outline;
          dbms_output.put_line('--- OUTLINE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');
          dbms_output.put_line('/*+');
          for r in (
              select (extractvalue(value(d), '/hint')) hint from table(xmlsequence(extract( outline_data , '/'))) d
          ) loop
           dbms_output.put_line('   '||r.hint);
          end loop;
          dbms_output.put_line('*/');
          dbms_output.put_line('--- Benefit: '||to_char(to_number(benefit),'FM99.99')||'%');
  end loop;
  dbms_output.put_line('');
end;
/

And here is the output:

--- 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)
*/

PL/SQL procedure successfully completed.

This is very interesting information. It says that the actual number of employees in this query (with that specific where clause) is 2 times what is estimated from statistics. And that the estimated number of departments is 5 times what is estimated.

So what it is used for?

That gives me several ways to improve, even without implementing the profile.

First, the number of employees for a ‘SALARY>2000’ predicate can be improved with histograms. That is better than a profile because it will improve all queries that filter on employee salary.

Then, for DEPARTMENTS, histograms will not help because the bad estimation comes from the LIKE ‘%ing’ predicate and I’ve no way to give that information with statistics. Ok, let’s go for the profile. If you want to implement the profile, will you choose ‘force matching’ or not? Of course not. The estimation adjustment makes sense only with our ‘%ing’ values. This is were looking at the OPT_ESTIMATE is very important, or you can’t do the right choice.

And you may choose something else than profile. Dynamic sampling makes sense in this case. Another solution may be to add OPT_ESTIMATE or CARDINALITY hints in the query.

Conclusion

SQL Tuning Advisor is powerful, when used intelligently. It gives ideas about what is wrong and proposes a way to fix it. But you can have more when retrieving the internal hints that the profile generate. Better choice to implement the profile, or alternative solutions. As usual, if you see something wrong or to improve in my query, please comment.

Quizz

The answer is left for another post, but if you have an idea, please don’t hesitate to comment.
Question is: In the above output, I’ve two OPT_ESTIMATE rows for each table. what is the reason for that?