אינדקסים באורקל - חלק 3 - ilDBA Portal

אינדקסים באורקל – חלק 3

02/04/2012 | פורסם על ידי

תזכורת של המאמרים הקודמים: חלק 1, חלק 2

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

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

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

מה זה rebuild index?

ברור לכולנו שהמבנה של אינדקס משתנה כאשר הנתונים בטבלה משתנים. כל רשומה שנכנסת לטבלה צריכה להיכנס גם לאינדקס (בהנחה שהערך המאונדקס הוא לא NULL) וכך גם מחיקות מהטבלה. מעבר לשינויים הטבעיים האלה, יש שתי פקודות שמאפשרות לעשות פעולות תחזוקה על האינדקס: rebuild ו- coalesce.

  • Coalesce – עובר על האינדקס הקיים ומבצע defrag לעלים. לאחר coalesce עומק העץ לא ישתנה, אך מספר העלים יכול לקטון.
  • Rebuild – מייצר אינדקס חדש לחלוטין. פעולת ה- rebuild דורשת מקום פנוי (כדי ליצור את העץ החדש) ולאחריה האינדקס יכול להיות בעל מספר רמות קטן יותר מהאינדקס המקורי.

מי שמעוניין, יכול לקרוא קצת על ההבדלים בין שתי הפקודות בתיעוד בלינק הבא: http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes002.htm#ADMIN13067

איך אינדקס גדל וקטן?

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

כדי לבחון את מבנה האינדקס נשתמש בטבלה index_stats. למי שלא מכיר, בעזרת הפקודה analyze index … validate structure ניתן לקבל מידע מפורט על מבנה האינדקס. שימו לב שלמרות שמדובר בפקודה analyze אין פה איסוף סטטיסטיקות שישפיעו על ה- optimizer אלא רק הכנסת מידע על האינדקס לטבלה index_stats. השדות שיעניינו אותנו הם:

  • HEIGHT – עומק העץ (כאן root מקבל את הערך 1 ולכן המספר יהיה תמיד גדול באחד מ- BLEVEL ב- DBA_INDEXES)
  • LF_ROWS – מספר הרשומות באינדקס
  • DEL_LF_ROWS – מספר כניסות באינדקס שמייצגות רשומות שנמחקו
  • LF_BLKS – מספר העלים שיש פיזית בעץ
  • BR_BLKS – מספר הצמתים בעץ שהם לא עלים

אז בואו נראה מה קורה בזמן מחיקה מאינדקס. לצורך זה יצרתי את הטבלה ildba והכנסתי אליה מיליון רשומות. זה המבנה של הטבלה, כאשר השדה ID מוגדר כ- primary key:

SQL> desc ildba
Name                              Null?    Type
--------------------------------- -------- ---------------------
ID                                NOT NULL NUMBER
NAME                                       VARCHAR2(100)

כעת, בואו נראה מה הנתונים של האינדקס:

SQL> analyze index ildba_pk validate structure;
Index analyzed.

SQL> select NAME,HEIGHT,LF_ROWS,DEL_LF_ROWS,LF_BLKS,BR_BLKS from index_stats;

NAME           HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS   BR_BLKS
---------- ---------- ---------- ----------- ---------- ---------
ILDBA_PK            3    1000000           0       2087         4

אנחנו רואים שעומק העץ הוא 3 ויש בו מיליון רשומות המחולקות ל- 2087 עלים. עכשיו נראה מה קורה כאשר מוחקים את כל הערכים מהטבלה:

SQL> delete from ildba;
1000000 rows deleted.

SQL> commit;
Commit complete.

SQL> analyze index ildba_pk validate structure;
Index analyzed.

SQL> select NAME,HEIGHT,LF_ROWS,DEL_LF_ROWS,LF_BLKS,BR_BLKS from index_stats;
NAME           HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS   BR_BLKS
---------- ---------- ---------- ----------- ---------- ---------
ILDBA_PK            3     754615      754615       2087         4

שימו לב שעומק העץ לא השתנה, למרות שהאינדקס בעצם ריק. בנוסף, רואים שב- index_stats מספר הרשומות הוא 754 אלף ולא מיליון. ה- 754 אלף רשומות האלה הן מחוקות (מופיעות ב- DEL_LF_ROWS) כלומר הן סוג של place holder. לאן נעלמו כמעט 250 אלף הרשומות האחרות? אורקל מבצע "ניקוי" של רשומות שנמחקו, רשומות אלו כבר לא קיימות בכלל במבנה של האינדקס ולכן הן לא נספרות. היינו מצפים לראות "ניקוי" הרבה יותר יעיל והרבה פחות רשומות מחוקות בטבלה, אבל צריך לזכור שאורקל הרבה פעמים מבצע את הניקוי הזה לא בזמן המחיקה עצמה, אלא בהכנסה חדשה אל הבלוקים האלה.

