שימוש ב-Append ו-redo logs

05/04/2011/13 תגובות/ב- Oracle, Performance /מאת זהר אלקיים/זמן קריאה משוער: 3 דקות

הייתי היום בכנס ilOUG שיוחד לנושאי Data Warehouse ומכוון שהנושא קרוב לליבי (עדיין, כמעט שלוש וחצי שנים ב-DWH של פרטנר) הלכתי לשמוע מה יש בנושא ונהנתי מאוד.

במהלך ההרצאה האחרונה הסבירו החבר'ה מאורקל על שיטות טעינה למחסני נתונים. במהלך ההרצאה התפתחה שיחה קצרה ביני ובין עמיאל דיוויס בנושא מתי יש להינט 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 וכו'. העניין הוא שמכוון שתהליך הכתיבה של ה-redo הוא יחסית כבד, ישנם מקרים (כמו לדוגמה ב-Data warehouse) בהם ניתן לבחור לחסוך בביצועים, להמר קצת ולא להפעיל את ה-archive log. זה אומר שאם יש קריסה של בסיס הנתונים אז לא ניתן יהיה להשתחזר לנקודה האחרונה וכל עוד זו החלטה מודעת אין עם זה בעיה.

בנקודה הזו חשוב להבהיר – לא משנה באיזה שיטה נבחר, בכל מקרה בכל פעולה שנבצע יש כתיבה כלשהי ל-redo log (נחזור לנקודה הזו אחר כך).

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

כדי לבצע את זה, אנחנו צריכים לבצע שני דברים (יש עוד שיטות לעשות direct inserts אבל נשאיר את זה לפעם אחרת):

להגדיר את הטבלה ככזו שמאפשרת שלא לתעד את השינויים בה על ידי שינוי מבנה הטבלה ל-NoLogging.
להוסיף רמז (hint) לשליפה ובו אנחנו מציינים שהפקודה היא ישירה. ההינט הוא Append.

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

23:29:12 SQL&amp;gt; create table redo_test logging as select * From dba_objects where 1=0;

Table created.

Elapsed: 00:00:00.04
23:29:12 SQL&amp;gt;
23:29:12 SQL&amp;gt; insert into redo_test select a.* from dba_objects a, dba_objects b
23:29:12   2  where rownum &amp;lt;= 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

שימו לב לשורה 12 – הפקודה לקחה כ-5 שניות. שימו לב גם לשורה 20 – גודל ה-redo שהשתמשנו בו.

הנה דוגמה להכנסה לטבלה ב-direct:

 
23:30:46 SQL&amp;gt; alter table redo_Test nologging;

Table altered.

Elapsed: 00:00:00.00
23:30:46 SQL&amp;gt;
23:30:46 SQL&amp;gt; insert /*+ APPEND*/ into redo_test select a.* from dba_objects a, dba_objects b
23:30:46   2  where rownum &amp;lt;= 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

לעומת זאת, לאחר ששינינו את מבנה הטבלה והוספנו את ההינט, הפקודה לקחה רק שנייה וחצי (שורה 12) וכמות ה-redo הצטמצמה מאוד (שורה 20).
אוקיי, אז כמו שניתן לראות – בכל מקרה יש כתיב ל-redo אבל במקרה השני הכתיבה הייתה מצומצמת ביותר והפקודה לקחה הרבה פחות זמן.

נחזור לכנס: בכנס טען עמי שכל הסיפור של ה-append ו-nologging עובד רק אם אנחנו נמצאים ב-archive log mode ובכל מקרה אחר לא יהיה הבדל משמעותי. אני טענתי את הטענה ההפוכה – שהדבר מועיל גם כאשר משתמשים ב-no archive mode. הבדיקה שלי נעשתה בסביבה שבה אנחנו עובדים ב-no archive log.

 23:37:41 SQL&amp;gt; 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&amp;gt; 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 אם זו התנהגות חדשה של אורקל 11g או שפיספסתי משהו בבדיקה שלי.

עריכה: הנה התוצאה של הבדיקה שעשיתי היום, אם מישהו רואה באג אני אשמח (לא באמת אשמח) לשמוע:

23:47:59 SQL&amp;gt; alter table redo_test logging;

Table altered.

Elapsed: 00:00:00.06
23:48:06 SQL&amp;gt; select logging from user_tables where table_name='REDO_TEST';

LOG
---
YES

Elapsed: 00:00:00.01
23:48:11 SQL&amp;gt; set autot on stat time on timi on
23:48:19 SQL&amp;gt; truncate table redo_test;

Table truncated.

Elapsed: 00:00:00.08
23:48:36 SQL&amp;gt; insert /*+ APPEND*/ into redo_test select a.* from dba_objects a, dba_objects b
23:48:41   2  where rownum &amp;lt;= 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) פרסם כבר לפני 9 (!) שנים את הטבלה הבאה:

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

13 תגובות

עמיאל דייויס says:
06/04/2011 בשעה 01:43

זהר, תודה על הפרגון.
כמה הערות:
א. ההינט של append מיועד ל- direct insert. אורקל לא יתעלם מההינט הזה כאשר הטבלה ב- logging. על מנת להנות מפעולה של insert ב- nologging חייבים להשתמש בו, אבל זה לא אומר שלא ניתן להרויח מהיכולת המקורית של ההינט (כתיבה מעל ל- hwm) במקרה שהטבלה ב- logging.
ב. בקשר להבדלים המעניינים בין הטסט שלך לשלי: האם אורקל שיפרו את המנגנון בין הגרסאות? חפרתי קצת וגילתי שלפחות על פי התיעוד הרשמי, לא כך הדבר, ככה שהטסט שלך הניב תוצאות "משונות".
הנה ציטוט:
"The default logging attribute is LOGGING. However, if you have put the database in NOARCHIVELOG mode, by issuing ALTER DATABASE NOARCHIVELOG, then all operations that can be done without logging do not generate logs, regardless of the specified logging attribute."
מתוך:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel007.htm#sthref1251
בברכה, עמי דייויס.
נ"ב – ידעת שאני כותב ספר עב כרס רק על היכולות המופלאות של משפט ה- select?
יש לי כבר 586 עמודים…
הגב
Zohar Elkayam says:
06/04/2011 בשעה 07:21

היי עמי,
בשמחה ובכיף – אני תמיד שמח לפרגן.

א. ראה את הטבלה שהוספתי. היא נכונה כבר מגרסה 9i ואני אבדוק היום על גרסה 10 את ההתנהגות. ברור שמבחינת עבודה נכונה כדאי להמשיך לעבוד עם nologging על הטבלה אם רוצים לעשות לה טעינות ישירות כי אם יום אחד נבחר לעבור ל-archivelog mode אז רצוי שדברים לא יפסיקו לעבוד… 🙂

ב. למיטב ידיעתי אורקל לא שיפרו את המנגנון בין הגרסאות: לא מצאתי לזה שום עדות בדוקומנטציה או ב-MOS (מטהלינק).

אני אשמח להבין מה היה משונה בתוצאות שקיבלתי – פרט ל-append עם logging שלא הכרתי קודם, שאר התוצאות היו בדיוק מה שציפיתי להן… 🙂

זהר.
נ"ב
לא ידעתי שאתה כותב ספר – יש כמה כאלה בשוק הבעיה היא שהשפה מתעדכנת כל הזמן (אני אפרסם דוגמה מגניבה בעוד כמה ימים). במה אתה מתרכז בספר?
הגב
עמיאל דייויס says:
06/04/2011 בשעה 21:22

א. הנה דיון מעניין שהייתי שותף בו על אותו נושא בדיוק:
http://forums.oracle.com/forums/thread.jspa?messageID=3743885&#3743885
ככה שמבחינתי, מה שהיה "משונה" בתוצאות שלך זה העובדה הצלחת לחסוך ב- redo, במוד של noarchivelog.
בקשר לספר – אין כמה כאלה בשוק. אחד כמו שלי יש רק אחד…
1. הוא בעברית!
2. הוא מלא דוגמאות, ויש בו אך ורק צילום מסך אחד. כל שאר הדוגמאות זה cut and paste
מ- sql*plus
3. הוא כולל כל פיצ'ר שיש בפקודת ה- select:
שאילתות היררכיות, שכלולים ב- group by, שליפות pivot ו- unpivot פונקציות
אנליטיות, משפט ה- Model יכולות ב- Xml ועוד יכולות מרשימות ויפות שלא מוכרות
לציבור הרחב.
4. השפה מתעדכנת כל גרסה. הספר שלי תואם לגרסאות של 9 עד ל- 11.2 כולל.
5. אני יכול להתחייב על דבר אחד:
דברים שקודם לכן עשיתי בתוכנית שלמה, על כל התקורה הכרוכה בכך, אני עושה היום
בשליפה אחת יעילה, מהירה וקלה לתחזוקה.
בקרוב הוא יוצא לאור, בהוצאה הוותיקה והאיכותית "הוד-עמי"
עמיאל.
הגב
Zohar Elkayam says:
06/04/2011 בשעה 21:48

באופן מפתיע, התעסקתי עם כל מה שתיארת פה – pivot (יש פוסט נחמד בנושא וגם פירסמתי על זה בניוזלטר של אורקל) וביחד עם זה התעסקתי עם model, פונקציות אנליטיות זה האל"ף-בי"ת של מחסני נתונים, XML, שליפות הררכיות. לא חשבתי אף פעם שאלה דברים שלא מוכרים לציבור הרחב וספר כזה נשמע כמו ספר מעניין – אני אשמח לקרוא אותו כשהוא יצא (או לפני… 😉 )

אם ב-SQL עסקינן, אני מתכוון לכתוב פוסט על הפיצ'ר החדש של רקורסיה (אמיתית) של אורקל 11.2 בהמשך השבוע. stay tuned… 🙂
הגב
עמיאל דייויס says:
06/04/2011 בשעה 22:07

