ProHoster > Blog > administração > Seguindo os passos do Highload++ Siberia 2019 - 8 tarefas no Oracle

Seguindo os passos do Highload++ Siberia 2019 - 8 tarefas no Oracle

Oi!

Nos dias 24 e 25 de junho, a conferência Highload++ Siberia 2019 foi realizada em Novosibirsk. Nossos rapazes também estiveram lá relatório “Bancos de dados contêineres Oracle (CDB/PDB) e seu uso prático para desenvolvimento de software”, publicaremos uma versão em texto um pouco mais tarde. Foi legal, obrigado olegbunina para a organização, bem como para todos que compareceram.

Seguindo os passos do Highload++ Siberia 2019 - 8 tarefas no Oracle
Neste post gostaríamos de compartilhar com vocês os problemas que tivemos em nosso estande para que vocês possam testar seus conhecimentos sobre Oracle. Abaixo do corte estão 8 problemas, opções de resposta e explicação.

Qual é o valor máximo de sequência que veremos como resultado da execução do script a seguir?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

  • 1
  • 5
  • 10
  • 25
  • Não, haverá um erro

respostaDe acordo com a documentação da Oracle (citada em 8.1.6):
Dentro de uma única instrução SQL, o Oracle incrementará a sequência apenas uma vez por linha. Se uma instrução contiver mais de uma referência a NEXTVAL para uma sequência, o Oracle incrementará a sequência uma vez e retornará o mesmo valor para todas as ocorrências de NEXTVAL. Se uma instrução contiver referências a CURRVAL e NEXTVAL, o Oracle incrementará a sequência e retornará o mesmo valor para CURRVAL e NEXTVAL, independentemente de sua ordem na instrução.

Assim, o o valor máximo corresponderá ao número de linhas, ou seja 5.

Quantas linhas haverá na tabela como resultado da execução do script a seguir?

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

  • 0
  • 3
  • 4
  • 5
  • 6
  • 9

respostaDe acordo com a documentação da Oracle (citada em 11.2):

Antes de executar qualquer instrução SQL, o Oracle marca um ponto de salvamento implícito (não disponível para você). Então, se a instrução falhar, o Oracle a reverte automaticamente e retornará o código de erro aplicável para SQLCODE no SQLCA. Por exemplo, se uma instrução INSERT causar um erro ao tentar inserir um valor duplicado em um índice exclusivo, a instrução será revertida.

Ligar para a HP do cliente também é considerado e processado como uma única declaração. Assim, a primeira chamada HP é concluída com sucesso, tendo sido inseridos três registros; a segunda chamada HP termina com erro e reverte o quarto registro que conseguiu inserir; a terceira chamada falha, e há três registros na tabela.

Quantas linhas haverá na tabela como resultado da execução do script a seguir?

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

respostaDe acordo com a documentação da Oracle (citada em 11.2):

Uma restrição de verificação permite especificar uma condição que cada linha da tabela deve satisfazer. Para satisfazer a restrição, cada linha da tabela deve tornar a condição VERDADEIRA ou desconhecida (devido a um valor nulo). Quando o Oracle avalia uma condição de restrição de verificação para uma linha específica, qualquer nome de coluna na condição refere-se aos valores da coluna nessa linha.

Assim, o valor null passará na verificação e o bloco anônimo será executado com sucesso até uma tentativa de inserir o valor 3. Após isso, o bloco de tratamento de erros limpará a exceção, nenhum rollback ocorrerá e haverá quatro linhas restantes na tabela com valores 1, nulo, 2 e nulo novamente.

Quais pares de valores ocuparão a mesma quantidade de espaço no bloco?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Вася', 10, 10, 'Д', 'Вася');

  • A e X
  • B e Y
  • C e K
  • C e Z
  • K e Z
  • Eu e J
  • J e X
  • Todos listados

respostaAqui estão trechos da documentação (12.1.0.2) sobre como armazenar vários tipos de dados no Oracle.

Tipo de dados CHAR
O tipo de dados CHAR especifica uma sequência de caracteres de comprimento fixo no conjunto de caracteres do banco de dados. Você especifica o conjunto de caracteres do banco de dados ao criar seu banco de dados. A Oracle garante que todos os valores armazenados em uma coluna CHAR tenham o comprimento especificado pelo tamanho na semântica de comprimento selecionada. Se você inserir um valor menor que o comprimento da coluna, o Oracle preencherá em branco o valor no comprimento da coluna.

Tipo de dados VARCHAR2
O tipo de dados VARCHAR2 especifica uma sequência de caracteres de comprimento variável no conjunto de caracteres do banco de dados. Você especifica o conjunto de caracteres do banco de dados ao criar seu banco de dados. O Oracle armazena um valor de caractere em uma coluna VARCHAR2 exatamente como você o especifica, sem qualquer preenchimento em branco, desde que o valor não exceda o comprimento da coluna.

NUMBER Tipo de dados
O tipo de dados NUMBER armazena zero, bem como números fixos positivos e negativos com valores absolutos de 1.0 x 10-130 até, mas não incluindo 1.0 x 10126. Se você especificar uma expressão aritmética cujo valor tenha um valor absoluto maior ou igual a 1.0 x 10126, o Oracle retornará um erro. Cada valor NUMBER requer de 1 a 22 bytes. Levando isso em consideração, o tamanho da coluna em bytes para um determinado valor de dados numéricos NUMBER(p), onde p é a precisão de um determinado valor, pode ser calculado usando a seguinte fórmula: REDONDA((comprimento(p)+s)/2))+1 onde s é igual a zero se o número for positivo e s é igual a 1 se o número for negativo.

Além disso, vamos pegar um trecho da documentação sobre como armazenar valores nulos.

Um nulo é a ausência de um valor em uma coluna. Nulos indicam dados ausentes, desconhecidos ou inaplicáveis. Nulos são armazenados no banco de dados se estiverem entre colunas com valores de dados. Nestes casos, necessitam de 1 byte para armazenar o comprimento da coluna (zero). Os nulos finais em uma linha não requerem armazenamento porque um novo cabeçalho de linha sinaliza que as colunas restantes na linha anterior são nulas. Por exemplo, se as três últimas colunas de uma tabela forem nulas, nenhum dado será armazenado para essas colunas.

Com base nesses dados, construímos o raciocínio. Assumimos que o banco de dados usa a codificação AL32UTF8. Nesta codificação, as letras russas ocuparão 2 bytes.

1) A e X, o valor do campo a 'Y' ocupa 1 byte, o valor do campo x 'D' ocupa 2 bytes
2) B e Y, 'Vasya' em b o valor será preenchido com espaços de até 10 caracteres e ocupará 14 bytes, 'Vasya' em d ocupará 8 bytes.
3) C e K. Ambos os campos possuem o valor NULL, depois deles existem campos significativos, portanto ocupam 1 byte.
4) C e Z. Ambos os campos possuem o valor NULL, mas o campo Z é o último da tabela, portanto não ocupa espaço (0 bytes). O campo C ocupa 1 byte.
5) K e Z. Semelhante ao caso anterior. O valor no campo K ocupa 1 byte, em Z – 0.
6) I e J. Segundo a documentação, ambos os valores ocuparão 2 bytes. Calculamos o comprimento usando a fórmula retirada da documentação: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J e X. O valor no campo J terá 2 bytes, o valor no campo X terá 2 bytes.

No total, as opções corretas são: C e K, I e J, J e X.

Qual será aproximadamente o fator de agrupamento do índice T_I?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

  • Cerca de dezenas
  • Cerca de centenas
  • Cerca de milhares
  • Cerca de dezenas de milhares

respostaDe acordo com a documentação da Oracle (citada em 12.1):

Para um índice de árvore B, o fator de agrupamento do índice mede o agrupamento físico de linhas em relação a um valor de índice.

O fator de agrupamento de índice ajuda o otimizador a decidir se uma varredura de índice ou uma varredura completa de tabela é mais eficiente para determinadas consultas). Um fator de agrupamento baixo indica uma varredura de índice eficiente.

Um fator de agrupamento próximo ao número de blocos em uma tabela indica que as linhas estão fisicamente ordenadas nos blocos da tabela pela chave do índice. Se o banco de dados realizar uma varredura completa da tabela, o banco de dados tenderá a recuperar as linhas à medida que são armazenadas no disco, classificadas pela chave do índice. Um fator de agrupamento próximo ao número de linhas indica que as linhas estão espalhadas aleatoriamente pelos blocos do banco de dados em relação à chave do índice. Se o banco de dados executar uma varredura completa da tabela, o banco de dados não recuperará linhas em nenhuma ordem de classificação por esta chave de índice.

Nesse caso, os dados estão idealmente ordenados, de forma que o fator de agrupamento será igual ou próximo ao número de blocos ocupados na tabela. Para um tamanho de bloco padrão de 8 kilobytes, você pode esperar que cerca de mil valores numéricos estreitos caibam em um bloco, portanto, o número de blocos e, como resultado, o fator de cluster será cerca de dezenas.

Em quais valores de N o script a seguir será executado com sucesso em um banco de dados regular com configurações padrão?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

  • 100
  • 200
  • 400
  • 800
  • 1600
  • 3200
  • 6400

respostaDe acordo com a documentação da Oracle (citada em 11.2):

Limites do banco de dados lógico

item
Tipo de Limite
Valor limite

