כיוונן של Oracle 12c הלכה למעשה - חלק א - ilDBA Portal

כיוונן של Oracle 12c הלכה למעשה – חלק א

12/11/2013 | פורסם על ידי

בסקירה הבאה נעבור על כמה מהחידושים של ה- optimizer בגרסת Oracle 12c באופן עניני ומעשי, עם קצת פחות דגש על הצד התיאורטי. אני אשתדל לתת דוגמאות מהסביבה שלנו. לצורך המחשת הנושאים אני משתמש בדוח HTML של SQLTXPLAIN, שהוא כלי מצוין בשימוש התמיכה של אורקל. רקע וצורת העבודה עם הכלי המצוין ניתן למצוא ב- notes הבאים של תמיכת Oracle:

  • 215187.1 SQLTXPLAIN (SQLT) Tool Download
  • 1454160.1 SQLTXPLAIN (SQLT) FAQ

מידע נוסף על הכלי אפשר לקבל מתקציר ההרצאה שלי בפורום משתמשי אורקל צפון מ-22.10.2013. שם ההרצאה:

ORACLE 12c, 11gR2 recipes for the busy DBA

תאור הסביבה:

סביבת הבדיקות שלי היא Oracle 12c R1 שמותקן על גבי Win 2012 עם  Oracle Multitenant DBעל פי הבא:

  • ORACLE_HOME: C:\oracle\product\12.1.0
  • TNS Listener port: 1521
  • Container databases:
    • SID: CDB
  • Pluggable databases (in CDB):
    • GISDB

מחיצת קבצי ה- data של ה- CDB נראית כך:

 tuning12c_1

מחיצת קבצי ה- Data של ה- Pluggable databases נראית כך: tuning12c_3

ה"מינימום" ה- Services הרלבנטיים של Oracle לצורך עבודה הם להלן ובשרטוט הבא. כל השאר יכולים להיות בסטאטוס stopped ו- disabled:

tuning12c_4

  • OracleServiceCDB – עבור ה- Oracle Instance.שימו לב שנוצר Service עבור ה- CDB בלבד ולא עבור כל Pluggable databases.
  • OracleOraDB12Home1TNSListener – עבור ה- Listener
  • OracleJobSchedulerCDB – עבור Oracle Scheduler

הערה: שימו לב שב- Win 2012 מטעמי אבטחה ה- Services של Oracle הנ"ל צריכים לרוץ תחת user ב- Domain. מינימום ההרשאות הנדרשות למשתמש הזה:

  • Log On as batch בשרת
  • Log On as a serviceבשרת
  • Local admin בשרת
  • חבר בקבוצה הלוקלית ORA_DBA

בסביבת העבודה שלנו ה- services הנ"ל ירוצו תחת local system account.

הפניה ל- PDB בשם GISDB נעשית באופן הבא על ידי שימוש ב- easy connect. דוגמא:

SQL> connect sys/password@localhost:1521/GISDB as sysdba

השתמשתי בסכמות הדוגמא של oracle (SH) שמותקנים ב- tablespace בשם user. לצורך פשטות נתתי למשתמש זה את ההרשאות הבאות של DBA ROLE ברמת Pluggable databases:

-- USER SQL 
ALTER USER "SH" DEFAULT TABLESPACE "EXAMPLE" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ; 
-- ROLES 
GRANT "DBA" TO "SH" ; 
ALTER USER "SH" 
DEFAULT ROLE "RESOURCE","SELECT_CATALOG_ROLE";

שימו לב שאם הייתי רוצה לתת למשתמש זה יכולת ניהול ברמת CDB הייתי נותן לו CDB_DBA Role באופן הבא:

-- ROLES 
GRANT "CDB_DBA" TO "SH"; 
GRANT "DBA" TO "SH"; 
ALTER USER "SH" DEFAULT ROLE "RESOURCE","SELECT_CATALOG_ROLE";

מושגים בסיסים של סביבת ה- Cost Based Optimizer (CBO)

נעבור על פי מספר מושגים בסיסים של סביבת ה- Cost Based Optimizer (CBO) לפני ש"נצלול" בהמשך.

