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