Migration היא תופעה לא רצויה אשר עלולה לגרום לפעולות I/O מיותרות רבות. מצב זה מתרחש בעת פעולות Update אשר גורמות לגודל שורה להתרחב מעבר לקיבולת הבלוק.
בפוסט זה נראה כיצד ניתן לזהות בעיה שכזו, כיצד ניתן לפתור אותה וכיצד ניתן למנוע אותה מלכתחילה.
כיצד Migration מתרחש
ניקח לדוגמא את הבלוק הבא אשר גודלו 8kb, בלוק זה מכיל 2 שורות ( Row A ו – Row B)
פעולת Update מסויימת הגדילה את שורה B ל 7k – מכיוון שגודל השורה החדשה עוברת את הקיבולת המקסימלית של הבלוק, שורה B אורזת את מטלטליה ועוברת דירה :
כל מה שנותר בבלוק המקורי בו השורה ישבה הוא פוינטר (forwarding address) אשר מצביע לעבר מיקומה החדש. כעת כאשר נרצה לאתר את שורה זו נצטרך לבצע פעולת I/O נוספת : הגישה הראשונית תנסה לאתר את השורה במקומה המקורי, כאשר זו לא תמצא שם – פעולת ה I/O הנוספת תיגש לבלוק החדש באמצעות ה forwarding address.
שימו לב כי נושא ה Migration רלוונטי לפעולות Index Scan , בפעולת ה FTS יש התעלמות מה forwarding address מכיוון שבכל מקרה השורה תתקבל ככל שפעולת ה FTS תושלם.
זיהוי Migration
ראשית נייצר טבלה עם שלוש עמודות :
RAM >CREATE TABLE mig_tab 2 (id_col number, 3 col_1 varchar2(3000), 4 col_2 varchar2(3000)) ;
לאחר מכן נכניס אל הטבלה 1000 שורות – כאשר כל שורה תהיה בקירוב k3
Table created.
RAM >BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO MIG_TAB(id_col, col_1)
VALUES (i, LPAD('*',3000,'*'));
4 END LOOP ;
5 COMMIT ;
6 END ;
7 /
כעת אם נתבונן בטבלאות המערכת נראה כי נכון לנקודה זו אין Migration (וגם לא אמור להיות..)
RAM >ANALYZE TABLE mig_tab COMPUTE STATISTICS;
Table analyzed.
RAM >SELECT NUM_ROWS, CHAIN_CNT
2 FROM dba_tables
3 WHERE TABLE_NAME = 'MIG_TAB';
NUM_ROWS CHAIN_CNT
---------- ----------
1000 0
נייצר Migration ע"י ניפוח כל שורה לגודל k6 – כך ששורה אחת תאלץ לעבור לבלוק אחר ושורה שניה תשאר ותגדל על חשבון המקום הפנוי של השורה שעזבה
RAM >UPDATE mig_tab SET col_2=LPAD('*',3000,'*') ;
1000 rows updated.
RAM >COMMIT ;
Commit complete.
כעת ניתן לראות כי ל 500 שורות (חצי מהטבלה) התבצע Migration
RAM >ANALYZE TABLE mig_tab COMPUTE STATISTICS;
Table analyzed.
RAM >SELECT NUM_ROWS, CHAIN_CNT
2 FROM dba_tables
3 WHERE TABLE_NAME = 'MIG_TAB';
NUM_ROWS CHAIN_CNT
---------- ----------
1000 500
נייצר אינדקס על עמודת id_col ונבדוק את תוכניות הפעולה, שימו לב לכמות פעולות ה I/O כאשר נרצה לגשת אל השורות באמצעות אינדקס – עבור חצי מהן (אלו שעברו דירה) נשלם פעולת I/O אחת יותר.
RAM >CREATE INDEX mig_id_ix ON mig_tab(id_col) ;
RAM >SET AUTOTRACE TRACEONLY
RAM >SELECT * FROM mig_tab WHERE id_col = 5;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6003 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MIG_TAB | 1 | 6003 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MIG_ID_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
...
1 recursive calls
0 db block gets
5 consistent gets
RAM >SELECT * FROM mig_tab WHERE id_col = 6;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6003 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MIG_TAB | 1 | 6003 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MIG_ID_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
...
1 recursive calls
0 db block gets
4 consistent gets
RAM >SELECT * FROM mig_tab WHERE id_col = 7;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6003 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MIG_TAB | 1 | 6003 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MIG_ID_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
...
1 recursive calls
0 db block gets
5 consistent gets
לסיום נבצע פעולת FTS ונראה כמה פעולות I/O נדרשו :
RAM >SELECT * FROM mig_tab ;
----------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1000 | 5865K| 208 (0)| 00:00:05 |
1 | TABLE ACCESS FULL| MIG_TAB | 1000 | 5865K| 208 (0)| 00:00:05 |
----------------------------------------------------------------------------
...
0 recursive calls
0 db block gets
1005 consistent gets
פתרון Migration
על מנת לפתור בעיה זו אנו צריכים לבנות מחדש את הטבלה כך שהבלוקים בה יופיעו בצורה מסודרת – כדי לעשות זאת ניתן להשתמש בפקודת ה MOVE
ALTER TABLE mig_tab MOVE ;
RAM >ANALYZE TABLE mig_tab COMPUTE STATISTICS;
Table analyzed.
RAM >SELECT NUM_ROWS, CHAIN_CNT
2 FROM dba_tables
3 WHERE TABLE_NAME = 'MIG_TAB';
NUM_ROWS CHAIN_CNT
---------- ----------
1000 0
לאחר שפקודה זו תושלם כל האינדקסים על הטבלה יהפכו למצב Unusable ואנו נצטרך לבצע להם Rebuild – זו אחת מחסרונותיה של פקודת ה MOVE
RAM >ALTER INDEX MIG_ID_IX REBUILD ;
כעת נבצע פעולת FTS שנית ונראה כמה פעולות I/O נדרשו :
RAM >SELECT * FROM mig_tab ;
----------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1000 | 5865K| 208 (0)| 00:00:05 |
1 | TABLE ACCESS FULL| MIG_TAB | 1000 | 5865K| 208 (0)| 00:00:05 |
----------------------------------------------------------------------------
...
0 recursive calls
0 db block gets
1005 consistent gets
שימו לב כי כמות פעולות ה I/O ב FTS לא השתנתה, גם לאחר שהטבלה "תוקנה"
מניעת Migration
כאשר אנו בונים טבלה חדשה, יש ביכולתנו להגדיר עבורה ערך אשר נקרא – PCTFREE, ערך זה קובע את נקודת המקסימום בבלוק אליו פקודות Insert חדשות יוכלו להגיע. לדוגמא – אם נקבע PCTFREE של 20, משמעות הדבר שבכל בלוק ישמרו 20 אחוז עבור גדילה עתידית (Update), פעולות Insert יוכלו להגיע מקסימום עד 80%.
האתגר של הוא למצוא את נקודת האיזון בין PCTFREE גדול דיו על מנת למנוע Migration
ומצד שני PCTFREE מספיק קטן כדי שלא יווצרו בתוך הבלוקים חללים ריקים לא מנוצלים.
RAM > CREATE TABLE mig_tab
2 (id_col number,
3 col_1 varchar2(3000),
4 col_2 varchar2(3000)) pctfree 60 ;
Table created.
1 BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO MIG_TAB(id_col, col_1)
4 VALUES (i, LPAD('*',3000,'*'));
5 END LOOP ;
6 COMMIT ;
7* END ;
8 /
PL/SQL procedure successfully completed.
RAM >UPDATE mig_tab SET col_2 = LPAD('*' , 3000 , '*') ;
1000 rows updated.
RAM >COMMIT ;
Commit complete.
RAM >ANALYZE TABLE mig_tab COMPUTE STATISTICS ;
Table analyzed.
1 SELECT num_rows , chain_cnt
2 FROM dba_tables
3* WHERE table_name = 'MIG_TAB';
NUM_ROWS CHAIN_CNT
---------- ----------
1000 0
נכתב ע"י רם קדם
