מציאת אינדקסים אשר אינם בשימוש - Oracle - ilDBA Portal

מציאת אינדקסים אשר אינם בשימוש – Oracle

01/01/2011 | פורסם על ידי

הקדמה

כולנו משתמשים באינדקסים בבסיסי הנתונים שלנו.

אינדקסים הם אובייקטים מצויינים הנועדים להאיץ את הגישה למידע שלנו וגורמים לשליפות להיות מהירות יותר.

עם זאת, לכל פלוס יש תמיד מינוס. עם כמה שהאינדקסים מעולים לקריאות, הם יוצרים 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 נותן לנו אפשרות לאתר אינדקסים אשר אינם בשימוש, אך מנגנון זה אינו חף מבעיות:

  1. איך ניתן באמת לקבוע מהו זמן הניטור הדרוש? לא כלכך פשוט לדעת מהי כמות הזמן האופטימלית שבמהלכה יהיה Full life cycle של האפליקציה על מנת שהניטור יהיה נכון ומקיף.
  2. Monitoring usage אכן "מסמן" אינדקסים אשר השתמשו בהם, אך הוא אינו מפרט את כמות ותדירות השימוש.

 

סיכום

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

על מנת לאתר אינדקסים אשר אינם בשימוש נוכל להשתמש ב-Index Monitoring Usage אשר תואר לעיל. השימוש בו נוח וקל, והמידע על האינדקסים המנוטרים יימצא ב-View: V$Object_usage.

מקווים שהמידע עזר,

נתראה ברשומה הבאה !

6 תגובות ל- “מציאת אינדקסים אשר אינם בשימוש – Oracle”

רותם ג. | 09/01/2011 בשעה 02:55
commenter

הי,
נקודה חשובה שצריך לקחת בחשבון:
לא יוצג מידע על אינדקס על FK ,אז תוודאו לפני שאתם מסירים אינדקס שלא מופיע לכם בשימוש שהוא לא FK .
להסבר יותר מפורט :
http://richardfoote.wordpress.com/2008/09/12/index-monitoring-and-foreign-keys-caution-kid-a/

[…] את הפוסט “מציאת אינדקסים אשר אינם בשימוש” שכתבה רוני ורד באתר, ומצאתי לנכון […]

commenter

קראתי את התוספות של גלית שמתייחסות לגירסה 11 עם שדות שלא קיימים ב- 9 כמו sql_id, כיצד ניתן לעשות משהו דומה בגירסה 9.

commenter

היי דורון,
בגרסת 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';

commenter

רוני יקירתי
קצת תשומת לב, sql_id לא קיים ב- v$sql וגם object_type לא קיים ב- v$sql_plan
בגירסה 9
אני מחפש דרך לראות יעילות של האינדקסים דרך מספר הפעמים שהופעלו, האם וכמה פעמים ניגשו לדיסק (disk read) וטעינות ל- buffer וכד' וכן עלות ההפעלה (cost)
במידה ויש רעיות אשמח לשמוע.

commenter

היי דורון, צודק – הפזיזות מהשטן.
אנא נסה את השליפה הבאה.


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.

מקווה שזה יעזור.
רוני.

השאר תגובה:

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

*



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

שחזור טבלה באמצעות RMAN

בפוסט זה אציג פיצ'ר שאורקל הוסיפו בגרסת  12c (12.1.0.1 ליתר דיוק) – שחזור טבלה\טבלאות לנקודת זמן מסוימת באמצעות RMAN. הפיצ'ר יכול להיות שימושי במקרים הבאים: נמחק או השתנה המידע בטבלה ולא ניתן להשתמש [...]
PL/SQL

PL/SQL Injection – The Beginning

עודד רז פותח בסדרת מאמרים על Pl/sql [...]

תיעוד בסיס הנתונים – למה זה טוב?

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

צבעים דינמיים ב-SSRS

כאשר לקוח שלי ולא משנה אם הוא מבוסס אורקל או SQL Server חושב על פתרון Reporting אני ישר מציע לו את ה- Sql Server Reporting Services. שתי סיבות עיקריות להצעה שלי: רישוי ויכולות. איתי בנימין מסביר על צבעים דינמים [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: