Oracle: SQL Profiles - WizardOfBits

993 words

5 minutes

Oracle: SQL Profiles

2025-11-06

Oracle

What are SQL Profiles#

A SQL Profile provides additional information to the optimizer about a specific statement thus helping it to select the best plan for it. A SQL profile does not force the optimizer to select any specific plan. It can however influence the optimizer to chose a better plan for your statement.
If SQL plan baselines are used, then the plan selection will be limited to the set of accepted plans.

If the intended purpose is to freeze the optimizer to a specific plan, then you should consider SQL plan baselines or stored outlines. Prefer SQL plan baselines over stored outlines which is a legacy feature.

WARNING
SQL Profiles require Diagnostic and Tuning Pack license.

SQL Profiles contain the following information:

information about the execution environment
object statistics
estimation corrections
cardinality information
optimizer hints

When to use SQL Profiles#

When you want to correct cardinality or selectivity misestimates by the CBO.
When the application code cannot be modified to rewrite the statement or add hints to the statement.

Creating SQL Profiles#

DECLARE
    sql_stmt clob;
BEGIN
    sql_stmt := '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('FULL(@"SEL$1" "PERF_TABLE"@"SEL$1"))'),
        force_match => true );
END;
/

The sqlprof_attr section can take any number of hints. For now we are interested only in the index hint.
The force_match parameter ensures statements differing only in literal values are treated as similar hence they can use same plan.

Literal values and Bind Values#

Literal values are data values that are part of the SQL statement.
For example, in SELECT * FROM users WHERE user_id = 'user001';, user001 is a literal value.

Each query using a different literal value is treated as a unique SQL statement and will require a hard parse.

The following queries will be treated as different unique queries:

SELECT * FROM users WHERE user_id = 'user001';
SELECT * FROM users WHERE user_id = 'user002';

WARNING
Such queries can be resource-intensive especially for frequently executed queries with different literal values.

Bind variables are placeholders that later get replaced with actual data values during SQL execution. For example, in SELECT * FROM users WHERE user_id = :user_id;, :user_id is a bind variable.

The database can reuse the same execution plan for queries using bind variables, even if the actual values change.
This avoids repeated hard parses, leading to better performance in many scenarios. Bind variables prevent SQL injection attacks since they are treated as data instead of code.

NOTE
force_match => true forces a profile to work on all SQL statements that are the same except for their literal values.

View SQL Profiles#

SELECT name, created, status, sql_text FROM dba_sql_profiles;

Enable|Disable SQL Profiles#

The SQL Profile can be enabled or disabled at any time as follows:

--- Enable SQL Profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name => 'profile_name',
    attribute_name => 'STATUS',
    value => 'ENABLED');

--- Disable SQL Profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name => 'profile_name',
    attribute_name => 'STATUS',
    value => 'DISABLED');

Drop SQL Profiles#

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'profile_name');

Example Usage#

Test data#

First, lets create our test data.

-- Create table profile_test
CREATE TABLE profile_test (
    data_id number,
    data_name varchar2(150),
    data_type varchar2(10));

-- Populate profile_test table with skewed data
-- The column data_type will have more AA than BB values
INSERT INTO profile_test (
    SELECT
        rownum,
        object_name,
        decode(mod(rownum,20),0,'BB','AA')
    FROM dba_objects
    WHERE rownum < 100000);
COMMIT;

-- Add a constraint to the table
ALTER TABLE profile_test ADD CONSTRAINT profile_test_pk PRIMARY KEY (data_id);

-- Create an index on data_type column
CREATE INDEX idx_data_type ON profile_test (data_type);

-- Gather table stats
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'SYSTEM',
        tabname => 'PROFILE_TEST',
        cascade=>true,
        estimate_percent=>100);
END;
/

Once created, The column data_type will contain more AA values compared to BB values.

Index Usage on Test Data#

We will use the following queries:

SELECT * FROM profile_test WHERE data_type = 'AA';
SELECT * FROM profile_test WHERE data_type = 'BB';

First observe the different explain plans for the two statements.

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 40973 |  1600K|   141   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PROFILE_TEST | 40973 |  1600K|   141   (1)| 00:00:01 |
----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               | 40973 |  1600K|   593   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST  | 40973 |  1600K|   593   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DATA_TYPE | 40973 |       |    81   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

With SQL profiles, we can influence the optimizer to use the same plan for matching statements and ignore the literal values.

For this case, we can influence the optimizer to use the index we created for both statements.

Getting Advanced Explain Plan#

We can use index hints to influence the optimizer to use the execution plan we want for our query. First we need to get the advanced explain plan for the query using the index.

EXPLAIN PLAN FOR SELECT /*+ INDEX(profile_test idx_data_type) */ * FROM profile_test WHERE data_type = 'AA';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ADVANCED'));

This gives the following plan:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 270397958

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               | 40973 |  1600K|   593   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST  | 40973 |  1600K|   593   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_DATA_TYPE | 40973 |       |    81   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - SEL$1 / PROFILE_TEST@SEL$1
   2 - SEL$1 / PROFILE_TEST@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "PROFILE_TEST"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "PROFILE_TEST"@"SEL$1" ("PROFILE_TEST"."DATA_TYPE"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The Outline Data section is important for us to create the SQL profile.

Creating the SQL Profile#

We can now create our profile with the index hint.

DECLARE
    sql_stmt clob;
BEGIN
    sql_stmt := 'SELECT * FROM profile_test WHERE data_type = ''BB''';

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
        sql_text => sql_stmt,
        name => 'profile_test',
        profile => sqlprof_attr('INDEX_RS_ASC(@"SEL$1" "PROFILE_TEST"@"SEL$1" ("PROFILE_TEST"."DATA_TYPE"))'),
        force_match => true );
END;
/