Andy Kerber (@dbakerber), Senior Consultant

Occasionally as a DBA you will come across a query that works very well, but for some reason insists on using an index, even when you know that the index is not appropriate for that query.

This problem may have multiple causes, and fixing it may not be easy.  Particularly if it is generated by an application, and you the DBA, do not have the ability to modify the query.

When to Manually Create a SQL Profile

In that case, it is helpful for the DBA to be able to manually create a hint for the query and load it into the database as a SQL profile.  This article will cover the steps for the manual creation of a SQL profile.

Create the Test Table and Load Sample Data

For the purposes of this article, I have created a table using the commands below:

create table perf_table
(prim_key number,
data_value1 varchar2(10),
data_value2 varchar2(20),
data_value3 varchar2(200))
tablespace users;
insert into perf_table (
select
prim_key.nextval,
decode(mod(prim_key.currval,2),1,'ODD','EVEN'),
decode(mod(prim_key.currval,2),1,'EVEN','ODD'),
decode(mod(prim_key.currval,10),0,'SKEWED','UNSKEWED')
from dual connect by rownum <=4000000);
commit;
alter table perf_table add constraint xpk1perf_table primary key (prim_key)
using index tablespace users;
create index xak1perf_table on perf_table (data_value1) tablespace users;
create index xak2perf_table on perf_table (data_value2) tablespace users;
create index xak3perf_table on perf_table (data_value3) tablespace users;
create index xak4perf_table on perf_table (data_value1, data_value3) tablespace users;
exec dbms_stats.gather_table_stats(
   ownname=>'AKERBER',
   tabname=>'PERF_TABLE',
   cascade=>true,
   estimate_percent=>100);

This is the query we are going to use as an example:

select count(1) from perf_table where data_value1='ODD';

The basic explain plan is below:

Plan hash value: 3828436769

-----------------------------------------------------------------------------
| Id |Operation           |Name          |Rows  |Bytes |Cost(%CPU)|Time     |
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT    |              |    1 |    5 |2363   (1)|00:00:01 |
|  1 |SORT AGGREGATE      |              |    1 |    5 |          |         |
|* 2 |INDEX FAST FULL SCAN|XAK1PERF_TABLE| 2000K| 9765K|2363   (1)|00:00:01 |
-----------------------------------------------------------------------------

Use a FULL hint to change the execution plan

As you can see, it is using an index fast full scan.  However, we would like it to use a full table scan.  Can we simply add the ‘FULL’ hint?

Let’s see what happens:

explain plan for select /*+FULL (PERF_TABLE) */ count(1) from perf_table where data_value1='ODD';
select * from table(dbms_xplan.display());

 

Plan hash value: 226030054

----------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes|Cost(%CPU)|Time    |
----------------------------------------------------------------------
|  0 | SELECT STATEMENT |          |    1 |    5 | 4419  (1)|00:00:01|
|  1 | SORT AGGREGATE   |          |    1 |    5 |          |        |
|* 2 | TABLE ACCESS FULL|PERF_TABLE| 2000K| 9765K| 4419  (1)|00:00:01|
----------------------------------------------------------------------

So yes, the ‘FULL’ hint accomplishes what we want.

In order to ensure that the ‘FULL’ hint is used each time, it is necessary to create a SQL profile.

Capture ADVANCED explain plans and Outline Data

The first step in creating your own SQL profile is to acquire the advanced ‘explain plan’ for the initial query and the improved query. In the output below, pay attention to the ‘Outline Data’ section (in bold):

Original Plan

explain plan for select count(1) from perf_table where data_value1='ODD';
select * from table(dbms_xplan.display(format=>'ADVANCED'));

 

Plan hash value: 3828436769

-------------------------------------------------------------------------
|Id |Operation           | Name         |Rows |Bytes|Cost (%CPU|Time    |
-------------------------------------------------------------------------
| 0 |SELECT STATEMENT    |              |   1 |   5 |2363   (1)|00:00:01|
| 1 |SORT AGGREGATE      |              |   1 |   5 |          |        |
|* 2|INDEX FAST FULL SCAN|XAK1PERF_TABLE|2000K|9765K|2363   (1)|00:00:01|
-------------------------------------------------------------------------

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

1 - SEL$1
2 - SEL$1 / PERF_TABLE@SEL$1

Outline Data
-------------
 /*+
     BEGIN_OUTLINE_DATA
     INDEX_FFS(@"SEL$1" "PERF_TABLE"@"SEL$1" ("PERF_TABLE"."DATA_VALUE1"))
     OUTLINE_LEAF(@"SEL$1")
     ALL_ROWS
     OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
     OPT_PARAM('_optimizer_gather_feedback' 'false')
     OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
     OPT_PARAM('_optimizer_adaptive_plans' 'false')
     OPT_PARAM('_optimizer_dsdir_usage_control' 0)
     OPT_PARAM('_px_adaptive_dist_method' 'off')
     OPT_PARAM('_optimizer_use_feedback' 'false')
     DB_VERSION('12.1.0.2')
     OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
     IGNORE_OPTIM_EMBEDDED_HINTS
     END_OUTLINE_DATA
 */

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

2 - filter("DATA_VALUE1"='ODD')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

Modified Plan

explain plan for select /*+FULL (PERF_TABLE) */ count(1) from perf_table where data_value1='ODD';
select * from table(dbms_xplan.display(format=>'ADVANCED'));

 

Plan hash value: 226030054

-------------------------------------------------------------------
|Id  |Operation        |Name      |Rows |Bytes|Cost(%CPU)|Time    |
-------------------------------------------------------------------
|  0 |SELECT STATEMEN  |          |   1 |   5 |z4419  (1)|00:00:01|
|  1 |SORT AGGREGATE   |          |   1 |   5 |          |        |
|* 2 |TABLE ACCESS FULL|PERF_TABLE|2000K|9765K| 4419  (1)|00:00:01|
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / PERF_TABLE@SEL$1

Outline Data
-------------

 /*+
     BEGIN_OUTLINE_DATA
     FULL(@"SEL$1" "PERF_TABLE"@"SEL$1")
     OUTLINE_LEAF(@"SEL$1")
     ALL_ROWS
     OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
     OPT_PARAM('_optimizer_gather_feedback' 'false')
     OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
     OPT_PARAM('_optimizer_adaptive_plans' 'false')
     OPT_PARAM('_optimizer_dsdir_usage_control' 0)
     OPT_PARAM('_px_adaptive_dist_method' 'off')
     OPT_PARAM('_optimizer_use_feedback' 'false')
     DB_VERSION('12.1.0.2')
     OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
     IGNORE_OPTIM_EMBEDDED_HINTS
     END_OUTLINE_DATA
 */

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

2 - filter("DATA_VALUE1"='ODD')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]
2 - (rowset=1020)

