הקדמה
כולנו משתמשים באינדקסים בבסיסי הנתונים שלנו.
אינדקסים הם אובייקטים מצויינים הנועדים להאיץ את הגישה למידע שלנו וגורמים לשליפות להיות מהירות יותר.
עם זאת, לכל פלוס יש תמיד מינוס. עם כמה שהאינדקסים מעולים לקריאות, הם יוצרים Overhead בעת ביצוע פעולות DML על הטבלה, מכיוון שכל עידכון של הטבלה מעדכן גם את האינדקס.
נקודה נוספת שצריך לקחת בחשבון, גם אם האינדקס כלל אינו בשימוש, הוא עדיין יהיה מתוחזק ויעודכן בהתאם לפעילות על הטבלה.
אז איך ניתן לדעת אם אינדקס אינו בשימוש על ידי המערכת שלנו ? אם נוכל לנטר את השימוש באינדקסים ולאתר אינדקסים מיותרים, נוכל לזרוק אותם ובכך להוריד קצת מהעומס של המערכת, כי האינדקס לא יצטרך להיות מתוחזק בצורה תכופה, לא נצטרך לעשות בו פעולות תחזוקה יקרות ואיטיות וכד'.
ניטור אינדקסים
יש דרך קלה מאוד לבדוק אם אינדקס נמצא בשימוש – אפשר לזרוק אותו ולחכות עד שמישהו יצעק עלינו שהמערכת פתאום הפכה להיות איטית בצורה בלתי נסבלת.
מכיוון שהדרך הזו היא לא כל כך יעילה וטובה, אורקל מאפשר לנו החל מגרסת 9i, לבצע ניטור שימוש באינדקסים הקיימים במערכת, ובכך לאתר את אותם אינדקסים מיותרים, ללא סיכון בזריקת אינדקסים חשובים ושמישים.
כאשר נפעיל ניטור על אינדקס מסויים, תתבצע "האזנה" על האינדקס. במידה והיה בו שימוש בשלב כלשהו בתקופה בה הניטור היה פעיל הוא "יסומן" כ-used.
חשוב לציין, שביצוע ניטור השימוש באינדקסים צריך להתבצע על פני זמן עבודת מערכת סבירה – כלומר, אם אנחנו יודעים שכל התכונות, המסכים, התהליכים, הטעינות ושאר פעולות המערכת מתבצעות במהלך יום אחד, אז ניטור אינדקסים על פני יום בודד יספיק, אבל אם ישנם תהליכים שמתבצעים לאורך זמן של מספר ימים או שבועות, מומלץ להאריך את תקופת הניטור על מנת "לתפוס" כמה שיותר פעילות מערכת ובכך לצמצם את האפשרות שאינדקס חשוב במקרה לא ייתפס בזמן הניטור.
הפעלת ניטור האינדקסים
על מנת להפעיל את הניטור יש להריץ את הפקודה הבאה :
| ALTER INDEX index_name MONITORING USAGE |
כדי לבטל את הניטור, יש להריץ את הפקודה הבאה:
| ALTER INDEX index_name NOMONITORING USAGE |
בחינת תוצאות הניטור:
מידע על השימוש או אי השימוש באינדקסים יירשם ב-View שנקרא V$OBJECT_USAGE.
| SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoringFROM v$object_usageWHERE index_name = 'INDEX_NAME'
ORDER BY index_name; |
הסבר על העמודות ב-View זה:
- INDEX_NAME – שם האינדקס.
- TABLE_NAME – הטבלה הרלוונטית של האינדקס.
- MONITORIING – מציין האם הניטור מופעל או לא.
- USED – מציין על ידי YES / NO האם האינדקס היה בשימוש בזמן הניטור.
- START_MONITORING – זמן תחילת הניטור.
- END_MONITORING – זמן הפסקת הניטור.
מספר הערות בנוגע ל-View זה:
- ב-View זה אין עמודת Owner, ולכן יש להתחבר עם היוזר שהוא ה-Owner של האובייקט שניטרנו על מנת לראות את סטטיסטיקת השימוש בו.
- בכל פעם שמשתמשים ב MONITORING USAGE על אינדקס,המידע על אותו אינדקס מסויים יתאפס ב- V$OBJECT_USAGE. המידע הישן ייעלם, ועמודת Start_monitoring תתאכלס בנתוני הזמן העדכניים.
סקריפטים שימושיים
מכיוון שניטור אינדקסים צריך להיות מצויין במפורש על כל אינדקס בנפרד, להלן שתי שליפות שירכזו את כל פקודות ההכנסה ויציאה מניטור (יש להחליף את <Owner> בסכמה הרלוונטית):
| SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'FROM dba_indexes iWHERE owner = <OWNER> |
| SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'FROM dba_indexes iWHERE owner = <OWNER> |
נקודות חשובות לציון
Monitoring index usage נותן לנו אפשרות לאתר אינדקסים אשר אינם בשימוש, אך מנגנון זה אינו חף מבעיות:
- איך ניתן באמת לקבוע מהו זמן הניטור הדרוש? לא כלכך פשוט לדעת מהי כמות הזמן האופטימלית שבמהלכה יהיה Full life cycle של האפליקציה על מנת שהניטור יהיה נכון ומקיף.
- Monitoring usage אכן "מסמן" אינדקסים אשר השתמשו בהם, אך הוא אינו מפרט את כמות ותדירות השימוש.
סיכום
אינדקסים הם אובייקטים מעולים וחשובים בבסיסי נתונים, אך יש להיזהר ולהימנע מ-Over Indexes מכיוון שעם התועלת הרבה שלהם מגיעה גם הכבדה על המערכת, מכיוון שהאינדקס יעודכן ויתוחזק בעת ביצוע פעולות DML על הטבלה. לא פעם יש לנו במערכת אינדקסים רבים אשר בכלל לא בשימוש, ואז הם רק מכבידים לנו על המערכת ולא מועילים כלל.
על מנת לאתר אינדקסים אשר אינם בשימוש נוכל להשתמש ב-Index Monitoring Usage אשר תואר לעיל. השימוש בו נוח וקל, והמידע על האינדקסים המנוטרים יימצא ב-View: V$Object_usage.
מקווים שהמידע עזר,
נתראה ברשומה הבאה !
Latest posts by רוני ורד (see all)
- Webinar – Oracle Data Guard Broker - 01/06/2012
- SQL Server 2008 – תקלה בסביבה בה מתקיימים טבלאות עם מחיצות ורפליקציה - 16/01/2011
- מציאת אינדקסים אשר אינם בשימוש – Oracle - 01/01/2011
6 תגובות ל- “מציאת אינדקסים אשר אינם בשימוש – Oracle”
הי,
נקודה חשובה שצריך לקחת בחשבון:
לא יוצג מידע על אינדקס על FK ,אז תוודאו לפני שאתם מסירים אינדקס שלא מופיע לכם בשימוש שהוא לא FK .
להסבר יותר מפורט :
http://richardfoote.wordpress.com/2008/09/12/index-monitoring-and-foreign-keys-caution-kid-a/
[…] את הפוסט “מציאת אינדקסים אשר אינם בשימוש” שכתבה רוני ורד באתר, ומצאתי לנכון […]
קראתי את התוספות של גלית שמתייחסות לגירסה 11 עם שדות שלא קיימים ב- 9 כמו sql_id, כיצד ניתן לעשות משהו דומה בגירסה 9.
היי דורון,
בגרסת 9i תוכל להריץ את השאילתה הבאה:
select a.executions,a.sql_id,b.operation, options
from
v$sql a,
v$sql_plan b
where a.HASH_VALUE=b.HASH_VALUE
and a.CHILD_NUMBER=b.CHILD_NUMBER
and b.object_type='INDEX'
and object_name='MY_INDEX_NAME';
רוני יקירתי
קצת תשומת לב, sql_id לא קיים ב- v$sql וגם object_type לא קיים ב- v$sql_plan
בגירסה 9
אני מחפש דרך לראות יעילות של האינדקסים דרך מספר הפעמים שהופעלו, האם וכמה פעמים ניגשו לדיסק (disk read) וטעינות ל- buffer וכד' וכן עלות ההפעלה (cost)
במידה ויש רעיות אשמח לשמוע.
היי דורון, צודק – הפזיזות מהשטן.
אנא נסה את השליפה הבאה.
select a.executions,b.operation, b.options, a.DISK_READS, a.BUFFER_GETS,a.CPU_TIME,a.ELAPSED_TIME
from
v$sql a,
v$sql_plan b,
dba_objects o
where a.HASH_VALUE=b.HASH_VALUE
and a.CHILD_NUMBER=b.CHILD_NUMBER
and b.OBJECT_NAME = o.OBJECT_NAME
and o.object_type='INDEX'
and o.object_name='MY_INDEX_NAME';
היא עוקפת את החוסר בשדות מסוימים בגרסת 9i על ידי שירשור עם טבלת dba_objects.
מקווה שזה יעזור.
רוני.
השאר תגובה: