مؤلف المقال هو
فيكتور فارلاموف (
varlamovVp18 ) ، OCP.
تم نشر
المقال الأصلي في 07/07/2017.
شكر خاص لمؤلف الترجمة -
brutaltag .
ينفّذ نظام الإبلاغ الخاص بنا عادة المئات من الطلبات المطولة التي تحدثها الأحداث المختلفة. معلمات الاستعلام عبارة عن قائمة من العملاء وفاصل زمني (يوميًا ، أسبوعيًا ، شهريًا). بسبب البيانات غير المتساوية في الجداول ، يمكن أن ينتج استعلام واحد صفًا واحدًا أو مليون صفًا ، وفقًا لمعلمات التقرير (لدى العملاء المختلفين أعداد مختلفة من الصفوف في جداول الحقائق). يتم إعداد كل تقرير على شكل حزمة ذات وظيفة رئيسية تأخذ معلمات الإدخال ، وتقوم بإجراء تحويلات إضافية ، ثم تفتح مؤشر ثابت مع
المتغيرات المرتبطة بها ، وتُرجع هذا المؤشر المفتوح أخيرًا. تم تعيين المعلمة DB CURSOR_SHARING إلى FORCE.
في مثل هذه الحالة ، يتعين على المرء أن يتعامل مع الأداء الضعيف ، سواء في حالة إعادة استخدام خطة الاستعلام من قِبل المُحسِّن ، وعندما يتم
تحليل الاستعلام
تمامًا باستخدام معلمات في شكل
حرفية . قد تسبب متغيرات Bound خطة استعلام دون المستوى الأمثل.
في كتابه "ممارسات خبير أوراكل" ، يقدم أليكس غورباتشوف قصة ممتعة رواها توم كايت. كل يوم الاثنين الممطر ، كان على المستخدمين التعامل مع خطة استعلام معدلة. من الصعب تصديق ذلك ، لكنه كان:
"وفقًا لملاحظات المستخدم النهائي ، عندما هطلت الأمطار بشدة يوم الاثنين ، كان أداء قاعدة البيانات فظيعًا. في أي يوم آخر من أيام الأسبوع أو يوم الاثنين لم تكن هناك مشاكل دون مطر. من محادثة مع DBA ، علمت Tom Kite أن الصعوبات استمرت حتى اضطرت قاعدة البيانات إلى إعادة التشغيل ، وبعد ذلك أصبح الأداء طبيعياً. كان هذا هو الحل: الأمطار الاثنين - إعادة التشغيل. "
هذه حالة حقيقية ، وتم حل المشكلة تمامًا دون أي سحر ، فقط بفضل المعرفة الممتازة لكيفية عمل Oracle. سأظهر الحل في نهاية المقال.
فيما يلي مثال صغير لكيفية عمل المتغيرات ذات الصلة.
إنشاء جدول مع بيانات غير متساوية.
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
بمعنى آخر ، لدينا جدول VVP_HARD_PARSE_TEST به مليون صف ، حيث يكون الحقل في 10000 حالة C2 = 99 ، 8 سجلات مع C2 = 1 ، والباقي مع C2 = 1،000،000. يشير الرسم البياني عبر الحقل C2 إلى مُحسِّن Oracle حول توزيع البيانات هذا. يُعرف هذا الموقف
بالتوزيع غير المتساوي للبيانات ، ويمكن أن يساعدك الرسم البياني في اختيار خطة الاستعلام الصحيحة بناءً على البيانات المطلوبة.
نلاحظ استفسارات بسيطة لهذا الجدول. من الواضح للطلب
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :pإذا كانت p = 1 ، فسيكون INDEX RANGE SCAN هو الخيار الأفضل ، بالنسبة للحالة p = 1000000 ، من الأفضل استخدام FULL TABLE SCAN. استعلامات Query1 و Query1000000 متطابقة ، باستثناء النص في التعليقات ، يتم ذلك للحصول على معرفات خطة استعلام مختلفة.
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
الآن دعونا نلقي نظرة على خطط الاستعلام:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
كما ترون ، يتم إنشاء خطة للطلبات المختلفة مرة واحدة فقط ، في وقت التنفيذ الأول (يوجد مؤشر فرعي واحد فقط مع CHILD_NUMBER = 0 لكل طلب). يتم تنفيذ كل طلب مرتين (EXECUTION = 2). أثناء التحليل الثابت ، يسترد Oracle قيم المتغيرات المرتبطة ويختار خطة وفقًا لهذه القيم. لكنه يستخدم نفس الخطة للتشغيل التالي ، على الرغم من تغير المتغيرات ذات الصلة في الجولة الثانية. يتم استخدام الخطط غير المثلى - Query1000000 مع المتغير C2 = 1 يستخدم FULL TABLE SCAN بدلاً من INDEX RANGE SCAN ، والعكس بالعكس.
من الواضح أن إصلاح التطبيق واستخدام المعلمات كحرفية في الاستعلام هو الطريقة الأنسب لحل المشكلة ، ولكنه يؤدي إلى SQL ديناميكي مع أوجه القصور المعروفة. هناك طريقة أخرى لتعطيل استعلام المتغيرات ذات الصلة (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) أو حذف المدرج التكراري (
الارتباط ).
أحد الحلول الممكنة هو الاستخدام البديل لسياسات الوصول إلى البيانات ، والمعروفة أيضًا باسم
قاعدة البيانات الافتراضية الخاصة (التحكم التفصيلي في الوصول والتحكم
الدقيق في الوصول والتحكم في مستوى الصف). يتيح لك هذا تغيير الطلبات أثناء التنقل ، وبالتالي يمكن أن يتسبب في تحليل كامل لخطة الطلب في كل مرة يستخدم فيها الطلب التحكم المفصل في الوصول. هذه التقنية موصوفة بالتفصيل في
مقال بقلم راندالف جيست . عيب هذه الطريقة هو العدد المتزايد من التحليلات الكاملة وعدم القدرة على التعامل مع خطط الاستعلام.
انظر ماذا سنفعل الآن. بعد تحليل بياناتنا ، نقرر تقسيم العملاء إلى ثلاث فئات - كبيرة ومتوسطة وصغيرة (LMS أو 9-5-1) - وفقًا لعدد المعاملات أو المعاملات خلال العام. أيضًا ، يعتمد عدد الأسطر في التقرير بشكل صارم على الفترة: شهري - كبير ، أسبوعي - متوسط ، يومي - صغير أو 9-5-1. علاوة على ذلك ، فإن الحل بسيط - سنجعل سياسة الأمان تعتمد على كل فئة وعلى كل فترة. لذلك ، لكل طلب نحصل عليه 9 مؤشرات الطفل المحتملة. علاوة على ذلك ، فإن الاستعلامات ذات السياسات المختلفة ستقودنا إلى نفس معرفات الاستعلام ، مما يجعل من الممكن تنفيذ SQL PLAN MANAGEMENT (خط الأساس لخطة sql).
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
الآن ، إذا أردنا تضمين هذه التكنولوجيا في التقرير ، فنحن بحاجة إلى إضافة HARD_PARSE_TABLE إلى الاستعلام (لن يؤدي ذلك إلى تدميره قليلاً) والاتصال بـ CALC_PREDICATES قبل تنفيذ الاستعلام الرئيسي.
دعونا نرى كيف يمكن لهذه التقنية تحويل المثال السابق:
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
لنلقِ نظرة على خطط التنفيذ:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
تبدو رائعة! يتم تنفيذ كل استعلام مرتين ، باستخدام مؤشرات فرعية مختلفة وخطط مختلفة. بالنسبة إلى المعلمة C2 = 1،000،000 ، نرى FULL TABLE SCAN في كلا الاستعلامات ، وللمعلمة C1 = 1 ، نرى دائمًا INDEX RANGE SCAN.
في النهاية ، أعطي حلاً لقضية الاثنين الممطرة:
"اتضح أنه في كل نهاية أسبوع يوم الأحد ، كان هناك نسخة احتياطية باردة ، بحيث تم تجديد جميع خطط الاستعلام عند التنفيذ الأول صباح الاثنين. عادة ما يبدأ أحد الموظفين أعمالهم في وقت مبكر عن غيرهم ، وتم تنفيذ خطة طلبه بشكل جيد للمستخدمين الآخرين خلال الأسبوع. ومع ذلك ، إذا كانت السماء تمطر ، فقد تأخر هذا المستخدم في بداية يوم العمل بسبب مشاكل في طريقه الصباحي. ثم كان حساب الدُفعات للتقارير هو أول من بدأ ، لكن خطة الاستعلام كانت سيئة تمامًا للحالات المتبقية نظرًا لقيم غير ملائمة للمتغيرات المرتبطة.
وبعض المشاهدات المفيدة للنظام:
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind