לפני כמה חודשים ביקרתי בכנס בסיסי נתונים של ilOUG שהוקדש לנושאי Data Warehouse. במהלך אחת ההרצאות הסבירו החבר'ה מאורקל על שיטות טעינה למחסני נתונים. בעקבות ההרצאה התפתח דיון ביני לבין עמיאל דיוויס בנושא מתי יש להינט Append משמעות והאם זה עוזר רק בסביבה של Archive log mode או לא.
למי שמתעסק בטעינות של מליוני רשומות, הנושא הזה מאוד מעניין אבל אם הגעתם עד כאן ועדיין לא הבנתם כלום, אולי הגיע הזמן להסביר קצת במה מדובר.
לאורקל יש מנגנון שמאפשר לו להשתחזר במקרה של קריסה. למנגנון הזה קוראים Redo log והוא בעצם שומר את הפעולות שבוצעו לפי סדר הביצוע שלהן ואת המידע שהשתמשנו בו כדי להריץ מחדש את הפעולות במקרה של ירידה לא מסודרת של בסיס הנתונים (ומכאן השם redo – לעשות שוב). אורקל לא יחודית בעניין הזה – כמעט כל בסיס נתונים רציונלי מחזיק שיטה כזו או אחרת של השתחזרות מקריסה.
באופן עקרוני, כאשר ה-Redo log מתמלא, בסיס הנתונים יכול לעשות אחד משני דברים: הוא יכול לכתוב את הקובץ הזה לדיסק או לא לעשות כלום. אם הוא כותב את הקובץ, אז קוראים למצב הזה archive log mode ואם לא, אז no archive log mode. את הדבר הזה מגדירים מראש לבסיס הנתונים ובאופן עקרוני מרבית בסיסי הנתונים של אורקל אמורים לעבוד ב-archive log mode. הכתיבה לקבצי archive מאפשרת השתחזרות מקריסה, אבל גם שימושים שונים כמו ב-standby database/DataGuard, Change data capture, Golden Gate וכו'. העניין הוא שמכוון שתהליך הכתיבה של ה-redo הוא יחסית כבד, ישנם מקרים (לדוגמה ב-Data warehouse) בהם ניתן לבחור לחסוך בביצועים, להמר קצת ולא להפעיל את ה-archive log. זה אומר שאם יש קריסה של בסיס הנתונים אז לא ניתן יהיה להשתחזר לנקודה האחרונה וכל עוד זו החלטה מודעת אין עם זה בעיה.
בנקודה הזו חשוב להבהיר – לא משנה באיזה שיטה נבחר, בכל מקרה בכל פעולה יש כתיבה כלשהי לקובץ ה-redo log (נחזור לנקודה הזו אחר כך).
אוקיי, נחזור לענייננו. ברירת המחדל של אורקל הוא להשתמש במנגנון ה-redo גם אם אין כתיבה של Archive-ים בסופו של דבר. כאשר אנחנו רוצים לטעון טבלאות גדולות של מליוני רשומות בזמן קצר, אנחנו יכולים להגיד לאורקל שאנחנו לא מעוניינים שהוא יכתוב את כל הפעולה ל-redo logs ובעצם נעשה את הפעולה בצורה של direct insert – נעקוף את המנגנון ה-redo במחיר של חוסר יכולת לשחזר את הפעולה הזו.
כדי לבצע את זה, אנחנו צריכים לבצע שני דברים (יש עוד שיטות לעשות direct inserts אבל נשאיר את זה לפעם אחרת):
- להגדיר את הטבלה ככזו שמאפשרת שלא לתעד את השינויים בה על ידי שינוי מבנה הטבלה ל-NoLogging.
- להוסיף רמז (hint) לשליפה ובו אנחנו מציינים שהפקודה היא ישירה. ההינט הוא Append.
כאשר שני התנאים מתמלאים אז אורקל מצמצם משמעותית את הכתיבה ל-redo והפקודה מסתיימת יותר מהר.
הנה דוגמה להכנסה רגילה לטבלה:
23:29:12 SQL> create table redo_test logging as select * 23:29:12 2 From dba_objects where 1=0; Table created. Elapsed: 00:00:00.04 23:29:12 SQL> 23:29:12 SQL> insert into redo_test select a.* 23:29:12 2 from dba_objects a, dba_objects b 23:29:12 3 where rownum <= 1000000; 1000000 rows created. Elapsed: 00:00:04.51 Statistics ---------------------------------------------------------- 4255 recursive calls 103321 db block gets 26319 consistent gets 3 physical reads 94192108 redo size 685 bytes sent via SQL*Net to client 666 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 13 sorts (memory) 0 sorts (disk) 1000000 rows processed
שימו לב לשורה 14 – הפקודה לקחה כ-5 שניות. שימו לב גם לשורה 22 – גודל ה-redo שהשתמשנו בו.
הנה דוגמה להכנסה לטבלה ב-direct:
23:30:46 SQL> alter table redo_Test nologging; Table altered. Elapsed: 00:00:00.00 23:30:46 SQL> 23:30:46 SQL> insert /*+ APPEND*/ into redo_test select a.* 23:30:46 2 from dba_objects a, dba_objects b 23:30:46 3 where rownum <= 1000000; 1000000 rows created. Elapsed: 00:00:01.67 Statistics ---------------------------------------------------------- 2917 recursive calls 13991 db block gets 2657 consistent gets 2 physical reads 225936 redo size 668 bytes sent via SQL*Net to client 679 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 1000000 rows processed
לעומת זאת, לאחר ששינינו את מבנה הטבלה והוספנו את ההינט, הפקודה לקחה רק שנייה וחצי (שורה 13) וכמות ה-redo הצטמצמה מאוד (שורה 21).
כמו שניתן לראות – בכל מקרה יש כתיבה ל-redo אבל במקרה השני הכתיבה הייתה מצומצמת ביותר והפקודה לקחה הרבה פחות זמן.
נחזור לכנס: בכנס טען עמיאל שכל הסיפור של ה-append ו-nologging עובד רק אם אנחנו נמצאים ב-archive log mode ובכל מקרה אחר לא יהיה הבדל משמעותי. הבדיקה שלי נעשתה בסביבה שבה אנחנו עובדים ב-no archive log.
23:37:41 SQL> select * From v$version; BANNER ------------------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Elapsed: 00:00:00.07 23:37:59 SQL> select log_mode from v$database; LOG_MODE ------------ NOARCHIVELOG
עוד כמה אזהרות והערות לפני שאני עובר לחלק הבא:
- הוזכר כבר כמה פעמים: כאשר טוענים direct לא ניתן לשחזר את הטבלה במקרה של קריסה של בסיס הנתונים.
- הינט של append מכריח את אורקל להתחיל את הכתיבה בבלוק חדש ונקי. שימוש מוגזם בהינט הזה עלול להביא להתנפחות משמעותית של הטבלה.
- אפשר להגיד לבסיס הנתונים שטעינות ישירות זה טוב ויפה, אבל אנחנו לא מסכימים לעקוף את מנגנון ה-redo. ניתן לבצע את זה ברמת כל בסיס הנתונים (עם הפקודה alter database force logging) או ברמת tablespace בודד.
- שימוש בהינט עלול לגרום לבעיות במערכות שמשתמשות ב-DataGuard או ב-CDC מכוון שהן מתבססות על מה שיש בקבצי ה-archive ובמהלך הזה אנחנו בעצם מונעים את הכתיבה ל-archive. נדמה לי שגם ב-GoldenGate יכולה להיות בעיה אבל אל תתפסו אותי במילה כי יכול להיות ששם אנחנו חייבים להכריח את הכתיבה של הלוגים.
- שימוש בהינט נועל את כל הטבלה ומכריח אותנו לבצע commit בסיום הפקודה לפני שניתן יהיה לשלוף את הנתונים מהטבלה. הדבר מונע מאיתנו שימוש פרללי בטבלה (כלומר לא ניתן לבצע שתי טעינות ישירות לתוך אותה טבלה בו זמנית.
- לפני גרסה 11.2 אי אפשר להשתמש בהינט של append אם מציינים values בפקודה. בגרסה 11.2 התווסף הינט נוסף append_values שמאפשר טעינה ישירה של רשומה בודדת. אני אכתוב על זה יותר בפעם אחרת.
משהו מעניין שגיליתי תוך כדי הבדיקה היה שכאשר ביצעתי insert append בזמן שהטבלה הייתה ב-logging, הפקודה רצה יותר מהר וביצעה פחות redo מאשר היא הייתה בלי ה-append. זה הפתיע אותי כי למיטב ידיעתי כאשר הטבלה ב-logging, אורקל אמור להתעלם לגמרי מההינט ויעידו עשרות תוצאות של "ההינט של append לא עובד לי" בגוגל שזה בדרך כלל המצב.
גם מבדיקה שערכתי בסביבה עם גרסה 10g התוצאה של הבדיקה הייתה דומה:
23:47:59 SQL> alter table redo_test logging; Table altered. Elapsed: 00:00:00.06 23:48:06 SQL> select logging from user_tables where table_name='REDO_TEST'; LOG --- YES Elapsed: 00:00:00.01 23:48:11 SQL> set autot on stat time on timi on 23:48:19 SQL> truncate table redo_test; Table truncated. Elapsed: 00:00:00.08 23:48:36 SQL> insert /*+ APPEND*/ into redo_test select a.* 23:48:41 2 from dba_objects a, dba_objects b 23:48:41 3 where rownum <= 1000000; 1000000 rows created. Elapsed: 00:00:01.72 Statistics ---------------------------------------------------------- 2756 recursive calls 13993 db block gets 2643 consistent gets 2 physical reads 226004 redo size 669 bytes sent via SQL*Net to client 679 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed
לסיום, אנקדוטה: תום קייט (Tom Kyte) פרסם כבר ב-2001 (!) את הטבלה המסכמת הבאה בנושא. טוב לראות שהיא עדיין תקפה:
Table Mode Insert Mode ArchiveLog mode result ----------- ------------- ----------------- ---------- LOGGING APPEND ARCHIVE LOG redo generated NOLOGGING APPEND ARCHIVE LOG no redo LOGGING no append ARCHIVE LOG redo generated NOLOGGING no append ARCHIVE LOG redo generated LOGGING APPEND noarchive log mode no redo NOLOGGING APPEND noarchive log mode no redo LOGGING no append noarchive log mode redo generated NOLOGGING no append noarchive log mode redo generated
לרשומה המקורית שימוש ב-Append ו-redo logs ולרשומות נוספות של זהר ניתן להיכנס לבלוג שלו.
זהר אלקיים
Latest posts by זהר אלקיים (see all)
- הזמנה ל-ILOUG Tech Days 2018 - 18/12/2017
- איך אורקל מצליחים לקרוא 2.5 מיליארד רשומות בשנייה - 12/06/2014
- בעיה מוזרה של תזמוני AWR (וגם איך מבצעים שינוי Timezone ב-RAC) - 10/06/2014
7 תגובות ל- “שימוש ב-Append ו-Redo Logs”
חשוב לציין שתחזוקה של אינדקסים לא יכולה להתבצע ב- nologgin. מה שאומר שאם יש אינדקס על הטבלה נראה עדיין לא מעט redo size (מהאינדקס) גם אם הטבלה היא ב- nologging ומשתמשים ב- append. לעומת זאת, בניה של אינדקס יכולה להתבצע ב- nologging.
אם אפשר, עדיף לפעמים לבטל את האינדקס, לבצע את הטעינה ואז לבנות את האינדקס מחדש עם nologging.
Zohar Elkayam liked this on Facebook.
Shelly Denish liked this on Facebook.
היי זוהר
בנוגע להערה של שימוש ב append עם data guard, האם יש איזשהו מסמך רשמי שאומר שהדבר יכול להיות בעייתי?
הרי במצב כזה ה DB הוא ב Force logging mode.
תודה,
מוטי
היי זוהר,
צריך להוסיף, למרות שזה פחות קשור למאמר, שההכנסה בDIRECT (וספציפית APPEND) גם לא אמורה לייצר UNDO..
בנושא הappend בlogging, הטבלה של טום קייט די מסבירה לך את העניין – כשאתה בNO ARCHIVE LOG MOD, ועם APPEND HINT – > no redo, וכמו שאמרת, הניסויים שעשית הם בDB בNO ARCHIVE LOG MODE..
אהה,
ועוד דבר, למרות שאני לא בטוח ב100%, שרק בAPPEND, הCOMPRESSION באמת עובד.. (הספק אם זה רק בAPPEND או בBULK בכלל..)
תודה על התגובות.
1.לגבי ההערה על ה-UNDO -זו אבחנה יפה ומהצד גם יכולה להראות נכונה אבל זה לא מדוייק מבחינת קשר של סיבה ותוצאה.
ההינט של APPEND לא מבטל יצירה של undo בהגדרה – הצורה שבה הוא עובד גורמת לזה כתוצאת לוואי של הפעילות המתוכנת שלו. הכנסה ב-direct מכניסה נתונים רק לבלוקים חדשים וזה אומר שלא צריך לשמור של עותקים ישנים של הבלוקים לטובת הבטחת הנכונות לתחילת השליפה של שליפות אחרות. הבלוקים לא יראו על ידי שליפות אחרות כי הן עדיין לא בוצע commit על הבלוקים החדשים ורק נתונים של פקודת ה-append נמצאת בבלוקים האלה… 🙂
2. עמיאל טען של- Nologging ו-append אין משמעות ב-noarchivelog ואני הראתי שזה לא נכון. הטבלה של טום קייט (שנוספה אחרי הבדיקה שלי, כשכבר כתבתי את הפוסט) איששה את הטענה שלי באופן סופי.. 🙂
3. Compression לפני גרסה 11 עבד רק בטעינה של בלוקים מלאים (כלומר בביצוע של פעולות bulk insert). זה לא חייב היה להיות direct אבל זה כן היה צריך למלא בלוקים.
החל מגרסה 11 יש את הפיצ'ר המגניב של dml compression (ידוע גם בשם advanced compression) שמאפשר compression של בלוגים גם על פעולות dml בודדות – וזאת כאשר הבלוק מתמלא.
זהר
השאר תגובה: