Execution Plan: Como interpretar as seções não estatísticas do plano de execução

Execution Plan
Seções não estatisticas

Nas publicações anteriores foram abordados os tópicos: como determinar a sequência de execução do plano, como interpretar a estatísticas estimadas e as estatísticas geradas durante a execução do plano, agora vamos abordar algumas seções complementares que fornecem informações importantes para entender por que o plano de execução foi criado com determinadas operações, por que essas operações utilizam determinados recursos e por que estas operações foram ordenadas numa sequência especifica.

Query Block Name / Object Alias Section

Uma instrução SQL pode ser composta de várias sub-consultas e as vezes é difícil fazer uma correlação entre a instrução SQL e o plano de execução, para facilitar esta leitura podemos utilizar esta seção e através do “Object Alias” identificar no plano cada umas das sub-consultas.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


SELECT outer.*
  FROM employees outer
 WHERE outer.salary > (SELECT avg(inner.salary)
                         FROM employees inner
                        WHERE inner.department_id = outer.department_id);


SELECT * FROM TABLE(dbms_xplan.display_cursor('b2dtq04ypz3kw',0,'+alias'));

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |     7 (100)|          |
|*  1 |  HASH JOIN           |           |    17 |  1615 |     7  (15)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1   |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$C772B8D1
   2 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
   3 - SEL$683B0107
   4 - SEL$683B0107 / INNER@SEL$2
   5 - SEL$C772B8D1 / OUTER@SEL$1

Na instrução SQL acima temos uma consulta principal e na clausula WHERE temos uma sub-consulta, se observarmos o plano de execução fica difícil determinar quais linhas se referem a sub-consulta, mas observando o “Object Alias” da linha 4 na seção “Query Block Name” SEL$683B0107 podemos ver que foi utilizado o “Alias” da tabela da sub-consulta “INNER” para compor o nome do “Object Alias” INNER@SEL$2, desta forma podemos dizer que as linha 2,3,4 que possuem o mesmo QB_NAME SEL$683B0107 são operações referentes a sub-consulta e as linhas 1 e 5 cujo QB_NAME é SEL$C772B8D1 são operações referentes a consulta principal.

Quando temos várias sub-consultas referenciando a mesma tabela e não foi utilizado “Alias” para identificar as tabelas fica difícil identificar as “Query Block”, neste caso podemos utilizar o “Hint QB_NAME” que vai facilitar muito essa identificação. A seguir temo um exemplo de utilização desse “Hint”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM employees WHERE job_id like '%REP') AS salesman_count,
       (SELECT /*+ QB_NAME(manager) */  COUNT(*) FROM employees WHERE job_id like '%MAN') AS manager_count
FROM   dual;

SELECT * FROM TABLE(dbms_xplan.display_cursor('gs5d0xtyta4zg',0,'+alias'))

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_JOB_IX |     5 |    45 |     1   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE   |            |     1 |     9 |            |          |
|*  4 |   INDEX RANGE SCAN| EMP_JOB_IX |     5 |    45 |     1   (0)| 00:00:01 |
|   5 |  FAST DUAL        |            |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SALESMAN
   2 - SALESMAN / EMPLOYEES@SALESMAN
   3 - MANAGER
   4 - MANAGER  / EMPLOYEES@MANAGER
   5 - SEL$1    / DUAL@SEL$1

Outline Data Section

A seção “Outline Data” é uma lista de “hints” que pode ser utilizada para replicar um plano de execução mesmo que as estatísticas das tabelas sejam alteradas, esta lista pode ser copiada para a instrução SQL sem alterações. A seguir um exemplo de como podemos listar esta seção utilizando o pacote DBMS_XPLAN.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT outer.*
  FROM employees outer
 WHERE outer.salary > (SELECT avg(inner.salary)
                         FROM employees inner
                        WHERE inner.DEPARTMENT_ID = outer.DEPARTMENT_ID);

SELECT * FROM TABLE(dbms_xplan.display_cursor('b2dtq04ypz3kw',0,'+outline'))


