Vous avez sans doute déjà rencontré ce cas de figure ou une requête SQL n’utilise pas le bon plan d’exécution
( n’utilise pas un index existant ou au contraire l’utilise alors qu’elle serait plus rapide en balayant toute la table ).
Cette situation est surtout embarrassante lorsque vous ne pouvez pas modifier le code de l’application
( ex: progiciel ,ou alors vous ne disposez pas des sources de l’application ).
 
Dans ce cas , voilà une façon simple et rapide de vous sortir d’affaire en créant « manuellement » un SQL Profile.
Il vous faut tout d’abord identifier la requête posant problème en récupérant son SQL_ID.
Vous trouverez ce SQL_ID , soit en interrogeant la vue V$SQLAREA , dans un rapport tkprof ou dans un rapport AWR.
Une fois le SQL_ID identifié , commençons par examiner son plan d’exécution :

SQL> select * from table(dbms_xplan.display_cursor('1vm8vkq1yxgq1')) ;
 OU
SQL> select * from table(dbms_xplan.display_awr('1vm8vkq1yxgq1')) ;
                                             -- si la requête n'est plus disponible dans le cache
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  1vm8vkq1yxgq1, child number 0
-------------------------------------
select CDETB9,NOENVE,XDMAJ from enveloppes where cdetb9 = 'F' and
noenve = '1311000026'
Plan hash value: 4217195821
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       | 17558 (100)|          |
|*  1 |  TABLE ACCESS FULL| ENVELOPPES |   640 | 77440 | 17558   (3)| 00:03:31 |
--------------------------------------------------------------------------------

La requête SQL part sur un FTS ( full table scan ) alors qu’un index ENVELOPPES_PK existe sur les champs du prédicat …
Testons donc l’efficacité de cet index sur notre requête SQL en l’exécutant manuellement avec un HINT forçant l’usage de cet index.
Si votre requête SQL comporte des bind variables , il vous faudra récupérer les valeurs utilisées en interrogeant les vues suivantes :

si la requête a été exécutée récemment et se trouve encore dans le cache mémoire :
   SQL> select name,value_string from V$SQL_BIND_CAPTURE where sql_id = '&SQL_ID' ;
sinon :
   SQL> select name,value_string from DBA_HIST_SQLBIND where sql_id = '&SQL_ID' order by snap_id ;
SQL> set timing on
SQL> select CDETB9,NOENVE,XDMAJ from enveloppes where cdetb9 = 'F' and noenve = '1311000026' ;
CDETB NOENVE     XDMAJ
----- ---------- --------------------------
F     1311000026 2013-11-19 10:27:43.597489
Elapsed: 00:00:03.30

La requête sans hint s’exécute en un peu plus de 3 secondes.
avec un hint forçant l’usage de l’index ENVELOPPES_PK :

select /*+ INDEX(enveloppes ENVELOPPES_PK) */ CDETB9,NOENVE,XDMAJ from enveloppes where cdetb9 = 'F' and
noenve = '1311000026' ;
CDETB NOENVE     XDMAJ
----- ---------- --------------------------
F     1311000026 2013-11-19 10:27:43.597489
Elapsed: 00:00:00.01

Cette fois la requête est beaucoup plus rapide !! récupérons son nouveau plan d’exécution  :

select * from table(dbms_xplan.display_cursor(null,null,'OUTLINE')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1318388879
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| ENVELOPPES    |     1 |   121 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | ENVELOPPES_PK |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_complex_view_merging' 'false')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ENVELOPPES"@"SEL$1" "ENVELOPPES_PK")
      END_OUTLINE_DATA
  */

Parfait … nous n’avons plus maintenant qu’à créer un SQL Profile pour figer le plan d’execution de la requete SQL originale et lui forcer systématiquement l’usage de cet index ENVELOPPES_PK
en récupérant les éléments appelés « Outline Data » de l’interrogation précédente ainsi que le SQL_ID de la requete concernée pour générer le SQL profile à partir des instructions suivantes :
Nous appelerons ce profile PROFILE_<SQL_ID de la requete> , mais vous pouvez changer ce nom tant qu’il ne dépasse pas 30 caractères.

DEFINE SQL_ID='1vm8vkq1yxgq1'
declare
clsql_text CLOB;
ar_profile_hints sys.sqlprof_attr;
begin
       ar_profile_hints := sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
      'DB_VERSION(''11.2.0.3'')',
      'OPT_PARAM(''_complex_view_merging'' ''false'')',
      'ALL_ROWS',
      'NO_PARALLEL',
      'OUTLINE_LEAF(@"SEL$1")',
      'INDEX_RS_ASC(@"SEL$1" "ENVELOPPES"@"SEL$1" "ENVELOPPES_PK")',
      'END_OUTLINE_DATA'
);
SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';  
                                                        -- si la requête est encore en cache
 Ou
SELECT sql_text clsql_text FROM SYS.WRH$_SQLTEXT WHERE sql_id = '&SQL_ID'
       AND ROWNUM = 1 ;                                 -- si elle ne l'est plus.
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  sql_text => clsql_text,
  profile => ar_profile_hints,
  name => 'PROFILE_&SQL_ID'
);
end;
/

Désormais votre requête utilisera systématiquement l’index ENVELOPPES_PK comme vous l’avez ordonné et codé dans le SQL Profile et vous pouvez le vérifier en exécutant :

SQL> select * from table(dbms_xplan.display_cursor('1vm8vkq1yxgq1')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1318388879
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| ENVELOPPES    |     1 |   121 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | ENVELOPPES_PK |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Note
-----
   - SQL profile PROFILE_1vm8vkq1yxgq1 used for this statement