Below are the 2 SQL statements, the first one uses the index AAA_IDX and then second one does a full table scan and in this case we want to make the first one that uses the index use the same execution plan as the 2nd one.
SQL statement:
select * from aaa e where ename = 'aaa';
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'
By query v$sql we found the sql_id, child_number and plan_hash_value also checked there isn't a sql_profile attached to the SQL.
SQL> select sql_id, child_number, sql_profile, plan_hash_value, sql_text from v$sql where sql_id IN ('63cg18v928540', '0tjtg6yqqbbxk');
SQL_ID CHILD_NUMBER SQL_PROFILE PLAN_HASH_VALUE
————- ———— ————————————————- —————
SQL_TEXT
————————————————-
63cg18v928540 0 2022030255
select * from aaa e where ename = 'aaa'
0tjtg6yqqbbxk 0 864433273
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'
Displaying the execution plan of the 2nd SQL with the outline option
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0tjtg6yqqbbxk', 0, 'outline'));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 0tjtg6yqqbbxk, child number 0
————————————-
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'
Plan hash value: 864433273
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “E”@”SEL$1”)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ENAME”='aaa')
From the outline generated used the hint to add to first SQL to change its execution plan i.e. full table scan.
define SQL_ID = '63cg18v928540';
DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM V$sqlarea where sql_id = '&SQL_ID';
dbms_sqltune.import_sql_profile(sql_text => clsql_text,
profile=> sqlprof_attr('FULL(@SEL$1 E@SEL$1)'),
name=>'PROFILE_&SQL_ID',
force_match=>true);
end;
/
Shows now when we execute the SQL it uses the same execution plan as the 2nd one i.e. full table scan
SQL> select * from aaa e where ename = 'aaa';
Execution Plan
———————————————————-
Plan hash value: 864433273
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ENAME”='aaa')
Note
—–
– SQL profile “PROFILE_63cg18v928540” used for this statement