א.למה מפתיע? אתה מקצוען ואתה אמור להכיר את היכולות האלה. הציבור הרחב, הוא זה שלא
תמיד מצליח להתעדכן/למצוא את הידיים והרגלים בנבכי הסינטקס. dba שמתעסק במערכות
oltp לא תמיד יכול להקדיש את הזמן ללימוד מעמיק של היכולות האלה; dba של dwh, לא
יתעסק (לרוב) עם Xml ויכולות דומות והספר יכול לפתוח לו חלון (אפרורפו פונקציות אנליטיות) למגוון יכולות מרשימות.
ב. הנה חידת טריוויה – באיזה גרסה Sql-Server, תמך לראשונה בשאילתות היררכיות?
זה קל – ב- 2005.
מתי אורקל תמך לראשונה ביכולת הזאת (דרך connect by)?
ג. בקשר לספר – שלח לי מייל ל- sivadleima כרוכית hotmail.com יש לי הצעה מעניינת
בשבילך…
הגב
Zohar Elkayam says:
06/04/2011 בשעה 22:29

תראה, זה עניין של גישה לעבודה – אני אוהב להכיר מה שאני יכול ואם יש לי הזדמנות להתעסק עם משהו חדש אני תמיד לוקח אותה. יותר מזה, זה לפעמים ענייןשל מזל בחיים. לפני שהשתחררתי הלכתי לראיון עבודה ושם שאלו אותי על שליפות הררכיות. באופן משעשע, כל מה שעשיתי כל היום באותה תקופה היה לכתוב שליפות הררכיות לעץ היחידות של צה"ל.
הם קיבלו אותי לעבודה ואחרי זה סיפרו לי את הפתרונות האחרים שהציעו אנשים – זה נע בין פונקציות PL/SQL ועד פיתוח של type-ים בתכנות מונחה עצמים. לי היה פשוט מזל להכיר את זה קודם… 🙂

אני ממש גרוע בהיסטוריה. בסיס הנתונים המוקדם ביותר שהיה לי העונג (?) להתעסק איתו היה 7.3.4 ושם זה לא היה. ב-8.0 אני לא זוכר (זו גם הייתה תקופה קצרה של התעסקות בשבילי) וב-8i זה כבר היה בוודאות כי כתבתי שליפות כאלה על 8.1.7. אם ככה אני אהמר באופן רופף על גרסה 8 שיצאה ב-98 או 99.

לגבי המייל – שלחתי לך.
הגב
עמיאל דייויס says:
06/04/2011 בשעה 22:37

כבעל תואר שימושי בטירוף בהיסטוריה כללית (;
אני יענה לחידת הטריוויה של עצמי.
ובכן, תחזיק חזק, היכולת לבצע connect by, הופיעה בגרסה 2 של אורקל,
כלומר 1979.
נדמה לי שלא כתבת שליפות אז…
הגב
Zohar Elkayam says:
06/04/2011 בשעה 23:06

ב-1979 אפילו לא נולדתי… 🙂
הגב
Ofir Manor says:
08/04/2011 בשעה 15:30

איזה דיון מעניו! סחטיין לשתיכם…
זוהר – רציתי לחדד נקודה שכבר עלתה – ההבדל בן no logging ל-direct insert.
בהכנסה רגילה אורקל מחפש מקום פנוי בבלוקים הקיימים, טוען את הבלוק הנבחר ל-SGA, מעדכן אותו שם ונותן ל-DBWR לכתוב אותו. ב-direct insert אורקל מקצה שטח חדש בסוף הטבלה וכותב לשם בלוקים מלאים ישירות, לא דרך ה-SGA.
בהחלט אפשר לעבוד direct insert ועדיין לעבוד ב-logging, כלומר, שכל ההכנסה תכתב ללוג השינויים. למשל, זה רלוונטי מאוד כמו שהזכרת אם עובדים עם Data Guard.
האמת שחידשת לי – לא זכרתי שההתנהגות הזו תלויה ב-archivelog mode…
הגב
ליאור קרן says:
05/07/2011 בשעה 15:19

מעניין ומוסיף, גם ל DBA שמכיר את העניין,
הערה אחת – נדמה לי שזה צריך להיות alter database force logging ולא alter system
הגב
- Zohar Elkayam says:
  05/07/2011 בשעה 16:08
  
  צודק. תיקנתי – תודה על ההערה.
  הגב
Eran says:
25/02/2012 בשעה 04:34

You are right. The main problem is the vast majority of the Oracle DBAs are OLTP DBAs and they don't have the mental ability to turn the redo log off. I learned it in 1998 at my first DWH project and since then I have a consistent battle with the DBAs in any DWH project in the USA and in Israel
הגב

Trackbacks & Pingbacks

שימוש ב-Append ו-Redo Logs - Israel Database Portal הגיב:

24/06/2012 בשעה 09:37

[…] לרשומה המקורית ולרשומות נוספות של זהר ניתן להיכנס לבלוג שלו. […]

הגב

השאירו תגובה

Want to join the discussion?
Feel free to contribute!

השאר תגובהלבטל

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

שימוש ב-Append ו-redo logs

אהבתי

אולי יעניין אותך לקרוא גם את הפוסטים הבאים:

Trackbacks & Pingbacks

השאירו תגובה

השאר תגובהלבטל