DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
10g에서는 강제적인 query rewrite, tuning advisor 등의 새로운 기능을 통해 materialized view의 관리 기능을 향상시켰습니다. 스냅샷(snapshot)이라 불리기도 하는 Materialized view(MV)는 오라클에서 오래 전부터 구현해 온 기능의 하나입니다. MV는 쿼리 결과를 별도 세그먼트에 저장하고, 쿼리가 재실행되는 경우 사용자에게 미리 저장된 결과를 전달함으로써 쿼리를 여러 차례 재실행하는 데 따르는 성능적인 부담을 줄여줍니다. MV는 데이타 웨어하우스 환경에서 특히 유용합니다. 또 “fast refresh” 메커니즘을 이용해 MV를 전체적으로 또는 부분적으로 refresh할 수 있습니다. 다음과 같은 materialized view를 구현한 경우를 가정해 봅시다: 어떻게 하면 이 MV가 완벽하게 동작하는데 필요한 오브젝트들이 모두 생성되었는지 확인할 수 있을까요 Oracle Database 10g 이전 버전에서는, DBMS_MVIEW 패키지의 EXPLAIN_MVIEW 프로시저와 EXPLAIN_REWRITE 프로시저를 이용해 이를 확인할 수 있었습니다. 이 프로시저들은 10g에서도 여전히 사용 가능합니다. 이 프로시저들을 이용하면 특정 MV에 “fast refreshability”, “query rewritability” 등의 기능 구현 여부를 정확하게 확인할 수 있었지만, 어떻게 하면 이러한 기능을 구현할 수 있는지에 대한 조언을 구할 수는 없었습니다. 이를 위해서는, 각각의 MV 구조를 비주얼한 방법으로 확인해야만 했으며, 실질적으로 이 방법은 효용성이 없었습니다. 10g에서는 새로 추가된 DBMS_ADVISOR 패키지에 포함된 TUNE_MVIEW 프로시저를 통해 이러한 작업을 간단하게 수행할 수 있습니다. 먼저 IN 매개변수에 MV 생성 스크립트의 텍스트를 저장한 후 패키지를 호출합니다. 프로시저는 자동 생성된 이름을 가진 Advisor Task를 생성하며, OUT 매개변수를 통해 사용자에게 생성된 Advisor의 이름을 반환합니다. 예를 들어 설명해 보겠습니다. 먼저 SQL*Plus에서 OUT 매개변수에 저장할 변수를 정의해야 합니다. 다음에는 adv_name 변수를 조회하여 Advisor의 이름을 확인합니다. 다음으로, DBA_TUNE_MVIEW 뷰를 질의하여 Advisor가 제공하는 권고내역을 확인합니다. 이 명령을 실행하기 전에 SET LONG 999999 명령을 실행하는 것을 잊지 마시기 바랍니다. (DBA_TUNE_MVIEW 뷰의 STATEMENT 컬럼은 CLOB 데이타타입을 사용하므로, 문자 출력을 80개로 제한하는 디폴트 환경을 수정해야 합니다.) 실행 결과는 아래와 같습니다: SCRIPT_TYPE 컬럼은 제공되는 조언현에 관련된 권고사항으로 구성되어 있으므로 SCRIPT_TYPE으로 “IMPLEMENTATION”이 설정된 것을 확인할 수 있습니다. 사용자가 승인하는 경우, 제시된 권고사항은 ACTION_ID 컬럼에 정의된 순서대로 실행됩니다. Advisor가 제시한 권고사항을 자세히 살펴보면, 우리가 비주얼 분석 작업을 통해 생성하는 것과 유사한 내용으로 구성되어 있다는 사실을 알 수 있을 것입니다. 위에서 제시된 권고사항은 매우 논리적입니다. Fast refresh를 구현하려면 베이스 테이블MATERIALIZED VIEW LOG가 생성되어야 하며, 이 때 “including new values”와 같은 조건을 포함하여야 합니다. STATEMENT 컬럼은 이러한 권고사항을 구현하기 위해 실행되는 SQL 구문을 담고 있습니다. 구현 작업의 마지막 단계로서, Advisor는 MV 생성 과정에서 수정이 필요한 부분을 지적해 줍니다. 위의 예에서 무엇이 달라졌는지 확인해 보시기 바랍니다. count(*)가 MV에 추가되었습니다. MV가 “fast refresh”를 지원하는 것으로 설정했기 때문에 count(*)가 반드시 포함되어야 하며, Advisor는 이 부분이 생략된 것을 발견하고 권고사항에 포함시킨 것입니다. 프로시저 TUNE_MVIEW 는 EXPLAIN_MVIEW 와 EXPLAIN_REWRITE가 제공하는 것보다 수준 높은 조언을 제공하며, MV를 생성하는 보다 쉽고 효율적인 방법도 함께 제시합니다. 때로 Advisor는 질의를 보다 효율적으로 하기 위한 방법으로 하나 이상의 MV를 제안하기도 합니다. 어떤 분은 그게 과연 얼마나 유용할까라고 의문을 던질 수도 있을 것입니다. 숙련된 DBA라면 누구나 MV 생성 스크립트에서 빠진 부분을 찾아내어 직접 수정할 수 있기 때문입니다. 사실 Advisor가 하는 역할이 바로 이것입니다. Advisor는 마치 숙련된 DBA처럼 전문적인 권고를 제시합니다. 한 가지 분명하게 다른 점은, Advisor가 돈을 안받고 일할 뿐 아니라 휴가나 월급인상을 요구하지도 않는다는 사실입니다. 이러한 기능이 있음으로 해서, 선임 DBA가 하급 DBA에게 반복적인 업무를 인계하고 보다 전략적인 목표에 집중할 수 있게 되는 것입니다. TUNE_MVIEW 프로시저를 실행할 때 Advisor name을 미리 지정해서 매개변수를 통해 전달할 수도 있습니다. 이렇게 하는 경우 Advisor는 자동 생성된 이름 대신 사용자가 지정한 이름을 사용합니다. 이제 권고사항을 확인하고 바로 구현 작업에 들어갈 준비가 되었습니다. 위 실행결과의 STATEMENT 컬럼을 조회한 결과를 별도 스크립트 파일에 스풀링한 다음, 그 파일을 실행하는 것도 한 방법입니다. 아니면 그보다 쉬운 방법으로 별도 제공되는 프로시저를 실행할 수도 있습니다: 이 프로시저는 아래와 같은 방법으로 디렉토리 오브젝트가 미리 생성되어 있음을 가정합니다: dbms_advisor를 호출하면 /home/oracle/mvtune_outdir 디렉토리에 mvtune_script.sql이라는 이름의 파일이 생성됩니다. 이 파일을 열어 보면 다음과 같은 내용이 포함되어 있음을 확인할 수 있을 것입니다: 이 파일은 권고사항을 구현하기 위해 필요한 모든 정보를 포함하고 있으며, 수작업으로 파일을 생성하는 수고를 덜어줍니다. DBA 로봇이 다시 한 번 그 위력을 발휘하는 순간입니다. 이제 여러분들도 Query Rewrite 기능이 얼마나 중요하고 유용한지 깨닫게 되셨으리라 믿습니다. Query Rewrite는 I/O 작업과 프로세싱 작업을 줄여주고 결과를 한층 빠르게 얻을 수 있게 합니다. 위의 예를 기준으로 계속 설명해 보겠습니를 생각해 봅시다: consistent gets가 6의 값을 갖는 것에 주목하시기 바랍니다. 이것은 매우 낮은 수치입니다. 이는 3 개의 테이블을 기반으로 생성된 2개의 MV를 이용하도록 쿼리가 재작성 되었기 때문에 얻어진 결과입니다. Select 작업은 테이블이 아닌 MV를 통해 수행되었으며, 그 덕분에 훨씬 적은 I/O와 CPU를 사용했습니다. 하지만 Query Rewrite가 실패하면 어떻게 될까요 실패의 이유에는 여러 가지가 있을 수 있습니다. 초기화 매개변수 query_rewrite_integrity이 “TRUSTED”로 설정되고 MV status가 “STALE”로 설정되었다면 쿼리는 재작성 되지 않을 것입니다. 쿼리를 실행하기 전에 세션 매개변수를 설정함으로써 이 현상을 테스트해 볼 수 있습니다: 위 명령을 실행하고 나면, explain plan은 MV가 아닌 3개 테이블로부터 select를 수행하는 것으로 변경됩니다. 실행 통계도 아래와 같이 달라지게 됩니다: consistent gets의 값이 6에서 16으로 크게 증가했음을 확인할 수 있습니다. 추가 자원을 활용하기 어려운 실제 환경에서는, 이러한 변화를 용납하기는 어려우며, 따라서 쿼리를 재작성 하는 방법을 선택할 수 밖에 없습니다. 이러한 경우 쿼리가 재작성 되는 경우에만 실행 가능하도록 설정해야 할 수도 있습니다. Oracle9i Database와 그 이전 버전에서는 단 한 가지 옵션만 가능했습니다. Query Rewrite를 disable할 수는 있없었습니다. 반면 Oracle Database 10g는 REWRITE_OR_ERROR라는 힌트를 이용하여 베이스 테이블에 대한 접근을 차단하는 기능을 제공합니다. 위의 쿼리는 아래와 같은 형태로 재작성될 수 있습니다: 에러 메시지는 아래와 같이 표시됩니다: ORA-30393은 구문이 MV를 사용할 수 있는 형태로 재작성될 수 없으며, 이로 인해 구문 실행에 실패하였음을 의미하는 에러입니다. 이 방법을 사용하여 시스템 리소스를 많이 잡아먹는 쿼리를 사전에 차단할 수 있습니다. 다만 한 가지 주의할 점은, MV 중 (전체가 아닌) 하나만이라도 이용이 가능한 경우 쿼리가 실행된다는 사실입니다. 예를 들어 MV_ACTUAL_SALES은 사라 해도, 쿼리는 재작성되고 실행되며, 에러는 발생하지 않습니다. 이 경우 execution plan은 아래와 같습니다: 이 쿼리는 MV_ACTUAL_SALES은 사용하지만 MV_HOTEL_RESV는 사용하지 않고, 대신 HOTELS 테이블과 RESERVATIONS 테이블에 직접 액세스합니다. 만일 HOTELS 또는 RESERVATIONS 테이블에 풀 테이블 스캔이 발생한다면, MV를 사용하는 경우보다 훨씬 많은 리소스를 사용하게 될 것입니다. 쿼리와 MV를 설계하는 과정에서 반드시 참고해야 할 부분입니다. 물론 Resource Manager를 이용하면 리소스 사용을 통제할 수도 있습니다. 하지만 REWRITE_OR_ERROR 힌트를 이용하면 Resource Manager가 호출되기도 전에 쿼리의 실행을 사전 차단하는 것이 가능합니다. Resource Manager는 옵티마이저 통계에 근거하여 필요한 리소스를 예측하며, 통계가 존재하지 않거나 그 정확도가 떨어지는 경우 잘못된 예측을 할 수도 있습니다. 반면 REWRITE_OR_ERROR 힌트를 이용하면 통계와 무관하게 테이블 액세스를 차단할 수 있다는 장점이 있습니다. Explain Plan의 개선 기능 앞의 explain plan 조회결과 예에서 아래 행을 참고하시기 바랍니다: MAT_VIEW REWRITE 액세스 방식은 10g에서 처음 소개되는 것입니다. 이것은 테이블 또는 세그먼 프로시저를 사용하면 테이블과 MV 중 어느 쪽이 사용되고 있는지를 바로 확인할 수 있습니다. 10g에 새로 추가된tuning advisor의 강력한 권고 기능을 이용하여 MV를 더 쉽게 관리할 수 있습니다. 필자는 개인적으로, 튜닝 권고내역이 바로 실행 가능한 형태의 완성된 스크립트로 제공된다는 점이 마음에 듭니다. 리소스를 최대한 아껴야 하는 의사결정 시스템에서는, REWRITE_OR_ERROR 힌트를 사용하여 쿼리 재작성이 불가능한 경우 실행 자체를 차단하는 방법이 매우 유용하게 사용될 수 있습니다. 10g에서 MV를 관리하는 방법에 대한 보다 자세한 정보는 Oracle Database Data Warehousing Guide 10g Release 1 (10.1)의 제 8 장을 참고하시기 바랍니다.Materialized Views
Materialized Views
열두번째. Materialized Views
create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';
SQL> -- first define a variable to hold the OUT parameter
SQL> var adv_name varchar2(20)
SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query
rewrite as select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;
SQL> print adv_nameADV_NAME
-----------------------
TASK_117
select script_type, statement
from dba_tune_mview
where task_name = 'TASK_117'
order by script_type, action_id;
SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUESIMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUESIMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUESIMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUESIMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITYUNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
쉬워진 구현 작업
begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Remset feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60whenever sqlerror CONTINUECREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID C2,
ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS
WHERE ARUP.HOTELS.HOTEL_ID = ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;whenever sqlerror EXIT SQL.SQLCODEbegin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/
Rewrite이 불가능한 경우의 실행 차단
Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
실행 통계는 아래와 같이 확인되었습니다:0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
alter session set query_rewrite_enabled = false;0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE)
(Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE))
(Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE)
(Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES'
(MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)결론