Privilegios necesarios para ejecutar un Explain Plan

Como sabemos, una de las maneras que tenemos  para obtener el plan de ejecución de una sentencia SQL es utilizar  la cláusula EXPLAIN PLAN.  De esta forma podemos determinar el plan de ejecución que una base de datos Oracle esta utiliza o utilizará para la ejecución  de determinada instrucción SQL. El comando EXPLAIN PLAN, inserta una fila en una tabla especifica (output table),  con la descripción de cada operación correspondiente a  cada paso del plan de ejecución. También se puede utilizar EXPLAIN PLAN como parte de la herramienta SQL trace.

EXPLAIN PLAN también determina el costo de ejecutar la sentencia SQL. En el costo calculado, entran en juego diversas variables como por ejemplo el número de bloques leídos, tiempo de lectura, lectura simple o multibloque, ciclos de procesador, velocidad de CPU, etc. pero en definitiva es lo que hace que Oracle determine un plan de ejecución específico sobre todos los calculados.

En el software de instalación del motor de base de datos podemos encontrar un script de ejemplo con la definición de la output table (PLAN_TABLE). La tabla de salida que finalmente  utilicemos debe tener los mismos nombres de columna y tipos de datos que esta tabla. Por lo general , el nombre común de este script es utlxplan.sql en versiones anteriores a 10.2 y catplan.sql a partir de esta versión. Lo podemos encontrar en @?/rdbms/admin/

La definición de la PLAN_TABLE (output table) en el script catplan.sql es la siguiente:

SQL> desc plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

Oracle Database también proporciona información sobre cursores en memoria a través de varias vistas dinámicas (v$). Para que un usuario no privilegiado pueda acceder a estas vistas (o cualquier vista dinámica), es necesario llevar adelante cualquiera de estas tres opciones:

• Otorgar privilegios de SELECT sobre las vistas base.
• Asignar el rol SELECT_CATALOG_ROLE. 
• Asignar el privilegio de sistema  SELECT ANY DICTIONARY

En caso de no cumplir con alguna de estas condiciones, obtendremos el siguiente error al tratar de consultar:

SQL> select * from v$sql_workarea;
select * from v$sql_workarea
              *
ERROR at line 1:

ORA-00942: table or view does not exist

Para otorgar los privilegios al usuario (user1), en este ejemplo con usuario sys debemos ejecutar la siguiente sentencia:

Para el caso de una vista en particular:

SQL> grant select on v_$sql_workarea to user1;

Grant succeeded.

Rol SELECT_CATALOG_ROLE:

SQL> grant select_catalog_role to user1;

Grant succeeded.

Privilegio SELECT ANY DICTIONARY:

SQL> grant select any dictionary to user1;

Grant succeeded.

 Por ejemplo, para obtener información sobre las áreas de trabajo (work areas) utilizadas por los cursores SQL podemos consultar la vista  v$sql_workarea.

SQL> desc v$sql_workarea

Name                   Type         Nullable Default Comments

---------------------- ------------ -------- ------- --------

ADDRESS                RAW(8)       Y                        

HASH_VALUE             NUMBER       Y                        

SQL_ID                 VARCHAR2(13) Y                        

CHILD_NUMBER           NUMBER       Y                         

WORKAREA_ADDRESS       RAW(8)       Y                        

OPERATION_TYPE         VARCHAR2(20) Y                        

OPERATION_ID           NUMBER       Y                        

POLICY                 VARCHAR2(10) Y                         

ESTIMATED_OPTIMAL_SIZE NUMBER       Y                        

ESTIMATED_ONEPASS_SIZE NUMBER       Y                        

LAST_MEMORY_USED       NUMBER       Y                        

LAST_EXECUTION         VARCHAR2(10) Y                         

LAST_DEGREE            NUMBER       Y                        

TOTAL_EXECUTIONS       NUMBER       Y                        

OPTIMAL_EXECUTIONS     NUMBER       Y                        

ONEPASS_EXECUTIONS     NUMBER       Y                         

MULTIPASSES_EXECUTIONS NUMBER       Y                        

ACTIVE_TIME            NUMBER       Y                        

MAX_TEMPSEG_SIZE       NUMBER       Y                        

LAST_TEMPSEG_SIZE      NUMBER       Y        

Para obtener  el plan de ejecución de un cursor en memoria,  podemos consultar  v$sql_plan.

SQL> desc v$sql_plan

Name              Type           Nullable Default Comments

----------------- -------------- -------- ------- --------

ADDRESS           RAW(8)         Y                        

HASH_VALUE        NUMBER         Y                        

SQL_ID            VARCHAR2(13)   Y                        

PLAN_HASH_VALUE   NUMBER         Y                        

CHILD_ADDRESS     RAW(8)         Y                         

CHILD_NUMBER      NUMBER         Y                        

TIMESTAMP         DATE           Y                        

OPERATION         VARCHAR2(30)   Y                        

OPTIONS           VARCHAR2(30)   Y                        

OBJECT_NODE       VARCHAR2(40)   Y                        

OBJECT#           NUMBER         Y                        

OBJECT_OWNER      VARCHAR2(30)   Y                        

OBJECT_NAME       VARCHAR2(30)   Y                        

OBJECT_ALIAS      VARCHAR2(65)   Y                        

OBJECT_TYPE       VARCHAR2(20)   Y                        

OPTIMIZER         VARCHAR2(20)   Y                        

ID                NUMBER         Y                        

PARENT_ID         NUMBER         Y                        

DEPTH             NUMBER         Y                        

POSITION          NUMBER         Y                        

SEARCH_COLUMNS    NUMBER         Y                        

COST              NUMBER         Y                         

CARDINALITY       NUMBER         Y                        

BYTES             NUMBER         Y                        

OTHER_TAG         VARCHAR2(35)   Y                        

PARTITION_START   VARCHAR2(64)   Y                        

PARTITION_STOP    VARCHAR2(64)   Y                        

PARTITION_ID      NUMBER         Y                        

OTHER             VARCHAR2(4000) Y                        

DISTRIBUTION      VARCHAR2(20)   Y                        

CPU_COST          NUMBER         Y                        

IO_COST           NUMBER         Y                        

TEMP_SPACE        NUMBER         Y                        

ACCESS_PREDICATES VARCHAR2(4000) Y                        

FILTER_PREDICATES VARCHAR2(4000) Y                         

PROJECTION        VARCHAR2(4000) Y                        

TIME              NUMBER         Y                        

QBLOCK_NAME       VARCHAR2(30)   Y                        

REMARKS           VARCHAR2(4000) Y                         

OTHER_XML         CLOB           Y

Para estadísticas de ejecución por cada paso u operación de un plan de ejecución de los cursores en caché (por ejemplo, número de filas devueltas, número de bloques leídos, etc.), podemos consultar  v$sql_plan_statistics.

SQL> desc v$sql_plan_statistics

Name                Type         Nullable Default Comments

------------------- ------------ -------- ------- --------

ADDRESS             RAW(8)       Y                        

HASH_VALUE          NUMBER       Y                        

SQL_ID              VARCHAR2(13) Y                        

PLAN_HASH_VALUE     NUMBER       Y                        

CHILD_ADDRESS       RAW(8)       Y                        

CHILD_NUMBER        NUMBER       Y                         

OPERATION_ID        NUMBER       Y                        

EXECUTIONS          NUMBER       Y                        

LAST_STARTS         NUMBER       Y                        

STARTS              NUMBER       Y                        

LAST_OUTPUT_ROWS    NUMBER       Y                        

OUTPUT_ROWS         NUMBER       Y                        

LAST_CR_BUFFER_GETS NUMBER       Y                        

CR_BUFFER_GETS      NUMBER       Y                        

LAST_CU_BUFFER_GETS NUMBER       Y                        

CU_BUFFER_GETS      NUMBER       Y                        

LAST_DISK_READS     NUMBER       Y                        

DISK_READS          NUMBER       Y                        

LAST_DISK_WRITES    NUMBER       Y                         

DISK_WRITES         NUMBER       Y                        

LAST_ELAPSED_TIME   NUMBER       Y                        

ELAPSED_TIME        NUMBER       Y 

Finalmente, para obtener un join  de las tres vistas anteriores, podemos consultar  v$sql_plan_statistics_all.

SQL> desc v$sql_plan_statistics_all

Name                   Type           Nullable Default Comments

---------------------- -------------- -------- ------- --------

ADDRESS                RAW(8)         Y                         

HASH_VALUE             NUMBER         Y                        

SQL_ID                 VARCHAR2(13)   Y                        

PLAN_HASH_VALUE        NUMBER         Y                        

CHILD_ADDRESS          RAW(8)         Y                         

CHILD_NUMBER           NUMBER         Y                        

TIMESTAMP              DATE           Y                        

OPERATION              VARCHAR2(30)   Y                        

OPTIONS                VARCHAR2(30)   Y                         

OBJECT_NODE            VARCHAR2(40)   Y                        

OBJECT#                NUMBER         Y                        

OBJECT_OWNER           VARCHAR2(30)   Y                        

OBJECT_NAME            VARCHAR2(30)   Y                         

OBJECT_ALIAS           VARCHAR2(65)   Y                        

OBJECT_TYPE            VARCHAR2(20)   Y                        

OPTIMIZER              VARCHAR2(20)   Y                        

ID                     NUMBER         Y                         

PARENT_ID              NUMBER         Y                        

DEPTH                  NUMBER         Y                        

POSITION               NUMBER         Y                        

SEARCH_COLUMNS         NUMBER         Y                         

COST                   NUMBER         Y                        

CARDINALITY            NUMBER         Y                        

BYTES                  NUMBER         Y                        

OTHER_TAG              VARCHAR2(35)   Y                        

PARTITION_START        VARCHAR2(64)   Y                        

PARTITION_STOP         VARCHAR2(64)   Y                        

PARTITION_ID           NUMBER         Y                        

OTHER                  VARCHAR2(4000) Y                        

DISTRIBUTION           VARCHAR2(20)   Y                        

CPU_COST               NUMBER         Y                        

IO_COST                NUMBER         Y                        

TEMP_SPACE             NUMBER         Y                        

ACCESS_PREDICATES      VARCHAR2(4000) Y                        

FILTER_PREDICATES      VARCHAR2(4000) Y                        

PROJECTION             VARCHAR2(4000) Y                        

TIME                   NUMBER         Y                        

QBLOCK_NAME            VARCHAR2(30)   Y                        

REMARKS                VARCHAR2(4000) Y                        

OTHER_XML              CLOB           Y                        

EXECUTIONS             NUMBER         Y                        

LAST_STARTS            NUMBER         Y                        

STARTS                 NUMBER         Y                        

LAST_OUTPUT_ROWS       NUMBER         Y                        

OUTPUT_ROWS            NUMBER         Y                        

LAST_CR_BUFFER_GETS    NUMBER         Y                        

CR_BUFFER_GETS         NUMBER         Y                        

LAST_CU_BUFFER_GETS    NUMBER         Y                        

CU_BUFFER_GETS         NUMBER         Y                        

LAST_DISK_READS        NUMBER         Y                        

DISK_READS             NUMBER         Y                        

LAST_DISK_WRITES       NUMBER         Y                        

DISK_WRITES            NUMBER         Y                        

LAST_ELAPSED_TIME      NUMBER         Y                        

ELAPSED_TIME           NUMBER         Y                        

POLICY                 VARCHAR2(10)   Y                        

ESTIMATED_OPTIMAL_SIZE NUMBER         Y                        

ESTIMATED_ONEPASS_SIZE NUMBER         Y                        

LAST_MEMORY_USED       NUMBER         Y                        

LAST_EXECUTION         VARCHAR2(10)   Y                        

LAST_DEGREE            NUMBER         Y                        

TOTAL_EXECUTIONS       NUMBER         Y                        

OPTIMAL_EXECUTIONS     NUMBER         Y                        

ONEPASS_EXECUTIONS     NUMBER         Y                         

MULTIPASSES_EXECUTIONS NUMBER         Y                        

ACTIVE_TIME            NUMBER         Y                        

MAX_TEMPSEG_SIZE       NUMBER         Y                        

LAST_TEMPSEG_SIZE      NUMBER         Y                         

Las estadísticas de ejecución para cada paso u operación del plan de ejecución de los cursores en caché, se pueden observar en la vista  v$sql_plan_monitor si la ejecución de la sentencia es monitoreada. Podemos forzar el monitoreo utilizando el hint  MONITOR.

SQL> desc v$sql_plan_monitor

Name                    Type         Nullable Default Comments

----------------------- ------------ -------- ------- --------

KEY                     NUMBER       Y                        

STATUS                  VARCHAR2(19) Y                         

FIRST_REFRESH_TIME      DATE         Y                        

LAST_REFRESH_TIME       DATE         Y                        

FIRST_CHANGE_TIME       DATE         Y                        

LAST_CHANGE_TIME        DATE         Y                         

REFRESH_COUNT           NUMBER       Y                        

SID                     NUMBER       Y                        

PROCESS_NAME            VARCHAR2(5)  Y                        

SQL_ID                  VARCHAR2(13) Y                         

SQL_EXEC_START          DATE         Y                        

SQL_EXEC_ID             NUMBER       Y                        

SQL_PLAN_HASH_VALUE     NUMBER       Y                        

SQL_CHILD_ADDRESS       RAW(8)       Y                         

PLAN_PARENT_ID          NUMBER       Y                        

PLAN_LINE_ID            NUMBER       Y                        

PLAN_OPERATION          VARCHAR2(30) Y                        

PLAN_OPTIONS            VARCHAR2(30) Y                         

PLAN_OBJECT_OWNER       VARCHAR2(30) Y                        

PLAN_OBJECT_NAME        VARCHAR2(30) Y                        

PLAN_OBJECT_TYPE        VARCHAR2(20) Y                        

PLAN_DEPTH              NUMBER       Y                         

PLAN_POSITION           NUMBER       Y                        

PLAN_COST               NUMBER       Y                        

PLAN_CARDINALITY        NUMBER       Y                        

PLAN_BYTES              NUMBER       Y                         

PLAN_TIME               NUMBER       Y                        

PLAN_PARTITION_START    VARCHAR2(64) Y                        

PLAN_PARTITION_STOP     VARCHAR2(64) Y                        

PLAN_CPU_COST           NUMBER       Y                         

PLAN_IO_COST            NUMBER       Y                        

PLAN_TEMP_SPACE         NUMBER       Y                        

STARTS                  NUMBER       Y                        

OUTPUT_ROWS             NUMBER       Y                         

IO_INTERCONNECT_BYTES   NUMBER       Y                        

PHYSICAL_READ_REQUESTS  NUMBER       Y                        

PHYSICAL_READ_BYTES     NUMBER       Y                        

PHYSICAL_WRITE_REQUESTS NUMBER       Y                         

PHYSICAL_WRITE_BYTES    NUMBER       Y                        

WORKAREA_MEM            NUMBER       Y                        

WORKAREA_MAX_MEM        NUMBER       Y                        

WORKAREA_TEMPSEG        NUMBER       Y                         

WORKAREA_MAX_TEMPSEG    NUMBER       Y

Pre-requisitos para utilizar EXPLAIN PLAN (privilegios)

Para utilizar la  instrucción EXPLAIN PLAN, debemos tener los privilegios necesarios para insertar filas en la  tabla de salida (PLAN_TABLE) que hayamos especificado.

Lógicamente, también debemos tener los privilegios necesarios para ejecutar la sentencia SQL  de la cual  queramos obtener el plan de ejecución.  Si la instrucción SQL accede a una vista, entonces debemos tener privilegios para acceder a las tablas y vistas en las que se basa la vista. Si la vista se basa en otra vista que se basan en tablas, debemos tener también privilegios para acceder tanto a la otra vista como a sus tablas subyacentes.

Por otro lado, para  poder examinar el plan de ejecución producido por EXPLAIN PLAN, debemos tener también los privilegios necesarios para consultar la tabla de salida (output table).

La instrucción EXPLAIN PLAN es una instrucción  DML y no una sentencia DDL,  por lo tanto, Oracle Database no realiza commit  implícitamente de los cambios realizados en la tabla por la sentencia EXPLAIN PLAN. Si queremos mantener las filas generadas por un EXPLAIN PLAN en la “output table”, debemos realizar un commit de la transacción que contiene la sentencia.