As we have talked about performing a SQL tuning for a specific SQL_ID, we know we can accept its recommended SQL profile for a better execution result. Here comes some situations:
- We know a better route but no SQL profile is recommended in the SQL tuning report.
- We want the SQL statement to stick with a specific index or go back full table scan.
- We urged developers to use hint, but they refused or can't modify their code.
Let's see how to create a SQL profile to fix the execution plan or the index we plan to use for a specific SQL_ID through hints.
In this post, we connect as SYS.
SQL> show user
USER is "SYS"
If this is a multitenant database, we should switch to the pluggable database.
SQL> alter session set container=orclpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
Let's see how we create a new SQL profile in the following steps.
Check Original Plan
To compare the new plan to be created, we check its current status.
SQL_ID
The SQL_ID can be retrieved by by querying V$SQL if the SQL cursor is still in shared SQL area.
SQL> select distinct sql_id from v$sql where sql_text = 'select employee_id from hr.employees where salary between 5000 and 10000';
SQL_ID
-------------
6t04amnphzcwd
Execution Plan
We use DBMS_XPLAN.DISPLAY_CURSOR to check the execution plan.
SQL> set heading off;
SQL> set linesize 100;
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '6t04amnphzcwd'));
SQL_ID 6t04amnphzcwd, child number 0
-------------------------------------
select employee_id from hr.employees where salary between 5000 and 10000
Plan hash value: 3246610255
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| SALARY_EMP_ID_X | 28 | 224 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SALARY">=5000 AND "SALARY"<=10000)
18 rows selected.
We note that the plan hash value is 3246610255.
As we can see, there's already an index in the execution plan, but we don't like it. We want the other index to be used.
Get Outline Data
To form a SQL profile, we need to input all required hints called outline data it wants. The outline data is actually a set of attributes used by the optimizer to generate an execution plan.
INDEX Hint
We explicitly add the index we want to the INDEX hint in the statement to run, which is the execution plan we want.
SQL> select /*+ INDEX(employees SALARY_X) */ employee_id from hr.employees where salary between 5000 and 10000;
...
Execution Plan
After the execution above, we can display its outline data. Outline data contains all hints used in the execution plan.
SQL> select * from table(dbms_xplan.display_cursor(format => 'outline'));
SQL_ID 9rmhxryf898m6, child number 0
-------------------------------------
select /*+ INDEX(employees SALARY_X) */ employee_id from hr.employees
where salary between 5000 and 10000
Plan hash value: 601977068
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 28 | 224 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALARY_X | 28 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
The execution plan shows that it does use the index we specified in INDEX hint.
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."SALARY"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALARY">=5000 AND "SALARY"<=10000)
35 rows selected.
The highlighted text is the hints a SQL profile needs.
Create SQL Profile
Since we have the outline data, we can use it to create a SQL profile by DBMS_SQLTUNE.IMPORT_SQL_PROFILE for it. The procedure seems a hidden feature, we can't find it in the specification of DBMS_SQLTUNE package from Oracle.
IMPORT_SQL_PROFILE
Here we use a PL/SQL block to create the profile for the statement, the most important step in which is to execute DBMS_SQLTUNE.IMPORT_SQL_PROFILE.
declare
v_sql_text varchar2(4000);
v_sqlprof_attr sys.sqlprof_attr;
begin
select sql_fulltext into v_sql_text from v$sql where sql_id = '6t04amnphzcwd';
v_sqlprof_attr := sys.sqlprof_attr (
q'!IGNORE_OPTIM_EMBEDDED_HINTS!',
q'!OPTIMIZER_FEATURES_ENABLE('19.1.0')!',
q'!DB_VERSION('19.1.0')!',
q'!ALL_ROWS!',
q'!OUTLINE_LEAF(@"SEL$1")!',
q'!INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."SALARY"))!',
q'!BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")!'
);
dbms_sqltune.import_sql_profile ( sql_text => v_sql_text, profile => v_sqlprof_attr, category => 'DEFAULT', name => 'sql_profile_for_6t04amnphzcwd');
end;
/
Then we execute the PL/SQL block.
Check New Execution Plan
Since we have created and imported a SQL profile to the statement, which should have been applied as of now.
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '6t04amnphzcwd'));
SQL_ID 6t04amnphzcwd, child number 0
-------------------------------------
select employee_id from hr.employees where salary between 5000 and 10000
Plan hash value: 601977068
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 28 | 224 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALARY_X | 28 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALARY">=5000 AND "SALARY"<=10000)
Note
-----
- SQL profile sql_profile_for_6t04amnphzcwd used for this statement
23 rows selected.
As we can see, the new profile named sql_profile_for_6t04amnphzcwd is engaged.
Next, we can also confirm it by querying V$SQL.
SQL> set heading on;
SQL> column sql_profile format a30;
SQL> select plan_hash_value, sql_profile from v$sql where sql_id = '6t04amnphzcwd';
PLAN_HASH_VALUE SQL_PROFILE
--------------- ------------------------------
601977068 sql_profile_for_6t04amnphzcwd
The new plan hash value (PLAN_HASH_VALUE) of the SQL profile is now 601977068.