Explore SQL Profiles - Part I

Suppose we identified a problem SQL whose access path is not optimal and we want to tune it, however we can not modify the text of the SQL. In this case, we can use SQL profiles (10g feature) to guide Oracle to choose optimal execution plan. In this post, I am going to describe a test case in order to be familiar with the use of SQL profile.

First of all, I created a test table (the test case in based on ML 271196.1 Automatic SQL Tuning - SQL Profiles)

create table test (n number);
declare
begin
  for i in 1 .. 20000
  loop
     insert into test values(i);
     commit;
  end loop;
end;
/
create index test_idx on test(n);
exec dbms_stats.gather_table_stats(user, 'TEST');

The problem SQL is as follows:

select /*+ no_index(test test_idx) */ * from test where n=1;

We know the best way to access the table TEST with predicate n=1 is through index scan, however due to the no_index hint we will have FTS. We are going to see with SQL profile, Oracle can choose index scan and ignore this hint.


Summary of the steps:

Step 1: Create a tuning task
Step 2: Execute the tuning task
Step 3: Report the tuning task
Step 4: Check SQL profile rationale
Step 5: Accept the SQL profile
Step 6: Verify SQL profile is used for the problem SQL
Step 7: Disable and Enable the SQL profile
Step 8: Modify category attribute of SQL profile
Step 9: Query DBA_SQL_PROFILES


Step 1: Create a tuning task

SQL>
SQL> declare
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select /*+ no_index(test test_idx) */ * from test where n=1';
  6   my_task_name := dbms_sqltune.create_tuning_task (
  7      sql_text  => my_sqltext,
  8      user_name => 'Z983294',
  9      scope     => 'COMPREHENSIVE',
 10      time_limit => 60,
 11      task_name => 'my_sql_tuning_task_3',
 12      description => 'Task to tune a query on a specified table');
 13  end;
 14  /

PL/SQL procedure successfully completed.

Step 2: Execute the tuning task

SQL> begin
  2   dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_3');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Step 3: Report the tuning task

SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_3') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_3')
--------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_3
Tuning Task Owner                 : Z983294
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 06/30/2009 05:24:39
Completed at                      : 06/30/2009 05:24:40
Number of SQL Profile Findings    : 1

--------------------------------------------------------------------------
Schema Name: Z983294
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

--------------------------------------------------------------------------
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: 89.76%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_3', replace => TRUE);

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

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

------------------------------------------------------------------------
 Id   Operation          Name  Rows   Bytes  Cost (%CPU) Time
-------------------------------------------------------------------------
*  1   TABLE ACCESS FULL TEST      1      4     10  (10) 00:00:01
-------------------------------------------------------------------------

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

   1 - filter("N"=1)

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

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT              1      4     1   (0) 00:00:01
*  1   INDEX RANGE SCAN TEST_IDX    1      4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Step 4: Check SQL profile rationale

SQL>
SQL> select
  2   rat.attr1
  3  from
  4   sys.wri$_adv_tasks      tsk,
  5   sys.wri$_adv_rationale      rat
  6  where
  7        tsk.name = 'my_sql_tuning_task_3'
  8  and     rat.task_id   = tsk.id;

ATTR1
--------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS

Step 5: Accept the SQL profile

SQL>
SQL> -- Accept the tuning profile
SQL> -- SQLs different only in case and space are treated as same
SQL> -- in applying the SQL profile. If  force_match = TRUE is specified,
SQL> -- differences in literals are also ignored
SQL>
SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3  BEGIN
  4    my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5      task_name => 'my_sql_tuning_task_3',
  6      name      => 'my_sql_profile',
  7      force_match => TRUE);
  8    END;
  9  /

PL/SQL procedure successfully completed.

Step 6: Verify SQL Profile is used for the problem SQL

SQL> -- Index scan due to sql profile
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID  apxdba96mcfqt, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=1

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX     1     4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


22 rows selected.

SQL>
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name      Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX      1    4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 7: Disable and Enable the SQL profile

SQL> -- disable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'DISABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS due to profile is disabled
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

----------------------------------------------------------------------
 Id   Operation          Name Rows  Bytes Cost (%CPU) Time
----------------------------------------------------------------------
   0  SELECT STATEMENT                       10 (100)     
*  1   TABLE ACCESS FULL TEST     1     4    10  (10) 00:00:01
----------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL> -- enable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'ENABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

Step 8: Modify category attribute of SQL profile

SQL> -- Modify the category to TEST
SQL> --  category default value: 'DEFAULT'
SQL>
SQL> begin
  2   DBMS_SQLTUNE.ALTER_SQL_PROFILE (
  3      name     => 'my_sql_profile',
  4      attribute_name  =>  'CATEGORY',
  5      value    =>  'TEST');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS after sql profile's category is modified
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

---------------------------------------------------------------------
 Id   Operation          Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------
   0  SELECT STATEMENT                      10 (100)     
*  1   TABLE ACCESS FULL TEST     1    4    10  (10) 00:00:01
---------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL>
SQL> alter session set sqltune_category='TEST';

Session altered.

SQL>
SQL> -- Index scan after change the session's sqltune_category
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 1
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-----------------------------------------------------------------------
 Id   Operation         Name     Rows Bytes Cost (%CPU)Time
-----------------------------------------------------------------------
   0  SELECT STATEMENT                          1 (100)    
*  1   INDEX RANGE SCAN TEST_IDX     1    4     1   (0)00:00:01
-----------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 9: Query DBA_SQL_PROFILES

SQL> -- Query dba_sql_profiles
SQL> col name format a10
SQL> col category format a10
SQL> col sql_text format a30
SQL> col force_matching format a5
SQL>
SQL> select name, category,sql_text,force_matching from dba_sql_profiles;

NAME       CATEGORY   SQL_TEXT                       FORCE
---------- ---------- ------------------------------ -----
my_sql_pro TEST       select /*+ no_index(test test_ YES
file                  idx) */ * from test where n=1

What if Oralce does not recommend a SQL Profile to the problem SQL? And we know there is a better access path? This is what I will explore next about SQL profiles.

Go to Part II