by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




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

15.1 머터리얼라이즈드 뷰

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 |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|   1 |  SORT GROUP BY           |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|*  2 |   HASH JOIN              |                      |   968 | 54208 |       |  2273   (3)| 00:00:28 |       |       |
|   3 |    VIEW                  | index$_join$_003     |    72 |  1512 |       |     3  (34)| 00:00:01 |       |       |
|*  4 |     HASH JOIN            |                      |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |    VIEW                  | VW_GBC_9             |   968 | 33880 |       |  2270   (3)| 00:00:28 |       |       |
|   8 |     HASH GROUP BY        |                      |   968 | 26136 |       |  2270   (3)| 00:00:28 |       |       |
|*  9 |      HASH JOIN           |                      |   918K|    23M|  1200K|  2228   (2)| 00:00:27 |       |       |
|  10 |       TABLE ACCESS FULL  | CUSTOMERS            | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |
|  11 |       PARTITION RANGE ALL|                      |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
|  12 |        TABLE ACCESS FULL | SALES                |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------------

   2 - access("ITEM_1"="P"."PROD_ID")
   4 - access(ROWID=ROWID)
   9 - access("S"."CUST_ID"="C"."CUST_ID")
CREATE MATERIALIZED VIEW sales_mv
AS
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
;
M-View 에서의 ORDER BY
  • M-View 생성시에만 정렬이 유효함
  • M-View refresh 할때는 정렬 무효
  • M-View 구문에 정렬이 제외되어 저장되기 때문
M-View 구성
  • Mview Object 와 Container table 로 구성
    • MView Object : 데이터 사전상 오브젝트로 그저 메타데이터일 뿐
    • Container table : 실제 데이터가 저장되는 일반 힙테이블로 MView 와 동일한 이름
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 |
---------------------------------------------------------------------------------
query rewrite 제약
1. Enterprise Edition
2. ALTER SESSION SET query_rewrite_enabled = true;
3. ALTER MATERIALIZED VIEW sales_mv ENABLE QUERY REWRITE;
4. 원본 데이터의 변경시 stale(유효하지 않은, 과거의) 상태가 되면 쿼리재작성 사용 불가.(refresh 후 사용 가능)
※ 힌트 제어 가능 : rewrite, no_rewrite
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 |
------------------------------------------------------------------------------------------
옵티마이져의 query rewrite 방법
1. 전체 문장 일치(full-text-match) 쿼리 재작성
2. 부분 문장 일치(partial-text-match) 쿼리 재작성 : 일부 컬럼만 사용
3. 범용(general) 쿼리 재작성 : 문맥의 의미를 분석하여 판단
범용 쿼리 재작성 시도 실패
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 |
------------------------------------------------------------------------------------------

refresh

리프레시(refresh)
  • 어떻게 수행할지?
    • refresh complete : 전체 삭제 후 재생성
    • refresh fast : 기존 자료 재사용, 변경 데이터만 반영
    • refresh force : fast refresh 시도후 불가능하면 complete refresh 수행
    • nevwr refresh : refresh 금지
  • 언제 수행할지?
    • on demand : 명시적 요청에 의해서 수행
    • on commit : 기반 테이블 변경 완료(commit)시 수행
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')

MATERIALIZED VIEW LOG

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
fast refresh 요건
  • 기본적인 materialized view log 생성만으로는 fast refresh 지원 못함
  • including new values : 변경 전,후 값 로그 기록
  • 시퀀스 추가
  • COUNT 추가
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 되었음을 확인 할 수 있다.
파티션 변경 추적 (PCT, Partition Change Tracking) refresh
  • 파티션 추가, 삭제 작업시 뷰로그를 이용한 fast refresh 가 불가능 하다.
  • 변경된 파티션만 refresh
  • PCT REFRESH 가 가능하려면 MView 가 다음 중 하나를 포함해야 한다.
    • 파티션키
    • ROWID
    • Partition Marker
    • 조인 의존 표현식(join-dependent expression)
MView 사용시기
  • 대규모 집계나 조인에서 논리적 읽기 횟수와 반환되는 로우 개수의 비율이 아주 크게 차이 날 경우 성능 개선을 위해서 사용한다.
  • 단일 테이블에 대한 full table scan 과 index trange scan 모두 만족스런 성능이 안나올 때 성응개선을 위해 사용한다. 기본적으로 이런 경우엔 파티션닝을 활용하면 되지만 파티셔닝을 사용하지 목할 경우 대안으로 MView 사용.
MView 함정
  • fast refresh 가 항상 complete refresh 보다 빠른 것은 아니다.
    • 대량의 데이터 변경
  • 뷰로그를 유지관리하는데 소요되는 오버헤드

15.2 결과 캐싱

캐싱
  • 데이터 블록 ==> 버퍼 캐시
  • 데이터 딕셔너리 ==> 딕셔너리 캐시
  • 커서 ==> 라이브러리 캐시
  • 결과 ==> 결과 캐시, 11GR1, Enterprise Edition

서버측 결과 캐시

SQL> SELECT p.prod_category
  2       , c.country_id
  3       , SUM(s.quantity_sold) AS quantity_sold
  4       , SUM(s.amount_sold  ) AS amount_sold
  5    FROM sales     s
  6       , customers c
  7       , products  p
  8   WHERE s.cust_id = c.cust_id
  9     AND s.prod_id = p.prod_id
 10   GROUP BY p.prod_category, c.country_id
 11   ORDER BY p.prod_category, c.country_id
 12  ;

PROD_CATEGORY                                      COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics                                             52769          3247   405416.65
...
Software/Other                                          52790        237071  8027258.63

81 개의 행이 선택되었습니다.

경   과: 00:00:01.87

Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|   1 |  SORT GROUP BY           |                      |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|*  2 |   HASH JOIN              |                      |   968 | 54208 |       |  2273   (3)| 00:00:28 |       |       |
|   3 |    VIEW                  | index$_join$_003     |    72 |  1512 |       |     3  (34)| 00:00:01 |       |       |
|*  4 |     HASH JOIN            |                      |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |    VIEW                  | VW_GBC_9             |   968 | 33880 |       |  2270   (3)| 00:00:28 |       |       |
|   8 |     HASH GROUP BY        |                      |   968 | 26136 |       |  2270   (3)| 00:00:28 |       |       |
|*  9 |      HASH JOIN           |                      |   918K|    23M|  1200K|  2228   (2)| 00:00:27 |       |       |
|  10 |       TABLE ACCESS FULL  | CUSTOMERS            | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |
|  11 |       PARTITION RANGE ALL|                      |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
|  12 |        TABLE ACCESS FULL | SALES                |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------------

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

   2 - access("ITEM_1"="P"."PROD_ID")
   4 - access(ROWID=ROWID)
   9 - access("S"."CUST_ID"="C"."CUST_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3185  consistent gets
          0  physical reads
          0  redo size
       2973  bytes sent via SQL*Net to client
        470  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         81  rows processed

SQL> SELECT /*+ result_cache */
  2         p.prod_category
  3       , c.country_id
  4       , SUM(s.quantity_sold) AS quantity_sold
  5       , SUM(s.amount_sold  ) AS amount_sold
  6    FROM sales     s
  7       , customers c
  8       , products  p
  9   WHERE s.cust_id = c.cust_id
 10     AND s.prod_id = p.prod_id
 11   GROUP BY p.prod_category, c.country_id
 12   ORDER BY p.prod_category, c.country_id
 13  ;

PROD_CATEGORY                                      COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics                                             52769          3247   405416.65
...
Software/Other                                          52790        237071  8027258.63

81 개의 행이 선택되었습니다.

경   과: 00:00:01.76

Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                            |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|   1 |  RESULT CACHE             | 4fq786v0sq49wgux1y518akv7q |       |       |       |            |          |       |       |
|   2 |   SORT GROUP BY           |                            |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|*  3 |    HASH JOIN              |                            |   968 | 54208 |       |  2273   (3)| 00:00:28 |       |       |
|   4 |     VIEW                  | index$_join$_003           |    72 |  1512 |       |     3  (34)| 00:00:01 |       |       |
|*  5 |      HASH JOIN            |                            |       |       |       |            |          |       |       |
|   6 |       INDEX FAST FULL SCAN| PRODUCTS_PK                |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |       INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX       |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   8 |     VIEW                  | VW_GBC_9                   |   968 | 33880 |       |  2270   (3)| 00:00:28 |       |       |
|   9 |      HASH GROUP BY        |                            |   968 | 26136 |       |  2270   (3)| 00:00:28 |       |       |
|* 10 |       HASH JOIN           |                            |   918K|    23M|  1200K|  2228   (2)| 00:00:27 |       |       |
|  11 |        TABLE ACCESS FULL  | CUSTOMERS                  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |
|  12 |        PARTITION RANGE ALL|                            |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
|  13 |         TABLE ACCESS FULL | SALES                      |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
--------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("ITEM_1"="P"."PROD_ID")
   5 - access(ROWID=ROWID)
  10 - access("S"."CUST_ID"="C"."CUST_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=4; dependencies=(SH.SALES, SH.PRODUCTS, SH.CUSTOMERS); parameters=(nls); name="SELECT /*+ result_cache */
       p.prod_category
     , c.country_id
     , SUM(s.quantity_sold) AS quantity_sold
     , SUM(s."



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3185  consistent gets
          0  physical reads
          0  redo size
       2973  bytes sent via SQL*Net to client
        470  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         81  rows processed

SQL> /

PROD_CATEGORY                                      COUNTRY_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ------------- -----------
Electronics                                             52769          3247   405416.65
...
Software/Other                                          52790        237071  8027258.63

81 개의 행이 선택되었습니다.

경   과: 00:00:00.33

Execution Plan
----------------------------------------------------------
Plan hash value: 1866882273

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                            |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|   1 |  RESULT CACHE             | 4fq786v0sq49wgux1y518akv7q |       |       |       |            |          |       |       |
|   2 |   SORT GROUP BY           |                            |    68 |  3808 |       |  2274   (4)| 00:00:28 |       |       |
|*  3 |    HASH JOIN              |                            |   968 | 54208 |       |  2273   (3)| 00:00:28 |       |       |
|   4 |     VIEW                  | index$_join$_003           |    72 |  1512 |       |     3  (34)| 00:00:01 |       |       |
|*  5 |      HASH JOIN            |                            |       |       |       |            |          |       |       |
|   6 |       INDEX FAST FULL SCAN| PRODUCTS_PK                |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |       INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX       |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   8 |     VIEW                  | VW_GBC_9                   |   968 | 33880 |       |  2270   (3)| 00:00:28 |       |       |
|   9 |      HASH GROUP BY        |                            |   968 | 26136 |       |  2270   (3)| 00:00:28 |       |       |
|* 10 |       HASH JOIN           |                            |   918K|    23M|  1200K|  2228   (2)| 00:00:27 |       |       |
|  11 |        TABLE ACCESS FULL  | CUSTOMERS                  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |
|  12 |        PARTITION RANGE ALL|                            |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
|  13 |         TABLE ACCESS FULL | SALES                      |   918K|    14M|       |   494   (3)| 00:00:06 |     1 |    28 |
--------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("ITEM_1"="P"."PROD_ID")
   5 - access(ROWID=ROWID)
  10 - access("S"."CUST_ID"="C"."CUST_ID")

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=4; dependencies=(SH.SALES, SH.PRODUCTS, SH.CUSTOMERS); parameters=(nls); name="SELECT /*+ result_cache */
       p.prod_category
     , c.country_id
     , SUM(s.quantity_sold) AS quantity_sold
     , SUM(s."



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       2973  bytes sent via SQL*Net to client
        470  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         81  rows processed

SQL> SELECT status
  2       , creation_timestamp
  3       , build_time
  4       , row_count
  5       , scan_count
  6    FROM v$result_cache_objects
  7   WHERE cache_id = '4fq786v0sq49wgux1y518akv7q'
  8  ;

STATUS    CREATION BUILD_TIME  ROW_COUNT SCAN_COUNT
--------- -------- ---------- ---------- ----------
Published 18/01/19        149         81          1
11GR2 에서의 result cache
  • 11GR1 : 힌트 /*+ result_cache */
  • 11GR2 : 테이블 result_cache(mode force)
  • 한 쿼리 내 모든 테이블이 force 로 지정되어야 result cache 동작
result cache 제어 동적 파라미터
  • result_cache_mode : manual(기본, 권장), force(모든 쿼리 캐시)
  • result_cache_max_size : 공유풀 결과캐시 메모리 상한값
  • result_cache_max_result : 단일 쿼리 결과가 result_cache_max_size 중에서 차지하는 비율, 기본값 5
  • result_cache_remote_expirstion : 원격 오브젝트에 대한 쿼리 캐싱 유효시간, 기본값 0
result cache 사용 불가 쿼리
  • 비결정적(nondeterministic) 함수, 시퀀스, 임시테이블 사용 쿼리
  • 읽기일관성에위배되는 쿼리, DML 수행 후 쿼밋하기 전 수행 쿼리
  • 데이터 딕셔너리에 대한 쿼리
dbms_result_cache 패키지
  • bypass : 일시적으로 결과캐시를 세션/시스템 레벨에서 활성화/비활성화
  • flush : 결과 캐시 제거
  • invalidate : 지정된 오브젝트 관련 결과 캐시 무효화
  • invalidate_object : 단일 캐시 항목 무효화
  • memory_report : 메모리 사용 보고서 생성
  • status : 결과 캐시 상태 조회

PL/SQL 함수 결과 캐싱

CREATE OR REPLACE FUNCTION f_rc_y(v_cust_id IN NUMBER)
    RETURN NUMBER
    RESULT_CACHE
IS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*)
      INTO v_cnt
      FROM customers
     WHERE cust_id||'' = v_cust_id
    ;
    RETURN v_cnt;
END;
/

CREATE OR REPLACE FUNCTION f_rc_n(v_cust_id IN NUMBER)
    RETURN NUMBER
--    RESULT_CACHE
IS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*)
      INTO v_cnt
      FROM customers
     WHERE cust_id||'' = v_cust_id
    ;
    RETURN v_cnt;
END;
/

SQL> SELECT COUNT(f_rc_y(1)) FROM customers;

COUNT(F_RC_Y(1))
----------------
           55500

경   과: 00:00:00.11
SQL> SELECT COUNT(f_rc_n(1)) FROM customers WHERE ROWNUM < 100;

COUNT(F_RC_N(1))
----------------
              99

경   과: 00:00:02.52
SQL>

PL/SQL 함수 결과 캐시 사용불가
  • OUT 파라미터를 가지는 함수
  • 호출자 권한으로 정의된 함수(12.1 부터 사용 가능)
  • 파이프라인된 테이블 함수
  • anonymos PL/SQL 블럭 내 정의된 함수
  • LOB, REF CURSOR, object, record 타입을 입력/반환 하는 함수
  • 예외(Exception) 는 캐시되지 않음

클라이언트 측 결과 캐시

클라이언트 캐시
  • 클라이언트에서 동작하는 캐시
  • OCI 라이브러리 기반 데이터베이스 드라이버 사용하는 어플리케이션에서 실행된 결과 캐시
    • JDBC OCI, ODP>NET, OCII, ODBC
  • 캐시의 목적과 동작은 서버 측과 동일
  • 클라이언트 측 구문 캐싱을 활용하는 SQL 구문(12장 참조)에서만 캐싱 결과를 사용 할 수 있다.
  • 장점 : 클라이언트,서버 간 라운드 트립을 피할 수 있다.
  • 단점 : 일관성이 보장되지 않음(폴링방식의 무효화)
  • 주의 : 12.1 멀티테넌트 환경에서 지원 안됨.
클라이언트 캐시 관련 파라미터
  • 서버 파라미터
    • client_result_cache_size
    • client_result_cache_lag
  • 클라이언트 파라미터(sqlnet.ora)
    • oci_result_cache_max_size
    • oci_result_cache_max_rset_size
    • oci_result_cache_max_rset_rows
result cache 사용시기
  • 동일 오퍼레이션을 끊임 없이 반복하는 어플리케이션으로 인한 성능 저하
  • 모든 쿼리를 캐시해서는 안된다. 선별적 사용
result cache 함정과 착오
  • 일관성 보장 안됨 주의

15.3 병렬 처리

병렬 쿼리

병렬 쿼리
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

병렬 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   |           |
------------------------------------------------------------------------

병렬 DDL

CTAS

삽입의 병렬화
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   |            |
-------------------------------------------------------------------------------

제약조건의 생성과 검증

  • 제약조건 생성시 검증용 재귀 호출 쿼리 병렬 수행
  • 기본 키 정의시 인덱스 생성 병렬 수행 못함
    • 인덱스를 미리 병렬로 생성 한 뒤 기본키 생성

사용시기

  • 가용한 리소스가 충분한 경우
  • 순차적 실행시 10초 이상 소요되는 SQL

함정과 착오

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 힌트는 병렬처리를 고려하도록 할 뿐 강제하지는 않는다.
병렬처리 함정과 착오
  • parallel 힌트는 병렬처리를 고려하도록 할 뿐 강제하지는 않는다.
  • 효율적인 병렬처리를 위해서는 전체 슬레이브 프로세스 사이의 작업량이 균등하게 분배되는 것이 중요하다.
  • 슬레이브 프로세스는 각각 세션을 맺는다.
  • 병렬DML 구문 수행 후에 커밋/롤백 하기 전에 동일 세션에서 Select 불가.
    • ora-12838: cannot read/modify an object after modifying it in parallel
    • 다른 세션에서는 커밋 전 데이터 조차 볼 수 없다.

15.4 direct-path insert

CTAS 는 direct-path insert 방식을 사용.
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 사용
direct-path insert
  • HWM 위에 직접 적재
  • 직접 쓰기이므로 버퍼 캐시 우회
  • 세그먼트 락
  • 세그먼트 크기 증가
  • 장점 : 최소한의 언두 사용
  • 성능을 더 개선하기 위해서 최소로깅을 병행하는 것이 좋다. (리두 최소)
  • 그림15-11
direct-path insert 가 안먹는 경우
  • Insert Trigger 가 활성화 되어 있는 경우
  • 외래키가 활성화 되어 있는 경우
  • IOT
  • 클러스터 테이블
  • 객체 타입 컬럼 포함
  • nonUnique index 를 통해 구현한 기본키가 존재할때(11.1부터는 가능)
사용시기
제한사항이 문제되지 않는다면? 대량 적재시 매우 유용

15.5 로우 프리페칭

row prefetching
  • 하나의 로우 요청 한번에 여러 로우를 미리 가져오는 것
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;
/

15.6 배열처리 인터페이스

array interface
  • 단순 값이 아닌 배열을 바인드 할 수 있다.
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 이 사용되었다는 것이 루프를 의미하지는 않는다
-- 단 한번의 호출로 모든 로우가 전달된다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.