ביצוע כוונון ביצועים זה אחד הדברים המעניינים ביותר בעולם של בסיסי נתונים – לפעמים אפילו שמעתי שאנשים מתייחסים לזה בתור סוג של "אומנות". אני חייב להסכים עם האמירה הזו: לפעמים כדי לפתור בעיית ביצועים צריך נפש של אומן – לחשוב מחוץ לקופסא, להיות יצירתי ולהפעיל הרבה אינטואיציה.

דבר נוסף שאני חושב שעוזר לנו הרבה פעמים כאשר מכווננים ביצועים זה זיכרון מעולה בתוספת של חיבה לנוסטלגיה. כל מי שניסה לעשות קצת טיונינג יותר מפעם אחת יודע שבפעם השנייה שהוא עושה טיונינג לאותה מערכת הוא מיד מנסה כדבר ראשון את הפתרון האחרון שעבד לו. ככה זה כולם – כל ניסיון לכוונן ביצועים מתחיל תמיד בהעלאת זיכרונות נוסטלגיים של כל בעיות הביצועים האחרות שאי פעם נתקלנו בהם ופתרנו כדי לראות אם משהו מתאים… 🙂

אחד הדברים הקשים ביותר לכוונון בעיני זה שליפות שלפעמים עובדות מהר ולפעמים לא ובתת הקטגוריה הזו, שליפות שמתנהגות ככה אבל לכולן יש את אותו ה-SQL ID ואותו Execution Plan (באותה סביבה, כמובן). איך דבר כזה יכול לקרות? לדוגמה אם משתמשים ב-Bind Variables אז התוכנית של השליפה מתוכננת על ידי ה-Optimizer פעם אחת וכל השליפות שבאות אחריה עושות soft parse וחוסכות לעצמן את הצורך ב-parse מיותר.
כל זה טוב ויפה אבל איך האופטימייזר יודע להעריך כמויות רשומות ותוכנית יעילה? בגרסה 9i נוספה תכונה מעניינת: Bind Peeking. האופטימייזר שנתקל בשליפה עם bind variables בפעם הראשונה "מציץ" לתוך המשתנים האלה, רואה איזה ערכים יש שם ומשתמש בהם כדי לקבוע תוכנית פעולה לשליפה שלנו.

תום קייט היה בארץ לפני כמה שנים וסיפר על העניין הזה סיפור מעניין. הסיפור היה כזה: פעם נפתחה קריאה מוזרה בתמיכה של אורקל בטענה שבימי שני בבוקר אבל רק בכאלה שיורד בהם גשם, אז בסיס הנתונים רץ לאט מהרגיל והדבר היחידי שהם מצאו שעוזר הוא לאתחל אותו בהפסקת הצהריים.

רגע, רגע, מה?!

כמובן שהתמיכה של אורקל מיד ענתה שבסיס הנתונים לא נוטים להתחשב בתחזיות מזג האוויר ואם כבר שואלים אותם, אז לפי דעתם בסיסי נתונים הם בכלל טיפוסים של חורף ולכן אין להם מושג מה רוצה ה-DBA האמור מהם. לאחר שהתחילו לבחון את הנושא ברצינות ולנסות לזהות מה קורה במערכת הזו התברר שאכן התופעה הזו אמיתית – אבל אין לה קשר לגשם בכלל (כמעט).

מה הם גילו? אז ככה:
בימי ראשון בערב באותה חברה, לוקחים גיבוי קר של בסיס הנתונים – זה אומר שבסיס הנתונים האמור מורד בצורה מסודרת. מכוון שבסיס נתונים שיורד לא תמיד עולה, בימי שני ה-DBA שלהם היה מתעורר מוקדם ומגיע למשרד ב-7 בבוקר כדי לוודא שבסיס הנתונים אכן עלה בצורה תקינה לאחר הגיבוי. לצורך כך הוא נכנס למערכת, מריץ מספר שליפות אפליקטיביות עם ערכים קבועים כדי לוודא שהנתונים תקינים, הביצועים לא נפגעו והכל בסדר. בימי שני בבוקר, כאשר יורד גשם, המנהרה שנמצאת בדרך לעבודה של ה-DBA נחסמת והוא נאלץ לנסוע דרך הגשר – מה שאומר שבמקום להגיע לעבודה ב-7, הוא מגיע לעבודה ב-10.

יופי – איך כל זה קשור לעניין שלנו? ובכן כאשר ה-DBA היה מגיע ראשון אחרי ההשבתה (אחרי שהזיכרון אותחל), הוא היה שם ערכים קבועים שהיו מחזירים רשומות בודדות ולכן היו נוצרים Execution Plans מסוג מסוים. בימים שהוא היה מאחר, האנשים הראשונים שהיו מגיעים לעבודה היו משתמשי הדוחות ומכוון שהם השתמשו באותן שליפות אך עם ערכים אחרים (כאלה שהיו מחזירים כמות גדולה של רשומות), היה נבחר Plan שמתאים להם וכל המשתמשים האחרים היו סובלים.

לצערי אני לא זוכר איזה פתרון הציעו אורקל בנושא – אבל אני מניח שזה היה משהו בסגנון של flush ל-shared pool או רילוקיישן של ה-DBA קרוב יותר לחברה או משהו כזה.

אז איך כל הסיפור הנפלא הזה מתקשר לכותרת של הפוסט הזה? ובכן התשובה היא שהבעיה הזו היא הרבה יותר נפוצה ממה שנדמה ולפעמים יש צורך למצוא לזה פתרונות יצירתיים שלא כוללים מחיקה של כל ה-execution plans וה-cache ששמרנו מאז שבסיס הנתונים עלה. פתרון אחד שנוסף באורקל 11 זה הפיצ'ר של bind aware שמאפשר לו להחזיק יותר מתוכנית אחת בו זמנית – וזו נקבעת על פי הערכים ב-bind-ים אבל פתרון זה טוב רק אם אפשר לשנות את השליפות ולהוסיף את ההינט BIND_AWARE. פתרון נוסף: Adaptive Cursor Sharing. זה נושא שאפשר וראוי לכתוב עליו פוסט שלם ואם תהיה דרישה, אני בטח גם אעשה את זה… 🙂

לצערי, בתקופה האחרונה נתקלתי יותר ויותר במקרים אצל לקוחות שבהם בגלל כמות גדולה של שאילתות נוצר "עומס" על ה-Library cache ה-execution plan של שליפות מסוימות "נדחף" החוצה תוך כדי עבודה ומה שקורה אז זה שהאופטימייזר מחשב execution plan מחדש וה-bind peeking לפעמים גורם לתוכניות "מקולקלות" להתחיל לעבוד ולדפוק את הביצועים של השליפות. כדי לעקוף את הבעיה חיפשתי דרך לגרום ל-cursor להיזרק החוצה מה-library cache כדי שתוכנית טובה אחרת תתפוס את מקומה (בגדול, מה שרציתי זה לבצע invalidation ל-plan בלי לשנות שום דבר אחר במערכת).

בגרסאות ישנות הדבר הזה לא היה קיים כל כך (אני אשמח לשמוע תיקון, אם למישהו יש) אבל החל מגרסה 11 נוספה אופציה כזו ויוצא שאני משתמש בה לא מעט לאחרונה. הפונקציה מדוברת נמצאת ב-dbms_shared_pool (שהיה קיים גם קודם) והיא נקראת  purge. פונקציה זו יודעת לקבל כתובת ו-hash value של קטע קוד או שליפה ולהעיף אותו מה-library cache.

כתבתי לעצמי קטע קוד קצר שעוזר לי להעיף את ה-Plan-ים של שליפה מסויימת (על פי SQL ID) מהזיכרון והנה הוא לשימושכם:

declare
    my_sql_id        varchar2(20) := '4ft4m4udscftj';
    sql_address_hash varchar2(30);
begin
    for cur in (select address || ',' || hash_value sql_address_hash
                  into sql_address_hash
                  from v$sqlarea
                 where sql_id = my_sql_id)
    loop
        sys.DBMS_SHARED_POOL.purge(cur.sql_address_hash, 'C', 1);
    end loop;
exception
    when no_data_found then
        null;
end;
/

למידע נוסף (בערך מה שכתבתי, רק באנגלית) ניתן לקרוא כאן בבלוג הזה או בספרות של אורקל.