גם האתגר החודש הוא בנושא אורקל, והפעם, התנהגות מוזרה של full table scan.
כשמבצעים full table scan, אורקל אינו מעלה את כל הטבלה לזכרון ובכך דורס את מה שיש כבר ב- buffer cache, אלא מעלה כל פעם מספר בלוקים ומיד מוציא אותם מהזכרון ומעלה במקומם בלוקים אחרים. אם אתם לא מכירים את המנגנון, אתם מוזמנים לקרוא עליו במאמר שפורסם לאחרונה באתר:
http://www.ildba.co.il/magazine/buffer-cache-and-full-scans.
כפי שמתואר במאמר, כדי לבקש מאורקל בכל זאת להעלות את הטבלה כולה לזכרון בעת full table scan נשתמש ב- cache hint או בהגדרת הטבלה כ-cached.
אולם, כאשר עובדים עם טבלאות גדולות, זה לא תמיד עובד (בגרסאות 11g לפחות). גם כאשר מגדירים cache, עדיין אורקל מבצע קריאות מהדיסק, גם כאשר הטבלה כולה יכולה להיכנס בזכרון.
זה מה שהיינו רוצים לראות (פעם ראשונה קריאה מהדיסק ופעם שניה לא):
SQL> select /*+ cache */ sum(num1) from tab1; Elapsed: 00:00:15.26 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63922 consistent gets 63901 physical reads <------- 0 redo size 428 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / Elapsed: 00:00:00.62 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63922 consistent gets 0 physical reads <------- 0 redo size 428 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
אבל בפועל, זה מה שקורה (בכל פעם יש קריאות מהדיסק):
SQL> select /*+ cache */ sum(num1) from tab1; Elapsed: 00:00:15.45 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63911 consistent gets 63901 physical reads <------- 0 redo size 428 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / Elapsed: 00:00:14.54 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63911 consistent gets 63901 physical reads <------- 0 redo size 428 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
למה זה קורה ואיך פותרים את זה?
כדי להשתתף באתגר יש להירשם לאתר ולשלוח את הפתרונות ל-ildba@brillix.co.il
שיהיה בהצלחה!
צוות אתר ilDBA.
——————————————————————————————–
עידכון 1.6.2001
מסתבר שאתגר מאי היה רק נראה פשוט ובסיסי.
מאחורי האתגר התמים עמד צורך בהבנת אופן פעולת ה-Buffer cache ובנוסף ידע על מניפולציות מתקדמות שונות בעזרת פרמטרים חבויים במערכת.
אבל, 2 חברי קהילה – יובל ברוך ואריק פורת הפתיעו אותנו, ונתנו לנו תשובה נכונה, למרות שהתשובה שלהם שונה מהתשובה שלנו, הפתרון שלהם עובד ופותר את הבעיה שהצגנו.
כל הכבוד לשניהם, אך יכול להיות רק זוכה אחד ולכן הזוכה של אתגר מאי הוא יובל ברוך.
מזל טוב לזוכה, אימייל עם פרטים על הזכייה יישלח בשבוע הקרוב.
ראשית, נתחיל עם תשובת המערכת.
כבר תיארנו לכם במאמר של לירון אמיצי על מנגנון ה-buffer cache ואיך הוא עובד ביחס ל-full scans (בקרוב יהיה מאמר נוסף בסדרה שיבאר עוד כמה יסודות בנוגע לרכיב זה).
הלוגיקה שמחליטה איך ה-DB יתמודד בהרצת שאילתה המבצעת FTS מושפעת על ידי פרמטר חבוי בשם: _small_table_threshold.
אם כמות הבלוקים שאמורים להיקרא בשאילתה גבוהה מהערך של הפרמטר, אורקל יבצע direct path read, כלומר קריאה של בלוקים מהדיסק ישירות אל ה- PGA. אם כמות הבלוקים שאמורה להיקרא בשאילתה שווה או נמוכה מהכתוב בערך הפרמטר, Oracle יבצע את הקריאה דרך ה-buffer cache ולא באמצאות direct read.
בנוסף, לפי הנתונים הרשמיים של אורקל, ההחלטה של האם לקרוא את הבלוקים דרך ה-buffer cache או לבצע direct read יכולה להתבצע גם על בסיס חישוב פנימי ה-DB. אם כמות הבלוקים הנקראים גדולה מ2% מגודלו של ה-buffer cache בקירוב, יתבצע direct read ויהיו Physical reads בכל פעם שהשאילתה תרוץ.
דוגמא לפיתרון #1: קובץ מצורף _small_table_threshold
בנוסף לאמור לעיל, נעבור לתשובתם של חברי הקהילה.
כאשר מתבצע FTS, מתבצע חישוב פנימי לפי הסטטיסטיקות ואז האופטימייזר מחליט אם לבצע direct path read או conventional read דרך ה-Buffer cache.
כאשר הטבלה גדולה, במקרים מסויימים ההחלטה יכולה להיות לטובת ה-direct path read והנתונים מגיעים ישר מהדיסק אל ה- PGA, ללא מעבר דרך ה-Buffer cache.
ניתן לבטל התנהגות זאת על ידי שימוש ב-event:
Alter session set events '10949 trace name context forever, level 1';
דוגמא לפיתרון #2: קובץ מצורף 10949_trace
פתרונות נוספים שקיבלנו
תשובה נפוצה שקיבלנו במייל היתה להשתמש ב-BUFFER_POOL KEEP. תשובה זו אינה עונה על הבעיה שהצגנו.
מנגנון ה-BUFFER_POOL KEEP עובד בדיוק באותה צורה כמו ה-BUFFER_POOL DEFAULT מבחינת LRU & MRU, שיקולים של direct path read וכל מנגנון אחר שקשור לניהול ה- buffer cache.
לכן, גם אם היינו מגדירים ערך בפרמטר DB_KEEP_CACHE_SIZE ומגדירים את הטבלה לעלות ל- BUFFER_POOL KEEP, עדיין הקריאה מהטבלה הייתה מתבצעת כ-direct path read ומבצעת Physical reads בכל הרצה.
לפרטים נוספים על הפרמטר _small_table_threashold, ניתן לפנות ל-Metalink note 787373.1.
בנוסף, מצורפים לינקים מעניינים נוספים בנושא:
http://jonathanlewis.wordpress.com/2011/03/24/small-tables/
http://dioncho.wordpress.com/2009/04/22/strong-doubt-on-small-table_small_table_threshold/
http://www.dbacomp.com.br/blog/?p=73
לפרטים נוספים על 10949 Event:
אנחנו רוצים לציין מספר תשובות שניתנו על ידי חברי הקהילה, מכיוון שבהחלט ניתן ללמוד מהם ושמחנו לראות תשובות רבות וכיוונים כה מגוונים ומעניינים:
יונתן יעקובסון תיאר לנו פי’צר נוסף בגרסת 11 – direct path read.
האופטימייזר יכול להחליט לדלג על הbuffer cache ולקרוא את הבלוקים ישירות לזיכרון. אפשר למנוע זאת על ידי שינוי הפרמטר החבוי _SERIAL_DIRECT_READ ולתת לו את הערך FALSE.
בדקנו את אפשרות זו במספר דרכים (ביצוע שליפות על טבלאות בגדלים שונים ביחס לגודל ה-buffer cache ) ובכל הרצה בוצעו Physical reads .
עם זאת, מצאנו לינק מעניין שמציג ניסוי בנוגע לפרמטר זה ומציג את מקרי הקצה בהם השמת ערך flase בפרמטר יכול לשנות את אופי קריאת הבלוקים:
http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/
(הניסוי מוצג בהערה מספר 18 של הפוסט בלינק לעיל.)
תשובה נוספת של יונתן, תיארה לנו את הפיצ’ר result caching.
זהו פתרון שדורש הגדרת שטח נוסף בזכרון (result_cache_max_size) והגדרת הפרמטר result_cache_mode לערך MANUAL/FORCE.
תשובה זו לא מתאימה לאתגר זה משום ש:result cache מבצע cache לתוצאות של שאילתא ולא לבלוקים של טבלה, במקרה שלנו דיברנו על בלוקים של טבלה שיוצאים מה- buffer cache ולכן לבצע cache לתוצאה של שאילתא לא רלוונטי.
בנוסף רודי שוורץ סיפר שכל מה שעלינו לעשות, זה להגדיל את ה-SGA שלנו. הנ”ל נכון בסופו של דבר, כי אם ה-buffer cache יהיה גדול יותר, אז יותר בלוקים יוכלו לשכון בו, אבל לא תמיד ניתן לעשות את זה וזה לא באמת פתרון של הבעיה אלא דרך שמאפשרת לנו במקרים מסויימים לעקוף אותה.
אנחנו רוצים לברך שוב את חברי הקהילה שניסו וטרחו וחיפשו את התשובות.
שמחנו שאנשים רבים נענו לאתגר.
ניפגש באתגר הבא, שאוטוטו יפורסם.
מערכת ilDBA
שלום לכולם וברוכים הבאים לאתר קהילת בסיסי הנתונים של ישראל.
באתר תוכלו למצוא מאמרים וסרטונים טכנולוגיים רבים הקשורים לבסיסי הנתונים המובילים בישראל - Oracle, SQL Server, MySQL ועוד.