Cardinality ו- Selectivity

ה-Cardinality מוגדר כמספר השורות שיחזרו משאילתה. מכוון שה- Optimizer לא באמת יכול לדעת את התשובה הוא יכול רק לנחש אותה בהתחשב בנתונים מסויימים והנחות מסוימות. הניחוש יכול להיות טוב ומדוייק או גרוע והוא תלוי בסטטיסטיקה הקיימת ובמה שנקרא skewness (ראו בהמשך). שימוש בהיסטוגרמות יכול לעזור בניחוש זה (ראו בהמשך). גם אם אספנו 100% סטטיסטיקה על הטבלה עדין ה- Optimizer מבצע ניחוש כי המידע בטבלה עלול להשתנות או להיות skewed.

לדוגמא: נניח שהרצנו את sqltxtract של SQLTXPLAIN על השאילתה הבאה. התוצאה היא 91969.

SQL> select count(*) from dba_objects;

פלט:

COUNT(*)
----------
91969

בראש הדוח html של SQLT (בדוגמא sqlt_s12950_main.html), נלחץ על הקישור Execution Plans ונגיע לחלק הבא. אם תסתכל בשלב TABLE ACCESS FULL OBJ$ תחת העמודה Estim Card מופיע הערך 92477. המשמעות: ה- CBO ציפה לקבל 92477 רשומות. מעשית יש 91969 רשומות.

ה-Selectivity זהה ל- Cardinality אבל בסקלה של 0 עד 1.  אם נניח שמספר הרשומות על פי הסטטיסטיקה של הטבלה הוא 92488 (השדה Num Rows תחת Column Statistics). ה- Cardinality עבור הפרדיקט של השאילתה (השדה Equality Predicate Cardinality תחת Column Statistics) הוא 2721. אזי ה- selectivity (השדה Equality Predicate Selectivity תחת Column Statistics) , כלומר ההסתברות לקבל 2721 רשומות מהשאילתה שווה ל 2721/92488=0.02942.

פעולות Join

סוג ה- JOIN והמחיר של כל JOIN ניתן לקבוע מקובץ 10553 trace file, שהוא חלק מהפלט של SQLTXPLAIN. בדרך כלל 4 סוגים עיקריים של JOIN. ניתן להלן בקצרה:

HASH JOINS (HJ)- לטבלה הקטנה מבוצע hash ומוכנסת לזיכרון. הטבלה הגדולה נסרקת על ידי הערכים שמתאימים לערכי ה- hash בזיכרון. סוג join זה הוא גרוע ולא יעיל כאשר:

  • אם הטבלה הגדולה יותר מוכנסת לזיכרון (למשל בגלל סטטיסטיקות לא נכונות)
  • אם הטבלאות לא גדולות (כי אז היו משתמשים ב- NESTED LOOP).

NESTED LOOP (NL) – טוב כאשר הטבלאות קטנות. יש כאלה אשר נותנים עדיפות לאינדקסים ו- NL על ידי עדכון הפרמטרים הבאים של האופטמיזר: Optimizer_index_cost_adj ו optimizer_

index_caching  (דוגמא בהמשך). בדרך כלל זוהי אסטרטגיה לא נכונה ויש להשאירם על ערך בררת מחדל שלהם 100 ו 0 בהתאמה.

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

SORT MERGE JOINS (SMJ) – JOIN שמבוצע בזיכרון אם מתאפשר – החיסרון של Join זה הוא ששתי הטבלאות צריכות להיות ממוינות.

אם ה- Cardinality הוא גבוה נצפה לראות joins מסוג SMJ ו- SH.

Adaptive Query Optimization

השינוי הגדול באופטימיזר של Oracle 12c הוא Adaptive Query Optimization שמורכב מ:

  • Adaptive Statistics מורכב מ- Dynamic Statistics, SQL Plan Directives ו-Automatic Reoptimizations,
  • Adaptive Plans

Adaptive Statistics

פרמטרי האתחול  של Adaptive Query Optimization

א.      פרמטר האתחול OPTIMIZER_ADAPTIVE_FEATURES, שערך בררת מחדל שלו הוא TRUE, מאפשר להשתמש ב- Adaptive Query Optimization.

