Plano de execução - EXPLAIN PLAN

Este artigo fornece algumas maneiras de formatação dos planos de execução, apresentando o plano em um formato mais legível.

 
Esses scripts permitem que DBAs e desenvolvedores vejam os planos de execução de modo mais claro. Os scripts ficam localizados em  “$ORACLE_HOME/rdbms/admin/” o primeiro tem o nome de “utlxplan.sql”, este script é utilizado para criar a tabela PLAN_TABLE. O segundo script é o “utlxpls.sql”, este script apresenta o plano de execução formatado.

A tabela PLAN_TABLE é populada com os dados do plano de execução toda vez que a instrução EXPLAIN PLAN. Uma vez que a PLAN_TABLE é preenchida, as consultas dos planos podem ser geradas acessando os dados armazenados.

IMPORTANTE: Ao usar estes scripts, lembre-se que o uso repetido da cláusula EXPLAIN PLAN não exclui automaticamente os planos de execução antigos, o ideal é remover estes planos antes de executar novos a fim de evitar resultados confusos.

A exclusão pode ser realizada manualmente, ou ainda pode-se utilizar a coluna STATEMENT_ID para indicar um nome para o plano de execução a ser consultado.

Criando a tabela PLAN_TABLE

Executar  o script “utlxplan.sql” para criar a tabela conforme as instruções abaixo.

SQL> @?/rdbms/admin/utlxplan

Table created.

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

 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)

 OTHER_XML                                          CLOB

Para executar um plano de execução deve-se utilizar o comando abaixo:

SQL> EXPLAIN PLAN FOR [CÓDIGO_SQL];

Segue um exemplo.

SQL> EXPLAIN PLAN FOR SELECT DISTINCT * from emp;

Explained.

Para visualizar o resultado do plano de excução deve-se utilizar o script “utlxpls.sql” conforme as instruções abaixo.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

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

Plan hash value: 3709190377

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |    14 |  1218 |     4  (25)| 00:00:01 |

|   1 |  HASH UNIQUE       |      |    14 |  1218 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

13 rows selected.

Importante observar que o script “utlxpls.sql”, formata o plano, exibindo as informações de modo colunar e também coloca observações sobre a consulta, esse item é interessante para grande consultas que utilizam muitos filtros, através desta observação é possível verificar a possibilidade de melhoria no código.

AUTOTRACE

Existe também outro mecanismo para a execução dos planos de execução, o AUTOTRACE. Desde a versão do Oracle 7.3.4 o SQL*Plus contém um utilitário chamado autotrace que permite que os planos de consulta a ser exibido em tempo de execução.

Para usar esse utilitário, o “schema” em uso deverá possuir a tabela PLAN_TABLE.
E para habilitar o plano de execução sem executar o comando EXPLAIN PLAN, usar.

SQL> set autotrace traceonly explain

Depois basta simplesmente executar a consulta SQL para que seja apresentada diretamente no prompt do SQL*Plus, conforme demonstrado abaixo:

SQL> SELECT DISTINCT * from emp;

Execution Plan

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

Plan hash value: 3709190377

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |    14 |  1218 |     4  (25)| 00:00:01 |

|   1 |  HASH UNIQUE       |      |    14 |  1218 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Veja que a consulta não foi executada, somente o plano de execução que foi apresentado.

Referências: Oracle® Database SQL Language Reference 11g Release 2 (11.2)

Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com

Usando a compactação de índices

Neste artigo será demonstrada a vantagem da utilização de compactação dos índices.

Um índice criado com uma coluna que possuam muitos registros duplicados pode ser ajustado para economizar espaço em disco, e também para melhorar o I/O.

Quando um índice é criado no Oracle, a compressão é desabilitada (por DEFAULT). Se um índice for definido em uma coluna que contenha nomes de todos os clientes,
alguns dos nomes são muito comuns, como por exemplo um sobrenome. Pode-se  tirar proveito desta duplicação de dados por meio da compressão do índice.

Índices compactados reduzem o espaço de armazenamento, permitindo caber mais linhas em menos folhas de blocos (Leaf Blocks) o que indicará menos atividades de I/O, durante os acessos aos dados.

[Conceito]

Folhas de Blocos: Ou ”Leaf Blocks” são os blocos de índice. Em um índice de B-tree, blocos de índices são blocos de um ramo (são o blocos do "topo" em um índice deB-tree) e os  blocos de folhas (são os blocos "inferiores").

Estrutura interna de um índice B-tree

Oracle Database 11g Concepts

A seguir será feita uma demonstração da compactação de índices:

Criação da tabela AUTORES.

SQL> CREATE TABLE AUTORES (AUTORES_ID INT, NOME_AUTORES VARCHAR2(40));

Table created.

Inclusão de 10 linhas na tabela AUTORES.

SQL> INSERT INTO AUTORES VALUES(1,'Ariano Suassuna');

1 row created.

SQL> INSERT INTO AUTORES VALUES(2,'Caetano Teixeira');

1 row created.

SQL> INSERT INTO AUTORES VALUES(3,'Machado de Assis');

1 row created.

SQL> INSERT INTO AUTORES VALUES(4,'Jorge Amado');

1 row created.

SQL> INSERT INTO AUTORES VALUES(5,'Eça de Queiroz');

1 row created.

SQL> INSERT INTO AUTORES VALUES(6,'Zelia Gatai');

1 row created.

SQL> INSERT INTO AUTORES VALUES(7,'José Sarney');

1 row created.

SQL> INSERT INTO AUTORES VALUES(8,'Clarice Linspector');

1 row created.

SQL> INSERT INTO AUTORES VALUES(9,'Cora Coralina');

1 row created.

SQL> INSERT INTO AUTORES VALUES(10,'Carlos Drummond');

1 row created.

SQL> COMMIT;

Commit complete.

Verificação das linhas incluídas na tabela.

SQL> SELECT * FROM AUTORES;

AUTORES_ID NOME_AUTORES

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

         2 Caetano Teixeira

         3 Machado de Assis

         4 Jorge Amado

         5 Eça de Queiroz

         6 Zelia Gatai

         7 José Sarney

         8 Clarice Linspector

         9 Cora Coralina

        10 Carlos Drummond

         1 Ariano Suassuna

10 rows selected.

Criação de um índice utilizando a coluna (NOME_AUTORES).

SQL> CREATE INDEX AUTORES_IDX ON AUTORES(NOME_AUTORES);

Index created.

Geração das estatísticas do índice.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);

PL/SQL procedure successfully completed.

Verificar as estatísticas do índice.

SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,

ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER BLOCK", DISTINCT_KEYS,

NUM_ROWS,(NUM_ROWS-DISTINCT_KEYS) DUP_ROWS

FROM USER_INDEXES

WHERE INDEX_NAME = 'AUTORES_IDX'

ORDER BY DUP_ROWS DESC;

INDEX_NAME  COMPRESS LEAF_BLOCKS ROWS PER BLOCK DISTINCT_KEYS NUM_ROWS  DUP_ROWS

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

AUTORES_IDX DISABLED           1             10            10       10         0

Nota:  Como demonstrado, existe um bloco de folhas (leaf blocks) que contém os dados, são 10 linhas por bloco.Há um total de 10 linhas no total, e não existem nomes duplicados.

Adicionando 1.000 linhas com o mesmo nome.

SQL> DECLARE v_a NUMBER;

BEGIN

   v_a := 11;

   WHILE v_a < 1000

   LOOP

    INSERT INTO AUTORES  VALUES(v_a,'Oliveira');

    v_a := v_a + 1;

   END LOOP;

   COMMIT;

END;

/

PL/SQL procedure successfully completed.

Verificando a quantidades de linhas inseridas na tabela AUTORES.

SQL> SELECT COUNT(*) FROM AUTORES;

  COUNT(*)

----------

       999

Atualizando as estatísticas do índice.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);

PL/SQL procedure successfully completed.

Verificar as estatísticas do índice

SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,

ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER BLOCK", DISTINCT_KEYS,

NUM_ROWS,(NUM_ROWS-DISTINCT_KEYS) DUP_ROWS

FROM USER_INDEXES

WHERE INDEX_NAME = 'AUTORES_IDX'

ORDER BY DUP_ROWS DESC;

INDEX_NAME   COMPRESS LEAF_BLOCKS ROWS PER BLOCK DISTINCT_KEYS  NUM_ROWS  DUP_ROWS

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

AUTORES_IDX  DISABLED           4            250            11       999       988

Nota: Como demonstrado, existe quatro blocos de folhas (leaf blocks) que contém os dados, observar, são 250 linhas por bloco.Há um total de 999 linhas no total, dos quais 988 entradas são duplicados (sobrenome = Oliveira).

Recriando o mesmo índice, porém de modo compactado.

SQL> DROP INDEX AUTORES_IDX;

Index dropped.

SQL> CREATE INDEX AUTORES_IDX ON AUTORES(NOME_AUTORES)COMPRESS TABLESPACE USERS;

Index created.

Geração das estatísticas do índice.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);

PL/SQL procedure successfully completed.

Verificação das estatísticas do índice compactado.

SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,

ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER BLOCK", DISTINCT_KEYS,

NUM_ROWS, (NUM_ROWS-DISTINCT_KEYS) DUP_ROWS

FROM USER_INDEXES

WHERE INDEX_NAME = 'AUTORES_IDX'

ORDER BY DUP_ROWS DESC;

INDEX_NAME   COMPRESS LEAF_BLOCKS ROWS PER BLOCK DISTINCT_KEYS NUM_ROWS DUP_ROWS

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

AUTORES_IDX  ENABLED            2            500            11      999      988

  

Como podemos verificar, agora, existem apenas dois blocos de folhas (leaf blocks) e 500 linhas estão num mesmo bloco. Com esse dispositivo pode-se verificar  a economia de espaço em disco.

Referências:     Oracle® Database SQL Language Reference 11g Release 2 (11.2)

Oracle® Database Concepts 11g Release 2 (11.2)

Oracle® Database Administrator's Guide 11g Release 2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com