שימוש ב-Append ו-Redo Logs

לפני כמה חודשים ביקרתי בכנס בסיסי נתונים של 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 אבל נשאיר את זה לפעם אחרת):

1. להגדיר את הטבלה ככזו שמאפשרת שלא לתעד את השינויים בה על ידי שינוי מבנה הטבלה ל-NoLogging.
2. להוסיף רמז (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

עוד כמה אזהרות והערות לפני שאני עובר לחלק הבא:

1. הוזכר כבר כמה פעמים: כאשר טוענים direct לא ניתן לשחזר את הטבלה במקרה של קריסה של בסיס הנתונים.
2. הינט של append מכריח את אורקל להתחיל את הכתיבה בבלוק חדש ונקי. שימוש מוגזם בהינט הזה עלול להביא להתנפחות משמעותית של הטבלה.
3. אפשר להגיד לבסיס הנתונים שטעינות ישירות זה טוב ויפה, אבל אנחנו לא מסכימים לעקוף את מנגנון ה-redo. ניתן לבצע את זה ברמת כל בסיס הנתונים (עם הפקודה alter database force logging) או ברמת tablespace בודד.
4. שימוש בהינט עלול לגרום לבעיות במערכות שמשתמשות ב-DataGuard או ב-CDC מכוון שהן מתבססות על מה שיש בקבצי ה-archive ובמהלך הזה אנחנו בעצם מונעים את הכתיבה ל-archive. נדמה לי שגם ב-GoldenGate יכולה להיות בעיה אבל אל תתפסו אותי במילה כי יכול להיות ששם אנחנו חייבים להכריח את הכתיבה של הלוגים.
5. שימוש בהינט נועל את כל הטבלה ומכריח אותנו לבצע commit בסיום הפקודה לפני שניתן יהיה לשלוף את הנתונים מהטבלה. הדבר מונע מאיתנו שימוש פרללי בטבלה (כלומר לא ניתן לבצע שתי טעינות ישירות לתוך אותה טבלה בו זמנית.
6. לפני גרסה 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 ולרשומות נוספות של זהר ניתן להיכנס לבלוג שלו.

The following two tabs change content below.

• Bio
• Latest Posts

זהר אלקיים

CTO at Brillix

זהר אלקיים הוא Oracle DBA מאז 1998 ויועץ בכיר מאז 2007 ובעל תואר Oracle ACE Associate. הוא מתמחה בתשתיות בסיסי נתונים, ארכיטקטורה ושיפור ביצועים. זהר הוא גם מדריך, מרצה בכנסים ובלוגר באתר www.realdbamagic.com מאז 2010. כיום הוא משמש כיום כ-CTO בחברת בריליקס ומנהל את האתר ilDBA.

Latest posts by זהר אלקיים (see all)

• הזמנה ל-ILOUG Tech Days 2018 - 18/12/2017
• איך אורקל מצליחים לקרוא 2.5 מיליארד רשומות בשנייה - 12/06/2014
• בעיה מוזרה של תזמוני AWR (וגם איך מבצעים שינוי Timezone ב-RAC) - 10/06/2014