Índices
Tamanho total da coluna indexada
75% do tamanho do bloco do banco de dados menos alguma sobrecarga

Assim, o tamanho total das colunas indexadas não deve ultrapassar 6Kb. O que acontece a seguir depende da codificação base selecionada. Para a codificação AL32UTF8, um caractere pode ocupar no máximo 4 bytes, portanto, na pior das hipóteses, cerca de 6 caracteres caberão em 1500 kilobytes. Portanto, o Oracle não permitirá a criação de índice em N = 400 (quando o comprimento da chave do pior caso for 1600 caracteres * 4 bytes + comprimento do rowid), enquanto em N = 200 (ou menos) a criação do índice funcionará sem problemas.

O operador INSERT com a dica APPEND foi projetado para carregar dados no modo direto. O que acontece se for aplicado à mesa na qual o gatilho está pendurado?

  • Os dados serão carregados em modo direto, o gatilho funcionará conforme o esperado
  • Os dados serão carregados em modo direto, mas o gatilho não será executado
  • Os dados serão carregados no modo convencional, o gatilho funcionará como deveria
  • Os dados serão carregados no modo convencional, mas o trigger não será executado
  • Os dados não serão carregados, um erro será registrado

respostaBasicamente, isso é mais uma questão de lógica. Para encontrar a resposta correta, sugiro o seguinte modelo de raciocínio:

  1. A inserção em modo direto é realizada pela formação direta de um bloco de dados, contornando o mecanismo SQL, o que garante alta velocidade. Assim, garantir a execução do gatilho é muito difícil, senão impossível, e não adianta isso, pois ainda desacelerará radicalmente a inserção.
  2. A não execução do gatilho fará com que, se os dados da tabela forem iguais, o estado do banco de dados como um todo (outras tabelas) dependerá do modo em que esses dados foram inseridos. Obviamente, isso destruirá a integridade dos dados e não poderá ser aplicado como solução na produção.
  3. A incapacidade de executar a operação solicitada é geralmente tratada como um erro. Mas aqui devemos lembrar que APPEND é uma dica, e a lógica geral das dicas é que elas sejam levadas em consideração se possível, mas se não, o operador é executado sem levar em conta a dica.

Então a resposta esperada é os dados serão carregados no modo normal (SQL), o gatilho será acionado.

De acordo com a documentação da Oracle (citada em 8.04):

Violações das restrições farão com que a instrução seja executada serialmente, utilizando o caminho de inserção convencional, sem avisos ou mensagens de erro. Uma exceção é a restrição de instruções que acessam a mesma tabela mais de uma vez em uma transação, o que pode gerar mensagens de erro.
Por exemplo, se gatilhos ou integridade referencial estiverem presentes na tabela, a dica APPEND será ignorada quando você tentar usar INSERT de carregamento direto (serial ou paralelo), bem como a dica ou cláusula PARALLEL, se houver.

O que acontecerá quando o script a seguir for executado?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

  • Execução bem-sucedida
  • Falha devido a erro de sintaxe
  • Erro: a transação autônoma não é válida
  • Erro relacionado ao excesso do aninhamento máximo de chamadas
  • Erro de violação de chave estrangeira
  • Erro relacionado a bloqueios

respostaA tabela e o gatilho foram criados corretamente e esta operação não deve causar problemas. Transações autônomas em um gatilho também são permitidas, caso contrário o registro não seria possível, por exemplo.

Após a inserção da primeira linha, um disparo bem-sucedido do gatilho faria com que a segunda linha fosse inserida, fazendo com que o gatilho disparasse novamente, inserindo uma terceira linha e assim por diante até que a instrução falhasse por exceder o aninhamento máximo de chamadas. No entanto, outro ponto sutil entra em jogo. No momento em que o gatilho é executado, o commit ainda não foi concluído para o primeiro registro inserido. Portanto, um gatilho rodando em uma transação autônoma tenta inserir na tabela uma linha que faça referência a uma chave estrangeira para um registro que ainda não foi confirmado. Isso resulta em uma espera (a transação autônoma espera que a transação principal seja confirmada para ver se pode inserir dados) e ao mesmo tempo a transação principal espera que a transação autônoma continue funcionando após o gatilho. Ocorre um deadlock e, como resultado, a transação autônoma é cancelada por motivos relacionados a bloqueios.

Apenas usuários registrados podem participar da pesquisa. Entrarpor favor

Foi difícil?

  • Como dois dedos, imediatamente decidi tudo corretamente.

  • Na verdade não, eu estava errado em algumas perguntas.

  • Resolvi metade disso corretamente.

  • Adivinhei a resposta duas vezes!

  • vou escrever nos comentários

14 usuários votaram. 10 usuários se abstiveram.

Fonte: habr.com

Adicionar um comentário