Le hint GATHER_PLAN_STATISTICS permet d’afficher l’ensemble des statistiques d’exécution d’un plan sous Oracle. Voici dans l’exemple ci-dessous les différences de sorties selon l’utilisation ou non de ce hint.

Avant de commencer
Il faut noter que nous allons utiliser la fonction de table dbms_xplan.display_cursor qui permet d’afficher un plan d’exécution d’un ordre dans la shared pool. Si vous utilisez cette fonction avec les paramètres : null,null,’ALLSTATS LAST’, la fonction retourne les informations du dernier ordre exécuté dans la session.
2 remarques pour que ça marche :

• Il faut accéder à v$session pour ça.
• Il faut que le dernier order SQL exécuté soit effectivement l’ordre recherché. Sous SQL*Plus par exemple, ne positionnez pas « set serveroutput on » ni « set autotrace on »

Exécuter un ordre sans le hint
SQL> select e.empno, d.loc
2 from dept d, emp e
3 where empno>1000
4 and d.deptno=e.empno;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 3u40yubzq6u1g, child number 0
————————————-
select e.empno, d.loc from dept d, emp e where empno>1000 and
d.deptno=e.empno

Plan hash value: 459569910

———————————————————
| Id | Operation | Name | E-Rows |
———————————————————
| 1 | NESTED LOOPS | | 1 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 14 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
———————————————————

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

2 – access(« E ». »EMPNO »>1000)
4 – access(« D ». »DEPTNO »= »E ». »EMPNO »)
filter(« D ». »DEPTNO »>1000)

Note
—–
– Warning: basic plan statistics not available. These are only collected when:
* hint ‘gather_plan_statistics’ is used for the statement or
* parameter ‘statistics_level’ is set to ‘ALL’, at session or system level

Exécuter un ordre avec le hint
SQL> select /*+ gather_plan_statistics */ e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 34tyumtrfbxkw, child number 0
————————————-
select /*+ gather_plan_statistics */ e.empno, d.loc from dept d, emp e where empno>1000
and d.deptno=e.empno

Plan hash value: 459569910

————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————————–
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 0 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 1 | 0 |00:00:00.01 | 2 |
————————————————————————————————–

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

2 – access(« E ». »EMPNO »>1000)
4 – access(« D ». »DEPTNO »= »E ». »EMPNO »)
filter(« D ». »DEPTNO »>1000)

Pour finir…
TKPROF permet biensûr aussi d’afficher les statistiques d’un ordre SQL. Pour créer un fichier trace et exécuter TKPROF, il faut activer SQL_TRACE pour la session (ou l’event 10046) et exécuter la requête comme ci-dessous :
SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’;

SQL> select e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno;

SQL> ALTER SESSION SE
T EVENTS ‘10046 trace name context off’;

Ensuite il faut se déconnecter et de regarder le fichier généré dans le répertoire correspondant au paramètre user_dump_dest (ou $ORACLE_BASE/admin//udump en OFA)

$ ls -ltr
-rw-r—– 1 oracle oinstall 2382 Mar 22 13:24 orcl_ora_4178.trc

On exécute ensuite TKPROF comme ci-dessous
tkprof orcl_ora_4178.trc orcl_ora_4178.tkprof explan=scott/tiger sys=n

On peut enfin visualiser le contenu du fichier généré. Voici un exemple pour la requête précédente :

select e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.02 0.02 0 3 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)

Rows Row Source Operation
——- —————————————————
0 NESTED LOOPS (cr=3 pr=0 pw=0 time=5367 us)
14 INDEX RANGE SCAN PK_EMP (cr=1 pr=0 pw=0 time=636 us)(object id 51252)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=4164 us)
0 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=2973 us)(object id 51250)

Et voilà… biensûr ça ne permet pas de tuner un ordre SQL mais en apprenant où votre requête passe un temps significatif ou se trompe, c’est un début. Les advisors de la 10g vous aideront à aller encore plus loin !

GarK!