Aula 4 OBD

(1)

1

AULA :

Rogério Morais Rocha

Bacharel em Ciência da Computação Pós-Graduação Gestão Estratégica da Informação

e-mail : moraisrocha@gmail.com

(2)

Utilizando o plano de explicação (EXPLAIN PLAN)

• Temas abordados

– Introdução aos planos de execução,

– Descrição da sentença SQL EXPLAIN PLAN – Maneira de interpretar a explicação do plano.

(3)

3

AULA :

• Introdução aos planos de execução – A sentença EXPLAIN PLAN

• Exibi o plano de execução escolhido pelo otimizador do Oracle.

• Utilizado em sentenças SELECT, UPDATE, INSERT e DELETE • Insere uma linha descrevendo cada etapa do plano de

execução.

(4)

• Introdução aos planos de execução – O plano de execução

• É a seqüência de operações que o Oracle realiza para executar o comando SQL

(5)

5

AULA :

• Introdução aos planos de execução – O núcleo do plano de execução exibi:

• Uma ordenação das tabelas referenciadas pela sentença.

• Um método de acesso para cada tabela mencionada na sentença.

• Um método para junção das tabelas afetadas pelas operações de junção na sentença.

• Operações com dados como filtrar, ordenar ou agregação

(6)

• Introdução aos planos de execução – O núcleo do plano de execução exibi:

• A otimização, tais como o custo de cada operação e cardinalidade.

• Particionamento, tais como o conjunto de partições acessadas.

• Execução paralela, tais como o método de distribuição de entradas na junção.

(7)

7

AULA :

• Diferenças dos planos de execução

– Os planos de execução do momento da explicação para o momento da execução podem diferenciar devido a dois fatores:

• Esquemas Diferentes • Custos Diferentes

(8)

– Esquemas Diferentes

– plano de execução e o comando SQL são executados em banco de dados diferentes.

– usuário que executa o plano de execução é diferente do usuário que executa o comando SQL. Dois usuários podem apontar para objetos diferentes do mesmo banco de dados, resultando em um plano de execução diferente.

(9)

9

AULA :

– Custos Diferentes

– Volume dos dados e estatística.

– Os tipos das variáveis e os valores. – Parâmetros de inicialização

(10)

– O plano de execução por si só não consegue diferenciar entre um comando SQL com boa performance de um comando SQL com uma performance ruim.

– Um resultado do EXPLAIN PLAN que mostra que o comando SQL utiliza um índice não significa

necessariamente que a execução é eficiente. – Deve-se analisar as seguintes situações:

(11)

11

AULA :

– Analise

• As colunas do índice a serem utilizadas.

• Sua seletividade (fração da tabela acessada).

(12)

A tabela PLAN TABLE

– É criada automaticamente como uma tabela temporária global.

– A definição de um exemplo da, PLAN_TABLE, está disponível em um script SQL denominado

UTLXPLAN.SQL.

– É possível criar sua própria tabela, desde que tenha os mesmos nomes das colunas e domínios (datatype) da tabela de definição.

(13)

13

AULA :

– É criada automaticamente como uma tabela temporária global.

– A definição de um exemplo da, PLAN_TABLE, está disponível em um script SQL denominado

UTLXPLAN.SQL.

– É possível criar sua própria tabela, desde que tenha os mesmos nomes das colunas e domínios (datatype) da tabela de definição.

(14)

– Alguns atributos da tabela:

• Statement_ID – identificador do plano de execução criado. • Timestamp – data e hora que o plano de execução foi gerado. • Operation – nome da operação interna de performance a ser

utilizada.

• Options – uma variação da operação descrita na coluna OPERATION.

• Object_name – nome da tabela ou do índice.

• Cost – custo da operação estimado pelo otimizador de consulta.

(15)

15

AULA :

– Alguns atributos da tabela:

• Cardinality – estimado pelo otimizador de consulta, o número de linhas acessados pelas operação.

• Bytes - estimado pelo otimizador de consulta, o número de bytes utilizados pelas operação.

• Time – o tempo estimado em segundos pelo otimizador de consultas.

(16)

Formas de visualizar o plano de execução

– Visões dinâmicas de desempenho

• V$SQL_WORKAREA - Obter informações sobre as áreas de trabalho utilizadas pelos cursores.

• V$SQL_PLAN - obter informações sobre a execução de um plano em cursores no cache

• V$SQL_PLAN_STATISTICS - execução de

estatísticas, em cada passo ou operação de um plano de execução em cursores no cache.

(17)

17

AULA :

– Customização da exibição do plano de execução • Script denominado UTLXPLS.sql

• Procedimento denominado DBMS_XPLAN.DISPLAY

(18)

– Customização da exibição do plano de execução • Script denominado UTLXPLS.sql

• Procedimento denominado DBMS_XPLAN.DISPLAY

(19)

19

AULA :

– Customização da exibição do plano de execução • Exibição do plano escrita pelo próprio usuário. • Comece com o valor ID igual a zero e o

STATEMENT_ID informado.

• Utilize a cláusula CONNECT BY para varrer a

hierarquia do registros (do pai para o filho). Utilize as chaves STATEMENT_ID = PRIOR STATEMENT_ID e

PARENT_ID = PRIOR ID. para junção.

• Utilize a falsa coluna LEVEL para indentar os registros filhos.

(20)

– Customização da exibição do plano de execução

(21)

21

AULA :

Explain Plan

• É uma linguagem de manipulação de dados. • O Banco de Dados Oracle não confirma

implicitamente as alterações realizadas por uma sentença EXPLAIN PLAN.

• Deve confirmar a transação existente na sentença EXPLAIN PLAN, para manter os registros gerados.

(22)

Sintaxe Explain Plan

Cláusula SET STATEMENT_ID

• Utilizar este valor para identificar estas linhas.

• Se omitir esta cláusula então o valor padrão para STATEMENT_ID será nulo.

(23)

23

AULA :

Cláusula INTO table

• Table é o nome da tabela de saída.

• Omitindo o esquema então assume-se que a tabela está no próprio esquema. • O dblink é o nome do database link.

• Se omitir esta cláusula o banco de dados assume a tabela de saída PLAN_TABLE no próprio esquema do banco de dados local.

(24)

Cláusula FOR statement

• Especifica a sentença SELECT, INSERT, UPDATE ou DELETE.

(25)

25

AULA :

Exemplo Sintaxe Explain Plan

– A declaração determina:

• O plano de execução,

• O custo de uma sentença UPDATE,

• Insere linhas descrevendo o plano de execução para a tabela especificada plan_table com o valor de STATEMENT_ID igual a 'Raise in Tokyo'

(26)

– SELECT consulta a tabela PLAN_TABLE e retorna o plano de execução e o custo.

(27)

27

AULA :

– Plano de execução

(28)

Análise do Plano de Execução

Otimização de Banco de Dados

Operação Opção Explicação

HASH JOIN

Operação de junção de dois conjuntos de linha e um resultado. Este método de junção é útil para a junção de volumosos conjuntos de dados. A condição de junção é uma maneira eficiente de acesso a segunda tabela.

INDEX UNIQUE SCAN Obtenção de um único rowid de um _índice.

INDEX RANGE SCAN

Obtenção de um ou mais rowids a partir de um índice. Valores indexados são varridos em ordem crescente.

(29)

29

AULA :

Otimização de Banco de Dados

INDEX SKIP SCAN Recuperação de rowids de um índice concatenado sem utilizar a

principal coluna (s) no índice.

INDEX FAST FULL SCAN Recuperação de todos os rowids _{usando a leitura de muitos blocos.}

NESTED LOOPS

Operação aceita dois conjuntos de linhas, um conjunto exterior e um conjunto interior. O Oracle compara cada fila do conjunto exterior com cada linha do conjunto interior, retornando as linhas que satisfazem uma condição. Este método é útil para juntar pequenos subgrupos de dados. A condição de junção é uma forma eficiente de acesso à segunda tabela.

(30)

Otimização de Banco de Dados

TABLE ACCESS FULL Recuperação de todas as linhas de _{uma tabela.}

MERGE JOIN

Operação aceita dois conjuntos de linhas, cada um ordenado por um valor específico, combinando cada linha de um conjunto com as linhas correspondentes do outro, e retornar o resultado.

MERGE JOIN CARTESIAN

Pode resultar de 1 ou mais das tabelas que não tenham qualquer

junção com outras tabelas

constantes da sentença SQL. Pode ocorrer mesmo com uma junção e não pode ser sinalizado como CARTESIAN no plano.