Pour poster un plan d’exécution dans les forums, il est important de montrer les statistiques d’exécution et pas seulement les estimations – car s’il on a un problème c’est peut-être justement que les estimations sont fausses.
Voici comment faire facilement sous sqlplus.
Les exemples complets sont ici: demo
1. On s’assure que SQLPlus ne lance pas d’autres requêtes
set serveroutput off autotrace off
et que le format est lisible
set pagesize 1000 linesize 200 trimspool on
2. On ajoute le hint GATHER_PLAN_STATISTICS et on exécute la requête:
select /*+ GATHER_PLAN_STATISTICS */ level,ename,dname,sal from emp join dept using(deptno) connect by empno= prior mgr start with empno=7499 order by 1 desc;
| LEVEL | ENAME | DNAME | SAL |
|---|---|---|---|
| 3 | KING | ACCOUNTING | 5000 |
| 2 | BLAKE | SALES | 2850 |
| 1 | ALLEN | SALES | 1600 |
3. On affiche les stats avec DBMS_XPLAN:
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
SQL_ID 9uxp6bpf203yn, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ level,ename,dname,sal from emp
join dept using(deptno) connect by empno= prior mgr start with
empno=7499 order by 1 desc
Plan hash value: 2206269999
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 12 | | | |
| 1 | SORT ORDER BY | | 1 | 2 | 3 |00:00:00.01 | 12 | 2048 | 2048 | 2048 (0)|
|* 2 | CONNECT BY WITH FILTERING | | 1 | | 3 |00:00:00.01 | 12 | 2048 | 2048 | 2048 (0)|
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 8 | NESTED LOOPS | | 3 | 1 | 2 |00:00:00.01 | 8 | | | |
| 9 | NESTED LOOPS | | 3 | 1 | 2 |00:00:00.01 | 4 | | | |
| 10 | CONNECT BY PUMP | | 3 | | 3 |00:00:00.01 | 0 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 1 | 2 |00:00:00.01 | 4 | | | |
|* 12 | INDEX UNIQUE SCAN | PK_EMP | 3 | 1 | 2 |00:00:00.01 | 2 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | DEPT | 2 | 1 | 2 |00:00:00.01 | 4 | | | |
|* 14 | INDEX UNIQUE SCAN | PK_DEPT | 2 | 1 | 2 |00:00:00.01 | 2 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=PRIOR NULL)
5 - access("EMP"."EMPNO"=7499)
7 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
12 - access("EMP"."EMPNO"="connect$_by$_pump$_004"."prior mgr ")
14 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)