תכנון וכוונון מסד הנתונים ( Tuning the Database )

1. תכנון וכוונון מסד הנתונים (Tuning the Database) קורס מסדי נתונים

2. ייעול פעולת מערכת מסד הנתונים • ניתן לייעל את פעולת מערכת מסד הנתונים על ידי שינוי פרמטרים שונים: • גודל ה-buffer pool • תדירות ביצוע checkpoints • תכנון נכון של מסד הנתונים (טבלאות, אינדקסים) תכנון (design) כוונון (tuning) עבודה שוטפת

3. דרישה ראשונית נדרשת הבנה של עומס העבודה על המערכת ואופי השימוש במערכת ריבוי משתמשים ועבודה במקביל שאילתות פעולות עדכון

4. עומס עבודה • רשימת שאילתות והתדירות שלהן ביחס לכלל השאילתות ופעולות העדכון • רשימת פעולות העדכון והתדירות שלהן ביחס לכלל השאילתות ופעולות העדכון • היעילות הנדרשת מכל סוג של שאילתה ושל פעולת עדכון

5. מידע על שאילתות בעומס העבודה • לאילו יחסים השאילתה ניגשת • אילו אטריביוטים השאילתה מחזירה (select) • אילו אטריביוטים מופיעים בתנאי בחירה או כחלק מתנאי צירוף (where) ועד כמה התנאי סלקטיבי

6. מידע על פעולות עדכון בעומס עבודה • סוג פעולת העדכון (update, insert, delete) • במקרה של פעולת update, אילו אטריביוטים השאילתה משנה (set) • אילו אטריביוטים מופיעים בתנאי בחירה או כחלק מתנאי צירוף (where) ועד כמה התנאי סלקטיבי

7. החלטות שיש לקבל תוך כדי תכנון וכוונון מסד הנתונים • אילו אינדקסים לייצר • על אילו יחסים ועל אילו שדות ליצור אינדקסים • איזה סוג אינדקס נדרש במקרים השונים (מקבץ/לא מקבץ, דליל/צפוף, על ידי עץ B+/על ידי המרה) • האם לשנות את הסכמה הכללית לצורך שיפור ביצועים • בחירה בין אלטרנטיבות פירוק שיש להן אותה צורה נורמלית • איחוד סכימות על אף הורדת הצורה הנורמלית • חלוקת אנכית של סכימות • יצירת הגדרות תצפית (views) • שכתוב שאילתות

8. קווים מנחים לבחירת אינדקסים • בוחנים את השאילתות החשובות • בוחנים את תוכניות הביצוע של השאילתות • בוחנים את השפעת הוספת האינדקסים על ביצוע השאילתות

9. יצירת אינדקסים ב-Oracle CREATE [UNIQUE] [BITMAP] INDEX index-name ON table (column [,column]…); • מייצרים אינדקס בעזרת הפקודה: • ניתן לייצר גם אינדקס מקבץ: CREATE CLUSTER ReservesCluster (A integer); CREATE TABLE Reserves ( sid INTEGER, bid INTEGER, rdate DATE) CLUSTER ReservesCluster (sid); CREATE INDEX ReservesNDX ON CLUSTER ReservesCluster;

10. קווים מנחים לבחירת אינדקסים • קו מנחה 1 (האם לייצר אינדקס): אין צורך לייצר אינדקס שאף שאילתה או פעולת עדכון לא ירוויחו ממנו. רצוי לייצר אינדקסים באופן שמספר שאילתות ופעולות עדכון ירוויחו מהם • קו מנחה 2 (בחירת מפתח חיפוש): בוחנים אטריביוטים שמופיעים ב-where של שאילתה • בחירה על ידי תנאי של התאמה מדוייקת מובילה לאינדקס על האטריביוט שמופיע בתנאי עם העדפה לאינדקס המרה (hash index) • תנאי בחירה על טווח ערכים מוביל לשימוש באינדקס מבוסס עץ B+ על האטריביוט המופיע בתנאי

11. קווים מנחים לבחירת אינדקסים • קו מנחה 3 (מפתח חיפוש מרובה אטריביוטים): אינדקס שמפתח החיפוש שלו מורכב ממספר אטריביוטים ישמש במקרים הבאים • תנאי ה-where כולל תנאים על מספר אטריביוטים של יחס • האינדקס מאפשר פעולות שיתבצעו על האינדקס במקום על היחס עצמו • קו מנחה 4 (האם על האינדקס להיות מקבץ): ניתן לבחור אינדקס יחיד שיהיה האינדקס המקבץ של היחס • תנאי בחירה על טווח ערכים רצוי לבצע עם אינדקס מקבץ • תנאי של התאמה מדויקת כאשר לכמה רשומות אותו ערך במפתח החיפוש • אינדקס שנועד לפעולות שיבוצעו על האינדקס במקום על היחס עצמו לא יהיה מקבץ

12. קווים מנחים לבחירת אינדקסים • קו מנחה 5 (אינדקס המרה לעומת אינדקס B+): • אינדקס מבוסס עצי B+ טוב לשאילתות עם חיפוש על פי טווח ערכים • אינדקס מבוסס המרה טוב לביצוע צירוף על פי index nested loop join • אינדקס מבוסס המרה טוב לאטריביוט שהפעולות עליו הן חיפוש על פי התאמה מדויקת בלבד (בלי חיפוש על פי טווח ערכים) • קו מנחה 6 (מחיר תחזוקת האינדקס): שינוי היחס שעליו מוגדר האינדקס עשוי לגרור שינוי של האינדקס עצמו ולכן לאינדקס מחיר תחזוקה • אם מחיר תחזוקת האינדקס עולה על התועלת מהאינדקס רצוי לבטל את האינדקס • יש לשים לב שלעיתים גם שאילתות עדכון מרוויחות מקיום אינדקס על היחס

13. ename,mgr Index nested loop join  dname=‘toy’ Employee Department אינדקס לתנאי בחירה ולצירוף SELECT E.ename, D.mgr FROM Employees E, Departments D WHERE D.dname=‘Toy’ AND E.dno=D.dno • אילו אינדקסים יש לבנות על מנת לקבל ביצוע יעיל של השאילתה? • כיצד תוכנית הביצוע של השאילתה משפיעה על ההחלטה?

14. ename,mgr Block nested loop join  dname=‘toy’ age=25 Department Employee אינדקס לתנאי בחירה ולצירוף SELECT E.ename, D.mgr FROM Employees E, Departments D WHERE D.dname=‘Toy’ AND E.dno=D.dno AND E.age=25 • אילו אינדקסים יש לבנות על מנת לקבל ביצוע יעיל של השאילתה? • כיצד תוכנית הביצוע של השאילתה משפיעה על ההחלטה?

15. אינדקס לתנאי טווח ולצירוף SELECT E.ename, D.dname FROM Employees E, Departments D WHERE E.sal BETWEEN 3000 AND 5000 AND E.city=‘Afula’ AND E.dno=D.dno • אילו אינדקסים יש לבנות על מנת לקבל ביצוע יעיל של השאילתה? • כיצד תוכנית הביצוע של השאילתה משפיעה על ההחלטה? SELECT E.ename, D.dname FROM Employees E, Departments D WHERE 3000<=E.sal AND E.sal<=5000 AND E.city=‘Afula’ AND E.dno=D.dno יש לבחון את הסלקטיביות של תנאי הבחירה

16. SELECT E.dno FROM Employees E WHERE E.city=‘Afula’ האם לבנות אינדקס על האטריביוט city של Employee? אינדקס מקבץ • אילו אינדקסים יש לבנות על מנת לקבל ביצוע יעיל של השאילתה? • האם יש חשיבות לשאלה אם האינדקס הוא מקבץ? • כיצד ישפיע הנתון על אחוז העובדים שגילם גדול מ-40? SELECT E.dno FROM Employees E WHERE E.age>40

17. השפעה של אינדקס מקבץ אינדקס לא מקבץ מחיר סריקה סדרתית אינדקס מקבץ 0 100 אחוז הרשומות שיש להביא

18. אינדקס על מספר עמודות SELECT E.eid FROM Employees E WHERE E.sal BETWEEN 3000 AND 5000 AND E.age BETWEEN 20 AND 30 • אילו אינדקסים יש לבנות על מנת לקבל ביצוע יעיל של השאילתה? • האם יש הבדל בין אינדקס לפי <age,sal> לאינדקס לפי <sal,age>? SELECT E.eid FROM Employees E WHERE E.sal BETWEEN 3000 AND 5000 AND E.age=25

19. שימוש באינדקס במקום ביחס • נניח שיש אינדקס צפוף על עמודה dno ביחס Employee • כיצד ניתן להשתמש באינדקס על מנת לקבל חישוב יעיל של השאילתה? • האם יש חשיבות לכך שהאינדקס יהיה צפוף? • האם יש חשיבות לכך שהאינדקס יהיה מקבץ? SELECT D.mgr FROM Departments D, Employees E WHERE D.dno=E.dno SELECT E.dno, COUNT(*) FROM Employees E GROUP BY E.dno

20. שימוש באינדקס במקום ביחס • האם בשאילתות הבאות ניתן להשתמש באינדקס כדי לא לגשת ליחס Employee? • אם כן כיצד יראה האינדקס? SELECT D.mgr, E.eid FROM Departments D, Employees E WHERE D.dno=E.dno SELECT E.dno, COUNT(*) FROM Employees E WHERE E.sal=10000 GROUP BY E.dno SELECT E.dno, MIN(E.sal) FROM Employees E GROUP BY E.dno

21. כוונון מסד הנתונים • להשגת ביצוע יעיל של מערכת מסד הנתונים יש לבצע • כוונון אינדקסים • כוונון הסכמה הכללית • כוונון השאילתות

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

23. כוונון הסכימה הכללית(מבנה הטבלאות) • הסכימה מאורגנת בעת תכנון מסד הנתונים ובצוע נורמליזציה של הטבלאות • לעיתים נדרש ארגון מחדש מטעמי יעילות ביצוע של שאילתות

24. פעולות שניתן לעשות בכוונון הסכימה של המסד • להתפשר על צורת 3NF במקום BCNF • אם יש יותר מפירוק אחד בצורה הנורמלית הרצויה הבחירה בין הפירוקים תעשה משיקולי יעילות ביצוע • ניתן לפרק סכימה שהיא כבר בצורת BCNF • אפשר לחבר סכמות שהופרדו בפירוק או להוסיף אטריביוטים לסכימה על מנת לייעל ביצוע שאילתות (אף שיתכן שזה יצור בעיה של כפילויות) • ניתן לפרק יחסים פירוק אופקי (חלוקת היחס לשני יחסים בעלי סכימה זהה) ניתן למסך פעולות כוונון על ידי שימוש בתצפיות

25. טבלאות דוגמה Contracts(cid, suplierid, projectid, deptid, partid, qty, value) Departments(did, budget, annualreport) Parts(pid, cost) Projects(jid, mgr) Suppliers(sid, address)

26. התפשרות על הצורה הנורמלית • את טבלת החוזים נייצג כ-CSJDPQV • אילוצי תקינות: • בפרוייקט חלק נרכש בחוזה יחיד: JPC • מחלקה לא קונה מספק יותר מחלק אחד: DSP • C הוא מפתח ראשי של טבלת החוזים • מפתחות של טבלת החוזים: C, JP • התלות DSP סותרת BCNF אך תואמת 3NF • פירוק לצורת BCNF שהוא משמר תלויות וחסר אבדן: SDP, CSJDQV, CJP • נניח ששאילתה נפוצה במערכת היא מהו מספר החלקים Q של חלק P שהוזמן בחוזה P, האם נרצה לבצע את הפירוק?

27. איחוד טבלאות (denormalization) • נניח ששאילתה נפוצה במערכת החוזים היא לבדוק שהערך של חוזה הוא פחות מהתקציב של המחלקה המבצעת • התקציב B של המחלקות נמצא ביחס של המחלקות ונדרש צירוף טבעי • ניתן להוסיף את B לסכימה של החוזים אך אז נקבל את התלות DB (באיזו צורה נורמלית יחס החוזים כעת?)

28. בחירת הפירוק • בדוגמה הקודמת בחרנו פירוק לצורת BCNF שהוא משמר תלויות וחסר אבדן : • SDP, CSJDQV, CJP • פירוק אפשרי אחרהוא פירוק לצורת BCNF וחסר אבדן שאינו משמר את התלויות: • SDP, CSJDQV • לא נשמרת התלות JPC

