Hace unos días alguien pidió ayuda sobre un ambiente de Oracle eBusiness Suite donde los accesos al diccionario de datos eran exorbitantes, tanto que los administradores preferían no utilizar ciertas vistas en sus sentencias. La sentencia en cuestión era
select count(*) from dba_extents;
La sentencia se ejecutó sobre una base de datos Oracle 9.2.0.8 64bits, Oracle eBusiness Suite versión 11.5.9. El tiempo de ejecución inicial fue de 12:36.28 minutos. Un raw trace del proceso mostró problemas con eventos de espera de tipo “db sequential reads” y “db scattered reads”.
Ahora bien, la pregunta a demostrar es la causa de la lentitud en los accesos al diccionario de datos. Las razones típicas que uno escucharía (sin ser por eso las más probables)
-esperas de algún tipo (sumamente vago),
-cantidad de bloques (obvio, pero no se trata del problema raíz),
-fragmentación de objetos del diccionario de datos (???),
-otro,
El plan de ejecución fue el siguiente
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12440 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | DBA_EXTENTS | 287 | | 12440 |
| 3 | UNION-ALL | | | | |
| 4 | NESTED LOOPS | | 286 | 14300 | 4204 |
| 5 | NESTED LOOPS | | 286 | 12012 | 4204 |
| 6 | VIEW | SYS_DBA_SEGS | 252 | 6552 | 3952 |
| 7 | UNION-ALL | | | | |
| 8 | NESTED LOOPS | | 2 | 214 | 8487 |
| 9 | NESTED LOOPS OUTER | | 2 | 206 | 8485 |
| 10 | NESTED LOOPS | | 2 | 198 | 8483 |
| 11 | HASH JOIN | | 52 | 4472 | 8379 |
| 12 | NESTED LOOPS | | 541 | 15148 | 253 |
| 13 | TABLE ACCESS FULL | SEG$ | 541 | 10820 | 253 |
| 14 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 15 | VIEW | SYS_OBJECTS | 36885 | 2089K| 8125 |
| 16 | UNION-ALL | | | | |
| 17 | TABLE ACCESS FULL | TAB$ | 176 | 3872 | 2024 |
| 18 | TABLE ACCESS FULL | TABPART$ | 870 | 16530 | 5 |
| 19 | TABLE ACCESS FULL | CLU$ | 10 | 130 | 2024 |
| 20 | TABLE ACCESS FULL | IND$ | 31236 | 671K| 2024 |
| 21 | TABLE ACCESS FULL | INDPART$ | 4581 | 87039 | 18 |
| 22 | TABLE ACCESS FULL | LOB$ | 9 | 198 | 2024 |
| 23 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 52 | 2 |
| 24 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 52 | 2 |
| 25 | TABLE ACCESS FULL | LOBFRAG$ | 1 | 54 | 2 |
| 26 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 13 | 2 |
| 27 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 |
| 28 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 |
| 29 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 30 | TABLE ACCESS CLUSTER | TS$ | 1 | 4 | 1 |
| 31 | INDEX UNIQUE SCAN | I_TS# | 1 | | |
| 32 | NESTED LOOPS | | 1 | 56 | 16 |
| 33 | NESTED LOOPS OUTER | | 1 | 52 | 15 |
| 34 | NESTED LOOPS | | 1 | 48 | 14 |
| 35 | NESTED LOOPS | | 12 | 288 | 2 |
| 36 | TABLE ACCESS FULL | UNDO$ | 12 | 192 | 2 |
| 37 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 38 | TABLE ACCESS CLUSTER | SEG$ | 1 | 24 | 1 |
| 39 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | |
| 40 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 |
| 41 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 42 | TABLE ACCESS CLUSTER | TS$ | 1 | 4 | 1 |
| 43 | INDEX UNIQUE SCAN | I_TS# | 1 | | |
| 44 | NESTED LOOPS | | 250 | 10000 | 320 |
| 45 | HASH JOIN | | 250 | 8000 | 320 |
| 46 | TABLE ACCESS FULL | TS$ | 378 | 1512 | 61 |
| 47 | HASH JOIN OUTER | | 250 | 7000 | 258 |
| 48 | TABLE ACCESS FULL | SEG$ | 250 | 6000 | 253 |
| 49 | TABLE ACCESS FULL | USER$ | 242 | 968 | 4 |
| 50 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 51 | TABLE ACCESS CLUSTER | UET$ | 1 | 16 | 1 |
| 52 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | |
| 53 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 54 | NESTED LOOPS | | 1 | 86 | 8236 |
| 55 | NESTED LOOPS | | 1 | 78 | 8236 |
| 56 | VIEW | SYS_DBA_SEGS | 252 | 6552 | 3952 |
| 57 | UNION-ALL | | | | |
| 58 | NESTED LOOPS | | 2 | 214 | 8487 |
| 59 | NESTED LOOPS OUTER | | 2 | 206 | 8485 |
| 60 | NESTED LOOPS | | 2 | 198 | 8483 |
| 61 | HASH JOIN | | 52 | 4472 | 8379 |
| 62 | NESTED LOOPS | | 541 | 15148 | 253 |
| 63 | TABLE ACCESS FULL | SEG$ | 541 | 10820 | 253 |
| 64 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 65 | VIEW | SYS_OBJECTS | 36885 | 2089K| 8125 |
| 66 | UNION-ALL | | | | |
| 67 | TABLE ACCESS FULL | TAB$ | 176 | 3872 | 2024 |
| 68 | TABLE ACCESS FULL | TABPART$ | 870 | 16530 | 5 |
| 69 | TABLE ACCESS FULL | CLU$ | 10 | 130 | 2024 |
| 70 | TABLE ACCESS FULL | IND$ | 31236 | 671K| 2024 |
| 71 | TABLE ACCESS FULL | INDPART$ | 4581 | 87039 | 18 |
| 72 | TABLE ACCESS FULL | LOB$ | 9 | 198 | 2024 |
| 73 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 52 | 2 |
| 74 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 52 | 2 |
| 75 | TABLE ACCESS FULL | LOBFRAG$ | 1 | 54 | 2 |
| 76 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 13 | 2 |
| 77 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 1 |
| 78 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 |
| 79 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 80 | TABLE ACCESS CLUSTER | TS$ | 1 | 4 | 1 |
| 81 | INDEX UNIQUE SCAN | I_TS# | 1 | | |
| 82 | NESTED LOOPS | | 1 | 56 | 16 |
| 83 | NESTED LOOPS OUTER | | 1 | 52 | 15 |
| 84 | NESTED LOOPS | | 1 | 48 | 14 |
| 85 | NESTED LOOPS | | 12 | 288 | 2 |
| 86 | TABLE ACCESS FULL | UNDO$ | 12 | 192 | 2 |
| 87 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 88 | TABLE ACCESS CLUSTER | SEG$ | 1 | 24 | 1 |
| 89 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | |
| 90 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 |
| 91 | INDEX UNIQUE SCAN | I_USER# | 1 | | |
| 92 | TABLE ACCESS CLUSTER | TS$ | 1 | 4 | 1 |
| 93 | INDEX UNIQUE SCAN | I_TS# | 1 | | |
| 94 | NESTED LOOPS | | 250 | 10000 | 320 |
| 95 | HASH JOIN | | 250 | 8000 | 320 |
| 96 | TABLE ACCESS FULL | TS$ | 378 | 1512 | 61 |
| 97 | HASH JOIN OUTER | | 250 | 7000 | 258 |
| 98 | TABLE ACCESS FULL | SEG$ | 250 | 6000 | 253 |
| 99 | TABLE ACCESS FULL | USER$ | 242 | 968 | 4 |
| 100 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
| 101 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 52 | 17 |
| 102 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | |
-------------------------------------------------------------------------------------------El plan de ejecución nos muestra incongruencias en la cardinalidad de tablas (de acuerdo al resultado de la sentencia) y también asumimos problemas en los costos.
A propósito de lo que se dice a menudo sin fundamentos sobre Oracle, existe una nota 375944.1 que dice “Analyze on Sys Tables Degrade Performance” (analyze en tablas de sys degrada el desempeño), la recomendación es hacer el upgrade a la verisón 9.2.0.8. Pero también existe una nota 457926.1 que dice que en ocasiones es bueno ejecutar estadísticas sobre las tablas de sys.
No hay como comprobar un enunciado, de modo que primero buscamos si existen estadísticas sobre las tablas involucradas en la vista dba_extents, para saber si se han ejecutado estadísticas en la tabla puede leerse la página “cómo saber si una tabla x$ tiene estadísticas“.
Dado que no habían estadísticas en las tablas involucradas en la vista entendemos la razón por la que el plan de ejecución fuera tan ineficiente.
El plan de ejecución nos ayuda a identificar que la causa raiz del problema no está en lo que comúnmente y sin fundamentos se pudo haber supuesto, es decir en
los eventos de espera,
la cantidad de bloques,
en la fragmentación de objetos del diccionario de datos.
El problema está en el plan de ejecución, que a su vez lo prefiere el optimizador en base a que las estadísticas no son las correctas.
Y obviamente el plan de ejecución tuvo cambios muy importantes,
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2507T| 405P| | 81G|
| 1 | VIEW | DBA_EXTENTS | 2507T| 405P| | 81G|
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 2359T| 440P| | 33M|
|* 4 | HASH JOIN | | 33M| 1925M| | 25 |
| 5 | TABLE ACCESS FULL | UET$ | 2057 | 43197 | | 3 |
| 6 | TABLE ACCESS FULL | FILE$ | 16360 | 623K| | 11 |
|* 7 | VIEW | SYS_DBA_SEGS | 70M| 9G| | 1 |
| 8 | UNION-ALL PARTITION | | | | | |
|* 9 | HASH JOIN | | 1771 | 446K| | 131 |
| 10 | NESTED LOOPS | | 43 | 9245 | | 128 |
| 11 | NESTED LOOPS OUTER | | 4 | 756 | | 128 |
| 12 | NESTED LOOPS | | 4 | 636 | | 124 |
|* 13 | HASH JOIN | | 3 | 258 | | 121 |
|* 14 | TABLE ACCESS FULL | SEG$ | 3 | 51 | | 3 |
|* 15 | VIEW | SYS_OBJECTS | 98814 | 6658K| | 99 |
| 16 | UNION-ALL | | | | | |
|* 17 | TABLE ACCESS FULL | TAB$ | 164 | 10660 | | 11 |
| 18 | TABLE ACCESS FULL | TABPART$ | 16360 | 830K| | 11 |
| 19 | TABLE ACCESS FULL | CLU$ | 16360 | 830K| | 11 |
|* 20 | TABLE ACCESS FULL | IND$ | 164 | 10660 | | 11 |
| 21 | TABLE ACCESS FULL | INDPART$ | 16360 | 830K| | 11 |
|* 22 | TABLE ACCESS FULL | LOB$ | 326 | 21190 | | 11 |
| 23 | TABLE ACCESS FULL | TABSUBPART$ | 16360 | 830K| | 11 |
| 24 | TABLE ACCESS FULL | INDSUBPART$ | 16360 | 830K| | 11 |
| 25 | TABLE ACCESS FULL | LOBFRAG$ | 16360 | 862K| | 11 |
|* 26 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 2 | 146 | | 1 |
|* 27 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | | |
| 28 | TABLE ACCESS CLUSTER | USER$ | 1 | 30 | | 1 |
|* 29 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |
|* 30 | INDEX UNIQUE SCAN | I_FILE2 | 10 | 260 | | |
| 31 | TABLE ACCESS CLUSTER | TS$ | 164 | 7052 | | 2 |
|* 32 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 1 |
| 33 | NESTED LOOPS OUTER | | 49 | 9212 | | 14 |
| 34 | NESTED LOOPS | | 1 | 158 | | 13 |
| 35 | NESTED LOOPS | | 1 | 115 | | 12 |
| 36 | NESTED LOOPS | | 1 | 95 | | 11 |
|* 37 | TABLE ACCESS FULL | UNDO$ | 8 | 552 | | 11 |
|* 38 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 26 | | |
|* 39 | TABLE ACCESS CLUSTER | SEG$ | 1 | 20 | | 1 |
|* 40 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | |
| 41 | TABLE ACCESS CLUSTER | TS$ | 41 | 1763 | | 1 |
|* 42 | INDEX UNIQUE SCAN | I_TS# | 1 | | | |
| 43 | TABLE ACCESS CLUSTER | USER$ | 4090 | 119K| | 1 |
|* 44 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |
| 45 | MERGE JOIN | | 1742K| 219M| | 15 |
|* 46 | HASH JOIN | | 42605 | 3702K| | 11 |
| 47 | TABLE ACCESS BY INDEX ROWID | FILE$ | 164 | 6396 | | 6 |
|* 48 | INDEX RANGE SCAN | I_FILE2 | 1 | | | 2 |
| 49 | NESTED LOOPS OUTER | | 4167 | 203K| | 4 |
|* 50 | TABLE ACCESS FULL | SEG$ | 1 | 20 | | 3 |
| 51 | TABLE ACCESS CLUSTER | USER$ | 4090 | 119K| | 1 |
|* 52 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |
|* 53 | SORT JOIN | | 164 | 7052 | | 4 |
| 54 | TABLE ACCESS CLUSTER | TS$ | 164 | 7052 | | 2 |
|* 55 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 1 |
| 56 | NESTED LOOPS | | 147T| 27P| | 81G|
| 57 | NESTED LOOPS | | 81G| 12T| | 710M|
| 58 | VIEW | SYS_DBA_SEGS | 69G| 9747G| | 384M|
| 59 | UNION-ALL | | | | | |
| 60 | NESTED LOOPS | | 70M| 17G| | 165 |
| 61 | NESTED LOOPS | | 69284 | 15M| | 165 |
| 62 | NESTED LOOPS OUTER | | 17 | 3213 | | 148 |
| 63 | NESTED LOOPS | | 17 | 2703 | | 131 |
|* 64 | HASH JOIN | | 10 | 860 | | 121 |
|* 65 | TABLE ACCESS FULL | SEG$ | 10 | 170 | | 3 |
| 66 | VIEW | SYS_OBJECTS | 98814 | 6658K| | 99 |
| 67 | UNION-ALL | | | | | |
|* 68 | TABLE ACCESS FULL | TAB$ | 164 | 10660 | | 11 |
| 69 | TABLE ACCESS FULL | TABPART$ | 16360 | 830K| | 11 |
| 70 | TABLE ACCESS FULL | CLU$ | 16360 | 830K| | 11 |
|* 71 | TABLE ACCESS FULL | IND$ | 164 | 10660 | | 11 |
| 72 | TABLE ACCESS FULL | INDPART$ | 16360 | 830K| | 11 |
|* 73 | TABLE ACCESS FULL | LOB$ | 326 | 21190 | | 11 |
| 74 | TABLE ACCESS FULL | TABSUBPART$ | 16360 | 830K| | 11 |
| 75 | TABLE ACCESS FULL | INDSUBPART$ | 16360 | 830K| | 11 |
| 76 | TABLE ACCESS FULL | LOBFRAG$ | 16360 | 862K| | 11 |
|* 77 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 2 | 146 | | 1 |
|* 78 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | | |
| 79 | TABLE ACCESS CLUSTER | USER$ | 1 | 30 | | 1 |
|* 80 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |
| 81 | TABLE ACCESS CLUSTER | TS$ | 4090 | 171K| | 1 |
|* 82 | INDEX UNIQUE SCAN | I_TS# | 1 | | | |
|* 83 | INDEX UNIQUE SCAN | I_FILE2 | 1022 | 26572 | | |
| 84 | NESTED LOOPS | | 310 | 58280 | | 14 |
| 85 | NESTED LOOPS OUTER | | 1 | 145 | | 13 |
| 86 | NESTED LOOPS | | 1 | 115 | | 12 |
| 87 | NESTED LOOPS | | 1 | 95 | | 11 |
|* 88 | TABLE ACCESS FULL | UNDO$ | 818 | 56442 | | 11 |
|* 89 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 26 | | |
|* 90 | TABLE ACCESS CLUSTER | SEG$ | 1 | 20 | | 1 |
|* 91 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | |
| 92 | TABLE ACCESS CLUSTER | USER$ | 4090 | 119K| | 1 |
|* 93 | INDEX UNIQUE SCAN | I_USER# | 1 | | | |
| 94 | TABLE ACCESS CLUSTER | TS$ | 4090 | 171K| | 1 |
|* 95 | INDEX UNIQUE SCAN | I_TS# | 1 | | | |
|* 96 | HASH JOIN | | 69G| 8568G| 880K| 60914 |
| 97 | TABLE ACCESS FULL | TS$ | 16360 | 686K| | 11 |
|* 98 | HASH JOIN OUTER | | 17M| 1446M| 304K| 54 |
| 99 | NESTED LOOPS | | 4167 | 240K| | 7 |
|*100 | TABLE ACCESS FULL | SEG$ | 4 | 80 | | 3 |
| 101 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1022 | 39858 | | 1 |
|*102 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | |
| 103 | TABLE ACCESS FULL | USER$ | 16360 | 479K| | 11 |
|*104 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 23 | | 1 |
| 105 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1818 | 70902 | | 1 |
|*106 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | |
---------------------------------------------------------------------------------------------------