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

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

טבלאות זמניות

2 חידושים עיקריים עבור הטבלאות הזמניות:

• Temporary Undo
• Session level statistics on Global Temporary Tables

Temporary Undo

ממש כמו ב- SQL Server (טבלאות מסוג #table או ##table), ה- Undo עבור טבלאות זמניות יכול להיות מנוהל ב- temporary tablespace ולא ב- undo tablespace. היתרונות הם:

• הקטנת כמו ה- Redo
• הקטנת כמו ה- UNDO הנוצרת ב- undo tablespace ושמירת ה- retention לטובת מידע אמיתי.
• ביצוע פקודות DML לטובת בטבלאות זמניות עבור Active Data Guard.

ניתן לקבוע זאת ברמת system או session באופן הבא:

ALTER SYSTEM/SESSION SET TEMP_UNDO_ENABLED=true|false

בבדיקות שערכתי לא תמיד כמות ה- redo באמת קטנה משמעותית הינה דוגמא על ה- partitioned table בשם sales. הנה קטע שמדגים זאת. דגשים: אני משתמש ב set autotrace on בשביל לראות את כמות ה- redo שנוצרה. אני מבצע commit כדי לרוקן את הטבלה הזמנית ( שהוגדרה כ on commit delete rows):

-- COMMIT DELETE ROWS to indicate the data should be deleted at the end of the transaction
create global temporary table gt_sales
on commit delete rows
as
select * from SH.SALES where 1=0;

--Temporary tables are designated with a Y in the TEMPORARY column. Regular tables contain ---- an N in the TEMPORARY column.
select table_name, temporary from user_tables;

alter session set temp_undo_enabled = false;

-- Turning on statistics tracing and viewing the redo size as you
-- insert records into a temporary table:

set autotrace on;
insert into gt_sales select * from SH.SALES ;
-- Output as follows
--Statistics
----------------
--    32485 redo size

-- Check the redo in the output
-- Do a commit so the table becomes empty
commit;
set autotrace off;
select * from gt_sales ;
alter session set temp_undo_enabled = true;
set autotrace on;
-- insert records into a temporary table:
insert into gt_sales select * from SH.SALES ;
-- Output as follows
--Statistics
----------------
--    250000 redo size
commit;

Session level statistics on Global Temporary Tables

ניתן כעת לשמור סטטיסטיקות ברמה של session בטבלה זמנית. בגרסה 11g הסטטיסטיקה משותפת וברור שזה היווה בעיה כאשר אופי המידע היה שונה בין ה- sessions השונים.

קטע הקוד והפלט להלן:

SQL> connect sh/password@localhost:1521/GISDB
Connected.

SQL> drop table gt_sales;
Table dropped.

SQL> create global temporary table gt_sales
2  on commit delete rows
3  as
4  select * from SH.SALES
5 ;
Table created.

SQL> -- get table pref
SQL> select dbms_stats.get_prefs ('GLOBAL_TEMP_TABLE_STATS','SH','gt_sales') from dual;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','SH','GT_SALES')
--------------------------------------------------------------------------------
SESSION

SQL> -- Change table pref to SHARED
SQL> begin
2 dbms_stats.set_table_prefs ('SH','gt_sales','GLOBAL_TEMP_TABLE_STATS','SHARED') ;
3 end;
4 /
PL/SQL procedure successfully completed.

SQL> -- get table pref
SQL> select dbms_stats.get_prefs ('GLOBAL_TEMP_TABLE_STATS','SH','gt_sales') from dual;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','SH','GT_SALES')
--------------------------------------------------------------------------------
SHARED

SQL>

Dynamic Statistics

 Dynamic Statistics  ב- Oracle 12c הוא אבולוציה של dynamic sampling מגרסת 11G. העקרונות:

• בזמן קומפילציה האופטימיזר יכול להחליט אם הסטטיסטיקה מספיקה ליצר תוכנית ביצוע טובה או לא.
• משתמשים ב- Dynamic Statistics כאשר סטטיסטיקה חסרה, למשל כאשר יוצרים אובייקט חדש,  או לא מעודכנת ( stale).
•  האופטמיזר משתמש ב Dynamic Statistics לא רק לגישה לטבלה אלא גם עבור פרדיקטים של GROUP By.
• לפרמטר האתחול OPTIMIZER_DYNAMIC_SAMPALING יש רמה חדשה 11.ברמה זו האופטימיזר מחליט מתי להשתמש ב Dynamic statistics וזה תלוי בסיבוביות הפרדיקט, סטטיסטיקה קיימת וזמן הביצוע של השאילתה. דוגמא עבור שאילתות שמשתמשות
• Dynamic Statistics משותף בן השאילתות על ידי SQL Plan Directives (חדש ב- Oracle12c) ראה בהמשך.

קטע הקוד המתאים והפלט להלן. תחת Note תוכלו לקבל חיווי שאכן היה שימוש ב- Dynamic Statistics.

SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
SQL> explain plan for
2 Select P.PROD_NAME,P.PROD_DESC
3 from SH.PRODUCTS P
4 where P.PROD_CATEGORY_ID in (17,18)
5 and P.PROD_NAME like '%HD%';
Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1954719464
------------------------------------------------------------------------------
| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time   |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |          |   1 |  58 |   3  (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PRODUCTS |   1 |  58 |   3  (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter(("P"."PROD_CATEGORY_ID"=17 OR "P"."PROD_CATEGORY_ID"=18)
AND "P"."PROD_NAME" LIKE '%HD%')

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

18 rows selected.

SQL>

עד כאן למאמר הזה.

להתראות,

ד. יצחק

The following two tabs change content below.

• Bio
• Latest Posts

דוד יצחק

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

Latest posts by דוד יצחק (see all)

• MongoDB ל DBA ומפתחים הלכה למעשה – חלק ב - 16/02/2016
• MongoDB ל DBA ומפתחים הלכה למעשה - 07/02/2016
• בדיקת ביצועים של Clustered ColumnStore Index - 06/11/2014