29. פירוק אנכי • נניח שנבחר הפירוק: SDP, CSJDQV • אין סיבה לפרק יותר מפני שהסכמות בצורת BCNF • נניח ששאילתות נפוצות הן: • מהם החוזים שקיבל ספק S • מהם החוזים שהוציאה מחלקה D • ניתן להשתמש בפירוק:SDP, CS, CD, CJQV • מה יקרה אם שאילתה נוספת נפוצה היא מציאת הערך הכולל של החוזים שקיבל ספק מסוים

30. פירוק אופקי • נניח שהחוזים מחולקים לשניים: • חוזים גדולים שבהם value>10000 • חוזים קטנים שבהם value<=10000 • ניתן לבצע הפרדה (אופקית) לשני יחסים: LargeContracts, SmallContracts • ניתן למסך את החלוקה בעזרת הגדרת תצפית: Create view Contracts(cid, suppliers, projectid, deptid, partid, qty, value) as ((Select * From LargeContracts) Union (Select * From SmallContracts))

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

32. כוונון שאילתות • מניעת מקרים שאינם מטופלים בצורה יעילה על ידי optimizers: • תנאי בחירה שכולל NULL • תנאי בחירה שכוללים • ביטויים חשבוניים (כגון, sal/100<5, E.age=D.age/2), • ביטויים על מחרוזות (כגון,ename like ‘%A’) , • תנאי OR • נתינת הנחיות ל-optimizer לגבי ביצוע השאילתה

33. SELECT E.dno FROM Employees E WHERE E.city=‘Afula’ UNION ALL SELECT E.dno FROM Employees E WHERE E.age=25 לדוגמא SELECT E.dno FROM Employees E WHERE E.city=‘Afula’ OR E.age=25 נניח שקיים אינדקס על city וקיים אינדקס על age, מה יעשה ה-optimizer? שתי השאילתות שקולות?

34.  בחלק מהמערכות, שאילתות שקולות שאחת כוללת תת שאילתות והשניה לא כוללת תת שאילתות, השאילתה בלי התת שאילתות תבוצע בצורה יעילה יותר! SELECT E.dno, AVG(E.sal) FROM Employees E, Departments D WHERE E.dno=D.dno AND D.mgr=‘Cohen’ GROUP BY E.dno שאילתה אחת במקום ביצוע בשלבים SELECT * INTO Temp FROM Employees E, Departments D WHERE E.dno=D.dno AND D.mgr=‘Cohen’ SELECT T.dno, AVG(T.sal) FROM Temp T GROUP BY T.dno 

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

36. מנעולים עם גרעיניות מרובהMultiple-Granularity Locks • אם עסקה צריכה לנעול את מרבית הדפים (בלוקים) של טבלה (יחס), אז עדיף שהעסקה תנעל את כל היחס, כדי להקטין את התקורה של הטיפול במנעולים • לעומת זאת, אם עסקה צריכה לנעול רק חלק קטן מהדפים של טבלה, אז מוטב שהעסקה תנעל רק דפים אלה, כדי לא למנוע מעסקאות אחרות לפעול במקביל על דפים אחרים של אותה טבלה

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

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

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

40. נעילת צומת ע"י מנעול S או X פירושה נעילת כל תת-העץ • אם מנעול מסוג S או X מוחזק על צומת בעץ, אז המשמעות היא שמנעול זה מוחזק על על כל הצאצאים של הצומת • לכן, עסקה אינה צריכה לנעול צומת אם היא מחזיקה מנעול מאותו הסוג על הורה או הורה קדמון (ancestor) של הצומת

41. צריך גם מנעולי כוונהIntention Locks • מנעול מסוג intention exclusive מסומן ע"י IX • אם עסקה נועלת צומת ע"י מנעול IX, אז פירוש הדבר שיש לה כוונה לנעול צאצאים של הצומת ע"י מנעול X • מנעול מסוג intention shared מסומן ע"י IS • אם עסקה נועלת צומת ע"י מנעול IS, אז פירוש הדבר שיש לה כוונה לנעול צאצאים של הצומת ע"י מנעול S

42. תאימות בין סוגי המנעולים • כמו קודם, על צומת יכולים להיות מספר מנעולי S כל עוד אין מנעול מסוג X (של עסקה אחרת) • על צומת יכולים להיות מספר מנעולי IS כל עוד אין מנעול מסוג X (של עסקה אחרת) • על צומת יכולים להיות מספר מנעולי IX כל עוד אין מנעולים מסוג S או X (של עסקאות אחרות) • לדוגמה, על צומת יכולים להיות מספר מנעולי IS ומספר מנעולי IX בו-זמנית • אפשר גם מספר מנעולי IS ומספר מנעולי S בו-זמנית • אם יש מנעול X, אז אין אפשרות לאף מנעול נוסף

43. הפרוטוקול לנעילות רב-גרעיניות • עסקה חייבת להתחיל בנעילת השורש ע"י S, X, IX או IS • עסקה יכולה לנעול צומת ע"י IS או S אם היא מחזיקה IS על ההורה של הצומת • עסקה יכולה לנעול צומת ע"י IX או X אם היא מחזיקה IX על ההורה של הצומת • הערה: כשעסקה נועלת צומת ע"י S או X, אז כל הצאצאים של הצומת נעולים גם הם ואין צורך להמשיך לרדת בעץ כדי לנעול

44. המשך הפרוטוקול • אי אפשר להסיר מנעול מכל סוג שהוא (IS, IX, S או X) מצומת נתון, אלא אם כן אין שום מנעולים על הילדים של הצומת • כל הנעילות (מכל ארבעת הסוגים) מתנהלות לפי הפרוטוקול של נעילות בשתי פאזות: • תחילה, עסקה מבצעת רק נעילות • לאחר השחרור הראשון של מנעול (מכל סוג שהוא), עסקה יכולה רק לשחרר מנעולים

45. מדוע התנאי ש • אי אפשר להסיר מנעול מכל סוג שהוא (IS, IX, S או X) מצומת נתון, אלא אם כן אין שום מנעולים על הילדים של הצומת? • אם עסקה מסירה את המנעול שלה מהשורש (ואין מנעולים אחרים על השורש), אז כל עסקה אחרת יכולה לנעול את השורש ע"י X ואז למעשה כל העץ נעול ע"י X • אם העסקה הראשונה עדיין מחזיקה מנעול S או X על צומת כלשהי בעץ זה סותר את הדרישה שהעסקה האחרת מחזיקה מנעול X בלעדי על כל העץ

46. מקרה נפוץ • עסקה רוצה לקרוא יחס במלואו ולשנות מספר קטן של רשומות השייכות ליחס • העסקה תנעל את הצומת המתאימה לכל היחס ע"י IX וע"י S • בהמשך העסקה תנעל צאצאים נוספים ע"י IX או X

47. נכונות • הפרוטוקול של נעילות רב גרעיניות מייצר רק תזמונים ברי סדרתיות קונפליקטית • ההוכחה בספר של אולמן, עמוד 506 • בספר של אולמן intention locks קרויים warnings

48. הסלמה של גירעוןGranularity Escalation • איך מחליטים מהי הגרעיניות המתאימה לעסקה נתונה? • אפשר להתחיל עם גרעיניות עדינה (למשל, נעילה ברמה של רשומות) ולאחר שמספר המנעולים שהעסקה מבקשת עובר סף מסוים, אז מגדילים את הגרעיניות לרמה הבאה (למשל, נעילות ברמה של דפים)

49. כוונון ב-Oracle

50. Oracle Query Optimizers • ה-optimizer פועל בשתי שיטות • Rule-based (RBO): • בוחן את מסלולי הביצוע האפשריים ומדרג את האלטרנטיבות לפי אוסף חוקים • Cost-based (CBO): • משתמש באינפורמציה שיש על המסד (כגון גודל הטבלאות) כדי לבחור את מסלול הביצוע הטוב ביותר • דורש אנליזה של הטבלאות במסד המתקבלת מהפעלת פקודת Analayze Oracle משתמש ב-rule-based אם לכל הטבלאות המשתתפות בשאילתה לא נעשתה להן אנליזה