Materialized view สุดยอดอาวุธลับบน Oracle DBMS

เคยเจอปัญหาแบบนี้กันไหมครับ? มีลูกค้าโทรเข้ามาว่า “งานออนไลน์ช้ามากกกกกกกกกกกกกกก” ส่วนเราเองก็ตรวจสอบปัญหาแล้วพบว่าปัญหามันเกิดจากคิวรี่เดิมที่อาจจะใช้ทรัพยากรเยอะพอสมควร ที่ถูกเรียกซ้ำๆ เพราะเป็นงานปกติของลูกค้า นอกจากการปรับจูนคิวรี่แล้ว เราจะหาทางแก้ไขมันอย่างไรได้อีกนะ? วันนี้ผมเอาอาวุธลับอีกตัวมาเสนอขายทุกท่านครับ นั่นคือ Mview หรือชื่อเต็มของมันเรียกว่า Materialized view นั่นเอง

Materialized view คืออะไรหรอ?

เจ้าตัว Materialized view หรือชื่อเก่าที่ทุกคนรู้จักกันคือ Snapshot คือตารางที่ประกอบไปด้วยชุดข้อมูลที่เป็นผลลัพธ์จากคิวรี่ที่เราต้องการ โดยจะทำการ refresh ข้อมูล (แล้วแต่เราต้องการ) เพื่อให้ข้อมูลตรงกับการคิวรี่ตรงๆ เสมอ โดยจะทำหน้าที่คล้าย View แต่แตกต่างกันที่มันจำเป็นต้องมีพื้นที่เก็บข้อมูลของ Materialized view เอง และประโยชน์ของมันไม่ใช่แค่ช่วยให้ประสิทธิภาพการทำงานของฐานข้อมูลดีขึ้นเท่านั้น มันยังถูกใช้ในการทำ Data replication ระหว่างฐานข้อมูลอย่างง่ายๆ ได้อีกด้วย

เพื่อไม่เป็นการเสียเวลา บทความนี้ผมจะแสดงให้ดูกันว่าเจ้า Materialized view ช่วยทำให้ประสิทธิภาพบนฐานข้อมูลดีขึ้นอย่างไร?

ก่อนจะเริ่ม:
เมื่อคุณกำลังจะใช้ Mview เพื่อช่วยเพิ่มประสิทธิภาพให้กับคิวรี่นั้น พารามิเตอร์ QUERY_REWRITE_INTEGRITY และ QUERY_REWRITE_ENABLED ต้องถูกกำหนดไว้ใน PFILE หรือ SPFILE เสมอ

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      TRUSTED

ขั้นแรกผมจะทำการสร้างตารางจำลองขึ้นมาก่อนนะครับ โดยกำหนดให้ตารางชื่อ T มีจำนวนทั้งสิ้น 10,000 บรรทัด

SQL> CREATE USER USER_MV IDENTIFIED BY USER_MV;

User created.

SQL> GRANT CONNECT,RESOURCE,CREATE MATERIALIZED VIEW TO USER_MV;

Grant succeeded.

SQL> CONN USER_MV/USER_MV
Connected.
SQL> create table T
  2  as
  3  select rownum id,
  4                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  5                 OBJECT_ID, DATA_OBJECT_ID,
  6                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  7                 TIMESTAMP, STATUS, TEMPORARY,
  8                 GENERATED, SECONDARY
  9    from all_objects a
 10  where 1=0
 11  /

Table created.

SQL> alter table T nologging;

Table altered.

SQL> declare
  2      l_cnt number;
  3      l_rows number := & 1;
  4  begin
  5      insert /*+ append */
  6      into T
  7      select rownum,
  8                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  9                 OBJECT_ID, DATA_OBJECT_ID,
 10                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 11                 TIMESTAMP, STATUS, TEMPORARY,
 12                 GENERATED, SECONDARY
 13        from all_objects a
 14       where rownum <= & 1;
 15
 16      l_cnt := sql% rowcount;
 17
 18      commit;
 19
 20      while (l_cnt < l_rows)
 21      loop
 22          insert /*+ APPEND */ into T
 23          select rownum+l_cnt,
 24                 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 25                 OBJECT_ID, DATA_OBJECT_ID,
 26                 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 27                 TIMESTAMP, STATUS, TEMPORARY,
 28                 GENERATED, SECONDARY
 29            from T
 30           where rownum <= l_rows-l_cnt;
 31          l_cnt := l_cnt + sql %rowcount;
 32          commit;
 33      end loop;
 34  end;
 35  /
