Execution Plan: Como interpretar os valores estatísticos estimados

dezembro 6, 2015 V. J.A. Execution Plan

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 para escolher dentre alguns planos criados, um que supostamente seria o mais rápido.

2) As estatísticas reais ou que representam o que foi realizado durante a execução da instrução SQL, essas estatísticas são recuperadas da visão “V$SQL_PLAN_STATISTICS_ALL”.

Nesse artigo vamos explorar os mecanismos utilizados pelo Otimizador para calcular as estatísticas estimadas e vamos deixar para o próximo post as estatísticas reais .

Plano de execução

A seguir temos um plano de execução gerado em uma instância 11.2.0.4 a partir do comando EXPLAIN PLAN:

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
44
45
46
47
48
49
50

appuser@LAB11 > EXPLAIN PLAN FOR
2 SELECT my_tables.owner,
3 my_tables.table_name,
4 my_tables.tablespace_name,
5 my_indexes.status
6 FROM my_tables,
7 my_indexes
8 WHERE my_tables.owner = my_indexes.table_owner
9 AND my_tables.table_name = my_indexes.table_name
10 AND my_indexes.index_type = 'FUNCTION-BASED NORMAL';

Explained.

appuser@LAB11 > select * from table(dbms_xplan.display(null, null, 'projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2089828105

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2550 | 21 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 34 | 2550 | 21 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| MY_INDEXES | 34 | 1360 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MY_INDEXES_IDX | 34 | | 2 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN | MY_TABLES_IDX | 8015 | 273K| 15 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

1 - access("MY_TABLES"."OWNER"="MY_INDEXES"."TABLE_OWNER" AND
"MY_TABLES"."TABLE_NAME"="MY_INDEXES"."TABLE_NAME")
3 - access("MY_INDEXES"."INDEX_TYPE"='FUNCTION-BASED NORMAL')

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

1 - (#keys=2) "MY_TABLES"."OWNER"[VARCHAR2,30],
"MY_TABLES"."TABLE_NAME"[VARCHAR2,30], "MY_INDEXES"."STATUS"[VARCHAR2,8],
"MY_TABLES"."TABLESPACE_NAME"[VARCHAR2,30]
2 - "MY_INDEXES"."TABLE_OWNER"[VARCHAR2,30],
"MY_INDEXES"."TABLE_NAME"[VARCHAR2,30], "MY_INDEXES"."STATUS"[VARCHAR2,8]
3 - "MY_INDEXES".ROWID[ROWID,10], "MY_INDEXES"."TABLE_OWNER"[VARCHAR2,30],
"MY_INDEXES"."TABLE_NAME"[VARCHAR2,30]
4 - "MY_TABLES"."OWNER"[VARCHAR2,30], "MY_TABLES"."TABLE_NAME"[VARCHAR2,30],
"MY_TABLES"."TABLESPACE_NAME"[VARCHAR2,30]

31 rows selected.

Utilizando as regras do artigo “Execution Plan: Como verificar a ordem que as operações são realizadas?” podemos identificar que a sequência de execução das operações desse plano será: 3,2,4,1,0

A primeira operação (Id 3) desse plano de execução será um “INDEX RANGE SCAN” no índice MY_INDEXES_IDX obtendo uma lista de 34 “ROWID”, esta lista será entregue a operação pai (Id 2) que vai acessar 34 registros da tabela MY_INDEXES utilizando esses “ROWID” e criar uma tabela “HASH” na memoria. Em seguida será realizada a operação (Id 4) “INDEX FAST FULL SCAN” que vai ler todo o índice MY_TABLES_IDX (8015 registros) e passar para operação (Id 1) “HASH JOIN”, esta por sua vez vai juntar os registros entregues pelas 2 operações filhas (2,4) e passar o resultado para operação (Id 0) “SELECT STATEMENT”.

Descrição básica das estatísticas

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

A seguir um descrição resumida do que representa cada uma das colunas de informações estatísticas estimadas:

ROWS: Número estimado de linhas resultantes de uma execução da operação examinada e que serão entregues a operação pai.

BYTES: Número estimado de bytes (rows x estimativa tamanho médio do registro) resultantes de uma execução da operação examinada e que serão entregues a operação pai.

COST: Número estimado de recursos necessários para uma execução da operação examinada, incluindo os valores de recursos de todas as operações descendentes necessárias para execução dessa operação pelo menos uma vez. O valor entre parenteses (%CPU) representa um percentual do COST que será atribuido as atividades de CPU.

TIME: Tempo de resposta estimado (horas:minutos:segundos) para execução da operação um vez e como a coluna COST, o tempo de resposta para executar um vez a operação inclui os tempos de todas as operações filhas necessárias para executar uma vez essa operação.

Um ponto importante a ser enfatizado é que os valores estatísticos descritos em uma linha do plano representam a execução dessa operação somente um vez. outro ponto importante é que os valores COST e TIME são cumulativos, ou seja, uma operação pai acumula esses valores de todas as operações filhas.

Entendendo o algoritmo do otimizador

Tomando como base as definições acima vamos aplica-las no plano de execução do inicio do artigo que é um plano simples. Considerando a sequência de execução das operações: 3,2,4,1,0

Linha 3: INDEX RANGE SCAN

O otimizador estimou 34 registros baseado nas “Histograms statistics” para os registros cuja coluna INDEX_TYPE =’FUNCTION-BASED NORMAL’, mas deixou em branco o numero de bytes. Ele estimou que para ler esses 34 registros haverá um custo de 2 (COST=2), ou seja, seria necessário realizar 2 leituras físicas. A estimativa de tempo de resposta seria o resultado da operação (2 x sreadtime), onde sreadtime (single block readtime) é igual a 10ms no caso da base que gerou o plano, ou seja fazendo o arredondamento para segundos é igual a 1 segundo.

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
44
45
46
47
48
49

appuser@LAB11 >select endpoint_number,
2 endpoint_number - nvl(prev_endpoint,0) frequency,
3 hex_val,
4 chr(to_number(substr(hex_val, 2,2),'XX')) ||
5 chr(to_number(substr(hex_val, 4,2),'XX')) ||
6 chr(to_number(substr(hex_val, 6,2),'XX')) ||
7 chr(to_number(substr(hex_val, 8,2),'XX')) ||
8 chr(to_number(substr(hex_val,10,2),'XX')) ||
9 chr(to_number(substr(hex_val,12,2),'XX'))
10 from (select endpoint_number,
11 lag(endpoint_number,1) over(order by endpoint_number) prev_endpoint,
12 to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') hex_val,
13 endpoint_actual_value
14 from dba_tab_histograms
15 where owner = 'APPUSER'
16 and table_name = 'MY_INDEXES'
17 and column_name = 'INDEX_TYPE')
18 order by endpoint_number;

ENDPOINT_NUMBER FREQUENCY HEX_VAL CHR(TO
--------------- ---------- ---------------------------------------- ------
15 15 4249544D414FF9A1D6D1D37F000000 BITMAO
16 1 444F4D41494E03F6FC852ECF800000 DOMAIN
50 34 46554E4354494BFD6E91EAC9000000 FUNCTI
208 158 494F54202D20622D7463C1D6A00000 IOT -
813 605 4C4F41FFFFFFEE7B00C6C106200000 LOA
12907 12094 4E4F524D414BFDC683A65461000000 NORMAK

6 rows selected.

appuser@LAB11 >
appuser@LAB11 > SET SERVEROUTPUT ON;
appuser@LAB11 > DECLARE
2 STATUS VARCHAR2(20);
3 DSTART DATE;
4 DSTOP DATE;
5 PVALUE NUMBER;
6 PNAME VARCHAR2(30);
7 BEGIN
8 PNAME := 'sreadtim';
9 DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab=>null,statid=>null,statown=>null);
10 DBMS_OUTPUT.PUT_LINE('SINGLE BLOCK READTIME IN MS: '||pvalue);
11 END;
12 /
SINGLE BLOCK READTIME IN MS: 10.085

PL/SQL procedure successfully completed.

appuser@LAB11 >

Linha 2: TABLE ACCESS BY INDEX ROWID

O otimizador estimou que serão lidos 34 registros da tabela “MY_INDEXES” que são correspondentes aos “ROWID” fornecidos pela operação da linha 3. Considerando esses 34 registros da tabela “MY_INDEXES” o otimizador verificou o tamanho médio dos campos dessa tabela que estão sendo tratados nesta instrução SQL (campo AVG_COL_LEN da visão DBA_TAB_COLUMNS), na seção “Column Projection Information” podemos visualizar as colunas que estão sendo tratadas em cada linha de operação, na linha 2 as colunas TABLE_OWNER, TABLE_NAME e STATUS da tabela “MY_INDEXES” tem uma tamanho médio de 33 Bytes (34 x 33 = 1122), a multiplicação desse tamanho pelo número de registros acessados não corresponde ao valor de bytes informado no plano (1360), para obter esse numero o otimizador acrescentou o tamanho médio da coluna INDEX_TYPE que foi utilizada na operação linha 3 (34 x 40 = 1360). COST foi estimado em 6 pelo otimizador que somou o custo da operação filha (2) na linha 3 e espera encontrar esses 34 registros da tabela “MY_INDEXES” distribuídos em 4 blocos o que demandará 4 leitura físicas. O tempo estimado será o resultado de ( 6 x sreadtime = 60ms) que arredondando será 1 segundo.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

appuser@LAB11 > SELECT owner, table_name, column_name, avg_col_len
2 FROM dba_tab_columns
3 WHERE owner = 'APPUSER'
4 AND table_name = 'MY_INDEXES'
5 AND column_name IN ('TABLE_OWNER','TABLE_NAME','STATUS','INDEX_TYPE');

Owner TABLE_NAME Coluna AVG_COL_LEN
---------------- -------------------- -------------------- -----------
APPUSER MY_INDEXES INDEX_TYPE 7
APPUSER MY_INDEXES TABLE_OWNER 9
APPUSER MY_INDEXES TABLE_NAME 18
APPUSER MY_INDEXES STATUS 6

4 rows selected.

Linha 4: INDEX FAST FULL SCAN

Existem 8015 registros na tabela MY_TABLES e esta operação vai ler todas as entradas do índice MY_TABLES_IDX correspondentes a essa tabela e vai calcular o tamanho em bytes baseado no tamanho médio dos campos OWNER, TABLE_NAME e TABLESPACE_NAME que são listados na seção “Column Projection Information” linha 4, nesse caso teremos (8015 x 35 / 1024 = 273K) que corresponde ao calculo realizado pelo otimizador no plano de execução. A coluna COST apresenta o valor 15 que corresponde a estimativa de 15 leituras físicas de blocos. Novamente o resultado do arredondamento do tempo vai exibir 1 segundo na coluna TIME.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

appuser@LAB11 > select NUM_ROWS from dba_indexes where index_name = 'MY_TABLES_IDX';

NUM_ROWS
----------
8015

appuser@LAB11 > SELECT owner, table_name, column_name, avg_col_len
2 FROM dba_tab_columns
3 WHERE owner = 'APPUSER'
4 AND table_name = 'MY_TABLES'
5 AND column_name IN ('OWNER','TABLE_NAME','TABLESPACE_NAME');

Owner TABLE_NAME Coluna AVG_COL_LEN
---------- ---------------- ----------------- -----------
APPUSER MY_TABLES OWNER 9
APPUSER MY_TABLES TABLE_NAME 18
APPUSER MY_TABLES TABLESPACE_NAME 8

appuser@LAB11 > select 8015*(9+18+8)/1024 from dual;

8015*(9+18+8)/1024
------------------
273.950195

Linha 1: HASH JOIN

Esta operação vai fazer a junção de 34 registros da operação (linha 2) com os 34 registros da operação (linha 4), o cálculo da quantidade de bytes para essa operação será a multiplicação do numero de registros (34) pela soma dos tamanhos médios dos campos das operações filhas (linha 2 e 4) que são respectivamente 40 e 35 bytes, dessa forma temos { 34 x (40 + 35) = 2550 }. A coluna COST foi calculada pelo otimizador como a soma do custo da duas operações filhas (6 + 15 = 21) e a coluna TIME como nas operações anteriores o resultado do calculo feito pelo otimizador é inferior a 1 segundo, logo o valor é arredondado para 1 segundo.

Conclusão

Utilizamos um plano de execução simples para compreender como o Otimizador faz a estimativa de cada uma das estatísticas, mas é importante frisar que a coluna COST é a mais importante, pois é essa coluna que o Otimizador utiliza para escolher dentre vários planos o que supostamente seria o melhor, o mais rápido. Entretanto precisamos saber que o Otimizador apresenta algumas falhas quando realiza esses cálculos estimados, ou por que as estatísticas dos objetos não esta atualizada, ou esta incompleta, ou ainda por algum erro no algoritmo do Otimizador. Como consequência disso o Otimizador as vezes acaba escolhendo um plano de execução que tem um tempo de resposta insatisfatório, é nesse momento que precisamos utilizar o conhecimento de como ele trabalha para identificar o que esta causando essa anomalia e aplicar uma correção para melhorar o tempo de resposta da instrução SQL.