ומה קורה אחרי rebuild?

SQL> alter index ildba_pk rebuild;
Index altered.

SQL> analyze index ildba_pk validate structure;
Index analyzed.

SQL> select NAME,HEIGHT,LF_ROWS,DEL_LF_ROWS,LF_BLKS,BR_BLKS from index_stats;
NAME           HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS   BR_BLKS
---------- ---------- ---------- ----------- ---------- ---------
ILDBA_PK            1          0           0          1         0

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

דבר אחרון שנבדוק זה את הגישה לטבלה דרך האינדקס בכל שלושת המקרים (מיליון רשומות, אחרי מחיקה ואחרי rebuild). את זה נעשה בעזרת autotrace ונסתכל על כמות הבלוקים שאנחנו ניגשים אליהם (הערך שנסתכל עליו הוא consistent gets + db block gets שזה מספר הבלוקים אליהם אנחנו ניגשים בזכרון). על הפקודה autotrace של SQL*Plus אתם יכולים לקרוא כאן

בטבלה עם מיליון רשומות:

SQL> set autotrace traceonly stat
SQL> select * from ildba where
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
474  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

שימו לב שכאן, כצפוי, יש מעבר על 4 בלוקים (בלוק אחד מכל רמה של האינדקס, סך הכל 3 בלוקים, ועוד בלוק אחד מהטבלה).

לאחר המחיקה:

SQL> select * from ildba where;
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
396  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

בהרצה הזאת יש גישה ל- 3 בלוקים בלבד. מדוע? הסיבה היא פשוטה, עברנו על שלושת הבלוקים של האינדקס (אחד לכל רמה), אבל בעלה הבנו שאין ערך מתאים בטבלה ולכן לא ניגשנו לטבלה.

אחרי rebuild:

SQL> select * from ildba where;
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
1  consistent gets
0  physical reads
0  redo size
396  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

כאן יש רק גישה לבלוק אחד, הסיבה היא שאחרי ה- rebuild עומק העץ הוא 1 (יש רק שורש). הבלוק שאנחנו קוראים כאן הוא השורש וממנו כבר מבינים שאין רשומה מתאימה ולכן אין פניה לטבלה.

מספר הרמות באינדקס

לעיתים אני שומע אמירה דומה לזאת: "צריך לעשות rebuild לאינדקס אם יש לו יותר מ- 4 או 5 רמות". עכשיו, כשאנחנו מבינים איך נראה אינדקס, אפשר גם להבין שהאמירה הזאת בעייתית. המטרה של אינדקס הוא לאפשר חיפוש פשוט ויעיל של ערכים. אינדקס מאפשר לנו למצוא נתון בטבלה תוך מעבר על הרבה פחות בלוקים מאשר בגישה לטבלה. גובה העץ קשור לכמה נתונים יש בו (גודל הנתונים וכמות הערכים), ולכן, rebuild לא בהכרח יעזור לאינדקסים בעלי 4 או 5 רמות. הוא יעזור במקרים בו אינדקס גדול משמעותית ממה שהוא אמור ויכול להיות, נבין את זה יותר בהמשך.

אינדקס שגדל מצד אחד

אחת מהגישות הנפוצות היא לבצע rebuild לאינדקסים שגדלים רק מצד אחד (למשל primary key שמכניסים אליו ערכים מ- sequence). כדי לבדוק את זה, ניצור טבלה זהה לטבלה שראינו למעלה ונכניס אליה 200 אלף רשומות. לאחר מכן נמחק 1000 רשומות ישנות ונכניס 1000 רשומות חדשות ואז נמחק עוד 1000 רשומות ישנות ונכניס עוד 1000 חדשות, וכן הלאה עד שיהיו לנו 200 אלף רשומות חדשות לאחר שה- 200 אלף הישנות נמחקו.

מצב התחלתי של האינדקס:

NAME         HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
---------- -------- ---------- ----------- ---------- ----------
ILDBA_PK          2     200000           0        417          1

לאחר הרצה אחת (החלפה של כל 200 אלף הרשומות):

NAME         HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
---------- -------- ---------- ----------- ---------- ----------
ILDBA_PK          2     213089       13089        442          1

לאחר 5 הרצות (החלפנו את כל ה- 200 אלף רשומות סך הכל 5 פעמים):

NAME         HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
---------- -------- ---------- ----------- ---------- ----------
ILDBA_PK          2     232321       32321        495          1

אחרי rebuild:

NAME         HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
---------- -------- ---------- ----------- ---------- ----------
ILDBA_PK          2     200000           0        446          1

הבדיקה הזאת מראה שאורקל השאיר כניסות "מחוקות" באינדקס. אבל צריך לשים לב שרוב המקום שהתפנה מהמחיקות נתפס על ידי הרשומות החדשות (אם זה לא היה המצב אז לאחר 5 הרצות היינו מצפים לראות 800 אלף DEL_LF_ROWS, לעומת זאת אנחנו רואים רק 32 אלף).

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

האם rebuild משפר ביצועים?

השאלה הזאת היא קצת כללית כי זה תלוי באיזה מצב נמצא האינדקס שלנו. אם הוא מנופח יתר על המידה (למשל בגלל מקום שהתפנה ואורקל לא הצליח לנצל אותו שוב, או בגלל טבלה שהיתה גדולה בעבר והיום קטנה משמעותית), אז יש יותר עלים בעץ ממה שיכולים להיות, וייתכן שאפילו יש רמות מיותרות. במקרה כזה rebuild ישפר לנו את מבנה האינדקס, יקטין אותו וישפר לנו ביצועים. אבל כמו שהראיתי בדוגמא למעלה, זה לא תמיד המצב. אורקל במקרים מסוימים יודע לנצל מקום שהתפנה ואז rebuild לא יתרום לנו שום דבר.

אז האם צריך לעשות Rebuild לאינדקס בצורה שוטפת?

ועכשיו הגענו לשורה התחתונה, האם כדאי לעשות rebuild לאינדקסים שלי בצורה שוטפת ואוטומטית?

כמו שאמרתי, אם תחפשו או תשאלו על הנושא תקבלו תשובות רבות ומגוונות. לדעתי? לא, לא צריך לעשות rebuild לאינדקס בצורה שוטפת או אוטומטית. אם יש לכם טבלה ספציפית שאתם יודעים שההתנהגות שלה גורמת לאינדקס לא אופטימלי, אז תעשו לו rebuild. אם אתם מגלים אינדקס ספציפי בעייתי, תעשו לו rebuild. אבל לעשות rebuild אוטומטי לכל האינדקסים זה כנראה יותר overhead מאשר תועלת.

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

בפרק האחרון בסדרה הזאת (כנראה), נדבר על clustering factor.
מקווה שנהניתם.
לירון אמיצי.
ניתן ליצור קשר עם לירון דרך עמוד האודות שלו: אודות.

The following two tabs change content below.
ירון אמיצי הוא סמנכ"ל שירותי מומחה בחברת בריליקס ו-DBA בכיר בעל נסיון של למעלה מ- 15 שנים. ללירון תואר Oracle Ace ומתמחה בנושאי ביצועים, תשתיות, פתרונות זמינות גבוהה, גיבויים ושחזורים. ללירון יש גם בלוג עצמאי בכתובת: https://amitzil.wordpress.com

תגובה אחת ל- “אינדקסים באורקל – חלק 3”

commenter

תודה רבה למאמר! מועיל ביותר

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

Baruch Osoveskiy

תיקון מהיר לדיסק איטי

  רציתי לשתף אתכם בתקלה שהיתה לי ובפיתרון שלה. לפעמים אני רוצה לבדוק גירסה חדשה או יכולת שלא מתועדת מספיק בספרות, ואז אני מסתפק בהקמה של סביבה ווירטואלית בדרך כלל על חומרה זולה (לפעמים זולה [...]
OS

OS Background operations

אורי לרנר בטיפ קצר ושימושי על העברת פעולות לרקע במערכת [...]
רשימת

רשימת הפיצ'רים החדשים של אורקל 12.1

אורקל פרסמו את הספרות הרשמית לגרסה 12.1 שיצאה לאחרונה וזמינה להורדה. בין שאר הספרים (החשובים כל אחד שלעצמו), פורסם הספר המסקרן ביותר בעיני – Oracle Database 12c Release 1 (12.1) New Features. זהו ספר שראוי שכל DBA [...]
גרסת

גרסת אורקל 12c זמינה להורדה

בשעה טובה ולאחר המתנה סופר ארוכה, גרסת אורקל 12c (גרסה 12.1) זמינה סוף סוף להורדה רשמית מהאתר של אורקל. הגרסה החדשה מנסה לתת פתרונות לעולם ה"ענן" – ומוסיפה פיצ'רים חדשים שבאים לתת מענה בדיוק [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: