السير على خطى Highload++ Siberia 2019 - 8 مهام على Oracle

مرحبا!

في الفترة من 24 إلى 25 يونيو، انعقد مؤتمر Highload++ Siberia 2019 في نوفوسيبيرسك. وكان رجالنا هناك أيضًا أبلغ عن "قواعد بيانات حاوية أوراكل (CDB/PDB) واستخدامها العملي في تطوير البرمجيات"، سننشر نسخة نصية بعد قليل. لقد كان رائعا، شكرا olegbunin للمنظمة، وكذلك لكل من حضر.

في هذا المنشور، نود أن نشاركك المشكلات التي واجهناها في جناحنا حتى تتمكن من اختبار معرفتك بـ 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 بزيادة التسلسل مرة واحدة فقط لكل صف. إذا كانت العبارة تحتوي على أكثر من مرجع واحد إلى NEXTVAL لتسلسل ما، فستقوم Oracle بزيادة التسلسل مرة واحدة وإرجاع نفس القيمة لجميع تكرارات 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 بإعادتها تلقائيًا وإرجاع رمز الخطأ المطبق إلى SQLCODE في SQLCA. على سبيل المثال، إذا تسببت عبارة INSERT في حدوث خطأ عن طريق محاولة إدراج قيمة مكررة في فهرس فريد، فسيتم التراجع عن العبارة.

يتم أيضًا أخذ استدعاء HP من العميل في الاعتبار ومعالجته كبيان واحد. وهكذا، تكتمل مكالمة HP الأولى بنجاح، بعد إدراج ثلاثة سجلات؛ تنتهي مكالمة HP الثانية بخطأ وتقوم بإرجاع السجل الرابع الذي تمكنت من إدراجه؛ فشل المكالمة الثالثة وهناك ثلاثة سجلات في الجدول.

كم عدد الصفوف التي ستكون في الجدول نتيجة تشغيل البرنامج النصي التالي؟

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 أو غير معروف (بسبب القيمة الخالية). عندما تقوم Oracle بتقييم شرط قيد التحقق لصف معين، تشير أسماء الأعمدة في الشرط إلى قيم الأعمدة في هذا الصف.

وبالتالي، فإن القيمة الخالية سوف تجتاز الاختبار، وسيتم تنفيذ الكتلة المجهولة بنجاح حتى تتم محاولة إدراج القيمة 3. بعد ذلك، ستقوم كتلة معالجة الأخطاء بمسح الاستثناء، ولن يحدث التراجع، و سيكون هناك أربعة صفوف متبقية في الجدول مع القيم 1، فارغة، 2 وخالية مرة أخرى.

ما هي أزواج القيم التي ستشغل نفس مقدار المساحة في الكتلة؟

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, 'Д', 'Вася');

• أ و العاشر
• ب و ي
• ج و ك
• ج و ز
• ك و ز
• أنا و ج
• ي و العاشر
• كل ما ورداعلاه

إجابةفيما يلي مقتطفات من الوثائق (12.1.0.2) حول تخزين أنواع مختلفة من البيانات في Oracle.

نوع بيانات CHAR
يحدد نوع البيانات CHAR سلسلة أحرف ذات طول ثابت في مجموعة أحرف قاعدة البيانات. يمكنك تحديد مجموعة أحرف قاعدة البيانات عند إنشاء قاعدة البيانات الخاصة بك. تضمن Oracle أن جميع القيم المخزنة في عمود CHAR لها الطول المحدد حسب الحجم في دلالات الطول المحددة. إذا قمت بإدراج قيمة أقصر من طول العمود، فستقوم Oracle بتوزيع القيمة على طول العمود.

نوع البيانات VARCHAR2
يحدد نوع البيانات VARCHAR2 سلسلة أحرف ذات طول متغير في مجموعة أحرف قاعدة البيانات. يمكنك تحديد مجموعة أحرف قاعدة البيانات عند إنشاء قاعدة البيانات الخاصة بك. تقوم Oracle بتخزين قيمة حرف في عمود VARCHAR2 تمامًا كما تحددها، دون أي حشوة فارغة، بشرط ألا تتجاوز القيمة طول العمود.

NUMBER نوع البيانات
يقوم نوع البيانات NUMBER بتخزين الصفر بالإضافة إلى الأرقام الثابتة الموجبة والسالبة ذات القيم المطلقة من 1.0 × 10-130 إلى ولكن لا تتضمن 1.0 × 10126. إذا قمت بتحديد تعبير حسابي تكون قيمته مطلقة أكبر من أو تساوي 1.0 × 10126، ثم تقوم Oracle بإرجاع خطأ. تتطلب كل قيمة NUMBER من 1 إلى 22 بايت. مع أخذ ذلك في الاعتبار، يمكن حساب حجم العمود بالبايت لقيمة بيانات رقمية معينة NUMBER(p)، حيث p هي دقة قيمة معينة، باستخدام الصيغة التالية: ROUND((الطول(ع)+s)/2))+1 حيث s تساوي صفراً إذا كان العدد موجباً، و s تساوي 1 إذا كان العدد سالباً.

بالإضافة إلى ذلك، لنأخذ مقتطفًا من الوثائق المتعلقة بتخزين القيم الخالية.

القيمة الخالية هي غياب القيمة في العمود. تشير القيم الخالية إلى بيانات مفقودة أو غير معروفة أو غير قابلة للتطبيق. يتم تخزين القيم الخالية في قاعدة البيانات إذا كانت تقع بين الأعمدة التي تحتوي على قيم البيانات. في هذه الحالات، فإنها تتطلب بايت واحد لتخزين طول العمود (صفر). لا تتطلب القيم الخالية الزائدة في الصف أي تخزين لأن رأس الصف الجديد يشير إلى أن الأعمدة المتبقية في الصف السابق فارغة. على سبيل المثال، إذا كانت الأعمدة الثلاثة الأخيرة من الجدول فارغة، فلن يتم تخزين أي بيانات لهذه الأعمدة.

وعلى أساس هذه البيانات، نبني المنطق. نحن نفترض أن قاعدة البيانات تستخدم ترميز AL32UTF8. في هذا الترميز، ستشغل الحروف الروسية 2 بايت.

1) A وX، قيمة الحقل "Y" تستغرق بايتًا واحدًا، وقيمة الحقل x "D" تستغرق 1 بايت
2) B وY، سيتم ملء القيمة 'Vasya' في b بمسافات تصل إلى 10 أحرف وستستغرق 14 بايت، وستستغرق 'Vasya' في d 8 بايت.
3) C و K. كلا الحقلين لهما القيمة NULL، وبعدهما توجد حقول مهمة، لذا فهما يشغلان بايت واحد.
4) C وZ. كلا الحقلين لهما القيمة NULL، لكن الحقل Z هو الأخير في الجدول، لذلك لا يشغل مساحة (0 بايت). يشغل الحقل C بايت واحد.
5) K و Z. على غرار الحالة السابقة. القيمة في الحقل K تشغل 1 بايت، في Z – 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-tree، يقيس عامل تجميع الفهرس التجميع الفعلي للصفوف فيما يتعلق بقيمة الفهرس.

يساعد عامل تجميع الفهرس المُحسِّن في تحديد ما إذا كان فحص الفهرس أو فحص الجدول الكامل أكثر كفاءة لاستعلامات معينة). يشير عامل التجميع المنخفض إلى إجراء فحص فهرس فعال.

يشير عامل التجميع القريب من عدد الكتل في الجدول إلى أن الصفوف مرتبة فعليًا في كتل الجدول بواسطة مفتاح الفهرس. إذا قامت قاعدة البيانات بإجراء فحص كامل للجدول، فإن قاعدة البيانات تميل إلى استرداد الصفوف حيث يتم تخزينها على القرص مرتبة حسب مفتاح الفهرس. يشير عامل التجميع القريب من عدد الصفوف إلى أن الصفوف متناثرة بشكل عشوائي عبر كتل قاعدة البيانات فيما يتعلق بمفتاح الفهرس. إذا قامت قاعدة البيانات بإجراء فحص كامل للجدول، فلن تقوم قاعدة البيانات باسترداد الصفوف بأي ترتيب تم فرزه بواسطة مفتاح الفهرس هذا.

في هذه الحالة، يتم فرز البيانات بشكل مثالي، بحيث يكون عامل التجميع مساويًا أو قريبًا من عدد الكتل المشغولة في الجدول. بالنسبة لحجم كتلة قياسي يبلغ 8 كيلو بايت، يمكنك أن تتوقع أن حوالي ألف من قيم الأرقام الضيقة سوف تتناسب مع كتلة واحدة، وبالتالي فإن عدد الكتل، ونتيجة لذلك، سيكون عامل التجميع حوالي العشرات.

عند أي قيم 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% من حجم كتلة قاعدة البيانات مطروحًا منه بعض النفقات العامة

وبالتالي، يجب ألا يتجاوز الحجم الإجمالي للأعمدة المفهرسة 6 كيلو بايت. ما يحدث بعد ذلك يعتمد على الترميز الأساسي المحدد. بالنسبة لترميز AL32UTF8، يمكن أن يشغل حرف واحد 4 بايت كحد أقصى، لذلك في أسوأ السيناريوهات، سيتم احتواء حوالي 6 حرف في 1500 كيلو بايت. لذلك، لن تسمح Oracle بإنشاء الفهرس عند N = 400 (عندما يكون طول مفتاح الحالة الأسوأ هو 1600 حرفًا * 4 بايت + طول الصف)، بينما عند N = 200 (أو أقل) سيعمل إنشاء الفهرس دون مشاكل.

تم تصميم عامل التشغيل INSERT مع تلميح APPEND لتحميل البيانات في الوضع المباشر. ماذا يحدث إذا تم تطبيقه على الجدول الذي تم تعليق المشغل عليه؟

• سيتم تحميل البيانات في الوضع المباشر، وسيعمل المشغل كما هو متوقع
• سيتم تحميل البيانات في الوضع المباشر، لكن لن يتم تنفيذ المشغل
• سيتم تحميل البيانات في الوضع التقليدي، وسيعمل المشغل كما ينبغي
• سيتم تحميل البيانات في الوضع التقليدي، لكن لن يتم تنفيذ المشغل
• لن يتم تحميل البيانات، سيتم تسجيل خطأ

إجابةفي الأساس، هذه مسألة منطق أكثر. للعثور على الإجابة الصحيحة، أود أن أقترح نموذج المنطق التالي:

1. يتم إجراء الإدراج في الوضع المباشر عن طريق التشكيل المباشر لكتلة البيانات، وتجاوز محرك SQL، مما يضمن سرعة عالية. وبالتالي، فإن ضمان تنفيذ الزناد أمر صعب للغاية، إن لم يكن مستحيلا، ولا يوجد أي نقطة في ذلك، لأنه سيظل يبطئ الإدراج بشكل جذري.
2. سيؤدي الفشل في تنفيذ المشغل إلى حقيقة أنه إذا كانت البيانات الموجودة في الجدول هي نفسها، فإن حالة قاعدة البيانات ككل (الجداول الأخرى) ستعتمد على الوضع الذي تم إدراج هذه البيانات فيه. من الواضح أن هذا سيؤدي إلى تدمير سلامة البيانات ولا يمكن تطبيقه كحل في الإنتاج.
3. يتم التعامل مع عدم القدرة على تنفيذ العملية المطلوبة بشكل عام على أنها خطأ. ولكن هنا يجب أن نتذكر أن APPEND عبارة عن تلميح، والمنطق العام للتلميحات هو أنها تؤخذ في الاعتبار إن أمكن، أما إذا لم يكن الأمر كذلك، فسيتم تنفيذ العامل دون أخذ التلميح في الاعتبار.

لذا فإن الجواب المتوقع هو سيتم تحميل البيانات في الوضع العادي (SQL)، وسيتم إطلاق المشغل.

وفقًا لوثائق Oracle (مقتبسة من 8.04):

ستؤدي انتهاكات القيود إلى تنفيذ العبارة بشكل تسلسلي، باستخدام مسار الإدراج التقليدي، دون تحذيرات أو رسائل خطأ. الاستثناء هو القيود المفروضة على البيانات التي تصل إلى نفس الجدول أكثر من مرة في المعاملة، مما قد يسبب رسائل خطأ.
على سبيل المثال، في حالة وجود مشغلات أو تكامل مرجعي في الجدول، فسيتم تجاهل تلميح APPEND عند محاولة استخدام INSERT للتحميل المباشر (تسلسلي أو متوازي)، بالإضافة إلى تلميح أو جملة 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);

• التنفيذ الناجح
• فشل بسبب خطأ في بناء الجملة
• خطأ: المعاملة الذاتية غير صالحة
• خطأ يتعلق بتجاوز الحد الأقصى لتداخل المكالمات
• خطأ انتهاك المفتاح الخارجي
• خطأ متعلق بالأقفال

إجابةتم إنشاء الجدول والمشغل بشكل صحيح تمامًا ويجب ألا تؤدي هذه العملية إلى حدوث مشكلات. يُسمح أيضًا بالمعاملات المستقلة في المشغل، وإلا فلن يكون التسجيل ممكنًا، على سبيل المثال.

بعد إدراج الصف الأول، سيؤدي إطلاق الزناد الناجح إلى إدراج الصف الثاني، مما يؤدي إلى إطلاق الزناد مرة أخرى، وإدراج صف ثالث، وهكذا حتى فشل البيان بسبب تجاوز الحد الأقصى لتداخل الاستدعاءات. ومع ذلك، هناك نقطة دقيقة أخرى تدخل حيز التنفيذ. في وقت تنفيذ المشغل، لم يكن الالتزام قد اكتمل بعد للسجل المدرج الأول. لذلك، يحاول المشغل الذي يتم تشغيله في معاملة مستقلة إدراج صف في الجدول يشير إلى مفتاح خارجي لسجل لم يتم الالتزام به بعد. يؤدي هذا إلى الانتظار (تنتظر المعاملة المستقلة التزام المعاملة الرئيسية لمعرفة ما إذا كان يمكنها إدراج البيانات) وفي نفس الوقت تنتظر المعاملة الرئيسية حتى تستمر المعاملة المستقلة في العمل بعد المشغل. يحدث طريق مسدود، ونتيجة لذلك، يتم إلغاء المعاملة المستقلة لأسباب تتعلق بالأقفال.

يمكن للمستخدمين المسجلين فقط المشاركة في الاستطلاع. تسجيل الدخول، من فضلك.

كان من الصعب أن؟

• مثل إصبعين، قررت على الفور كل شيء بشكل صحيح.

• ليس حقًا، لقد كنت مخطئًا في بعض الأسئلة.

• لقد قمت بحل نصفها بشكل صحيح.

• لقد خمنت الإجابة مرتين!

• سأكتب في التعليقات

صوت 14 مستخدمين. امتنع 10 مستخدما عن التصويت.

المصدر: www.habr.com