use SQL Profile to fix execution plan

This test is about fixing execution plan by using sql profile. 

By default, the sql is using index unique scan. By adding sql profile, the sql will be forced to use full table 

step 1:  create test table and index

SQL> conn cloudapp/cloudapp
Connected.
SQL> create table t as select * from dba_objects where object_id is not null;

Table created.

SQL> alter table t add constraint pk_obj_id primary key(object_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'CLOUDAPP',TABNAME=>'T',cascade => true);

PL/SQL procedure successfully completed.

step2: Show the original execution plan, by default, it uses index unique scan

SQL> var x number;
SQL> exec :x := 28;
SQL> select * from t where object_id=:x;
..
SQL> select * from table(dbms_xplan.display_cursor('9r2gykqt5j1ss',0,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9r2gykqt5j1ss, child number 0
-------------------------------------
select * from t where object_id=:x

Plan hash value: 3004845637

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_OBJ_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=:X)


33 rows selected.

step3: add hint to use full table scan and fetch new outline

SQL> select /*+ full(t) */ * from t where object_id=:x;
..
SQL> select * from table(dbms_xplan.display_cursor('f0v2h6jrjrmaf',0,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f0v2h6jrjrmaf, child number 0
-------------------------------------
select /*+ full(t) */ * from t where object_id=:x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 351 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 98 | 351 (1)| 00:00:05 |
--------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=:X)


32 rows selected.

step 4: create sql profile to force the sql using full table scan

SQL> declare
v_hints sys.sqlprof_attr;
begin
v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "T"@"SEL$1")');
dbms_sqltune.import_sql_profile(
SQL_TEXT => 'select * from t where object_id=:x',
PROFILE => v_hints,
NAME => 'PROF_T',
force_match => true);
end;
/

PL/SQL procedure successfully completed.

Privilege needs to be granted, if error ORA-38171 happens.

SQL> grant advisor to cloudapp;

Grant succeeded.

SQL> grant administer sql management object to cloudapp;

Grant succeeded.

step 5: now the sql profile is taking effect

SQL> select * from t where object_id=:x;
SQL> select * from table(dbms_xplan.display_cursor('9r2gykqt5j1ss',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9r2gykqt5j1ss, child number 0
-------------------------------------
select * from t where object_id=:x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 351 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 98 | 351 (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=:X)

Note
-----
   - SQL profile PROF_T used for this statement


22 rows selected.