Highload++ Siberia 2019 の足跡をたどる - Oracle での 8 つのタスク

Привет！

24 月 25 日と 2019 日、Highload++ Siberia XNUMX カンファレンスがノボシビルスクで開催されました。 報告 「Oracleコンテナデータベース（CDB/PDB）とソフトウェア開発におけるその活用法」は、少し後からテキスト版を公開する予定です。 かっこよかったです、ありがとう オレグブニン 組織にとっても、そして来てくれたすべての人にとっても。

この投稿では、Oracle の知識をテストできるように、当社のブースで発生した問題を共有したいと思います。 カットの下には 8 つの問題、解答の選択肢、説明が表示されます。

次のスクリプトを実行した結果として表示される最大シーケンス値は何ですか?

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
• いいえ、エラーが発生します

答えOracle のドキュメントによると (8.1.6 から引用):
単一の SQL ステートメント内で、Oracle は行ごとに XNUMX 回だけシーケンスをインクリメントします。 文にシーケンスの NEXTVAL への参照が複数含まれている場合、Oracle はシーケンスを XNUMX 回インクリメントし、出現するすべての NEXTVAL に対して同じ値を返します。 文に CURRVAL と NEXTVAL の両方への参照が含まれている場合、Oracle はシーケンスをインクリメントし、文内の順序に関係なく、CURRVAL と NEXTVAL の両方に同じ値を返します。

このように、 最大値は行数、つまり 5 に対応します。.

次のスクリプトを実行した結果、テーブルには何行が表示されますか?

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

答えOracle のドキュメントによると (11.2 から引用):

SQL ステートメントを実行する前に、Oracle は暗黙的なセーブポイント (ユーザーは使用できません) をマークします。 その後、ステートメントが失敗した場合、Oracle はステートメントを自動的にロールバックし、該当するエラー コードを SQLCA の SQLCODE に返します。 たとえば、INSERT ステートメントが一意のインデックスに重複した値を挿入しようとしてエラーが発生した場合、ステートメントはロールバックされます。

クライアントから HP への呼び出しも XNUMX つのステートメントとして考慮され、処理されます。 したがって、最初の HP 呼び出しは正常に完了し、XNUMX つのレコードが挿入されます。 XNUMX 番目の HP 呼び出しはエラーで終了し、挿入できた XNUMX 番目のレコードをロールバックします。 XNUMX回目の呼び出しは失敗します。 テーブルには XNUMX つのレコードがあります.

次のスクリプトを実行した結果、テーブルには何行が表示されますか?

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

答えOracle のドキュメントによると (11.2 から引用):

チェック制約を使用すると、テーブル内の各行が満たさなければならない条件を指定できます。 制約を満たすには、テーブル内の各行の条件を TRUE または不明 (NULL のため) にする必要があります。 Oracle が特定の行のチェック制約条件を評価する場合、条件内の列名はその行の列値を参照します。

したがって、値 null はチェックに合格し、値 3 を挿入しようとするまで匿名ブロックは正常に実行されます。この後、エラー処理ブロックは例外をクリアし、ロールバックは発生しません。 テーブルには XNUMX 行が残ります 値は 1、null、2、そして再び null です。

ブロック内で同じ量のスペースを占める値のペアはどれですか?

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とX
• BとY
• CとK
• CとZ
• KとZ
• 私とJ
• JとX
• リストされているすべて

答え以下は、Oracle でのさまざまなタイプのデータの保管に関するドキュメント (12.1.0.2) からの抜粋です。

CHAR データ型
CHAR データ型は、データベース文字セットの固定長文字列を指定します。 データベースの作成時にデータベースの文字セットを指定します。 Oracle は、CHAR 列に格納されるすべての値が、選択された長さセマンティクスのサイズで指定された長さを持つことを保証します。 列の長さよりも短い値を挿入すると、Oracle はその値を列の長さまで空白で埋め込みます。

VARCHAR2 データ型
VARCHAR2 データ型は、データベース キャラクタ セットの可変長文字列を指定します。 データベースの作成時にデータベースの文字セットを指定します。 Oracle では、値が列の長さを超えない限り、文字値は空白埋めなしで指定どおりに VARCHAR2 列に格納されます。

NUMBER データ型
NUMBER データ型には、ゼロのほか、1.0 x 10-130 から 1.0 x 10126 を除く絶対値を持つ正および負の固定数が格納されます。値が以上の絶対値を持つ算術式を指定した場合1.0 x 10126 の場合、Oracle はエラーを返します。 各 NUMBER 値には 1 ～ 22 バイトが必要です。 これを考慮すると、特定の数値データ値 NUMBER(p) のバイト単位の列サイズ (p は指定された値の精度) は、次の式を使用して計算できます。 ROUND((長さ(p)+s)/2))+1 ここで、数値が正の場合、s は 1 に等しく、数値が負の場合、s は XNUMX に等しくなります。

さらに、Null 値の保存に関するドキュメントから抜粋してみましょう。

NULL は、列に値が存在しないことを意味します。 Null は、データが欠落しているか、不明であるか、または適用できないことを示します。 Null がデータ値を含む列の間にある場合、Null はデータベースに格納されます。 このような場合、列の長さ (ゼロ) を格納するために 1 バイトが必要です。 新しい行ヘッダーは前の行の残りの列が NULL であることを示すため、行の末尾の NULL には記憶域が必要ありません。 たとえば、テーブルの最後の XNUMX つの列が null の場合、これらの列にはデータは格納されません。

これらのデータに基づいて推論を構築します。 データベースでは AL32UTF8 エンコーディングが使用されていると仮定します。 このエンコードでは、ロシア語の文字は 2 バイトを占めます。

1) A と X、フィールド a の値 'Y' は 1 バイト、フィールド x の値 'D' は 2 バイト
2) B と Y、b の 'Vasya' 値は最大 10 文字のスペースで埋め込まれ、14 バイトかかります。d の 'Vasya' は 8 バイトかかります。
3) C と K。どちらのフィールドも値 NULL を持ち、その後に重要なフィールドがあるため、1 バイトを占めます。
4) C と Z。両方のフィールドの値は NULL ですが、フィールド Z はテーブルの最後であるため、スペースを占有しません (0 バイト)。 フィールド C は 1 バイトを占めます。
5) K と Z。前のケースと同様です。 K フィールドの値は、Z – 1 の 0 バイトを占めます。
6) I と J。ドキュメントによると、どちらの値も 2 バイトかかります。 ドキュメントから引用した公式、round( (1 + 0)/2) +1 = 1 + 1 = 2 を使用して長さを計算します。
7) J および X。J フィールドの値は 2 バイト、X フィールドの値は 2 バイトかかります。

合計すると、正しいオプションは次のとおりです: C と K、I と J、J と X。

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

• 数十個くらい
• 約数百
• 約数千
• 数万くらい

答えOracle のドキュメントによると (12.1 から引用):

B ツリー インデックスの場合、インデックス クラスタリング係数は、インデックス値に関連する行の物理的なグループ化を測定します。

インデックス クラスタリング係数は、オプティマイザが特定のクエリに対してインデックス スキャンとフル テーブル スキャンのどちらが効率的であるかを判断するのに役立ちます。 クラスタリング係数が低い場合は、インデックス スキャンが効率的であることを示します。

テーブル内のブロック数に近いクラスタリング係数は、行がインデックス キーによってテーブル ブロック内で物理的に順序付けされていることを示します。 データベースがテーブル全体のスキャンを実行する場合、データベースはインデックス キーでソートされてディスクに格納されている行を取得する傾向があります。 行数に近いクラスタリング係数は、行がインデックス キーに関連してデータベース ブロック全体にランダムに分散していることを示します。 データベースがテーブル全体のスキャンを実行する場合、データベースはこのインデックス キーによるソート順で行を取得しません。

この場合、データは理想的にソートされているため、クラスタリング係数はテーブル内の占有ブロックの数と同じかそれに近い値になります。 標準ブロック サイズ 8 キロバイトの場合、約 XNUMX 個の狭い数値が XNUMX つのブロックに収まると予想できるため、ブロック数、およびその結果としてクラスタリング係数は次のようになります。 数十個くらい.

N の値がどのくらいであれば、次のスクリプトは標準設定の通常のデータベースで正常に実行されますか?

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

答えOracle のドキュメントによると (11.2 から引用):

論理データベースの制限

アイテム価格
制限の種類
限界値

インデックス
インデックス付き列の合計サイズ
データベース ブロック サイズの 75% から一部のオーバーヘッドを差し引いたもの

したがって、インデックス付き列の合計サイズは 6Kb を超えてはなりません。 次に何が起こるかは、選択した基本エンコーディングによって異なります。 AL32UTF8 エンコードの場合、4 文字は最大 6 バイトを占めることができるため、最悪の場合、約 1500 文字が 400 キロバイトに収まります。 したがって、Oracle は N = 1600 (最悪の場合のキー長が 4 文字 * XNUMX バイト + ROWID 長の場合) でのインデックスの作成を許可しません。 N = 200 (またはそれ以下) の場合 インデックスの作成は問題なく機能します。

APPEND ヒントを含む INSERT 演算子は、ダイレクト モードでデータをロードするように設計されています。 トリガーが掛かっているテーブルに適用するとどうなるでしょうか?

• データはダイレクト モードでロードされ、トリガーは期待どおりに機能します。
• データはダイレクトモードでロードされますが、トリガーは実行されません
• データは従来のモードでロードされ、トリガーは正常に機能します。
• データは従来のモードでロードされますが、トリガーは実行されません
• データはロードされず、エラーが記録されます

答え基本的に、これは論理の問題です。 正しい答えを見つけるには、次の推論モデルをお勧めします。

1. ダイレクト モードでの挿入は、SQL エンジンをバイパスしてデータ ブロックを直接形成することによって実行されるため、高速性が保証されます。 したがって、トリガーを確実に実行することは、不可能ではないにしても非常に困難であり、それでも挿入の速度が大幅に低下するため、これには意味がありません。
2. トリガーの実行に失敗すると、テーブル内のデータが同じである場合、データベース全体 (他のテーブル) の状態は、このデータが挿入されたモードに依存することになります。 これは明らかにデータの整合性を破壊するため、運用環境にソリューションとして適用することはできません。
3. 要求された操作を実行できない場合は、通常、エラーとして扱われます。 ただし、ここで APPEND はヒントであることを覚えておく必要があります。ヒントの一般的なロジックは、可能であれば考慮されますが、そうでない場合はヒントを考慮せずに演算子が実行されるというものです。

したがって、期待される答えは次のとおりです データは通常 (SQL) モードでロードされ、トリガーが起動されます。

Oracle のドキュメントによると (8.04 から引用):

制限に違反すると、警告やエラー メッセージが表示されることなく、従来の挿入パスを使用してステートメントがシリアルに実行されます。 例外として、トランザクション内で同じテーブルに複数回アクセスするステートメントに対する制限があり、エラー メッセージが発生する可能性があります。
たとえば、テーブルにトリガーまたは参照整合性が存在する場合、ダイレクト ロード INSERT (シリアルまたはパラレル) を使用しようとすると、APPEND ヒントは無視され、PARALLEL ヒントまたは句 (存在する場合) も無視されます。

次のスクリプトを実行すると何が起こるでしょうか?

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

• 実行成功
• 構文エラーによる失敗
• エラー: 自律トランザクションが無効です
• 最大呼び出しネストの超過に関連するエラー
• 外部キー違反エラー
• ロックに関するエラー

答えテーブルとトリガーは正確に作成されており、この操作で問題が発生することはありません。 トリガー内の自律トランザクションも許可されます。そうでないと、たとえばログ記録が不可能になります。

最初の行を挿入した後、トリガーの起動が成功すると XNUMX 行目が挿入され、再びトリガーが起動して XNUMX 行目が挿入され、呼び出しの最大ネストを超えてステートメントが失敗するまで、これが繰り返されます。 ただし、別の微妙な点が関係します。 トリガーが実行された時点では、最初に挿入されたレコードのコミットはまだ完了していません。 したがって、自律トランザクションで実行されているトリガーは、まだコミットされていないレコードへの外部キーを参照する行をテーブルに挿入しようとします。 これにより待機が発生し (自律トランザクションは、データを挿入できるかどうかを確認するためにメイン トランザクションがコミットするのを待ちます)、同時にメイン トランザクションは自律トランザクションがトリガー後に動作を継続するのを待ちます。 デッドロックが発生し、その結果、ロックに関連する理由により自律トランザクションがキャンセルされる.

登録ユーザーのみがアンケートに参加できます。 ログインお願いします。

難しかったですか？

• 二本の指のように、私はすぐにすべてを正しく判断しました。

• そうではありませんが、いくつかの質問で間違っていました。

• 半分は正解しました。

• 答えをXNUMX回予想してみました！

• コメントに書いておきます

14 人のユーザーが投票しました。 10名のユーザーが棄権した。

出所： habr.com