כמה מילים על bind peeking ואיך למחוק Execution Plan מה-Library Cache

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

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

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

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

רגע, רגע, מה?!
המשך קריאה…

איך לפתור את שגיאה ORA-04068

כל אחד שמפתח package-ים ב-PL/SQL נתקל בבעיה הזו לפחות פעם אחת במהלך הפיתוח: ORA-04068: existing state of packages has been discarded. זו בעיה נפוצה למדי בפיתוח package-ים שהפתרון המיידי שלה הוא להריץ שוב את הפרוצדורה או הפונקציה מתוך ה-package כדי שזה יסתדר. בוא נבין מה הבעיה ואיך ניתן לטפל בבעיה.

המשך קריאה…

יצירת קובץ CSV על ידי שימוש ב-SQLPLUS

היום התבקשתי על ידי אחת המפתחות לעזור לה לשמור שליפה של 230 עמודות ולמעלה מ-50 מליון רשומות לקובץ CSV. מבחינתי זו הזדמנות טובה לתעד את זה המסקנות שלי מהבעיה והפתרון היחסית גנרי שיצרתי בשבילו.

שליפות ב-sqlplus מחזירות בדרך כלל את הרשומות באחד משתי דרכים: הדרך הראשונה היא fixed length columns (כלומר העמודה הראשונה מתחילה תמיד במיקום 1, העמודה השנייה תמיד במיקום 7 וכן הלאה). הדרך השנייה היא כ-HTML ועל זה נדבר בהזדמנות אחרת. קובץ CSV הוא בסופו של דבר קובץ טקסט לכל דבר שבו העמודות (ערכים) מופרדים על ידי פסיקים (ומכאן שמו: Comma-separated values).

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

המשך קריאה…

איך לשחרר שטח מ-tablespace לגודל המינימלי שלו

כאשר נוצר מצב שבו ב-tablespace מתפנה מקום רב, אורקל מאפשרים להקטין את ה-tablespace על ידי מעבר על ה-datafile-ים שלו וביצוע פקודות resize. הבעיה מתעוררת כאשר ישנו Tablespace עם מספר datafiles (ולפעמים זה יכול להגיע גם לעשרות) ואז העבודה של מציאת הסגמנט ש”חוסם” את שחרור השטח הפנוי הופך להיות משימה מהגהינום: גם הזיהוי בצורה ידנית של הבלוק התפוס העליון (HWM) וגם ה-resize שלפעמים לוקח זמן.

דרך אגב, אם יש tablespace  במצב read-only ובו אינדקסים של אובייקט שהוא לא ב-read-only, אז כאשר זורקים את הטבלה או ה-partition אז השטח ב-tablespace הזה לא משתחרר – הסגמנטים של האינדקס הופכים ל-temp segments ועד שלא נפתח את ה-tablespace ל-read write השטח לא יזוהה כפנוי.

כדי להתגבר על כל הבעיות האלה ניתן להשתמש בפרוצדורה הסופר שימושית הבאה.

המשך קריאה…

פונקציית משתמש סיכומית

לפעמים עולה הצורך לכתוב פונקצייה סיכומית (כלומר שעובדת עם Group by). אורקל מאפשרים את האופציה על ידי שימוש ב-API מיוחד שהם מספקים.

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

-- Create type object for the aggrigation
create or replace type MultSum as object
(
-- Author  : ZELKAYAM
-- Created : 09/02/2009 18:45:45
-- Purpose :

    v_sum NUMBER, -- second highest value seen so far
    v_is_first number,

    static function ODCIAggregateInitialize(sctx IN OUT MultSum) return number,

    member function ODCIAggregateIterate(self  IN OUT MultSum,
                                         value IN number) return number,

    member function ODCIAggregateTerminate(self        IN MultSum,
                                           returnValue OUT number,
                                           flags       IN number) return number,

    member function ODCIAggregateMerge(self IN OUT MultSum,
                                       ctx2 IN MultSum) return number
)
/

-- Create type body for the aggrigation
create or replace type body zelkayam2.MultSum is

    static function ODCIAggregateInitialize(sctx IN OUT MultSum) return number is
    begin
        sctx := MultSum(null, 1);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self  IN OUT MultSum,
                                         value IN number) return number is
    begin
        if self.v_is_first = 1
        then
            self.v_sum := 1;
        end if;
        self.v_sum := self.v_sum * value;

        self.v_is_first := 0;

        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self        IN MultSum,
                                           returnValue OUT number,
                                           flags       IN number) return number is
    begin
        returnValue := self.v_sum;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT MultSum,
                                       ctx2 IN MultSum) return number is
    begin
        self.v_sum := self.v_sum * ctx2.v_sum;
        return ODCIConst.Success;
    end;
end;
/

-- Create actual function whith aggrigation property
CREATE or replace FUNCTION MultSum1 (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING MultSum;

-- Create test table
create table chk_for_shim
(a number, b number);

-- Create test data
insert into chk_for_shim values (1, 0);
insert into chk_for_shim values (2, 0);
insert into chk_for_shim values (3, 0);
insert into chk_for_shim values (4, 1);
insert into chk_for_shim values (5, 1);
insert into chk_for_shim values (6, 1);

-- use the function
SQL> select b, multsum1(a) from chk_for_shim group by b;

         B MULTSUM1(A)
---------- -----------
         0           6
         1         120