ProHoster > Blog > administration > Sur les traces de Highload++ Sibérie 2019 - 8 tâches sur Oracle

Sur les traces de Highload++ Sibérie 2019 - 8 tâches sur Oracle

Salut!

Les 24 et 25 juin s'est tenue à Novossibirsk la conférence Highload++ Sibérie 2019. Nos gars étaient également là rapport « Les bases de données de conteneurs Oracle (CDB/PDB) et leur utilisation pratique pour le développement de logiciels », nous publierons une version texte un peu plus tard. C'était cool, merci olégbunine pour l'organisation, ainsi qu'à tous ceux qui sont venus.

Sur les traces de Highload++ Sibérie 2019 - 8 tâches sur Oracle
Dans cet article, nous souhaitons partager avec vous les problèmes que nous avons rencontrés sur notre stand afin que vous puissiez tester vos connaissances Oracle. Sous la coupe se trouvent 8 problèmes, des options de réponse et des explications.

Quelle est la valeur de séquence maximale que nous verrons à la suite de l’exécution du script suivant ?

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
  • Non, il y aura une erreur

réponseSelon la documentation Oracle (citée dans 8.1.6) :
Au sein d'une seule instruction SQL, Oracle n'incrémentera la séquence qu'une seule fois par ligne. Si une instruction contient plusieurs références à NEXTVAL pour une séquence, Oracle incrémente la séquence une fois et renvoie la même valeur pour toutes les occurrences de NEXTVAL. Si une instruction contient des références à la fois à CURRVAL et à NEXTVAL, Oracle incrémente la séquence et renvoie la même valeur pour CURRVAL et NEXTVAL, quel que soit leur ordre dans l'instruction.

Ainsi, le la valeur maximale correspondra au nombre de lignes, soit 5.

Combien de lignes y aura-t-il dans le tableau suite à l’exécution du script suivant ?

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

réponseSelon la documentation Oracle (citée dans 11.2) :

Avant d'exécuter une instruction SQL, Oracle marque un point de sauvegarde implicite (non disponible pour vous). Ensuite, si l'instruction échoue, Oracle l'annule automatiquement et renvoie le code d'erreur applicable à SQLCODE dans SQLCA. Par exemple, si une instruction INSERT provoque une erreur en essayant d'insérer une valeur en double dans un index unique, l'instruction est annulée.

L'appel à HP depuis le client est également considéré et traité comme une seule déclaration. Ainsi, le premier appel HP se termine avec succès, après avoir inséré trois enregistrements ; le deuxième appel HP se termine par une erreur et annule le quatrième enregistrement qu'il a réussi à insérer ; le troisième appel échoue, et il y a trois enregistrements dans la table.

Combien de lignes y aura-t-il dans le tableau suite à l’exécution du script suivant ?

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

réponseSelon la documentation Oracle (citée dans 11.2) :

Une contrainte de vérification vous permet de spécifier une condition que chaque ligne du tableau doit satisfaire. Pour satisfaire la contrainte, chaque ligne du tableau doit rendre la condition VRAIE ou inconnue (en raison d'une valeur nulle). Lorsqu'Oracle évalue une condition de contrainte de vérification pour une ligne particulière, tous les noms de colonnes dans la condition font référence aux valeurs de colonne de cette ligne.

Ainsi, la valeur null passera le contrôle et le bloc anonyme sera exécuté avec succès jusqu'à une tentative d'insertion de la valeur 3. Après cela, le bloc de gestion des erreurs effacera l'exception, aucune restauration ne se produira et il restera quatre lignes dans le tableau avec les valeurs 1, null, 2 et encore null.

Quelles paires de valeurs occuperont la même quantité d’espace dans le bloc ?

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 et X
  • B et Y
  • C et K
  • C et Z
  • K et Z
  • Moi et J
  • J et X
  • Tous répertoriés

réponseVoici des extraits de la documentation (12.1.0.2) sur le stockage de différents types de données dans Oracle.

Type de données CHAR
Le type de données CHAR spécifie une chaîne de caractères de longueur fixe dans le jeu de caractères de la base de données. Vous spécifiez le jeu de caractères de la base de données lorsque vous créez votre base de données. Oracle garantit que toutes les valeurs stockées dans une colonne CHAR ont la longueur spécifiée par taille dans la sémantique de longueur sélectionnée. Si vous insérez une valeur plus courte que la longueur de la colonne, Oracle remplit la valeur en fonction de la longueur de la colonne.

Type de données VARCHAR2
Le type de données VARCHAR2 spécifie une chaîne de caractères de longueur variable dans le jeu de caractères de la base de données. Vous spécifiez le jeu de caractères de la base de données lorsque vous créez votre base de données. Oracle stocke une valeur de caractère dans une colonne VARCHAR2 exactement comme vous la spécifiez, sans aucun remplissage, à condition que la valeur ne dépasse pas la longueur de la colonne.

Type de données NUMÉRO
Le type de données NUMBER stocke zéro ainsi que des nombres fixes positifs et négatifs avec des valeurs absolues de 1.0 x 10-130 à 1.0 x 10126 non compris. Si vous spécifiez une expression arithmétique dont la valeur a une valeur absolue supérieure ou égale à 1.0 x 10126, Oracle renvoie une erreur. Chaque valeur NUMBER nécessite de 1 à 22 octets. En tenant compte de cela, la taille de colonne en octets pour une valeur de données numérique particulière NUMBER(p), où p est la précision d'une valeur donnée, peut être calculée à l'aide de la formule suivante : ROND((longueur(p)+s)/2))+1 où s est égal à zéro si le nombre est positif et s est égal à 1 si le nombre est négatif.

De plus, prenons un extrait de la documentation sur le stockage des valeurs Null.

Un null est l'absence de valeur dans une colonne. Les valeurs nulles indiquent des données manquantes, inconnues ou inapplicables. Les valeurs nulles sont stockées dans la base de données si elles se situent entre des colonnes contenant des valeurs de données. Dans ces cas, ils nécessitent 1 octet pour stocker la longueur de la colonne (zéro). Les valeurs NULL de fin d'une ligne ne nécessitent aucun stockage, car un nouvel en-tête de ligne signale que les colonnes restantes de la ligne précédente sont nulles. Par exemple, si les trois dernières colonnes d'une table sont nulles, aucune donnée n'est stockée pour ces colonnes.

Sur la base de ces données, nous construisons un raisonnement. Nous supposons que la base de données utilise le codage AL32UTF8. Dans ce codage, les lettres russes occuperont 2 octets.

1) A et X, la valeur du champ a 'Y' prend 1 octet, la valeur du champ x 'D' prend 2 octets
2) B et Y, 'Vasya' en b, la valeur sera complétée par des espaces jusqu'à 10 caractères et prendra 14 octets, 'Vasya' en d prendra 8 octets.
3) C et K. Les deux champs ont la valeur NULL, après eux se trouvent des champs significatifs, ils occupent donc 1 octet.
4) C et Z. Les deux champs ont la valeur NULL, mais le champ Z est le dernier du tableau, il ne prend donc pas de place (0 octet). Le champ C occupe 1 octet.
5) K et Z. Similaire au cas précédent. La valeur dans le champ K occupe 1 octet, en Z – 0.
6) I et J. Selon la documentation, les deux valeurs prendront 2 octets. On calcule la longueur à l'aide de la formule tirée de la documentation : round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J et X. La valeur dans le champ J prendra 2 octets, la valeur dans le champ X prendra 2 octets.

Au total, les bonnes options sont : C et K, I et J, J et X.

Quel sera approximativement le facteur de regroupement de l’indice 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);

  • Une dizaine
  • Une centaine
  • Environ des milliers
  • Environ des dizaines de milliers

réponseSelon la documentation Oracle (citée dans 12.1) :

Pour un index B-tree, le facteur de clustering d'index mesure le regroupement physique des lignes par rapport à une valeur d'index.

Le facteur de clustering d'index aide l'optimiseur à décider si une analyse d'index ou une analyse de table complète est plus efficace pour certaines requêtes). Un faible facteur de clustering indique une analyse d’index efficace.

Un facteur de clustering proche du nombre de blocs dans une table indique que les lignes sont physiquement ordonnées dans les blocs de la table par la clé d'index. Si la base de données effectue une analyse complète de la table, elle a alors tendance à récupérer les lignes au fur et à mesure qu'elles sont stockées sur le disque, triées par clé d'index. Un facteur de clustering proche du nombre de lignes indique que les lignes sont dispersées de manière aléatoire dans les blocs de base de données par rapport à la clé d'index. Si la base de données effectue une analyse complète de la table, elle ne récupérera pas les lignes dans un ordre trié selon cette clé d'index.

Dans ce cas, les données sont idéalement triées, le facteur de clustering sera donc égal ou proche du nombre de blocs occupés dans le tableau. Pour une taille de bloc standard de 8 kilo-octets, vous pouvez vous attendre à ce qu'environ un millier de valeurs numériques étroites tiennent dans un bloc, donc le nombre de blocs et, par conséquent, le facteur de regroupement seront une dizaine.

À quelles valeurs de N le script suivant sera-t-il exécuté avec succès dans une base de données standard avec des paramètres standard ?

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

réponseSelon la documentation Oracle (citée dans 11.2) :

Limites de la base de données logique

Produit
Type de limite
Valeur limite

Index
Taille totale de la colonne indexée
75 % de la taille du bloc de base de données moins une certaine surcharge

Ainsi, la taille totale des colonnes indexées ne doit pas dépasser 6 Ko. Ce qui se passe ensuite dépend du codage de base sélectionné. Pour le codage AL32UTF8, un caractère peut occuper un maximum de 4 octets, donc dans le pire des cas, environ 6 1500 caractères tiendront dans 400 kilo-octets. Par conséquent, Oracle interdira la création d'index à N = 1600 (lorsque la longueur de clé dans le pire des cas est de 4 XNUMX caractères * XNUMX octets + longueur de rowid), tandis que à N = 200 (ou moins) la création de l'index fonctionnera sans problème.

L'opérateur INSERT avec l'indice APPEND est conçu pour charger des données en mode direct. Que se passe-t-il s'il est appliqué à la table sur laquelle le déclencheur est suspendu ?

  • Les données seront chargées en mode direct, le déclencheur fonctionnera comme prévu
  • Les données seront chargées en mode direct, mais le trigger ne sera pas exécuté
  • Les données seront chargées en mode conventionnel, le déclencheur fonctionnera comme il se doit
  • Les données seront chargées en mode conventionnel, mais le déclencheur ne sera pas exécuté
  • Les données ne seront pas chargées, une erreur sera enregistrée

réponseAu fond, c'est plutôt une question de logique. Pour trouver la bonne réponse, je suggère le modèle de raisonnement suivant :

  1. L'insertion en mode direct s'effectue par formation directe d'un bloc de données, contournant le moteur SQL, ce qui garantit une vitesse élevée. Ainsi, assurer l'exécution du déclencheur est très difficile, voire impossible, et cela ne sert à rien, puisque cela ralentira quand même radicalement l'insertion.
  2. L'échec de l'exécution du déclencheur entraînera le fait que, si les données de la table sont les mêmes, l'état de la base de données dans son ensemble (autres tables) dépendra du mode dans lequel ces données ont été insérées. Cela détruira évidemment l’intégrité des données et ne peut pas être appliqué comme solution en production.
  3. L'impossibilité d'effectuer l'opération demandée est généralement traitée comme une erreur. Mais ici, nous devons rappeler que APPEND est un indice, et la logique générale des indices est qu'ils sont pris en compte si possible, mais sinon, l'opérateur est exécuté sans prendre en compte l'indice.

La réponse attendue est donc les données seront chargées en mode normal (SQL), le déclencheur se déclenchera.

Selon la documentation Oracle (citée dans 8.04) :

Les violations des restrictions entraîneront l'exécution de l'instruction en série, en utilisant le chemin d'insertion conventionnel, sans avertissements ni messages d'erreur. Une exception est la restriction des instructions accédant à la même table plus d'une fois dans une transaction, ce qui peut provoquer des messages d'erreur.
Par exemple, si des déclencheurs ou une intégrité référentielle sont présents sur la table, alors l'indication APPEND sera ignorée lorsque vous tenterez d'utiliser INSERT à chargement direct (série ou parallèle), ainsi que l'indication ou la clause PARALLEL, le cas échéant.

Que se passera-t-il lorsque le script suivant sera exécuté ?

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);

  • Exécution réussie
  • Échec dû à une erreur de syntaxe
  • Erreur : la transaction autonome n'est pas valide
  • Erreur liée au dépassement de l'imbrication maximale des appels
  • Erreur de violation de clé étrangère
  • Erreur liée aux serrures

réponseLa table et le déclencheur sont créés tout à fait correctement et cette opération ne devrait pas poser de problèmes. Les transactions autonomes dans un déclencheur sont également autorisées, sinon la journalisation ne serait pas possible, par exemple.

Après l'insertion de la première ligne, un déclenchement réussi du déclencheur entraînerait l'insertion de la deuxième ligne, provoquant le déclenchement à nouveau du déclencheur, l'insertion d'une troisième ligne, et ainsi de suite jusqu'à ce que l'instruction échoue en raison du dépassement de l'imbrication maximale des appels. Cependant, un autre point subtil entre en jeu. Au moment de l'exécution du déclencheur, la validation n'est pas encore terminée pour le premier enregistrement inséré. Par conséquent, un déclencheur exécuté dans une transaction autonome tente d'insérer dans la table une ligne faisant référence à une clé étrangère vers un enregistrement qui n'a pas encore été validé. Cela se traduit par une attente (la transaction autonome attend que la transaction principale s'engage pour voir si elle peut insérer des données) et en même temps la transaction principale attend que la transaction autonome continue de fonctionner après le déclencheur. Une impasse se produit et, par conséquent, la transaction autonome est annulée pour des raisons liées aux verrous.

Seuls les utilisateurs enregistrés peuvent participer à l'enquête. se connecters'il te plait.

C'était difficile de le faire ?

  • Comme deux doigts, j'ai immédiatement tout décidé correctement.

  • Pas vraiment, j'avais tort sur quelques questions.

  • J'en ai résolu la moitié correctement.

  • J'ai deviné la réponse deux fois !

  • j'écrirai dans les commentaires

14 utilisateurs ont voté. 10 utilisateurs se sont abstenus.

Source: habr.com

Ajouter un commentaire