ב.      הפרמטר OPTIMIZER_ADAPTIVE_REPORTING_ONLY (בררת מחדל FALSE), כאשר הוא TRUE מפעיל את OPTIMIZER_ADAPTIVE_REPORTING_ONLY לצורך דיווח בלבד עבור execution plan לצורכי הגירה של שאילתה לסביבת 12c לראות כיצד היא תושפע באופן הבא:

SELECT *
FROM table(dbms_xplan.display_cursor(format=>'report'))

 

Online System gathering

ב- Oracle 12c סטטיסטיקות נאספות כחלק ישיר מפעולת הטעינה הבאות:

  • Create Table As Select (CAS)
  • Insert as select (IAS)

שים לב שעבור IAS נדרש שהטבלה תהיה ריקה. בדוגמא הבא בסכימה הדוגמא SH, ניצור טבלה חדשה בשם SALES2 על ידי CTAS ונרשום לעצמנו את הזמן.שימו לב שהסטטיסטיקות זמינות מיד אחרי הטעינה (ראה העמודה notes והעמודה LAST_ANALYZED של USER_TAB_COL_STATISTICS). מאידך אין היסטוגרמות לעמודות (העמודה HITOGRAMS) ויש להשתמש בפרוצדורה GATHER_TABLE_STATS עם האופציה AUTO.

tuning12c_8

קטע הקוד המתאים:

SQL> drop table "SH"."SALES2" cascade constraints PURGE;
Table dropped.

SQL> Select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') clock_time From dual;

CLOCK_TIME
--------------------
17-OCT-2013 23:09:17

SQL> -- CTAS command
SQL> CREATE TABLE SH.SALES2 as SELECT * FROM SH.SALES;
Table created.

SQL> SELECT table_name,num_rows, TO_CHAR( LAST_ANALYZED,'DD-MON-YYYY HH24:MI:SS') LAST_ANALYZED from user_tables where table_name='SALES2';

עד כאן למאמר הזה. במאמר הבא נדבר על Global temporary tables, שינויים ב-undo ובסטטיסטיקות דינמיות…

להתראות,

ד. יצחק

The following two tabs change content below.

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

3 תגובות ל- “כיוונן של Oracle 12c הלכה למעשה – חלק א”

[…] במאמר זה נמשיך את הסקירה שהתחלנו על פיצ’רים חדשים הקשורים לאופטימייזר בגרסה 12c. המאמר הזה הוא המשך ישיר של מאמר קודם שפרסמנו פה אתמול: כיוונן של Oracle 12c הלכה למעשה – חלק א. […]

[…] הלכה למעשה”, והוא ידון בנושא Skewness. תזכורת למאמרים: חלק א, חלק ב, חלק […]

[…] הלכה למעשה”, והוא ידון בנושא Skewness. תזכורת למאמרים: חלק א, חלק ב, חלק […]

השאר תגובה:

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

*



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

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]
מבוא

מבוא לבעיות ביצועים באורקל

The following two tabs change content below.BioLatest Posts עודד רז עודד רז, מנכ"ל חברת בריליקס ומייסד אתר זה. עודד הוא Oracle ACE Director ואחד מה-DBA-ים הבכירים ביותר בישראל, עם מעל 15 שנות ניסיון כ-DBA תשתיתי ואפליקטיבי. לעודד [...]

ה-SQL Performance Analyzer ב-Real Application Testing

בפוסט הקודם הזכרתי בקצרה את ה-SQL Performance Analyzer, בפוסט הזה אני אציג את ה-SPA, יתרונות וחסרונות של המוצר ואיך כדאי להשתמש [...]

מבוא ל- Real Application Testing

הפעם רציתי לסקור feature שלם שנקרא RAT (או בשמו המלא Real Application Testing). ה- feature הזה הוא database option של Oracle Enterprise Edition והוא לא חדש בכלל. הוא הוצג ב- 11gR1 וגם נמצא בשימוש לא מועט בעולם. משום מה, אצלנו בארץ לא יוצא [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: