Manual Creation of a SQL Profile

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.

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.

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 my_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 my_table add constraint xpk1my_table primary key (prim_key)

using index tablespace users;

create index xak1my_table on my_table (data_value1) tablespace users;

create index xak2my_table on my_table (data_value2) tablespace users;

create index xak3my_table on my_table (data_value3) tablespace users;

create index xak4my_table on my_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 my_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 |

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

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 my_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.

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 my_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 my_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.

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 my_table where data_value1='ODD'^';

dbms_sqltune.import_sql_profile(

sql_text => sql_stmt,

name=>’MYPERF_JAY’,

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.

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 my_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.

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/2017 6:40:09.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=>‘MYPERF_JAY’,

attribute_name=>’STATUS’,

value=>ENABLED);

end;

/

To drop the SQL profile, this is the syntax:

begin

dbms_sqltune.drop_sql_profile(

name=>‘MYPERF_JAY’,

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.