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

כידוע, הסטטיסטיקות נאספות על ידי שימוש ב-dbms_stats ומכילות מידע הן ברמת טבלה – מספר הרשומות, גודל רשומה ממוצע, מספר בלוקים,  והן ברמת עמודה – מספר הערכים היחודים (NDV), מספר ערכי NULL, התפלגות הנתונים (היסטוגרמות, הערך הנמוך והגבוה) וכו'. האופטימייזר משתמש במידע הזה כדי לבחור כיצד להריץ את ה-SQL-ים שלנו – חישוב העלות של כל אלטרנטיבה על פי הערכה של כמות העבודה הנדרשת. כמה חידושים:

שיפור משמעותי בביצועים ובדיוק (NDV) עבור DBMS_STATS.AUTO_SAMPLE_SIZE
בגרסת 11g איסוף סטטיסטיקות עם AUTO_SAMPLE_SIZE היא האופציה המומלצת. אנחנו מקבלים ביצועים השקולים לדגימה של בין 1-10 אחוז מהטבלה עם דיוק בהערכת ה-NDV שקרוב מאוד לערך האמיתי המתקבל מניתוח 100 אחוז מהטבלה.

גרג רהן מנתח בבלוג שלו איסוף סטטיסטיקות בעזרת estimate 10%, compute ו-auto_sample_size בגרסת 10.2.0.3 מול 11.1.0.5, עבור מידע skewed (בעל התפלגות מאוד לא אחידה). הוא מראה שיפור בכל המקרים בין 10g ל-11g ובעיקר שיפור מדהים בזמן הריצה ובדיוק (NDV) עבור AUTO_SAMPLE_SIZE בין הגרסאות.

טיפול חכם בסטטיסטיקות של partition חדש

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

  • לא נאספו עדיין סטטיסטיקות עבור ה-partition הזה. למשל, זהו partition יומי ונאסוף עליו סטטיסטיקות רק בלילה. אורקל צריך לנחש את התפלגות וכמות הנתונים או לדגום בזמן ה-parse נתונים בסיסיים (Dynamic Sampling).
  • נאספו סטטיסטיקות אך התאריך שלנו מחוץ לטווח הערכים הידוע. למשל, זהו partition חודשי, אספנו עליו סטטיסטיקה אתמול בערב או בשבת והשליפה מתבצעת על נתונים של היום הזה. במקרה הזה אורקל ינחש כמה ערכים יחזרו – ככל שהערך (היום) רחוק יותר מטווח הערכים הידוע, כך האופטימייזר יעריך שיחזרו פחות רשומות.

בגרסת 11g (וגם ב-10.2.0.4) אורקל מציע פיתרון אלגנטי ופשוט לבעיה. כאשר יוצרים partition חדש, ניתן לבקש מאורקל להעתיק את הסטטיסטיקה של partition קיים (שהוא לרוב הקודם) על ידי  DBMS_STATS.COPY_TABLE_STATS. אורקל ישתמש בסטטיסטיקה של ה-partition המועתק כבסיס (זה ניחוש טוב) ויתקן אוטומטית את טווחי הערכים הצפויים של שדה ה-partition שלנו! אפשר אפילו לציין אופציונלית מכפלה כלשהי לגודל הצפוי של ה-partition.
רק כאשר נסיים לטעון נתונים ל-partition, נאסוף עליו סטטיסטיקות.

חישוב מהיר של סטטיסטיקה גלובלית מסטטיסטיקות של partitions

כאשר אנחנו משתמשים ב-partitioning, אורקל אוסף סטטיסטיקה הן ברמה של כל partition בודד והן ברמה גלובלית (על כל הטבלה). האופטימייזר משתמש בסטטיסטיקות הגלובליות כאשר הוא לא יודע בזמן ה-parse לאיזה partition ניגש (למשל, כאשר משתמשים ב-bind variables) או כאשר ניגשים לטווח של partitions (אז הוא משלב את שני סוגי הסטטיסיטיקות).

לפני גרסת 11g, כדי לחשב סטטיסטיקה גלובלית על טבלה,אורקל היה צריך לסרוק את כל ה-partitions שלה, כי לא ניתן למזג באופן טריוויאלי NDV ברמת partition ל-NDV ברמה גלובלית. למה? הנה דוגמא. אם בכל יום בחודש יוני השנה ביקרו באתר שלכם אלף משתמשים יחודיים, כמה משתמשים יחודיים ביקרו באתר שלכם במהלך החודש? התשובה היא שקשה להעריך – זהו מספר כלשהו בין אלף (אם בכל יום הגיעו בדיוק אותם משתמשים) לשלושים אלף (אם בכל יום הגיעו אלף משתמשים אחרים).

בגרסת 11g אורקל משתמשת באלגוריתם מאוד מתוחכם השומר "חתימות" (synopsis) של התפלגות הערכים בכל עמודה בכל partition (למתעניינים באלגוריתם – מאמר טכני מאוד בנושא). הדבר מאפשר לאורקל למזג את כלל החתימות ולקבל הערכה מדוייקת מאוד של ה-NDV הגלובלי של הטבלה. כלומר, כאשר נאסוף סטטיסטיקה על partition בודד אורקל יכול להעריך ולחשב מחדש את הסטטיסטיקה הגלובלית בצורה מדוייקת ומאוד מהירה, ללא כל צורך ב-full table scan! הדבר מוביל לחיסכון משמעותי של משך איסוף הסטטיסטיקה ומעלה מאוד את הדיוק שלה (לעומת estimate של אחוז קטן של הטבלה).

כדי להשתמש ביכולת החדשה, פשוט צריך לציין זאת בהעדפות שלנו ברמת הטבלה (DBMS_STATS.SET_TABLE_PREFS) או הסכימה: INCREMENTAL = TURE. בנוסף, דרישות הקדם הם להשתמש ב-ESTIMATE_PERCENT==> AUTO_SAMPLE_SIZE ו-GRANUALITY==> AUTO. למהדרין, מומלץ להתקין גם את פאטצ' 8558256 (מעל 11.1.0.7).

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

עריכה – כתבתי פוסט המשך בנושא כאן

אהבתם את הפוסט? הרשמו לעדכונים במייל!