15. 데이터 액세스와 조인 최적화를 넘어서

MView 직접 조회

SELECT * FROM sales_mv;
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    81 |  5346 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS FULL| SALES_MV |    81 |  5346 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

MView 간접 조회(query rewrite 이용)

ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE;

SELECT p.prod_category, c.country_id
     , SUM(quantity_sold) AS quantity_sold
     , SUM(amount_sold)   AS amount_sold
  FROM sales s
     , customers c
     , products p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
 ORDER BY p.prod_category, c.country_id
;
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    81 |  5346 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY                |          |    81 |  5346 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_MV |    81 |  5346 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

범용 쿼리 재작성 시도 실패

SELECT UPPER(p.prod_category) prod_category
     , SUM(s.amount_sold) amount_sold
  FROM sales s
  JOIN products p
    ON s.prod_id = p.prod_id
 GROUP BY p.prod_category
 ORDER BY p.prod_category
;
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     5 |   190 |   539  (11)| 00:00:07 |    |  |
|   1 |  SORT GROUP BY           |                      |     5 |   190 |   539  (11)| 00:00:07 |    |  |
|*  2 |   HASH JOIN              |                      |    72 |  2736 |   538  (11)| 00:00:07 |    |  |
|   3 |    VIEW                  | VW_GBC_5             |    72 |  1224 |   535  (11)| 00:00:07 |    |  |
|   4 |     HASH GROUP BY        |                      |    72 |   648 |   535  (11)| 00:00:07 |    |  |
|   5 |      PARTITION RANGE ALL |                      |   918K|  8075K|   494   (3)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL  | SALES                |   918K|  8075K|   494   (3)| 00:00:06 |     1 |    28 |
|   7 |    VIEW                  | index$_join$_002     |    72 |  1512 |     3  (34)| 00:00:01 |    |  |
|*  8 |     HASH JOIN            |                      |       |       |            |          |    |  |
|   9 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |    |  |
|  10 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |    |  |
-----------------------------------------------------------------------------------------------------------------
   2 - access("ITEM_1"="P"."PROD_ID")
   8 - access(ROWID=ROWID)

범용 쿼리 재작성 재시도 성공

ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk VALIDATE;
ALTER TABLE customers MODIFY CONSTRAINT customers_pk VALIDATE;

SELECT UPPER(p.prod_category) prod_category
     , SUM(s.amount_sold) amount_sold
  FROM sales s
  JOIN products p
    ON s.prod_id = p.prod_id
 GROUP BY p.prod_category
 ORDER BY p.prod_category
;
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |    81 |  3240 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                |          |    81 |  3240 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_MV |    81 |  3240 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

MView 정보 확인

SELECT refresh_method
     , refresh_mode
     , staleness
     , last_refresh_type
     , last_refresh_date
  FROM user_mviews
 WHERE mview_name = 'SALES_MV'
;

REFRESH_METHOD REFRESH_MODE STALENESS LAST_REFRESH_TYPE LAST_REFRESH_DATE
-------------- ------------ --------- ----------------- -----------------
FORCE          DEMAND       FRESH     COMPLETE          18/01/18

dbms_mview 패키지로 MView Refresh 하기

dbms_mview.refresh(list => 'sh.sales_mv,sh.cal_month_sales_mv');
dbms_mview.refresh_all_mviews(number_of_failures => :r);
dbms_mview.refresh_dependent(number_of_failures => :r, list => 'sh.sales_mv');
mathod => ( c / f / p / ? : complete / fast / PCT / force )
atomic_refresh => false : complete refresh 수행시 deltet가 아닌 truncate 하고 수행하므로 빠름.
out_of_place => true : 새 컨테이너 테이블에 direct-path-insert 후 바꿔치기  (v12.1)

MView Refresh 주기적으로 하기

ALTER MATERIALIZED VIEW sales_mv
REFRESH COMPLETE ON DEMAND
START WITH sysdate
NEXT sysdate + TO_DSINTERVAL('0 00:10:00')
;
SELECT what, interval
  FROM user_jobs
;
WHAT                                     INTERVAL
---------------------------------------- -------------------------------------
dbms_refresh.refresh('"SH"."SALES_MV"'); sysdate + TO_DSINTERVAL('0 00:10:00')

MView log

CREATE MATERIALIZED VIEW LOG ON sales     WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON products  WITH ROWID;

SELECT master, log_table
  FROM user_mview_logs
 WHERE master IN ('SALES', 'CUSTOMERS', 'PRODUCTS')
   AND log_owner = 'SH'
;
MASTER    LOG_TABLE
--------- -----------------
CUSTOMERS MLOG$_CUSTOMERS
PRODUCTS  MLOG$_PRODUCTS
SALES     MLOG$_SALES

MView log 재생성(fast refresh 를 지원하기 위함)

DROP MATERIALIZED VIEW LOG ON sales    ;
DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON products ;

CREATE MATERIALIZED VIEW LOG ON sales     WITH ROWID, SEQUENCE
(cust_id, prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID, SEQUENCE
(cust_id, country_id) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products  WITH ROWID, SEQUENCE
(prod_id, prod_category) INCLUDING NEW VALUES;

MView 재생성(COUNT(*) 추가)

DROP MATERIALIZED VIEW sales_mv;

CREATE MATERIALIZED VIEW sales_mv
REFRESH FORCE ON DEMAND
AS
SELECT p.prod_category
     , c.country_id
     , SUM(s.quantity_sold)   AS quantity_sold
     , SUM(s.amount_sold)     AS amount_sold
     , COUNT(*)               AS count_star
     , COUNT(s.quantity_sold) AS count_quantity_sold
     , COUNT(s.amount_sold)   AS count_amount_sold
  FROM sales     s
     , customers c
     , products  p
 WHERE s.cust_id = c.cust_id
   AND s.prod_id = p.prod_id
 GROUP BY p.prod_category, c.country_id
;

fast refresh 수행시간 확인

INSERT INTO products
SELECT 619 prod_id
     , prod_name
     , prod_desc
     , prod_subcategory
     , prod_subcategory_id
     , prod_subcategory_desc
     , prod_category
     , prod_category_id
     , prod_category_desc
     , prod_weight_class
     , prod_unit_of_measure
     , prod_pack_size
     , supplier_id
     , prod_status
     , prod_list_price
     , prod_min_price
     , prod_total
     , prod_total_id
     , prod_src_id
     , prod_eff_from
     , prod_eff_to
     , prod_valid
  FROM products
 WHERE prod_id = 136
;

INSERT INTO sales
SELECT 619 prod_id
     , cust_id
     , time_id
     , channel_id
     , promo_id
     , quantity_sold
     , amount_sold
  FROM sales
 WHERE prod_id = 136
;

COMMIT;

SQL> EXEC dbms_mview.refresh(list => 'sh.sales_mv', method => 'f');

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.06    -- 빠르게 refresh 되었음을 확인 할 수 있다.

병렬 쿼리

SELECT /*+ leading(t1) use_hash(t2)
           index(t1) parallel_index(t1 2)
           full(t2)  parallel(t2 2)
           pq_distribute(t2 hash, hash) */ *
  FROM t1, t2
 WHERE t1.id > 9000
   AND t1.id = t2.id + 1
;
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Pstart | Pstop | TQ    | IN-OUT | PQ Distri |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |        |       |       |        |           |
|   1 |  PX COORDINATOR                |          |        |       |       |        |           |
|   2 |   PX SEND QC (RANDOM)          | :TQ10002 |        |       | Q1,02 | P->S   | QC (RAND) |
|*  3 |    HASH JOIN BUFFERED          |          |        |       | Q1,02 | PCWP   |           |
|   4 |     PX RECEIVE                 |          |        |       | Q1,02 | PCWP   |           |
|   5 |      PX SEND HASH              | :TQ10000 |        |       | Q1,00 | P->P   | HASH      |
|   6 |       PX PARTITION HASH ALL    |          |      1 |     4 | Q1,00 | PCWC   |           |
|   7 |       TABLE ACCESS BY INDEX ROW| T1       |        |       | Q1,00 | PCWP   |           |
|*  8 |        INDEX RANGE SCAN        | T1       |      1 |     4 | Q1,00 | PCWP   |           |
|   9 |     PX RECEIVE                 |          |        |       | Q1,02 | PCWP   |           |
|  10 |       PX SEND HASH             | :TQ10001 |        |       | Q1,01 | P->P   | HASH      |
|  11 |        PX BLOCK ITERATOR       |          |        |       | Q1,01 | PCWC   |           |
|* 12 |         TABLE ACCESS FULL      | T2       |        |       | Q1,01 | PCWP   |           |
-------------------------------------------------------------------------------------------------
그림15-10

병렬 DML

ALTER SESSION DISABLE PARELLEL QUERY;
ALTER SESSION DISABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | UPDATE STATEMENT    |      |
|   1 |  UPDATE             |      |
|   2 |   TABLE ACCESS FULL | T    |
------------------------------------

병렬 DML

ALTER SESSION  ENABLE PARELLEL QUERY;
ALTER SESSION DISABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------------------------------------------
| Id  | Operation              | Name     | TQ    | IN-OUT | PQ Distri |
------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |          |       |        |           |
|   1 |  UPDATE                | T        |       |        |           |
|   2 |   PX COORDINATOR       |          |       |        |           |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S   | QC (RAND) |
|   4 |     PX BLOCK ITERATOR  |          | Q1,00 | PCWC   |           |
|   5 |      TABLE ACCESS FULL | T        | Q1,00 | PCWP   |           |
------------------------------------------------------------------------

병렬 DML

ALTER SESSION  ENABLE PARELLEL QUERY;
ALTER SESSION  ENABLE PARELLEL DML;
ALTER TABLE t PARELLEL 2;
UPDATE t SET id = id + 1;
------------------------------------------------------------------------
| Id  | Operation              | Name     | TQ    | IN-OUT | PQ Distri |
------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |          |       |        |           |
|   1 |  PX COORDINATOR        |          |       |        |           |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 | Q1,00 | P->S   | QC (RAND) |
|   3 |    UPDATE              | T        | Q1,00 | PCWP   |           |
|   4 |     PX BLOCK ITERATOR  |          | Q1,00 | PCWC   |           |
|   5 |      TABLE ACCESS FULL | T        | Q1,00 | PCWP   |           |
------------------------------------------------------------------------

삽입의 병렬화

CREATE TABLE t2 PARALLEL 2
AS
SELECT /*+ no_parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id  | Operation                | Name     | TQ    | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |          |       |        |           |
|   1 |  PX COORDINATOR          |          |       |        |           |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 | Q1,01 | P->S   | QC (RAND) |
|   3 |    LOAD AS SELECT        | T2       | Q1,01 | PCWP   |           |
|   4 |     PX RECEIVE           |          | Q1,01 | PCWP   |           |
|   5 |      PX SEND ROUND-ROBIN | :TQ10000 |       | S->P   |           |
|   6 |       TABLE ACCESS FULL  | T1       |       |        |           |
--------------------------------------------------------------------------

쿼리의 병렬화

CREATE TABLE t2 NOPARALLEL
AS
SELECT /*+ parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id  | Operation                | Name     | TQ    | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |          |       |        |           |
|   1 |  LOAD AS SELECT          | T2       |       |        |           |
|   2 |   PX COORDINATOR         |          |       |        |           |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 | Q1,00 | P->S   | QC (RAND) |
|   4 |     PX BLOCK ITERATOR    |          | Q1,00 | PCWC   |           |
|   5 |      TABLE ACCESS FULL   | T1       | Q1,00 | PCWP   |           |
--------------------------------------------------------------------------

둘다 병렬화

CREATE TABLE t2 PARALLEL 2
AS
SELECT /*+ parallel(t1) */ * FROM t1
;
--------------------------------------------------------------------------
| Id  | Operation                | Name     | TQ    | IN-OUT | PQ Distri |
--------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |          |       |        |           |
|   1 |  PX COORDINATOR          |          |       |        |           |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 | Q1,00 | P->S   | QC (RAND) |
|   3 |    LOAD AS SELECT        | T2       | Q1,00 | PCWP   |           |
|   4 |     PX BLOCK ITERATOR    |          | Q1,00 | PCWC   |           |
|   5 |      TABLE ACCESS FULL   | T1       | Q1,00 | PCWP   |           |
--------------------------------------------------------------------------

-- 12C --
CREATE /*+ parallel(2) */ TABLE t2 AS SELECT  * FROM t1;

인덱스 생성

CREATE INDEX i1 ON t1(id) PARALLEL 4;
-------------------------------------------------------------------------------
| Id  | Operation                    | Name     | TQ    | IN-OUT | PQ Distrib |
-------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT       |          |       |        |            |
|   1 |  PX COORDINATOR              |          |       |        |            |
|   2 |   PX SEND QC (ORDER)         | :TQ10001 | Q1,01 | P->S   | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE    | T1       | Q1,01 | PCWP   |            |
|   4 |     SORT CREATE INDEX        |          | Q1,01 | PCWP   |            |
|   5 |      PX RECEIVE              |          | Q1,01 | PCWP   |            |
|   6 |       PX SEND RANGE          | :TQ10000 | Q1,00 | P->P   | RANGE      |
|   7 |        PX BLOCK ITERATOR     |          | Q1,00 | PCWC   |            |
|   8 |         TABLE ACCESS FULL    | T1       | Q1,00 | PCWP   |            |
-------------------------------------------------------------------------------

인덱스 재생성

ALTER INDEX i1 REBUILD PARALLEL 4;
-------------------------------------------------------------------------------
| Id  | Operation                    | Name     | TQ    | IN-OUT | PQ Distrib |
-------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT        |          |       |        |            |
|   1 |  PX COORDINATOR              |          |       |        |            |
|   2 |   PX SEND QC (ORDER)         | :TQ10001 | Q1,01 | P->S   | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE    | T1       | Q1,01 | PCWP   |            |
|   4 |     SORT CREATE INDEX        |          | Q1,01 | PCWP   |            |
|   5 |      PX RECEIVE              |          | Q1,01 | PCWP   |            |
|   6 |       PX SEND RANGE          | :TQ10000 | Q1,00 | P->P   | RANGE      |
|   7 |        PX BLOCK ITERATOR     |          | Q1,00 | PCWC   |            |
|   8 |         INDEX FAST FULL SCAN | T1       | Q1,00 | PCWP   |            |
-------------------------------------------------------------------------------

parallel 힌트는 강제성 없음

-- 병렬도에 따른 코스트 확인 --
EXPLAIN PLAN SET STATEMENT_ID 'dop1' FOR SELECT /*+ full(t) palallel(t 1) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop2' FOR SELECT /*+ full(t) palallel(t 2) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop3' FOR SELECT /*+ full(t) palallel(t 3) */ * FROM t WHERE id > 93000;
EXPLAIN PLAN SET STATEMENT_ID 'dop4' FOR SELECT /*+ full(t) palallel(t 4) */ * FROM t WHERE id > 93000;
SELECT statement_id, cost
  FROM plan_table
 WHERE statement_id LIKE 'dop%'
   AND id = 0
;
STATEMENT_ID COST
------------ ----
dop1          296
dop2          164
dop3          110
dop4           82

-- 힌트 없이 쿼리 수행 --
SELECT * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id  | Operation                    | Name | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT             |      |   125   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |   125   (0)|
|*  2 |   INDEX RANGE SCAN           | I    |    17   (0)|
----------------------------------------------------------
-- 병렬도를 바꿔 가며 쿼리 수행 --
SELECT /*+ palallel(t 2) */ * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id  | Operation                    | Name | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT             |      |   125   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |   125   (0)|
|*  2 |   INDEX RANGE SCAN           | I    |    17   (0)|
----------------------------------------------------------
SELECT /*+ palallel(t 3) */ * FROM t WHERE id > 93000;
----------------------------------------------------------
| Id  | Operation                | Name     | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELETE STATEMENT         |          |   110   (1)|
|   1 |  PX COORDINATOR          |          |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000 |   110   (1)|
|   3 |     PX BLOCK ITERATOR    |          |   110   (1)|
|*  4 |      TABLE ACCESS FULL   | T        |   110   (1)|
----------------------------------------------------------
-- 정리하면
-- parallel 힌트는 병렬처리를 고려하도록 할 뿐 강제하지는 않는다.

direct-path insert

-- 1. append 힌트 --
INSERT /*+ append */ INTO ... SELECT ...;
INSERT ALL /*+ append */ INTO ... SELECT ...;
MERGE INTO ... NOT MATCHED THEN INSERT /*+ append */ VALUES (...);
INSERT /*+ append        */ INTO ... VALUES (...);  -- Only 11.1
INSERT /*+ append_values */ INTO ... VALUES (...);  -- 11.2 부터
-- 2. INSERT INTO SELECT 구문을 병렬로 수행
-- 3. OCI direct-path API 사용
-- 4. SQL*Loader 사용

PL/SQL 에서 프리 페칭

-- 커서를 이용한 FOR LOOP 문에서는 자동 프리페치(100 row)
BEGIN
    FOR c IN (SELECT * FROM t)
    LOOP
        -- 데이터 처리
        NULL;
    END LOOP;
END;
-- 다른 유형의 커서에서는 bulk collect into 사용
DECLARE
    TYPE t_t IS TABLE OF t%ROWTYPE;
    l_t t_t;
BEGIN
    SELECT * BULK COLLECT INTO l_t FROM t;
    FOR i IN l_t.FIRST..l_t.LAST
    LOOP
        -- 데이터 처리
        NULL;
    END LOOP;
END;
/
DECLARE
    CURSOR c IS SELECT * FROM t;
    TYPE t_t IS TABLE OF t%ROWTYPE;
    l_t t_t;
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO l_t LIMIT 100;
        EXIT WHEN l_t.COUNT = 0;
        FOR i IN l_t.FIRST..l_t.LAST
            -- 데이터 처리
            NULL;
        END LOOP;
    END LOOP;
    CLOSE c;
END;
/

PL/SQL 에서 배열처리

DECLARE
    TYPE t_id  IS TABLE OF t.id%TYPE;
    TYPE t_pad IS TABLE OF t.pad%TYPE;
    l_id  t_id  := t_id();
    l_pad t_pad := t_pad();
BEGIN
    -- 데이터 준비
    l_id.EXTEND(100000);
    l_pad.EXTEND(100000);
    FOR i IN 1..100000
    LOOP
        l_id(i)  := i;
        l_pad(i) := RPAD('*', 100, '*');
    END LOOP;
    -- 데이터 입력
    FORALL i IN l_id.FIRST..l_id.LAST
        INSERT INTO t VALUES (l_id(i), l_pad(i));
END;

-- FORALL 이 사용되었다는 것이 루프를 의미하지는 않는다
-- 단 한번의 호출로 모든 로우가 전달된다.