Si on effectue une requête SQL et que l’on demande l’affichage de son plan d’exécutio par défaut on obtient le coût individuel de chaque action, ainsi un parcours complet de table ou d’index est associé à un coût global associé à son pourcentage de coût CPU et c’est là que ça se complique un poil. En effet, si on interroge directement les tables ou les vues du dictionnaire la relation en le contenu de la colonne CPU_COST des tables contenant les plans d’exécution rapportée à la colonne COST contenant le coût global de la l’action, on est loin de trouver un rapport quelconque avec le pourcentage affiché.
select * from sys.dual ;
D
-
X
select * from table(dbms_xplan.display_cursor()) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cps942q7r3w9m, child number 0
-------------------------------------
select * from sys.dual
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
13 rows selected.
l
1 select id
2 , lpad(' ',DEPTH)||OPERATION||' '||OPTIONS operation
3 , OBJECT_NAME NAME
4 , CARDINALITY "ROWS"
5 , BYTES
6 , COST
7 , CPU_COST
8 , IO_COST
9 from v$sql_plan
10* where plan_hash_value=272002086
/
ID OPERATION NAME ROWS BYTES COST CPU_COST IO_COST
---------- -------------------- ---- ----- ------ ----- ---------- ----------
0 SELECT STATEMENT 2
1 TABLE ACCESS FULL DUAL 1 2 2 7271 2
Comment calculer le pourcentage de CPU de la colonne COST affichée par DBMS xplan et en d’autres termes que représente-t-il ?
Si 7271 et 0 n’ont aucun rapport, il est par contre évident dans l’exemple donné que le coût lié aux lectures et écritures sur disque (IO_COST) est lui du même ordre de grandeur que le coût total. Du coup si on admet que le coût total se décompose en coût CPU et coût des entrées et sorties sur disque les 0% et 100% affichés par les outils Oracle devient évident voire trivial. On déduit assez facilement de là que le pourcentage de coût CPU est affiché en équivalent d’entrées sorties disque et est donc égal à 100 – pourcentage(IO_COST). On en déduit aussi que le coût affiché par Oracle est un coût équivalent à un nombre d’entrées sorties.
Jolie supposition, mais en vrais ?
Je vais vérifier ma théorie sur un plan choisi dans l’historique AWR et contenant une vingtaine de lignes (je ne garde que les lignes et colonnes qui m’intéressent)
select *
from table(dbms_xplan.display_awr('71mrtzv6zj1d6'))
------------------------------------------------------
| Id | Operation | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | 6 (100)|
| 1 | SORT ORDER BY | 6 (17)|
| 2 | FILTER | |
| 3 | NESTED LOOPS | 3 (0)|
| 4 | NESTED LOOPS | 2 (0)|
| 5 | TABLE ACCESS FULL | 2 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| 0 (0)|
| 7 | INDEX UNIQUE SCAN | 0 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | 1 (0)|
| 9 | INDEX UNIQUE SCAN | 0 (0)|
| 10 | FAST DUAL | 2 (0)|
| 11 | NESTED LOOPS | 2 (0)|
| 12 | INDEX RANGE SCAN | 2 (0)|
| 13 | FIXED TABLE FULL | 0 (0)|
| 14 | HASH JOIN | 3 (34)|
| 15 | INDEX RANGE SCAN | 2 (0)|
| 16 | FIXED TABLE FULL | 0 (0)|
| 17 | NESTED LOOPS | 1 (0)|
| 18 | INDEX FULL SCAN | 1 (0)|
| 19 | FIXED TABLE FULL | 0 (0)|
------------------------------------------------------
Je tente une requête
select id
, lpad(' ',DEPTH)||OPERATION||' '||OPTIONS OPERATIONS
, lpad(to_char(nvl(COST,0)),6,' ')||
lpad('('||case when nvl(cost,0) != 0
then to_char(ceil(100-((nvl(IO_COST,0)/COST)*100)))
else '0' end ||')', 6, ' ' ) "Cost (%CPU)"
from dba_hist_sql_plan
where sql_id='71mrtzv6zj1d6'
/
ID OPERATIONS Cost (%CPU)
---------- --------------------------------- ------------
0 SELECT STATEMENT 6 (100)
1 SORT ORDER BY 6 (17)
2 FILTER 0 (0)
3 NESTED LOOPS 3 (0)
4 NESTED LOOPS 2 (0)
5 TABLE ACCESS FULL 2 (0)
6 TABLE ACCESS BY INDEX ROWID 0 (0)
7 INDEX UNIQUE SCAN 0 (0)
8 TABLE ACCESS BY INDEX ROWID 1 (0)
9 INDEX UNIQUE SCAN 0 (0)
10 FAST DUAL 2 (0)
11 NESTED LOOPS 2 (0)
12 INDEX RANGE SCAN 2 (0)
13 FIXED TABLE FULL 0 (0)
14 HASH JOIN 3 (34)
15 INDEX RANGE SCAN 2 (0)
16 FIXED TABLE FULL 0 (0)
17 NESTED LOOPS 1 (0)
18 INDEX FULL SCAN 1 (0)
19 FIXED TABLE FULL 0 (0)
Apparemment c’est ça. On note au passage l’arrondi avec la fonction CEIL, qui retourne l’entier immédiatement supérieur à un nombre non entier et non de ROUND (valeur entière la plus proche d’un nombre décimal) ni FLOOR (entier immédiatement inférieur à un nombre décimal) qui sortent des valeurs erronnées (33 en ligne 14 pour le premier et 16 en ligne 2 et toujours 33 en ligne 14 pour le second).