Creation of a SQL PROFILE creation with a twist
Often As DBA’s we would suggest developers to include a new hint or fix to make the query run faster, but that would only apply
to individual query runs, what if we want to make the fix permanent ?
The only way would be to incorporate those changes into the code , so the batch or the program calling it would make sure
every subsequent call of that SQL will also use the hints/fixes.
But what if the Dev’s say the code change is very complicated process and we cannot edit the code ? But they still want the
query to use the hints and work faster even when the Batch or the program calls it ?
Here is an example using sql profiles that helps to fix the sql , but not edit the code.
We take an EMP table as an example below with sample data.
CREATE TABLE EMP (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-
YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-
YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-
YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-APR-1981', 'DD-MON-
YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-
YYYY'), 1250, 1400, 30);
Now gather stats on the table
exec
dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'EMP',cascade=>true,estimate_perce
nt=>100);
Run the query (our test case) → We will be using the same query for our test case.
SQL> select * from emp where empno=7521;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
As expected the query will use a FTS (Full table scan)
SQL> explain plan for select * from emp where empno=7521 ;
Explained.
Here the query uses a Full table scan as expected.
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EMP"@"SEL$1") → this confirm FTS
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7521)
Now lets create an index on the table
create index INDEMPNO on EMP (EMPNO);
Index created
Since the table is pretty small even after creating the index the optimizer chooses to do
a full table scan
SQL> select * from emp where empno=7521 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Now if we want our SQL to use the new created index , we need to use it as a hint in the as part of the sql
itself. This forces the use of the new index.
SQL> explain plan for select /*+ INDEX(EMP indempno) */ * from emp where empno=7521 ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED'));
Execution Plan
----------------------------------------------------------
Plan hash value: 2675604526
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEMPNO | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMP"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) →Confirm hint use
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Creation of SQL profile using HINT
===========================================================================
Now for the interesting part, in the new explain plan with the Index hint , copy over the
entire outline data block and replace in the declare block below with q'^ as the suffix
and prefix as shown below.
What this does is that for the same SQL (same SQL text) , it will force the optimizer to
use the SQL profile we create and uses the hint as apart of the SQL whenever it is run.
This way we don’t have to change the code or modify existing packages.
DECLARE
sql_stmt clob;
BEGIN
select sql_fulltext into sql_stmt from gv$sqlarea where sql_id='9th5zrrtnxkjy';
dbms_sqltune.import_sql_profile(
sql_text => sql_stmt,
name=>'Profile_9th5zrrtnxkjy',
profile => sqlprof_attr(
q'^BEGIN_OUTLINE_DATA^',
q'^BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMP"@"SEL$1")^',
q'^INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))^',
q'^OUTLINE_LEAF(@"SEL$1")^',
q'^ALL_ROWS^',
q'^DB_VERSION('19.1.0')^',
q'^OPTIMIZER_FEATURES_ENABLE('19.1.0')^',
q'^IGNORE_OPTIM_EMBEDDED_HINTS^',
q'^END_OUTLINE_DATA^'
),
replace=>true,
force_match=>true);
end;
PL/SQL procedure successfully completed.
=========================================================================================
Parameter description
Replace : If the profile already exists, it is replaced if this argument is TRUE
Force_match : If TRUE this causes SQL profiles to target all SQL statements which have
the same text
=========================================================================================
After the profile creation whenever anyone runs the query it will always use the Index
hint based on the profile. This is helpful in larger queries where we can adjust the
hints and make it permanent using a profile without touching the actual code.
SQL> select * from emp where empno=7521;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
Execution Plan
----------------------------------------------------------
Plan hash value: 2675604526
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEMPNO | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7521)
Note
-----
- SQL profile "Profile_9th5zrrtnxkjy" used for this statement
=========================================================================================
Steps to Disable/Enable/Drop the SQL profile if not needed
=========================================================================================
Enable the SQL Profile in Oracle Database:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'Profile_9th5zrrtnxkjy',
attribute_name=>'STATUS', value=>'ENABLED');
Disable the SQL Profile in Oracle Database:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'Profile_9th5zrrtnxkjy',
attribute_name=>'STATUS', value=>'DISABLED');
Drop the SQL Profile if not needed:
Exec dbms_sqltune.drop_sql_profile(name=>'Profile_9th5zrrtnxkjy');