Note the difference in the outline data, we see the ‘FULL’ hint in the modified plan, and INDEX FFS in the original plan.

Build the SQL Profile with DBMS_SQLTUNE.IMPORT_SQL_PROFILE

Next, we create a SQL profile using the information from the explain plans.  Oracle has a stored procedure for exactly this purpose, which is called DBMS_SQLTUNE.IMPORT_SQL_PROFILE.

This stored procedure is what we will use to create our own SQL profile with the ‘FULL’ hint.  The parameters in the procedure we are concerned with are the SQL_TEXT, which is the SQL statement; the name, which will be the name of the SQL profile; and the profile itself, which we will take from the OUTLINE_DATA segment of the advanced explain plan above.

Below is the statement to create the SQL profile.  Note that in this statement, we are using the q’ method to redefine the quotation mark to make it easier to load the data.  This could also be done with the ‘replace’ function, but the following method is easier:

DECLARE
sql_stmt clob;
BEGIN
sql_stmt:=q'^select count(1) from perf_table where data_value1='ODD'^';
dbms_sqltune.import_sql_profile(
sql_text => sql_stmt,
name=>'SQL_PROFILE_PERF',
profile => sqlprof_attr(q'^FULL(@"SEL$1" "PERF_TABLE"@"SEL$1"))^',
q'^OUTLINE_LEAF(@"SEL$1")^',
q'^ALL_ROWS^',
q'^OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')^',
q'^OPT_PARAM('_optimizer_gather_feedback' 'false')^',
q'^OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')^',
q'^OPT_PARAM('_optimizer_adaptive_plans' 'false')^',
q'^OPT_PARAM('_optimizer_dsdir_usage_control' 0)^',
q'^OPT_PARAM('_px_adaptive_dist_method' 'off')^',
q'^OPT_PARAM('_optimizer_use_feedback' 'false')^',
q'^DB_VERSION('12.1.0.2')^',
q'^OPTIMIZER_FEATURES_ENABLE('12.1.0.2')^',
q'^IGNORE_OPTIM_EMBEDDED_HINTS^'),
force_match=>true);
end;
/

Once the procedure is executed, the profile is loaded and enabled.

Verify the SQL Profile is applied

Now that we have loaded the SQL profile, let’s get the explain plan again and make sure that the SQL profile works:

explain plan for select count(1) from perf_table where data_value1='ODD';
 select * from table(dbms_xplan.display());


Plan hash value: 226030054

 ------------------------------------------------------------------
 |Id |Operation        | Name     |Rows |Bytes|Cost(%CPU)|Time    |
 ------------------------------------------------------------------
 | 0 |SELECT STATEMENT |          |   1 |   5 | 4419  (1)|00:00:01|
 | 1 |SORT AGGREGATE   |          |   1 |   5 |          |        |
 |* 2|TABLE ACCESS FULL|PERF_TABLE|2000K|9765K| 4419  (1)|00:00:01|
 ------------------------------------------------------------------

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

2 - filter("DATA_VALUE1"='ODD')

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

We can see that the SQL profile works.

List, disable, or drop the SQL Profile

To get a list of all SQL profiles, and their statuses, run this query:

select name, created, status from dba_sql_profiles;
 

NAME              CREATED                       STATUS
SQL_PROFILE_PERF  5/11/2016 4:40:41.000000 PM   ENABLED

Should you decide that the profile does not work as well as expected, it can be deleted or disabled. To disable the profile, use the syntax:

begin
dbms_sqltune.alter_sql_profile(
name=>’SQL_PROFILE_PERF’,
attribute_name=>’STATUS’,
value=>ENABLED);
end;
/

To drop the SQL profile, this is the syntax:

begin
dbms_sqltune.drop_sql_profile(
name=>’SQL_PROFILE_PERF’,
ignore=>true);
end;
/

These are the basic steps for creating a SQL profile manually.  While this is not a skill that is used a lot, it is definitely a skill every DBA should have.

Licensing note for Oracle Tuning Pack

NOTE: A license for the Oracle tuning pack is required in order to use a SQL profile.