Bien souvent, nous sommes confrontés à des problèmes de performance dus principalement à la mauvaise écriture d’une requête ou alors au choix d’un plan d’exécution non optimal par l’optimiseur de requêtes. Nous pouvons toujours nous en sortir en créant des indexes ou ajouter des ‘hints’ à la requête initiale pour influer sur le calcul du plan d’exécution, mais cela présuppose que nous ayons accès au code source de la requête, ce qui n’est malheureusement pas toujours le cas. En effet, lorsque nous sommes en présence de progiciel, il est strictement interdit de modifier la requête native ou d’adjoindre des indexes supplémentaires. Aussi, la seule solution qui nous reste est de trouver le plan d’exécution optimal et de le fixer pour éviter toute dérive potentielle des performances. Oracle propose pour ce faire l’option SQL_PROFILE qui nous autorise à influer directement sur le plan d’exécution indépendamment de l’application. Mais attention, l’utilisation des packages pour manipuler SQL_PROFILE nécessite l’obtention de licences supplémentaires pour l’utilisation de Tuning et Diagnostic Pack dont SQL_PROFILE est une option.
Alors, comment fixer le plan d’exécution d’une requête ?
Un petit exemple vaudra mieux que de longs discours. Supposons que nous ayons une requête qui accède à la donnée par un index mais pour laquelle nous désirions privilégier la lecture complète de la table du fait que la sélection des données n’est pas assez restrictive.
SQL> variable easy_value number;
SQL> exec :easy_value:=2;
SQL> set autotrace traceonly
SQL> select count(*) from easy_objects where object_id > :easy_value and status='VALID';
Plan d'execution
----------------------------------------------------------
Plan hash value: 1335039132
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 11 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 11 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EASY_OBJECTS | 2848 | 31328 | 11 (0)| 00:00:01
|* 3 | INDEX RANGE SCAN | UK_EASY_OBJECTS | 513 | | 3 (0)| 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"='VALID')
3 - access("OBJECT_ID">TO_NUMBER(:EASY_VALUE))
Statistiques
----------------------------------------------------------
1 recursive calls
0 db block gets
815 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- Nous allons générer le plan d’exécution avec une lecture complète (FULL SCAN) sur la table EASY_OBJECTS en posant un hint sur la requête initiale.
SQL> alter system flush buffer_cache;
SQL> select /*+ FULL(easy_objects) */ count(*) from easy_objects where object_id > :easy_value and status='VALID';
Plan d'execution
----------------------------------------------------------
Plan hash value: 3729411821
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 228 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| * 2 | TABLE ACCESS FULL| EASY_OBJECTS | 2848 | 31328 | 228 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - filter("OBJECT_ID">TO_NUMBER(:EASY_VALUE) AND "STATUS"='VALID')
Statistiques
----------------------------------------------------------
57 recursive calls 0 db block gets
845 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
- Visualisons les identifiants des requêtes
SQL> set autotrace off SQL> col sql_text for a100 SQL> select sql_id, sql_text from v$sql where sql_text like '%easy_objects%' and sql_text like 'select %' and sql_text not like 'select sql_id%'; SQL_ID SQL_TEXT ------------- -------------------------------------------------------------------------------- 8zs2qh9uq4dy1 select /*+ FULL(easy_objects) */ count(*) from easy_objects where object_id > :easy_value and status='VALID' 8ddrupfkp0phy select count(*) from easy_objects where object_id > :easy_value and status='VALID'
- Identifions le plan d’exécution de la requête dont nous désirons figer le plan.
SQL> select distinct t.sql_id, p.plan_hash_value from v$sqltext t, v$sql_plan p where t.sql_id = p.sql_id and p.sql_id = '8ddrupfkp0phy'; SQL_ID PLAN_HASH_VALUE ------------- --------------- 8ddrupfkp0phy 1335039132
Il suffit maintenant de substituer le plan d’exécution 1335039132 pour la requête ‘8ddrupfkp0phy’ par celui valide 3729411821 de la requête ‘8zs2qh9uq4dy1’. Nous allons récupérer les informations du champs OTHER_XML pour le plan d’exécution valide et l’importer dans la requête dont nous désirons figer le plan d’exécution. Le script SQL_Profile.sql génère les commandes SQL qui doivent être exécutées pour importer le plan d’exécution (DBMS_SQLTUNE.IMPORT_SQL_PROFILE)
- Récupération des informations du plan d’exécution valide
- Import des informations du plan dans la requête à figer
SQL> @SQL_Profile.sql
Enter good SQL ID:- 8zs2qh9uq4dy1
Enter good SQL plan_hash_value :- 3729411821
Enter child number of good SQL:- 0
Enter bad SQL ID to be fixed:- 8ddrupfkp0phy
Enter bad SQL plan_hash_value:- 1335039132
'======================= OUTPUT ======================='
declare
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.2'')',
'DB_VERSION(''11.2.0.2'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "EASY_OBJECTS"@"SEL$1")',
'END_OUTLINE_DATA');
for sql_rec in (
select t.sql_id, t.sql_text
from v$sqltext t, v$sql_plan p
where t.sql_id = p.sql_id
and p.sql_id = '8ddrupfkp0phy'
and p.plan_hash_value = 1335039132
and p.parent_id is null
) loop
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => sql_rec.sql_text,
profile => ar_profile_hints,
name => 'PROFILE_8ddrupfkp0phy',
replace => true,
FORCE_MATCH => TRUE);
end loop;
end;
/
'======================= OUTPUT ======================='
Nous recopions les lignes entre les deux bornes OUTPUT et les exécutons sous sqlplus, nous pouvons vérifier le changement de plan d’exécution pour la requête.
SQL> set autotrace traceonly
SQL> select count(*) from easy_objects where object_id > :easy_value and status='VALID';
1 ligne selectionnee.
Plan d'execution
----------------------------------------------------------
Plan hash value: 3729411821
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 228 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| EASY_OBJECTS | 2848 | 31328 | 228 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">TO_NUMBER(:EASY_VALUE) AND "STATUS"='VALID')
Note
-----
- SQL profile "PROFILE_8ddrupfkp0phy" used for this statement
Statistiques
----------------------------------------------------------
21 recursive calls
826 db block gets
826 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select sql_id, sql_profile , sql_text from v$sql where sql_profile is not null;
SQL_ID SQL_PROFILE SQL_TEXT
-------------- --------------------- ---------------------------------------------
g0rsggu6r1vsh PROFILE_8ddrupfkp0phy select count(*) from easy_objects where object_id > :easy_value and status='VALID'
- La suppression du plan SQL_Profile s’effectue de la manière suivante
SQL> exec dbms_sqltune.drop_sql_profile('PROFILE_8ddrupfkp0phy');
Vous trouverez ci-joint le script SQL_Profile.sql pour générer la sortie à exécuter dans sqlplus.
accept HINTED_SQL_ID prompt 'Enter good SQL ID:- '
accept GOOD_PLAN_HASH_VALUE prompt 'Enter good SQL plan_hash_value :- '
accept CHILD_NO prompt 'Enter child number of good SQL:- '
accept BAD_SQL_ID prompt 'Enter bad SQL ID to be fixed:- '
accept PLAN_HASH_VALUE prompt 'Enter bad SQL plan_hash_value:- '
set pagesize 0
set line 9999
set verify off;
set heading off;
set feedback off;
set echo off;
set pagesize 0
prompt '======================= OUTPUT ======================='
select CHR(10) from dual;
select 'declare'
||CHR(10)||CHR(9)
||'ar_profile_hints sys.sqlprof_attr;'
||CHR(10)
||'begin'||CHR(10)||CHR(9)
||'ar_profile_hints := sys.sqlprof_attr('||CHR(10)||CHR(9)
||'''BEGIN_OUTLINE_DATA'','
from dual;
select CHR(9)||''''
||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
|| ''','
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '&HINTED_SQL_ID'
and plan_hash_value = &GOOD_PLAN_HASH_VALUE
and CHILD_NUMBER = 0
and other_xml is not null)) d;
select CHR(9)
||'''END_OUTLINE_DATA'');'||CHR(10)||CHR(9)
||'for sql_rec in ('||CHR(10)||CHR(9)
||'select t.sql_id, t.sql_text'||CHR(10)||CHR(9)
||'from v$sqltext t, v$sql_plan p'||CHR(10)||CHR(9)
||'where t.sql_id = p.sql_id'||CHR(10)||CHR(9)
||'and p.sql_id = '''||'&BAD_SQL_ID'||'''' ||CHR(10)||CHR(9)
||'and p.plan_hash_value = '||&PLAN_HASH_VALUE ||CHR(10)||CHR(9)
||'and p.parent_id is null'||CHR(10)||') loop' ||CHR(10)
||'DBMS_SQLTUNE.IMPORT_SQL_PROFILE(' ||CHR(10)||CHR(9)
||'sql_text => sql_rec.sql_text,'||CHR(10)||CHR(9)
||'profile => ar_profile_hints,' ||CHR(10)||CHR(9)
||'name => ''PROFILE_'||'&BAD_SQL_ID'||''','||CHR(10)||CHR(9)
||'replace => true,'||CHR(10)||CHR(9)
||'FORCE_MATCH => TRUE); '||CHR(10)||CHR(9)
|| 'end loop;'||CHR(10)|| 'end;'|| CHR(10)||'/'
from dual;
select CHR(10) from dual;
prompt '======================= OUTPUT ======================='