Enter value for 1: 10000
old   3:     l_rows number := & 1;
new   3:     l_rows number := 10000;
Enter value for 1: 10000
old  14:      where rownum <= & 1;
new  14:      where rownum <= 10000;

PL/SQL procedure successfully completed.

SQL> alter table T add constraint
  2  PK_T primary key(id)
  3  /

Table altered.

SQL> BEGIN
  2     DBMS_STATS.GATHER_TABLE_STATS
  3     ( OWNNAME    => USER,
  4       TABNAME    => 'T' ,
  5       CASCADE    => TRUE );
  6  END;
  7  /

PL/SQL procedure successfully completed.

ถ้าเราทำการคิวรี่จากตารางนี้โดยตรงจะได้ execution plan หน้าตาแบบข้างล่างนี้ครับ

  
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(*) FROM T;

Execution Plan
----------------------------------------------------------
Plan hash value: 949213647

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_T | 10000 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------

ต่อมาผมจะทำการสร้าง Mview เพื่อเข้ามาช่วยในกรณีสมมุติที่ลูกค้าแจ้งเรามาว่า งานออนไลน์(คิวรี่ในตัวอย่าง) ทำงานช้ามาก แต่ก่อนที่จะสร้าง Mview เราจำเป็นต้องสร้าง Mview เพื่อทำให้สามารถใช้ Fast refresh ได้ครับ

  
SQL> CREATE MATERIALIZED VIEW LOG ON T
  2  WITH PRIMARY KEY
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T
  2  BUILD IMMEDIATE
  3  REFRESH FORCE
  4  ON COMMIT
  5  ENABLE QUERY REWRITE
  6  AS
  7  SELECT COUNT(*) FROM T;

Materialized view created.

จากด้านบนจะเห็นว่าผมใช้ Refresh method เป็น FORCE นั่นคือ ครั้งแรกฐานข้อมูลจะทำ FAST REFRESH ก่อนถ้าไม่สามารถทำได้จะทำ COMPLETE REFRESH ให้แทนนั่นเองครับ และทำการ Refresh ทุกๆ ครั้งที่มีการ Commit ครับ

  
SQL> SELECT OWNER,MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD
  2  FROM USER_MVIEW_ANALYSIS;

OWNER                          MVIEW_NAME                     LAST_REFRESH_DATE   REFRESH_METHOD
------------------------------ ------------------------------ ------------------- --------
USER_MV                        MV_T                           2015-02-17 14:25:20 FORCE

และจากนั้นก็ทำการ Gather statistics แบบหล่อๆ ซักรอบหนึ่งพอเป็นพิธี

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MV_T');

PL/SQL procedure successfully completed.

จากนั้นเรามาดูครับว่าจะเกิดอะไรขึ้นเมื่อเราทำการคิวรี่ Base table ชื่อ T ที่สร้างมาตอนแรก

SQL> SET AUTOTRACE TRACE EXP
SQL> SELECT COUNT(*) FROM T;

Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     3 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T |     1 |     3 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

เฮ้ย! Execution plan เปลี่ยนมาคิวรี่จาก Mview แทนแล้ว! เอ๊ะ! เดี๋ยวๆ ไหนลองแกล้งทำเป็น Insert ข้อมูลซิ ดูจาก Mview จะ Refresh ให้ไหม?

SQL> INSERT INTO T (ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME)
  2  VALUES (10001,'USER_MV','USER_MV','USER_MV',10001,SYSDATE,SYSDATE);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM MV_T;

  COUNT(*)
----------
     10001

สุดยอดไปเลยลวกเพ่! นี่แหละครับคืออาวุธลับที่ทุกคนสามารถนำมาใช้กันได้ครับ

ข้อควรระวัง
– ควรวางแผนให้ดีนะครับเวลาเลือก Refresh method เพราะการ Refresh on commit นั้น ในระบบใหญ่ๆ สามารถทำให้ประสิทธิภาพโดยรวมแย่ลงมากกว่าที่จะดีขึ้นได้นะครับ ระวังกันด้วยนะ

ขอบคุณข้อมูลดีๆ จาก
Oracle-Base

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out /  เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out /  เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out /  เปลี่ยนแปลง )

Connecting to %s