Oracle – הצגת Execution Plan

הקדמה

כשאנחנו חוקרים ביצועים של שאילתא, אחד הדברים המשמעותיים, אם לא המשמעותי ביותר, הוא ה- execution plan – אותה טבלה שמציגה לנו איך אורקל מריץ את השאילתא שלנו.
מתוך ה- execution plan אנחנו יכולים להבין האם אורקל משתמש באינדקסים, באיזה סדר הוא ניגש לטבלאות, באיזו צורה הוא בוחר לבצע join ועוד ועוד. כאשר אנחנו מעוניינים לבצע כוונון לשאילתא, יש לנו מספר דרכים להציג את ה- execution plan שלה. במאמר זה נסקור את הדרכים השונות.

Explain plan

הדרך הראשונה שנציג היא שימוש בפקודה Explain plan. פקודה זו מאכלסת את הטבלה plan_table שממנה אפשר לאחר מכן לקבל את ה- execution plan המלא והמפורט. את הטבלה plan_table או טבלה במבנה זהה נוכל ליצור על ידי הסקריפט ORACLE_HOME/rdbms/admin/catplan.sql.

מבנה הפקודה הוא:

Explain plan
[ set statement id = ‘<id>’ ]
[ into <table> ]
for
<sql statement>

פרמטרים:

• id הוא שם שאנחנו יכולים להגדיר לשאילתא על מנת לזהות אותה ב- plan_table
• table הוא שם של טבלה במבנה של plan_table אשר אליה ייכנס המידע מהפקודה, במקום ל- plan_table
• sql statement היא השאילתא שעליה אנו מעוניינים להריץ את הפקודה

דבר חשוב בנוגע לפקודה explain plan הוא שבמידה והשאילתא מכילה bind variables, תוצאות הפקודה אינן אמינות.

באורקל קיים מנגנון שנקרא bind peeking שמטרתו "להציץ" בערכי ה- bind variables בתהליך ה- parse של השאילתא ולייצר execution plan אופטימלי בהתאם אליהם. השימוש  בפקודה explain plan אינו משתמש במנגנון הזה, מה שיכול לגרום ל- plan שונה כאשר נריץ את השאילתא דרך האפליקציה.

הצגת המידע מ- plan_table

אחרי שהרצנו את explain plan, המידע שמעניין אותנו נמצא ב- plan table. ניתן לראות את ה- execution plan בצורה יפה בכמה שיטות:

1. הסקריפט ORACLE_HOME/rdbms/admin/utlxpls יציג את ה- plan האחרון מתוך plan_table לשאילתות שרצות ב- serial
2. הסקריפט ORACLE_HOME/rdbms/admin/utlxplp יציג את ה- plan האחרון מתוך plan_table לשאילתות שרצות ב- parallel
3. באמצעות dbms_xplan, למשל:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())

Autotrace

אופציה נוספת להצגת ה- execution plan הוא על ידי הפקודה set autotrace של SQL*Plus. לאחר הרצת הפקודה, כל שאילתא תציג לנו את ה- execution plan ו/או סטטיסטיקות על הריצה, בהתאם למה שביקשנו.

מבנה הפקודה הוא:

set autotrace [ on | traceonly | traceonly statistics | traceonly explain | off]

• off – autotrace כבוי, זאת ההתנהגות הרגילה. הרצה של שאילתא מציגה את תוצאות השאילתא על המסך.
• on – הרצה של שאילתא תציג את תוצאות השאילתא, אחריהן את ה- execution plan ולבסוף סטטיסטיקות על ההרצה (כמה מיונים, כמה גישות לדיסק וכו').
• Traceonly – השאילתא תורץ, אבל תוצאות השאילתא לא יוצגו על המסך, יוצגו ה- execution plan וסטטיסטיקות על ההרצה.
• Traceonly statistics – השאילתא תורץ, אבל תוצאות השאילתא וגם ה- execution plan לא יוצגו על המסך. יוצגו רק הסטטיסטיקות על ההרצה.
• Traceonly explain – עם אופציה זו, השאילתא לא תרוץ כלל. Oracle רק יפעיל את ה- optimizer כדי לקבוע execution plan, ורק ה- execution plan יוצג על המסך.

DBMS_XPLAN

דרך נוספת שנסקור כאן, היא שימוש ב- dbms_xplan. ל- dbms_xplan אפשרויות רבות (למשל להציג מידע מה- plan_table כפי שכבר ראיהו.
אנו נציג כאן אפשרות אחת חשובה למדי: להציג execution plan של שאילתא שרצה כבר ונמצאת עדיין ב- shared pool. חשוב להדגיש כי זוהי הדרך האמינה ביותר לקבל את ה- execution plan, מכיוון שאנחנו מציגים את ה- plan האמיתי של שאילתא שהורצה על ידי האפליקציה ולא על ידינו. כל מה שצריך כדי להציג את ה- plan הוא ה- sql id שאותו ניתן להשיג בהרבה דרכים, למשל מ- v$session אם השאילתא רצה ברגע זה, או מדו"ח AWR שיש לנו.
דבר נוסף שנצטרך הוא ה- child_number. רבים שוכחים אותו, אבל ה- child number הוא חשוב מאד, מכיוון שכל child של השאילתא יכול להכיל execution plan שונה לגמרי.

על מנת להציג את ה- execution plan נריץ את השאילתא הבאה:

Select * from table(dbms_xplain.display_cursor('<sid_id>',<child_number>));

awrsqrpt.sql

דרך אחרונה שנראה, היא לשלוף את ה- execution plan מתוך ה- AWR, גם אם השאילתא כבר מזמן לא ב- shared pool. זו דרך פשוטה על ידי שימוש בסקריפט ORACLE_HOME/rdbms/admin/awrsqrpt. הסקריפט מבקש את ה- snapshots מהם להביא את המידע (ממש כמו שמריצים את awrrpt.sql) ואת ה- sql id של השאילתא. התוצר הוא קובץ text או html שמכיל את השאילתא, ה- execution plan שלה וסטטיסטיקות נוספות.

סיכום

ה- execution plan הוא מידע חשוב על שאילתות. במאמר זה ראינו מספר דרכים להציג את ה- execution plan לשאילתות שמריצים ידנית, שאילתות שכבר רצו ונמצאות בזכרון, ואפילו שאילתות שכבר לא בזכרון אבל עדיין שמורות ב- AWR.

מקווה שנהניתם.
נתראה ברשומה הבאה !
לירון אמיצי.

ניתן ליצור קשר עם לירון דרך עמוד האודות שלו: אודות

The following two tabs change content below.

• Bio
• Latest Posts

לירון אמיצי

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

Latest posts by לירון אמיצי (see all)

• ה-SQL Performance Analyzer ב-Real Application Testing - 18/12/2014
• עד כמה דינאמי יכול להיות SQL דינאמי? - 02/10/2014
• מבוא ל- Real Application Testing - 23/07/2014