----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |     7 (100)|          |
|*  1 |  HASH JOIN           |           |    17 |  1615 |     7  (15)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1   |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$683B0107")
      OUTLINE_LEAF(@"SEL$C772B8D1")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$7511BFD2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
      FULL(@"SEL$C772B8D1" "OUTER"@"SEL$1")
      LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "OUTER"@"SEL$1")
      USE_HASH(@"SEL$C772B8D1" "OUTER"@"SEL$1")
      FULL(@"SEL$683B0107" "INNER"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      END_OUTLINE_DATA
  */

Remote SQL Information Section

A seção “Remote SQL Information” fornece informações complementares a repeito da operação “REMOTE” do plano de execução sempre que uma instrução SQL acessar dados de um objeto em outro banco de dados. Nessa seção vamos encontrar a instrução SQL que será submetida na base remota e uma identificação do nome do DBLINK utilizado para acessar a base remota.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT first_name, hire_date, department_name
  FROM employees e,
       (select department_id, department_name from departments@APPL2) d
 WHERE e.department_id = d.department_id
   AND department_name = 'IT';

SELECT * FROM TABLE(dbms_xplan.display_cursor('08qz6fgcc1cgp',0,'TYPICAL'))

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     3 (100)|          |        |      |
|   1 |  NESTED LOOPS                |                   |    10 |   390 |     3   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS               |                   |    10 |   390 |     3   (0)| 00:00:01 |        |      |
|   3 |    REMOTE                    | DEPARTMENTS       |     1 |    21 |     2   (0)| 00:00:01 |  APPL2 | R->S |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |        |      |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   180 |     1   (0)| 00:00:01 |        |      |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "DEPARTMENT_ID","DEPARTMENT_NAME" FROM "DEPARTMENTS" "DEPARTMENTS" WHERE
       "DEPARTMENT_NAME"=:1 (accessing 'APPL2' )

 

Predicate Information Section

A seção “Predicate Information” fornece informações valiosas de como os dados são recuperados do banco de dados, normalmente vamos ver dois tipos de informação nessa seção:

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

ACCESS – significa que vamos utilizar algum índice para selecionar os registros específicos que necessitamos.

FILTER – significa que estamos selecionando mais registros do que necessitamos e precisamos excluir registros de acordo com algum critério informado na clausula WHERE.

Vamos ver a seguir alguns exemplos na prática:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select * from DEPARTMENTS where DEPARTMENT_NAME = 'Administration';

 
SELECT * FROM TABLE(dbms_xplan.display_cursor('277tz6xdc6v80',0,'TYPICAL'))


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPARTMENTS |     1 |    21 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_NAME"=:SYS_B_0)

 

Neste exemplo na seção “Predicate Information” temos um “FILTER” na linha 1 do plano de execução, ou seja a execução dessa instrução SQL vai realizar uma operação “TABLE ACCESS FULL” na tabela DEPARTMENTS lendo todos os registros e excluindo aqueles cujo DEPARTMENT_NAME não seja igual a ‘Administration’.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select * from DEPARTMENTS where DEPARTMENT_ID = 10;

SELECT * FROM TABLE(dbms_xplan.display_cursor('9a2fjq5g05s7p',0,'TYPICAL'))


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    21 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=:SYS_B_0)

 

Neste exemplo na seção “Predicate Information” temos um “ACCESS” na linha 2 do plano de execução, ou seja a execução dessa instrução SQL vai buscar no índice DEPT_ID_PK todos os registros cujo DEPARTMENT_ID é igual 10 e vai passar o ROWID desses registros para operação pai na linha 1 do plano. Nesta simulação o índice é uma Primary_Key e nesse caso só podemos ter 1 registro para cada valor.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select * from DEPARTMENTS where DEPARTMENT_ID > 120 and DEPARTMENT_NAME like '%Control%';


SELECT * FROM TABLE(dbms_xplan.display_cursor('d78t1gwv11wx6',0,'TYPICAL'))


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEPT_ID_PK  |    16 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_NAME" LIKE :SYS_B_1)
   2 - access("DEPARTMENT_ID">:SYS_B_0)

 

Neste exemplo na seção “Predicate Information” temos um “ACCESS” na linha 2 e um “FILTER” na linha 1 do plano de execução, levando em consideração que a operação filha sempre será executada primeiro, a execução dessa instrução SQL vai buscar no índice DEPT_ID_PK todos os registros cujo DEPARTMENT_ID seja maior que 120 e vai passar os ROWID desses registros para a operação pai na linha 1 que vai excluir todos os registros que não tenham a palavra “Control” no campo DEPARTMENT_NAME.

Column Projection Information Section

A seção “Column Projection Information” mostra quais são as colunas das tabelas que estão sendo trabalhadas em cada linha de operação do plano de execução, seus respectivos tipos e tamanhos. A seguir um exemplo de como essa seção é exibida.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT first_name, hire_date, department_name
  FROM employees e,
       departments d
 WHERE e.department_id = d.department_id
   AND department_name = 'IT';

SELECT * FROM TABLE(dbms_xplan.display_cursor('apj7zuzx3v4w7',0,'TYPICAL +PROJECTION -PREDICATE'))

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |                   |    10 |   340 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                   |    10 |   340 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|   4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   180 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEPARTMENT_NAME"[VARCHAR2,30], "FIRST_NAME"[VARCHAR2,20], "HIRE_DATE"[DATE,7]
   2 - "DEPARTMENT_NAME"[VARCHAR2,30], "E".ROWID[ROWID,10]
   3 - "D"."DEPARTMENT_ID"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
   4 - "E".ROWID[ROWID,10]
   5 - "FIRST_NAME"[VARCHAR2,20], "HIRE_DATE"[DATE,7]

 

Peeked Binds Section

Quando estamos analisando o desempenho de uma instrução SQL é muito útil conseguir criar um “TEST CASE” reproduzindo o mesmo plano de execução que esta sendo utilizado em produção, nesse momento é muito importante identificar quais são as variáveis que estão sendo passadas para a instrução SQL. A seção “Peeked Binds” fornece essa informação, veja a seguir um exemplo dessa seção.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
variable dname varchar2(30)
exec :dname := 'IT'

SELECT first_name, hire_date, department_name
  FROM employees emp,
       departments dpt
 WHERE emp.department_id = dpt.department_id
   AND department_name = :dname;

SELECT * FROM TABLE(dbms_xplan.display_cursor('2rkdknf03nj05',0,'TYPICAL LAST +PEEKED_BINDS'))

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |                   |    10 |   340 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                   |    10 |   340 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   180 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :DNAME (VARCHAR2(30), CSID=178): 'IT'

Note Section

A seção “Note” fornece informações relevantes que foram aplicadas ao plano de execução, por exemplo a seguir temos um plano de execução que utilizou uma profile durante sua execução.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
select o.owner, o.object_name, s.segment_type
from my_segments s,
     my_objects o
where o.object_name = s.segment_name
  and o.owner = s.owner
  and segment_name = 'DEPARTMENTS';


SELECT * FROM TABLE (dbms_xplan.display_cursor ('g208t609capq4',0,'TYPICAL LAST -PREDICATE'));

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |   555 (100)|          |
|   1 |  HASH JOIN         |             |     1 |    65 |   555   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| MY_SEGMENTS |     1 |    35 |    83   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MY_OBJECTS  |     2 |    60 |   472   (1)| 00:00:06 |
----------------------------------------------------------------------------------

Note
-----
   - SQL profile coe_g208t609capq4_1701876989 used for this statement



SELECT name, sql_text FROM dba_sql_profiles;

NAME                           SQL_TEXT
------------------------------ -------------------------------------------------------
coe_g208t609capq4_1701876989
                               select o.owner, o.object_name, s.segment_type
                               from my_segments s,
                               my_objects o
                               where o.object_name = s.segment_name
                               and o.owner = s.owner
                               and segment_name = :"SYS_B_0"

Conclusão

Este é o ultimo de quatro artigos sobre como fazer a leitura de um plano de execução gerado pelo pacote DBMS_XPLAN, nesse artigo procuramos mostrar a importância das seções não estatísticas do plano de execução e como elas complementam o entendimento do plano de execução. O propósito desse artigo foi trazer algumas informações de como essas seções podem ser interpretadas e o seu relacionamento com o plano de execução, com a prática na utilização desse pacote nas tarefas de tuning de instruções SQL vamos melhorar nossa habilidade em interpretar essas informações e em alguns casos vamos resolver o problema de desempenho das instruções SQL apenas analisando as informações dessas seções.

Referências

http://allthingsoracle.com/execution-plans-part-7-query-blocks-and-inline-views/

https://jonathanlewis.wordpress.com/2009/07/01/distributed-queries/

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:7807480400346035212

https://alexanderanokhin.wordpress.com/2012/07/18/dont-forget-about-column-projection/

https://blogs.oracle.com/optimizer/entry/displaying_and_reading_the_execution_plans_for_a_sql_statement

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Artigos semelhantes:

  • Optimizer, CBO, statistics
    Execution Plan: Como interpretar os valores estatísticos estimados

    No artigo anterior sobre plano de execução abordamos como identificar a sequência de execução das operações, outra parte muito importante no entendimento do plano de execução são as informações estatísticas que podem ser classificadas em dois grupos: 1) As estatísticas estimadas pelo otimizador que são usadas no processo de parse…

  • Execution Plan: Como verificar a ordem que as operações são realizadas

    Quando iniciamos um trabalho de Tuning de uma instrução SQL, precisamos ler o plano de execução Oracle dessa instrução e entender o que cada uma das operações esta realizando e a sequência que essas operações estão sendo executadas. O manuais da Oracle abordam essa questão de forma muito simplista e…

  • Estatísticas coletadas durante a execução
    Execution Plan: Como interpretar os valores estatísticos coletados durante a execução

    No artigo anterior foram abordadas as estatísticas estimadas pelo Otimizador  para o plano de execução, agora vamos tratar das estatísticas geradas após a execução do plano, é nesse momento que temos condições de avaliar se o Otimizador acertou em suas estimativas. A eficácia do plano de execução esta diretamente relacionada com…

Leave a comment

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *