Oracle Database 성능 튜닝 가이드
로그를 통한 데이터베이스 성능 향상에 대한 조언을 제공하는 튜닝 도구입니다. 이 장은 다음 항목이 있습니다.
18.1 SQL 액세스 관리자 개요
데이터 집약적인 복잡한 쿼리를 실행할 때 최적의 성능을 실현할 수 있도록 데이터베이스를 튜닝하는 경우, Materialized View, 파티션 및 인덱스가
필수적입니다. SQL 액세스 관리자는 특정 작업에 대한 Materialized View, Materialized View 로그 파티션 및 인덱스의 적 절한 설정을 권장하고, 성능
목표를 지원합니다. SQL을 최적화할 때 이러한 구조를 이해하고 사용하는 것이 중요합니다. 이를 통해 데 이터를 검색할 때 성능이 크게 향상됩니다.
그러나 이러한 이점을 이용하기 위해서는 나름의 부담이 수반됩니다. 이러한 개체를 만들 고 유지하는 데 시간이 걸리고, 또한 공간 요구 사항도
중요합니다. 특히 분할되지 않은 실제 테이블 파티셔닝은 신중한 계획을 필요로 하는 복잡한 작업입니다.
SQL 액세스 어드바이저 색인 권장 사항은 비트맵 인덱스, 함수 인덱스 및 B - 트리 인덱스가 포함됩니다. 비트맵 인덱스를 사용하면 여 러 종류의
비정형 질의 응답 시간이 단축되고 다른 색인 방법에 비해 저장소 요구 사항이 줄어 듭니다. B - 트리 인덱스는 고유하거나 거의 고유 키 인덱스를
붙이는 방식으로 데이터웨어 하우스에서 가장 일반적으로 사용되고 있습니다. SQL 액세스 관리자 Materialized View 권장 사항은 일반 재작성 또는
텍스트 일치 재작성의 고속 재생 및 완전 재생 가능한 MV 권장도 포함됩니다.
SQL Access Advisor는 TUNE_MVIEW 시저를 사용하여 빠른 재생이 가능하며, 보통의 질의 재작성를 활용할 수있는 구체화된 뷰 최적화 방법도 권장됩니다.
또한 SQL Access Advisor는 기존 파티션되지 않는다 실제 테이블 파티셔닝 성능 개선을 권장하는 경우도 있습니다. 또한 파티션화된 새로운 인덱스 및
구체화된 뷰를 권장 수도 있습니다. 파티션화된 새로운 인덱스 및 구체화된 뷰를 생성은 분할되지 않은 경우와 동일 하지만 기존의 열매 테이블
파티셔닝은 신중하게 실행해야합니다. 테이블에 인덱스, 뷰, 제약 조건 또는 트리거가 정의되어있는 경우 에는 특히 주의해야합니다. 이 작업을
온라인으로 실행하는 경우 실제 테이블 파티셔닝의 문제 자세한 내용은 "스크립트에서 파티션 권장 사항이 포함되어있는 경우 특별한 고려 사항" 을 참조하십시오.
SQL 액세스 관리자를 실행하려면 Oracle Enterprise Manager에서 SQL 액세스 관리자 마법사를 사용하거나 ( "관리자 센트럴"페이지에 서 액세스 가능)
DBMS_ADVISOR 패키지를 시작합니다. DBMS_ADVISOR 패키지는 임의의 PL / SQL 프로그램에서 호출할 수있는 분석 및 관 리자의 기능과 절차의 집합입니다.
그림 18-1 에 SQL 액세스 관리자가 지정된 작업을 사용자 정의 테이블 또는 SQL 캐시에서 검색하고 액세스 구조를 권장하는 방법을 보여줍니다.
작업을 지정하지 않으면 SQL Access Advisor는 가상 워크로드를 생성하고 사용할 수 있습니다 (단, CREATE DIMENSION 키 워드로 정의된 차원이
사용자 스키마에 포함되어 있다고 가정합니다).
그림 18-1 Materialized View와 SQL 액세스 어드바이저
"그림 18-1 Materialized View와 SQL 액세스 어드바이저"설명
Enterprise Manager의 SQL 액세스 어드바이저 마법사 또는 API를 사용하여 다음 작업을 수행할 수 있습니다.
또한 SQL 액세스 관리자 API를 사용하여 다음 작업을 수행할 수 있습니다.
추천 때 SQL Access Advisor는 차원 수준의 열, JOIN KEY 열 및 팩트 테이블의 키 열을의 테이블과 인덱스 카디에
관한 구조 통계에 의 존합니다. DBMS_STATS DBMS_STATS 패키지를 사용하여 정확한 통계 또는 견적 통계를 수집할
수 있습니다. 통계 수집은 시간이 오래 걸 리는 작업이며, 전체 통계 정밀도는 필요하지 않기 때문에 일반적으로 통계를
계산하는 것을 권장합니다. 특정 테이블에 대한 통계를 수집하지 않으면 해당 테이블을 참조하는 쿼리가 작업에서
무효라고 표시되고 그 질의 내용은 권장 사항은 생성되지 않습니다. 또한 기존의 모든 색인 및 구체화된 뷰를 분석하여
두는 것도 좋습니다. DBMS_STATS 패키지의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.1.1 SQL Access Advisor 사용에 대한 개요
SQL Access Advisor를 사용하려면 Enterprise Manager의 "관리자 센트럴"페이지에서 마법사를 시작하는 방법이 간단합니다.
DBMS_ADVISOR 패키지에서 SQL 액세스 관리자를 사용하려면이 절에서 설명하는 기본 구성 요소 및 프로 시저 호출 순서를 참조하십시 오.
이 단원에서는 일련의 권장 사항을 생성하기위한 4 가지 단계에 대해 설명합니다.
1 단계 : 작업 만들기
SQL Access Advisor의 권장 사항을 생성하려면 먼저 작업을 만들어 둘 필요가 있습니다. 추천 과정 결과 등을 권장
프로세스에 대한 모든 정보 작업에 저장되기 때문에 작업이 중요합니다. Oracle Enterprise Manager 마법사를 사용하거나
DBMS_ADVISOR.QUICK_TUNE 시저를 사용하면 작업은 자동으로 생성됩니다. 다른 모든 경우에서는 DBMS_ADVISOR.CREATE_TASK
시 저를 사용하여 작업을 만들 필요가 있습니다.
작업이 어떤 작업을 제어하려면 DBMS_ADVISOR.SET_TASK_PARAMETER 시저를 사용하여 작업의 매개 변수를 정의합니다.
작업 만들기 자세한 내용은 "작업 만들기" 를 참조하십시오.
2 단계 : 작업 정의
작업은 SQL 액세스 어드바이저의 주요 입력 데이터이며, 1 개 이상의 SQL 문장과 각 문장을 완전하게 설명하는 각종 통계와 특성으로
구성됩니다. 작업 부하에 대상 사업 응용 프로그램의 모든 SQL 문이 포함된 경우 작업은 전체 작업으로 간주됩니다. 작 업에 SQL 구문의
하위 집합이 포함되는 경우,이 작업은 부분 작업이라고합니다. 모든 작업 부하 및 부분 작업의 차이는 모든 작 업의 경우 사용되지 않은
기존 Materialized View와 색인을 검색하면 SQL 액세스 어드바이저가 이들을 제거하도록 권장하는 점 입니다.
일반적으로 SQL 액세스 관리자는 모든 분석 작업의 기초로 작업을 사용합니다. 작업에는 다양한 종류의 문장이 포함될 수 있지 만,
작업 부하는 특정 통계, 사업의 중요성, 또는 통계와 사업의 중요성 조합에 따라 항목에 순위가 매겨집니다. 이 순위는 중요 합니다.
이러한 순위를 지정하면 SQL Access Advisor는 사업에 영향이 적은 SQL 문을보다 가장 중요한 SQL 문장을 먼저 처리하 게 될 것입니다.
데이터의 집합을 효과적인 작업으로 간주 위해 SQL 액세스 관리자는 특정 특성이 필요할 수 있습니다. 일부 항목이 누락되어도
관리자는 분석을 수행할 수 있지만 권장 사항의 품질이 크게 저하될 수 있습니다. 예를 들어, SQL Access Advisor는 SQL 쿼리와 이
쿼리를 실행하는 사용자가 작업에 포함되어 있어야합니다. 다른 모든 속성은 옵션입니다. 그러나 작업에 I / O 및 CPU 정보 를
포함하면 SQL Access Advisor는 문장의 현재의 효율성을보다 정확하게 평가할 수 있습니다. 이 작업은 SQL 튜닝 집합 개체로
데이터베이스에 저장되고 DBMS_SQLTUNE 패키지를 사용하여 액세스할 수 있기 때문에 많은 관리자 작업간에 쉽게 공유할 수 있
습니다. 작업은 독립하고 있기 때문에, DBMS_ADVISOR.ADD_STS_REF 시저를 사용하여 작업에 연결해야합니다. 이 링크를 설치한
후 모든 관리자 작업의 워크로드에 대한 의존도가 삭제될 때까지 작업을 삭제하거나 변경할 수 없습니다. 사용자가 부모 관리자
작업을 삭제하거나 DBMS_ADVISOR.DELETE_STS_REF 시저를 사용하여 관리자 작업에서 작업 참조를 수동으로 제거하면 작업
참 조가 제거됩니다.
작업없이 SQL 액세스 관리자를 사용하는 것은 허용되지 않지만 차원과 제약 조건을 분석하여 스키마에서 가상 워크로드를 만들
수 있습니다. 최상의 결과를 얻으려면 실제 작업을 SQL 튜닝 세트 형식으로 지정합니다.
DBMS_SQLTUNE 패키지에 제공되는 도우미 함수는 일반적인 작업 소스 (SQL 캐시 테이블에 저장된 사용자 정의 작업 가상 작 업)에서
SQL 튜닝 세트를 만들 수 있습니다.
권장 사항을 생성할 때 작업 부하에 필터를 적용하여 분석 대상을 제한할 수 있습니다. 이렇게 제한하는 다양한 작업 시나리오 를
기반으로 권장 사항 세트를 생성할 수 있습니다.
작업 권장 프로세스와 사용자는 SQL 액세스 어드바이저 파라미터에 의해 제어됩니다. 이 매개 변수는 필요한 권장 사항의 유형 및
권장 사항의 네이밍 규칙 등 추천 과정의 다양한 측면을 제어합니다.
이 매개 변수를 설정하려면 SET_TASK_PARAMETER 시저를 사용합니다. 매개 변수는 작업의 존속 기간은 설정된 상태로 유지한다 는
점에서 영구적입니다. SET_TASK_PARAMETER 시저를 사용하여 매개 변수 값을 설정하면 SET_TASK_PARAMETER 을 1 번 호출하 기
전에 해당 값이 변경되지 않습니다.
작업에 작업 부하를 연결하고 적절한 매개 변수를 설정한 후 DBMS_ADVISOR.EXECUTE_TASK 시저를 사용하여 권장 사항을 생성할
수 있습니다. 이러한 권장 사항은 SQL 액세스 어드바이저 저장소에 저장됩니다.
추천 과정을 통해 여러 권장 사항이 생성됩니다. 각 권장 사항은, 1 개 이상의 동작이 지정됩니다. 예를 들어, 1 개의 권장 사항 은
여러 Materialized View 로그를 만들 Materialized View를 만들고, 그 분석을 통해 통계 수집가 포함되어 있습니다.
작업 권장 사항은 단순한 제안에서 일련의 기존의 열매 테이블 파티셔닝 및 색인, Materialized View 및 Materialized View 로그
등 일련의 데이터베이스 개체의 구현을 필요로하는 복잡한 솔루션까지 다양합니다. 관리자 작업을 수행하면 SQL 액세스 관리자 는
수집된 데이터와 사용자 조정 작업 매개 변수를 신중하게 분석됩니다. 분석 후 사용자가보고 구현할 수있는 구조화된 권장가 형성됩니다.
권장 사항 발생의 자세한 내용은 "최선의 생성" 을 참조하십시오.
SQL Access Advisor의 권장 사항을 표시하는 방법에는 2 가지가 있습니다. 카탈로그 뷰를 사용하는 방법과
DBMS_ADVISOR.GET_TASK_SCRIPT 시저를 사용하여 스크립트를 생성하는 방법입니다. SQL Access Advisor
작업이 완료되면 Enterprise Manager 권장 사항을 볼 수 있습니다. 권장 사항을 표시하기 위해 카탈로그 뷰를
사용하는 방법에 대한 자세한 내용 은 "권장 사항보기" 를 참조하십시오. 스크립트 작성에 대한 자세한 내용은
"SQL 스크립트 생성" 을 참조하십시오.
모든 권고 사항을 받아들일 필요는없고, 권장 사항 스크립트 포함 권장 사항을 표시할 수 있습니다. 그러나
실제 테이블 파티셔 닝이 권장되는 경우 일부 권장 사항은 그렇지 권장 사항에 따라 달라집니다 (예를 들어,
인덱스의 실제 테이블에 대한 파티션 권 장 사항을 구현해야 로컬 색인도 구현할 수 없습니다 ).
마지막 단계에서는 권장 사항을 구현하고 쿼리 성능이 향상되었는지 여부를 확인합니다.
SQL 액세스 관리자에서 생성된 모든 필요한 정보는 데이터베이스 사전의 일부인 관리자 저장소에 저장됩니다.
저장소를 사용하는 이 점은 다음과 같습니다.
이 단원에서는 SQL 액세스 어드바이저에 대한 일반 정보 및 SQL Access Advisor를 사용하는 데 필요한 단계를 설명합니다. 이 절의 내 용은 다음과 같습니다.
Oracle Enterprise Manager에서 SQL 액세스 관리자를 사용하는 방법에 대한 자세한 내용은 Oracle
18.2.1 SQL Access Advisor 사용 방법
그림 18-2 에 SQL Access Advisor 사용 방법, SQL Access Advisor의 모든 매개 변수에 대한 설명과 어느 시점에서이 매개 변수를 사용 하는 것이 적절 여부를 나타냅니다.
그림 18-2 SQL 액세스 어드바이저의 흐름도
"그림 18-2 SQL 액세스 어드바이저 순서도"설명
18.2.2 SQL Access Advisor 사용에 필요한 권한
SQL Access Advisor를 관리하거나 사용하려면 ADVISOR 권한이 있어야합니다. 작업 부하를 처리할 때 SQL Access Advisor는 테이블 열 참조를 식별하기 위해 각 문장의
검증을 시도합니다. 유효성 검사는 원래 사용자가 문장을 실행하는 경우와 마찬가지로 각 문장이 처 리됩니다.
사용자가 특정 테이블에 대한 SELECT 권한이없는 경우, SQL Access Advisor는 테이블을 참조하는 문장을 우회합니다. 이 때문에 많은 문장 분석에서 제외시킬 수
있습니다. SQL 액세스 관리자가 작업의 모든 문장을 제외하면 작업 부하가 비활성화됩니다. SQL Access Advisor는 다음과 같은 메시지를 리턴합니다.
QSM - 00774, there are no SQL statements to process for task TASK_NAME
중요한 작업 질의가 누락되지 않도록하려면 현재 데이터베이스 사용자가 구체화된 뷰 분석 대상 테이블에 대한 SELECT 권한이 있어야 합니다. 이 테이블에서
역할을 통해 이들의 SELECT 권한을 얻을 수 없습니다.
또한 SQL 튜닝 집합 개체의 작업을 만들고 관리하려면 ADMINISTER SQL TUNING SET 권한이 있어야합니다. 다른 사용자가 소유하는
SQL 튜닝 세트 관리자를 실행하려면 ADMINISTER ANY SQL TUNING SET 권한이 있어야합니다.
18.2.3 작업 및 템플릿 설정
이 단원에서는 작업 및 템플릿 설정에 대한 다음의 측면을 설명합니다.
템플릿 사용
템플릿 만들기
18.2.3.1 작업 만들기
관리자 작업은 분석하는 내용으로,이 분석 결과를 배치할 위치를 정의하는 작업입니다. 사용자는 각 작업을 전문으로하는 개수 작업을 만들 수 있습니다. 이러한 모든
작업은 같은 관리자 작업 모델에 근거하고있어 같은 저장소를 공유합니다.
작업을 만들려면, CREATE_TASK 시저를 사용합니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.CREATE_TASK (
advisor_name IN VARCHAR2,
task_id OUT NUMBER,
task_name IN OUT VARCHAR2,
task_desc IN VARCHAR2 : =
NULL, template IN VARCHAR2 : = NULL,
is_template IN VARCHAR2 : = 'FALSE',
how_created IN VARCHAR2 : = NULL);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
VARIABLE task_id NUMBER;
VARIABLE task_name
VARCHAR2 (255);
EXECUTE : task_name : = 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', : task_id, : task_name);
CREATE_TASK 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
작업 또는 작업의 이상적인 구성이 발견되면 해당 구성을 템플릿으로 저장하여두고, 향후 작업 및 작업의 기반으로 사용할 수 있습니 다.
작업 또는 작업을 템플릿으로 설정하면 향후 작업 만들기에서 지능형 출발점 또는 템플릿으로 사용할 수 있습니다. 템플릿을 설정하여 튜닝 분석의 실행 시간을 단축할
수 있습니다. 또한이 방식을 통해 비즈니스 운영에 적합한 튜닝 분석이 가능합니다.
템플릿에서 작업을 만들려면 새 작업을 만들 때 사용할 서식 파일을 지정합니다. 이 때, SQL Access Advisor는 새로 만든 작업에 템플릿 데이터와 매개 변수 설정을
복사합니다. 또한 기존의 작업을 템플릿으로 설정하려면 작업을 만들 때 템플릿 속성을 설정하거나 나중 UPDATE_TASK_ATTRIBUTE 시저를 사용합니다.
작업을 템플릿으로 사용하려면 새 작업을 만들 때 작업을 사용하는 SQL 액세스 어드바이저에게 통지합니다. 이 때, SQL Access Advisor는 새로 만든 작업에 템플릿
데이터와 매개 변수 설정을 복사합니다. 또는 명령줄 또는 Enterprise Manager에서 템플릿의 속성 을 설정하여 기존 작업을 템플릿으로 설정할 수 있습니다.
18.2.3.3 템플릿 만들기
템플릿 만들기 예제는 다음과 같습니다.
1. MY_TEMPLATE 라는 템플릿을 만듭니다.
VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2 (255); EXECUTE : template_name : = 'MY_TEMPLATE';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( 'SQL Access Advisor', : template_id -
: template_name, is_template => "TRUE ');
2. 템플릿 매개 변수를 설정합니다. 예를 들어, 다음 예제에서는 권장되는 인덱스 및 구체화된 뷰 이름 지정 규칙과 기본 테이블 스 페이스를 설정합니다.
- set naming conventions for recommended indexes / mvs
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-: template_name 'INDEX_NAME_TEMPLATE', 'SH_IDX $ $ _ <SEQ>');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'MVIEW_NAME_TEMPLATE', 'SH_MV $ $ _ <SEQ>');
- set default tablespace for recommended indexes / mvs
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_INDEX_TABLESPACE', 'SH_INDEXES');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
3. 이제이 템플릿은 다음과 같이 작업을 만들 때 시작점으로 사용할 수 있습니다.
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2 (255); EXECUTE : task_name : = 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( 'SQL Access Advisor', : task_id -
: task_name, template => "MY_TEMPLATE ');
EXECUTE DBMS_ADVISOR.CREATE_TASK ( 'SQL Access Advisor'-
: task_id, : task_name, template => "SQLACCESS_WAREHOUSE ');
18.2.4 SQL Access Advisor 작업 부하
SQL Access Advisor는 다른 유형의 작업 부하를 지원합니다. 이 단원에서는 작업 부하 관리에 대한 다음 측면에 대해 설명합니다.
작업 및 작업로드 링크
SQL Access Advisor의 입력 작업 원본은 SQL 튜닝 세트입니다. SQL 튜닝 세트를 사용하여 중요한 장점은 SQL 튜닝 집합은 별도의 엔터 티로 저장되기 때문에 많은
관리자 작업간에 쉽게 공유할 수 있습니다. SQL 튜닝 집합 개체가 관리자 작업에서 참조 후 모든 관리자 작 업에서 데이터에 대한 의존도가 삭제될 때까지 데이터를
삭제하거나 변경할 수 없습니다. 작업 참조가 제거되는 것은 부모 관리자 작 업이 삭제된 경우, 또는 관리자 작업에서 작업 참조 사용자가 수동으로 삭제한 경우입니다.
SQL Access Advisor의 성능은 실제 사용에 따라 작업 부하가 사용 가능한 경우 가장 높습니다. 여러 작업을 SQL 튜닝 세트로 저장하여 장기 및 데이터베이스 인스턴스
시작에서 종료까지 전체 수명주기에 걸쳐 실제 데이터웨어 하우 스나 트랜잭션 처리 환경의 다양한 사 용을 볼 수 있습니다 .
18.2.4.2 SQL 튜닝 세트 사용 방법
SQL 튜닝 세트 작업은 DBMS_SQLTUNE 패키지를 사용하여 구현합니다. SQL 튜닝 세트 생성 및 관리 자세한 내용은 Oracle Database
PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
DBMS_ADVISOR 패키지는 기존의 SQL 작업 개체를 SQL 튜닝 세트로 마이 그 레이션하기 위해 SQL 워크로드 데이터를 사용자가 지정한 SQL 튜닝 세트에 복사하는
프로 시저를 제공합니다. 이 절차를 사용하려면 SQL 튜닝 세트 필요한 권한과 ADVISOR 권한이 있어야합니 다.
이 구문은 다음과 같습니다.
DBMS_ADVISOR.COPY_SQLWKLD_TO_STS (
workload_name IN ARCHAR2,
sts_name IN VARCHAR2,
import_mode IN VARCHAR2 : = 'NEW');
다음 예제를 보여줍니다.
EXECUTE DBMS_ADVISOR.COPY_SQLWKLD_TO_STS ( 'MYWORKLOAD', 'MYSTS', 'NEW');
COPY_SQLWKLD_TO_STS 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.4.3 작업 및 작업로드 링크
추천 과정을 시작하려면 먼저 SQL 튜닝 세트 작업을 연결해야합니다. 그러기 위해서는 ADD_STS_REF 시저를 사용하여 작업 및 튜닝 세 트를 각각의 이름을 사용하여
연결합니다. 이 절차는 관리자 작업 및 튜닝 세트 사이의 연결을 설정합니다. 링크가 정의한 후, 삭제 또 는 업데이 트되지 않는 SQL 튜닝 세트가 보호됩니다. 이 구문은
다음과 같습니다.
DBMS_ADVISOR.ADD_STS_REF
(task_name IN VARCHAR2,
sts_owner IN VARCHAR2,
sts_name IN VARCHAR2);
sts_owner 매개 변수는 NULL 일 수 있습니다이 경우 STS의 소유자는 현재 사용자로 간주됩니다.
다음 예제에서는 사용자가 만든 작업 MYTASK 과 현재 사용자의 SQL 튜닝 세트 MYWORKLOAD 를 연결합니다.
EXECUTE DBMS_ADVISOR.ADD_STS_REF ( 'MYTASK', null, 'MYWORKLOAD');
ADD_STS_REF 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.4.3.1 SQL 튜닝 세트 작업과 작업 사이의 링크 제거
작업 또는 SQL 튜닝 세트 작업을 삭제하려면 먼저 DELETE_STS_REF 시저를 사용하여 작업과 작업 사이의 링크를 삭제해야합니다 (링크 가 설정되어있는 경우).
다음 예제에서는 작업 MYTASK 과 현재 사용자의 SQL 튜닝 세트 MYWORKLOAD 사이의 연결을 제거합니다.
EXECUTE DBMS_ADVISOR.DELETE_STS_REF ( 'MYTASK', null, 'MYWORKLOAD');
18.2.5 권장 사항의 처리
이 항목에서는 권장 사항의 처리에 관한 다음 측면에 대해 설명합니다.
SQL Access Advisor는 여러 권장 사항을 제공하고 각 권장 사항에는 1 개 이상의 개별 작업이 포함됩니다. 일반적으로 각 권장 사항은,
1 개 이상의 질의에 대해 이점을 제공합니다. 이점을 얻기 위해 최선의 개별 작업을 모두 정리하고 구현해야합니다. 여러 권장 사항에 대한 작업을 공유할 수 있습니다.
예를 들어, CREATE INDEX 문은 여러 질의에 대해 이점을 제공하지만, 그 질의 일부는 다른 CREATE MATERIALIZED VIEW 구문으로 이득 을 얻을 수 있습니다. 이 경우
관리자는 2 가지 권장 사항을 생성합니다. 최적으로 실행하기 위해 인덱스만을 필요로하는 쿼리 세트와 색인 및 Materialized View를 모두 필요로하는 쿼리 세트입니다.
파티션 권장 사항은 특수한 유형의 것이 좋습니다. SQL Access Advisor는 지정된 실제 테이블 파티셔닝을 통해 워크로드 성능을 향상 시킬 수 있다고 판단하는 경우, 그
열매 테이블을 참조하는 쿼리를 포함하는 모든 권고 사항에 파티션 작업을 추가합니다. 이렇게하면 인덱스 및 구체화된 뷰를 권장 사항을 적절하게 분할되는 테이블로
구현됩니다.
18.2.5.2 권장 옵션
관리자 권장 사항을 생성하려면 먼저 SET_TASK_PARAMETER 시저를 사용하여 작업의 매개 변수를 정의해야합니다. 매개 변수 정의가없 는 경우 데이터베이스가 기본값을
사용합니다.
작업 매개 변수를 설정하려면 SET_TASK_PARAMETER 시저를 사용합니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.SET_TASK_PARAMETER
( task_name IN VARCHAR2,
parameter IN VARCHAR2,
value IN [VARCHAR2 | NUMBER);
작업 매개 변수는 다수 존재하기 때문에 관련 매개 변수를 식별할 수 있도록,이 분류 분류하여 테이블을 표 18-1 에 나와 있습니다. 작 업 필터 작업 매개 변수는 사용되지
않게되어있다는 것을주의하십시오.
표 18-1 관리자 작업 매개 변수의 종류와 사용 방법
작업 필터 작업 구성 스키마 속성 권장 옵션
RANKING_MEASURE INDEX_NAME_TEMPLATE PARTITIONING_TYPES SQL_LIMIT MVIEW_NAME_TEMPLATE REFRESH_MODE START_TIME STORAGE_CHANGE
TIME_LIMIT USE_SEPARATE_TABLESPACES VALID_ACTION_LIST WORKLOAD_SCOPE VALID_MODULE_LIST
VALID_SQLSTRING_LIST
다음 예제에서는 작업 MYTASK 저장소 변경이 100MB로 설정되어 있습니다. 이것은 권장 사항의 추가 공간이 100MB임을 나타냅니다. 값이 0 인 경우에는 추가 공간을
할당할 수 없습니다. 마이너스 값은 지정된만큼 관리자가 현재 영역 사용량을 줄일 필요가 있음을 나 타냅니다.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( 'MYTASK', 'STORAGE_CHANGE', 100000000);
다음 예제에서는 SH.SALES 및 SH.CUSTOMERS 테이블을 구성하지 않은 모든 연락처를 필터링하도록 VALID_TABLE_LIST 매개 변수를 설 정합니다.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
'MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');
SET_TASK_PARAMETER 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
SQL Access Advisor는 문제 해결 모드와 평가 모드 2 가지 모드로 작동합니다. SQL Access Advisor는 기본적으로 접근 방법의 문제를 해결하기 위해, 인덱스 구조,
파티션, Materialized View 및 Materialized View 로그 확장을 요구합니다. 예를 들어, 문제 해결 모드로 실 행하면 새 인덱스를 만들거나 Materialized View 로그에 새 열
추가 등이 권장됩니다.
평가만으로 실행하면 SQL Access Advisor는 지정된 작업에서 사용되는 액세스 구조에 대해서만 설명합니다. 평가에만 실행하면 인덱 스의 유지와 Materialized View
관리 등의 추천에서만 가능합니다. 평가 모드에서 작업 부하가 어떤 색인 및 구체화된 뷰를 사용하는지 정확하게 알 수 있습니다. 기존의 열매 테이블 파티셔닝이 성능에
미치는 영향은 평가되지 않습니다.
18.2.5.4 권장 사항 분석하는 중간 결과보기
SQL Access Advisor는 분석 작업 중 중간 결과를 볼 수 있습니다. 이전에 분석 작업의 결과는 프로세스가 완료되거나 사용자가 끼어들 까지 사용할 수 없습니다. 현재는
SQL Access Advisor 작업이 실행중인 경우에도 해당 권장 사항 및 작업 테이블의 결과에 액세스할 수 있습니다. 그 혜택이 오래 걸리는 작업에서 장시간 실행이 완료될
때까지 기다리지 않고 작업에 끼어들하여 최신 결과를받을 수있게 된 것입니다.
최신 권장 사항 세트를 받기 위해서는 작업에 끼어들 필요가 있습니다. 이 인터럽트는 SQL Access Advisor는 작업을 중지하고 작업 INTERRUPTED 로 표시됩니다.
이때 최선의 특성을 업데이 트하여 스크립트를 생성할 수 있습니다. 또는 SQL 액세스 어드바이저 권장 프 로세스를 완료할 수 있습니다.
중간 결과는 그때까지의 작업 내용에 대한 권장 사항에 유의하십시오. 권장 사항이 워크로드의 영향을 받기 쉬운 것이 중요이면 작업 이 실행을 완료하는 것을
권장합니다. 또한 권장 프로세스의 초기 단계에서 관리자에 의해 창조되는 권장 사항은 실제 테이블 파티셔 닝 권장 포함되지 않습니다. 분할 분석에서는 파티션을
사용할 수 있는지 여부를 결정하기 전에 작업의 대부분을 처리할 필요가 있습 니다. 따라서 SQL 액세스 어드바이저가 이득을 발견한 경우에는 최신 단계의 중간
결과로 실제 테이블 파티셔닝에 대한 권장 사항이 포함되어 있습니다.
권장 사항을 생성하려면 작업 이름을 지정하여 EXECUTE_TASK 시저를 사용합니다. 절차가 끝난 후, DBA_ADVISOR_LOG 테이블을 확인하 고 실제 실행 상태, 생성된
권장 사항 및 작업의 수를 확인할 수 있습니다. {DBA, USER}_ADVISOR_RECOMMENDATIONS 에서는 작업 이름 추천 항목 쿼리 수 있습니다. {DBA, USER}_ADVISOR_ACTIONS
에서는 이러한 권장 사항에 대한 작업을 작업별로 볼 수 있습니다.
지정된 작업이 SQL 액세스 관리자에서 분석 또는 평가됩니다. 작업 실행 동기화 프로세스이므로 작업이 완료되거나 사용자가 인터럽 트가 감지될 때까지 제어를
사용자에게 반환되지 않습니다 작업 실행이 반환되면 실제 실행 상태에 대한 DBA ADVISOR LOG 테이블을 확인할 수 있습니다.
EXECUTE_TASK 을 실행하면 권장 사항이 생성됩니다. 이 경우 권장 사항은 구체화된 뷰를 기록 및 Materialized View를 만드는 등 1 개 이상의 조치로 구성됩니다.
이 구문은 다음과 같습니다.
DBMS_ADVISOR.EXECUTE_TASK (task_name IN VARCHAR2);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.EXECUTE_TASK ( 'MYTASK');
EXECUTE_TASK 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.5.6 권장 사항보기
SQL 액세스 관리자에서 생성된 각 권장 사항은 (DBA, USER)_ADVISOR_RECOMMENDATIONS 등 일부 카탈로그 뷰를 사용하여 볼 수 있습니 다. 그러나 GET_TASK_SCRIPT
시저를 사용하거나 Enterprise Manager에서 SQL 액세스 관리자를 사용하는 것이 더 쉽습니다. 이 경우 권 장 사항이 그래픽으로 표시되며 권장 사항이 도움이되는 SQL
문장을 쉽게 볼 수있는 하이퍼 링크가 포함되어 있습니다. SQL 액세스 관 리자에서 생성된 각 권장 사항은 권장 사항이 할 수있는 SQL 문장에 링크되어 있습니다.
다음 예제에서는 관리자를 실행하여 생성되는 권장 사항 ( rec_id )와 계급과 모든 이점을 나타냅니다. 평가는 권장 사항을 지원하는 쿼리의 중요성 척도입니다.
이점은 권장 사항을 사용하는 모든 쿼리의 실행 비용 (옵티마 이저 비용의 관점에서)의 개선 결과입니다.
VARIABLE workload_name VARCHAR2 (255); VARIABLE task_name VARCHAR2 (255); EXECUTE : task_name : = 'MYTASK';
EXECUTE : workload_name : = 'MYWORKLOAD';
SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = : task_name;
REC_ID RANK BENEFIT
---------- ---------- ----------
1 2 2754
2 3 1222
3 1 5499
4 4 594
권장 사항이 도움이 쿼리를 확인하려면 DBA_* 및 USER_ADVISOR_SQLA_WK_STMTS 보기를 사용합니다. 사전 비용과 사후 비용 수는 각각 권장되는 접근 구조의
변경이있는 경우와없는 경우의 추산되었다 최적화 프로그램의 비용 ( EXPLAIN PLAN 참조)에 관한 것입니다. 각 쿼리의 권장 사항을 보려면 다음 문을 실행합니다.
SELECT sql_id, rec_id, precost, postcost,
(precost - postcost) * 100/precost AS percent_benefit
FROM USER_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME = : task_name AND workload_name = : workload_name;
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------- -----
121 1 3003 249 91.7082 917
122 2 1404 182 87.0370 37
123 3 5503 4 99.9273124
124 4 730 136 81.3698 63
각 권장 사항은 1 개 이상의 조치로 구성됩니다. 최선의 이득을 얻기 위해서는 이러한 작업을 함께 구현해야합니다. SQL 액세스 관리 자는 다음 유형의 작업을 생성합니다.
PARTITION BASE TABLE 조치는 기존의 파티션되지 않은 실제 테이블을 분할합니다. CREATE 작업은 새로운 액세스 구조에 해당합니다. RETAIN 권장 사항은 기존 액세스
구조를 유지할 필요가 있음을 나타냅니다. DROP 권장 사항이 생성되는 것은 WORKLOAD_SCOPE 매개 변 수가 FULL 로 설정되어있는 경우입니다. GATHER STATS 조치는
DBMS_STATS 프로 시저에 대한 호출을 생성하고 새로 생성된 액세스 구 조에 대한 통계를 수집합니다. 여러 권장 사항이 동일한 작업을 참조할 수 있습니다. 그러나
최선의 스크립트 생성에서 각 작업은 1 번 만 표시됩니다.
다음 예제에서는 이러한 일련의 권장 사항에 대해 개별 작업의 수를 확인할 수 있습니다.
SELECT 'Action Count', COUNT (DISTINCT action_id) cnt
FROM USER_ADVISOR_ACTIONS WHERE task_name = : task_name;
'ACTIONCOUNT CNT
------------ -------- Action Count 20
- see the actions for each recommendations
SELECT rec_id, action_id, SUBSTR (command, 1,30) AS command
FROM user_advisor_actions
WHERE task_name = : task_name
ORDER BY rec_id, action_id;
REC_ID ACTION_ID COMMAND
---------- ---------- ------------------------------
1 5 CREATE MATERIALIZED VIEW LOG
1 6 ALTER MATERIALIZED VIEW LOG
1 7 CREATE MATERIALIZED VIEW LOG
1 8 ALTER MATERIALIZED VIEW LOG
1 9 CREATE MATERIALIZED VIEW LOG
1 10 ALTER MATERIALIZED VIEW LOG
1 11 CREATE MATERIALIZED VIEW
1 12 GATHER TABLE STATISTICS
1 19 CREATE INDEX
1 20 GATHER INDEX STATISTICS
2 5 CREATE MATERIALIZED VIEW LOG
2 6 ALTER MATERIALIZED VIEW LOG
2 9 CREATE MATERIALIZED VIEW LOG
...
각 작업에 액세스 구조의 속성에 관련된 여러 속성이 있습니다. 각 액세스 구조의 이름과 테이블 공간은 필요에 따라 attr1 및 attr2
에 각각 저장됩니다. 새로운 각 액세스 구조가 차지하는 공간은 num_attr1 있습니다. 다른 모든 속성은 작업에 따라 다릅니다.
표 18-2 에 SQL 액세스 어드바이저 작업 정보와 그에 해당하는 DBA_ADVISOR_ACTIONS 의 열을 나타냅니다. 테이블의 "MV"는 구체화된 뷰를 의미합니다.
표 18-2 SQL 액세스 어드바이저의 액션 속성
|
|
ATTR1 |
ATTR2 |
ATTR3 |
ATTR4 |
ATTR5 |
ATTR6 |
NUM_ATTR1 | |
|
CREATE INDEX |
|
인덱스 이름 |
인덱스 테이블 스페이스 |
목표 테이블 |
BITMAP 또는 BTREE |
인덱스 컬럼리 스트 / 식 |
미사용 |
색인 저장소 크기 (바이트) |
|
CREATE MATERIALIZED |
VIEW |
MV 이름 |
MV 테이블 스 페이스 |
REFRESH COMPLETE |
ENABLE QUERY REWRITE , |
SQL SELECT 문 |
미사용 |
MV 저장소 크 기 (바이트) |
|
|
REFRESH |
DISABLE |
|
|
| |||
|
FAST , |
QUERY |
|
|
| ||||
|
REFRESH |
REWRITE |
|
|
| ||||
|
FORCE , |
|
|
|
| ||||
|
NEVER |
|
|
|
| ||||
|
REFRESH |
|
|
|
| ||||
CREATE 대상 테이블 이 MV 로그 테이 ROWID
INCLUDING
테이블 열 목록 파티션 부차 어구 미사용
MATERIALIZED VIEW 름
LOG
블 스페이스
PRIMARY KEY
, SEQUENCE
OBJECT ID
NEW VALUES , EXCLUDING NEW VALUES
CREATE REWRITE EQUIVALENCE
해당 이름 체크섬 값이 미사용 미사용 원본 SQL 문 해당 SQL 문을 미사용
DROP INDEX 인덱스 이름 미사용 미사용 미사용 인덱스 열 미사용 색인 저장소 크기 (바이트)
|
CREATE |
MV 이름 |
미사용 |
미사용 |
미사용 |
미사용 |
미사용 |
MV 저장소 크 |
|
MATERIALIZED VIEW |
|
|
|
|
|
|
기 (바이트) |
|
DROP MATERIALIZED |
대상 테이블 이 미사용 |
미사용 |
미사용 |
미사용 |
미사용 |
미사용 |
|
VIEW LOG |
름 |
|
|
|
|
|
PARTITION TABLE 테이블 이름 RANGE , INTERVAL , LIST , HASH , RANGE-HASH , RANGE-LIST
파티션의 파 티션 키 (열 또는 열 이름 목록)
사부빠티숀화 된 파티션 키 (열 또는 열 이 름 목록)
SQL PARTITION 절 을
SQL SUBPARTITION 어 구
미사용
PARTITION INDEX 인덱스 이름 LOCAL , RANGE
, HASH
파티션의 파 티션 키 (열 이름 목록)
미사용 SQL PARTITION 절 을
미사용 미사용
PARTITION ON MATERIALIZED VIEW
MV 이름 RANGE , INTERVAL , LIST , HASH , RANGE-HASH , RANGE-LIST
파티션의 파 티션 키 (열 또는 열 이름 목록)
사부빠티숀화 된 파티션 키 (열 또는 열 이 름 목록)
SQL SUBPARTITION 어구
SQL SUBPARTITION 어 구
미사용
|
RETAIN INDEX |
인덱스 이름 |
미사용 |
목표 테이블 |
BITMAP 또는 |
인덱스 열 |
미사용 |
색인 저장소 |
|
|
|
|
|
BTREE |
|
|
크기 (바이트) |
|
RETAIN |
|
MV 이름 |
미사용 |
REFRESH |
미사용 |
SQL SELECT 문 |
미사용 |
MV 저장소 크 |
|
MATERIALIZED |
VIEW |
|
|
COMPLETE 또 |
|
|
|
기 (바이트) |
|
|
|
|
|
는 REFRESH FAST |
|
|
|
|
대상 테이블 이 미사용 미사용 미사용 미사용 미사용 미사용 름
다음은 PL / SQL 프로 시저는 권장 사항의 일부 특성을 출력할 수 있습니다.
CONNECT SH / SH;
CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS CURSOR curs IS
SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
FROM user_advisor_actions
WHERE task_name = in_task_name
ORDER BY action_id; v_action number; v_command VARCHAR2 (32); v_attr1 VARCHAR2 (4000); v_attr2 VARCHAR2 (4000); v_attr3 VARCHAR2 (4000); v_attr4 VARCHAR2 (4000); v_attr5 VARCHAR2 (4000);
BEGIN
OPEN curs;
DBMS_OUTPUT.PUT_LINE ('========================================='); DBMS_OUTPUT.PUT_LINE ( 'Task_name ='| | in_task_name);
LOOP
FETCH curs INTO
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4; EXIT when curs % NOTFOUND;
DBMS_OUTPUT.PUT_LINE ( 'Action ID :'| | v_action); DBMS_OUTPUT.PUT_LINE ( 'Command :'| | v_command); DBMS_OUTPUT.PUT_LINE ( 'Attr1 (name) :'| | SUBSTR (v_attr1, 1,30));
DBMS_OUTPUT.PUT_LINE ( 'Attr2 (tablespace) :'| | SUBSTR (v_attr2, 1,30)); DBMS_OUTPUT.PUT_LINE ( 'Attr3 :'| | SUBSTR (v_attr3, 1,30)); DBMS_OUTPUT.PUT_LINE ( 'Attr4 :'| | v_attr4);
DBMS_OUTPUT.PUT_LINE ( 'Attr5 :'| | v_attr5);
DBMS_OUTPUT.PUT_LINE ('----------------------------------------'); END LOOP;
CLOSE curs;
DBMS_OUTPUT.PUT_LINE ('========= END RECOMMENDATIONS ============'); END show_recm;
/
- see what the actions are using sample procedure set serveroutput on size 99999
EXECUTE show_recm (: task_name);
A fragment of a sample output from this procedure is as follows : Task_name = MYTASK
Action ID : 1
Command : CREATE MATERIALIZED VIEW LOG Attr1 (name) : "SH" "CUSTOMERS"
Attr2 (tablespace)
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES Attr5 :
----------------------------------------
..
---------------------------------------- Action ID : 15
Command : CREATE MATERIALIZED VIEW Attr1 (name) : "SH" "SH_MV $ $ _0004" Attr2 (tablespace) : "SH_MVIEWS"
Attr3 : REFRESH FAST WITH ROWID Attr4 : ENABLE QUERY REWRITE Attr5 :
----------------------------------------
..
---------------------------------------- Action ID : 19
Command : CREATE INDEX
Attr1 (name) : "SH" "SH_IDX $ $ _0013" Attr2 (tablespace) : "SH_INDEXES"
Attr3 : "SH" "SH_MV $ $ _0002" Attr4 : BITMAP
Attr5 :
Attr5 및 Attr6 의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.5.7 권장 프로세스 중지
EXECUTE_TASK 시저를 사용하여 SQL Access Advisor의 권장 사항을 생성하는 데 처리 시간이 너무 오래 걸릴 경우 CANCEL_TASK 프로 시 저를 호출하고 그 추천 과정 task_name 를 전달하면 작업을 중지할 수 있습니다. CANCEL_TASK 를 사용하면 SQL Access Advisor의 권장 사항은 없습니다. 따라서 권장 사항이 필요한 경우, INTERRUPT_TASK 시저의 사용을 고려하십시오.
18.2.5.7.1 작업에 대한 인터럽트
INTERRUPT_TASK 시저를 사용하면 관리자 작업이 성공에 도달하는 것으로 종료됩니다. 이를 통해 사용자는 인터럽트 지점까지 형성된 권장 사항이 표시됩니다.
인터럽트 작업을 재개할 수 없습니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.INTERRUPT_TASK ( 'MY_TASK');
18.2.5.7.2 작업 취소
CANCEL_TASK 시저를 사용하면 현재 실행중인 작업이 종료됩니다. 이 경우 관리자 작업이 요청에 응답하는 데 몇 초가 걸릴 수 있습니 다. 모든 관리자 작업 절차는 동기화 작업이기 때문에 작업을 취소하려면 다른 데이터베이스 세션을 사용해야합니다.
취소 명령은 취소된 작업을 시작하기 전의 상태로 작업을 효율적으로 복원합니다. 따라서 삭제된 태스크 또는 데이터 개체는 다시 시 작할 수 없습니다 (단, DBMS_ADVISOR.RESET_TASK 를 사용하고 작업을 다시 실행하여 작업을 다시 설정할 수 있습니다.) 이 구문은 다음 과 같습니다.
DBMS_ADVISOR.CANCEL_TASK (task_name IN VARCHAR2);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.CANCEL_TASK ( 'MYTASK');
CANCEL_TASK 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.5.8 권장 사항 표시
기본적으로 SQL 액세스 어드바이저의 모든 권장 사항 구현 가능한 상태에 있지만, MARK_RECOMMENDATION 시저를 사용하면 선택한 권장 사항을 생략하거나 제외할 수 있습니다. MARK_RECOMMENDATION 에서는 사용자가 REJECT 또는 IGNORE 설정을 사용하여 권장 사항에 주 석을 달 수 있습니다. 구현 절차를 생성할 때, GET_TASK_SCRIPT 은 주석이 권장 사항을 생략합니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.MARK_RECOMMENDATION (
task_name IN VARCHAR2 id IN NUMBER,
action IN VARCHAR2);
다음 예제에서는 ID가 2 권장 사항을 REJECT 로 표시합니다. 이 권장 사항 및 이것에 의존하는 권장 사항은 스크립트에 표시되지 않습 니다.
EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION ( 'MYTASK', 2, 'REJECT');
관리자는 파티셔닝되지 않은 1 개 이상의 실제 테이블 파티셔닝이 권장되는 경우 권장 사항을 스킵하기 전에 신중하게 검토하십시오. 테이블 파티셔닝 스키마를 변경하면 테이블에 정의된 모든 쿼리, 인덱스 및 구체화된 뷰를 비용에 영향을 미칩니다. 따라서 분할 권장 사항을 생략하면 해당 테이블에 관리자가 수행한 기타 권장 적합하지 않습니다. 파티션을 포함하지 않고 작업 권장 사항을 확인하려면 ANALYSIS_SCOPE 매개 변수를 변경하여 파티션의 권장 사항을 제외하고 관리자 작업을 리셋하고 다시 작업을 수행합니다.
MARK_RECOMMENDATIONS 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.5.9 권장 사항의 변경
UPDATE_REC_ATTRIBUTES 시저를 사용하여 SQL 액세스 관리자는 분석 작업시 인덱스 및 구체화된 뷰를 같은 새 개체에 이름이 소유자가 할당됩니다. 그러나 반드시 적절한 이름을 선택된다고는 할 수는 없으므로 새 개체의 소유자 이름과 테이블 스페이스의 값을 수동으로 설정할 수 있습니다. 기존 데이터베이스 개체를 참조 권장 사항의 경우에는 소유자와 이름의 값을 변경할 수 없습니다. 이 구문은 다음 과 같습니다.
DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES (
task_name IN VARCHAR2 rec_id IN NUMBER, action_id IN NUMBER,
attribute_name IN VARCHAR2, value IN VARCHAR2);
attribute_name 매개 변수는 다음 값을 사용할 수 있습니다.
추천 개체의 소유자 이름을 지정합니다.
추천 개체의 이름을 지정합니다.
추천 개체의 테이블 스페이스를 지정합니다.
다음 예제에서는 권장 사항 ID 1, 작업 ID 1 TABLESPACE 특성을 SH_MVIEWS 로 변경합니다.
EXECUTE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES ( 'MYTASK', 1, 1 -
'TABLESPACE', 'SH_MVIEWS');
UPDATE_REC_ATTRIBUTES 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.5.10 SQL 스크립트 생성
권장 사항을 표시하기 위해 메타 데이터를 연락하실 또 1 개의 방법은 GET_TASK_SCRIPT 시저를 사용하여 권장 사항의 SQL 스크립트를 만드는 방법입니다. 이 결과 생성된 스크립트 실행 SQL 파일, DROP , CREATE 및 ALTER 문장을 포함할 수 있습니다. 새 개체의 경우, Materialized View의 이름, Materialized View 로그 및 인덱스는 사용자 지정 이름 템플릿을 사용하여 자동으로 생성됩니다. 생성된 SQL 스크립트를 실행하기 전에 검토할 필요가 있습니다.
네이밍 규칙 ( MVIEW_NAME_TEMPLATE 및 INDEX_NAME_TEMPLATE )이 새 개체의 소유자 ( DEF_INDEX_OWNER 및 DEF_MVIEW_OWNER ), 테이블
스페이스 ( DEF_MVIEW_TABLESPACE 및 DEF_INDEX_TABLESPACE )를 제어하는 여러 작업 매개 변수가 있습니다.
다음은 권장 사항에 스크립트가 포함된 CLOB를 생성하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT ( 'MYTASK'),
'ADVISOR_RESULTS', 'advscript.sql');
스크립트 파일에 저장하려면 CREATE_FILE 시저 스크립트를 저장할 위치를 나타내는 디렉토리 경로를 지정해야합니다. 또한이 디렉토 리는 읽기 및 쓰기 권한을 부여해야합니다. 다음 예제는 CLOB 관리자 스크립트를 파일로 저장하는 방법을 보여줍니다.
- create a directory and grant permissions to read / write to it
CONNECT SH / SH;
CREATE DIRECTORY ADVISOR_RESULTS AS '/ mydir'; GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
다음 예제에서는이 스크립트에 의해 생성된 스크립트의 조각입니다. 또한이 스크립트에는 권장되는 접근 구조에 대한 통계를 수집하 는 PL / SQL 호출을 포함, 마지막으로 권장 사항을 IMPLEMENTED 로 표시합니다.
Rem Access Advisor V11.1.0.0.0 - Production
Rem
Rem Username : SH Rem Task : MYTASK
Rem Execution date : 15/08/2006 11:35
Rem
set feedback 1 set linesize 80 set trimspool on set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON "SH" "PRODUCTS"
WITH ROWID, SEQUENCE ( "PROD_ID", "PROD_SUBCATEGORY") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "PRODUCTS"
ADD ROWID, SEQUENCE ( "PROD_ID", "PROD_SUBCATEGORY") INCLUDING NEW VALUES;
..
CREATE MATERIALIZED VIEW "SH" "MV $ $ _00510002" REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, COUNT (*) M1 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP
BY SH.CUSTOMERS.CUST_STATE_PROVINCE; BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( ' "SH",' "MV $ $ _00510002" ', NULL, DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
..
CREATE BITMAP INDEX "SH" "MV $ $ _00510004_IDX $ $ _00510013" ON "SH" "MV $ $ _00510004"( "C4");
whenever sqlerror EXIT SQL.SQLCODE BEGIN
DBMS_ADVISOR.MARK_RECOMMENDATION ( ' "MYTASK"', 1, 'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION ( ' "MYTASK"', 2, 'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION ( ' "MYTASK"', 3, 'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION ( ' "MYTASK"', 4 'IMPLEMENTED');
END;
/
18.2.5.11 스크립트 파티션 권장 사항이 포함되어있는 경우의 특수 고려 사항
관리자는 쿼리 성능을 향상시키기 위해 기존의 파티션되지 않은 실제 테이블 파티셔닝을 권장할 수 있습니다. 관리자 구현 스크립트 파티션 권장 사항이 포함되어있는 경우 다음 사항에 유의하십시오.
테이블과 동일한 용량의 공간을 사용합니다. 따라서 다시 분할 프로세스는 다시 분할하는 가장 큰 테이블의 복사본을 1 개 만들 수있을 정도로 충분한 여유 디스크 공간이 필요합니다. 구현 스크립트를 실행하기 전에 사용 가능한 공간을 확보하고 있어야합 니다.
파티션 구현 스크립트 색인, Materialized View 및 제약 조건과 같은 종속 개체의 전환을 시도합니다. 그러나 일부 개체는 자동으 로 마이그레이션할 수 없습니다. 예를 들어, 재분화된 실제 테이블에 대한 PL / SQL 저장 프로 시저의 정의는 일반적으로 비활성 화되어 다시 컴파일해야합니다.
18.2.5.12 권장 사항이 더 이상 필요없는
RESET_TASK 시저는 작업을 초기 시작점으로 재설정합니다. 이렇게하면 모든 권장 사항과 중간 데이터가 작업에서 제거됩니다. 실제 작 업의 상태는 INITIAL 로 설정됩니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.RESET_TASK (task_name IN VARCHAR2);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.RESET_TASK ( 'MYTASK');
RESET_TASK 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.6 빠른 조정을 실행
단일 SQL 문장을 튜닝하려면 task_name 과 SQL 문장을 입력으로 받아 QUICK_TUNE 시저를 사용합니다. 이 프로 시저는 작업 및 작업을 만들고 작업을 수행합니다. QUICK_TUNE 를 사용해도 결과에는 차이가 없습니다. 결과는 EXECUTE_TASK 를 사용하는 경우와 동일합니 다. 튜닝 대상 SQL 문이 1 개만있는 경우이 방법을 사용하는 것이 더 쉽습니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.QUICK_TUNE ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, attr1 IN CLOB,
attr2 IN VARCHAR2 : = NULL, attr3 IN NUMBER : = NULL, task_or_template IN VARCHAR2 : = NULL);
다음 예제에서는 단일 SQL 문장을 빠른 조정하는 방법을 보여줍니다.
VARIABLE task_name VARCHAR2 (255); VARIABLE sql_stmt VARCHAR2 (4000);
EXECUTE : sql_stmt : = 'SELECT COUNT (*) FROM customers
WHERE cust_state_province = '' CA '' '; EXECUTE : task_name : = 'MY_QUICKTUNE_TASK';
EXECUTE DBMS_ADVISOR.QUICK_TUNE (DBMS_ADVISOR.SQLACCESS_ADVISOR,
: task_name, : sql_stmt);
QUICK_TUNE 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.7 작업 관리
권장 사항을 생성하면 항상 작업이 생성됩니다. 이러한 작업을 관리하지 않으면 곧 작업 수가 증가하고 저장 공간이 점령됩니다. 또한 일부 작업을 유지하는 경우 실수로 삭제되지 않도록 보호합니다. 작업 관리는 다음과 같은 작업을 수행할 수 있습니다.
18.2.7.1 작업 특성 업데이트
UPDATE_TASK_ATTRIBUTES 시저를 사용하면 다음 작업을 수행할 수 있습니다.
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES (
task_name IN VARCHAR2
new_name IN VARCHAR2 : = NULL, description IN VARCHAR2 : = NULL, read_only IN VARCHAR2 : = NULL, is_template IN VARCHAR2 : = NULL, how_created IN VARCHAR2 : = NULL);
다음 예제에서는 작업 MYTASK 이름이 TUNING1 업데이 트됩니다.
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( 'MYTASK', 'TUNING1');
다음 예제에서는 작업 TUNING1 이 읽기 전용으로 설정됩니다.
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( 'TUNING1', read_only => "TRUE ');
다음 예제에서는 작업 MYTASK 가 템플릿으로 설정됩니다.
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( 'TUNING1', is_template => "TRUE ');
UPDATE_TASK_ATTRIBUTES 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.7.2 작업 삭제
DELETE_TASK 시저는 관리자의 기존 작업을 저장소에서 삭제됩니다. 이 구문은 다음과 같습니다.
DBMS_ADVISOR.DELETE_TASK (task_name IN VARCHAR2);
그런 다음이 절차를 사용하는 방법을 보여줍니다.
EXECUTE DBMS_ADVISOR.DELETE_TASK ( 'MYTASK');
DELETE_TASK 시저 및 매개 변수의 자세한 내용은 Oracle Database PL / SQL 패키지 절차와 형식 참조를 참조하십시오.
18.2.7.3 DAYS_TO_EXPIRE 매개 변수 설정
작업 또는 작업 개체를 만들면 DAYS_TO_EXPIRE 매개 변수를 30으로 설정됩니다. 이 값은 작업이나 개체가 자동으로 삭제될 때까지 남 은 일 수를 나타냅니다. 작업 또는 작업 부하를 무한정 저장 계속하려면 DAYS_TO_EXPIRE 매개 변수를 ADVISOR_UNLIMITED 로 설정합니 다.
18.2.8 SQL 액세스 어드바이저의 상수를 사용하는 방법
SQL 액세스 관리자는 표 18-3 에 나와있는 상수를 사용할 수 있습니다.
ADVISOR_ALL 모든 가능한 값을 나타내는 값입니다. 문자열 매개 변수는이 값은 와일드 카드 문자인 % 에 해당합니다. ADVISOR_CURRENT 현재 시간 또는 활성 요소 집합을 나타냅니다. 일반적으로 시간 매개 변수로 사용합니다. ADVISOR_DEFAULT 기본값을 보여줍니다. 일반적으로 작업 또는 작업 매개 변수를 설정할 때 사용합니다. ADVISOR_UNLIMITED 전면적인 숫자를 나타내는 값입니다.
ADVISOR_UNUSED 사용되지 않은 엔터티를 나타내는 값입니다. 매개 변수가 ADVISOR_UNUSED 로 설정되어있는 경우 현재 작 업이 매개 변수의 영향을받지 않습니다. 일반적으로이 상수를 사용하여 매개 변수에 의존하는 처리에 매 개 변수를 사용하지 않는 설정합니다.
SQLACCESS_GENERAL SQL 액세스 일반 작업 서식 파일의 기본 이름을 지정합니다. 이 템플릿은 DML_VOLATILITY 작업 매개 변수 를 TRUE 로 설정하고 ANALYSIS_SCOPE 를 INDEX , MVIEW 로 설정합니다.
SQLACCESS_OLTP SQL 액세스 OLTP 작업 서식 파일의 기본 이름을 지정합니다. 이 템플릿은 DML_VOLATILITY 작업 매개 변 수를 TRUE 로 설정하고 ANALYSIS_SCOPE 을 INDEX 로 설정합니다.
SQLACCESS_WAREHOUSE SQL 액세스웨어 하우스 작업 서식 파일의 기본 이름을 지정합니다. 이 템플릿은 DML_VOLATILITY 작업 매 개 변수를 FALSE 로 설정하고 EXECUTION_TYPE 를 INDEX , MVIEW 로 설정합니다.
18.2.9 SQL Access Advisor 사용 예
이 단원에서는 SQL 액세스 어드바이저의 일반적인 사용법에 대해서 설명합니다. Oracle Database는이 장의 예제를 포함
aadvdemo.sql 라는 스크립트를 제공하고 있습니다.
18.2.9.1 사용자 정의 작업에 대한 권장 사항
다음 예제에서는 사용자 정의 테이블 SH.USER_WORKLOAD 에서 작업 부하를 가져옵니다. 다음 MYTASK 라는 작업이 생성되고 저장소 제한 이 100MB로 설정되며, 작업이 실행됩니다. PL / SQL 프로 시저는 권장 사항이 표시됩니다. 마지막으로 권장 사항을 구현하기위한 스크 립트가 생성됩니다.
1 단계 : USER_WORKLOAD 표 준비
SQL 문을 사용하여 USER_WORKLOAD 테이블을로드합니다.
CONNECT SH / SH;
- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ( 'SH', 'Example1', 'Action', 2,
'SELECT t.week_ending_day, p.prod_subcategory,
SUM (s.amount_sold) AS dollars, s.channel_id, s.promo_id
FROM sales s, times t, products p WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id AND s.prod_id> 10 AND s.prod_id <50
GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id ')
/
- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ( 'SH', 'Example1', 'Action', 2,
'SELECT t.calendar_month_desc, SUM (s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
AND s.time_id between TO_DATE ('' 01 - JAN - 2000 '', '' DD - MON - YYYY '')
AND TO_DATE ('' 01 - JUL - 2000 '', '' DD - MON - YYYY '') GROUP BY t.calendar_month_desc ')
/
- Load all SQL queries.
INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ( 'SH', 'Example1', 'Action', 2,
'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM (s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id AND c.cust_state_province = '' CA '' AND ch.channel_desc IN ('' Internet '','' Catalog '')
AND t.calendar_quarter_desc IN ('' 1999 - Q1 '','' 1999 - Q2 '') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc ')
/
- order by
INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ( 'SH', 'Example1', 'Action', 2,
'SELECT c.country_id, c.cust_city, c.cust_last_name FROM customers c WHERE c.country_id IN (52790, 52789) ORDER BY c.country_id, c.cust_city, c.cust_last_name ')
/ COMMIT;
CONNECT SH / SH;
set serveroutput on;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2 (255); VARIABLE workload_name VARCHAR2 (255);
2 단계 : SQL 튜닝 세트 MYWORKLOAD 만들기
EXECUTE : workload_name : = 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET (: workload_name, 'test purposeV);
3 단계 : 사용자 정의 테이블 SH.USER_WORKLOAD에서 SQL 튜닝 세트로드
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR / * a sqlset cursor variable * / BEGIN
OPEN sqlset_cur FOR SELECT
SQLSET_ROW (null, sql_text, null, null, username, null, null, 0,0,0,0,0,0,0,0,0, null, 0,0,0,0)
AS ROW
FROM USER_WORKLOAD;
DBMS_SQLTUNE.LOAD_SQLSET (: workload_name, sqlset_cur); END;
4 단계 : 작업 MYTASK 만들기
EXECUTE : task_name : = 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( 'SQL Access Advisor', : task_id, : task_name);
5 단계 : 작업 매개 변수 설정
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (: task_name 'STORAGE_CHANGE', 100); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (: task_name 'ANALYSIS_SCOPE, INDEX');
6 단계 : SQL 튜닝 세트와 작업 간의 링크 만들기
EXECUTE DBMS_ADVISOR.ADD_STS_REF (: task_name, : workload_name);
7 단계 : 작업 수행
EXECUTE DBMS_ADVISOR.EXECUTE_TASK (: task_name);
8 단계 : 권장 사항보기
- See the number of recommendations and the status of the task. SELECT rec_id, rank, benefit
FROM user_advisor_recommendations WHERE task_name = : task_name;
자세한 내용은 "권장 사항보기" 또는 "SQL 스크립트 생성" 을 참조하십시오.
- See recommendation for each query. SELECT sql_id, rec_id, precost, postcost,
(precost - postcost) * 100/precost AS percent_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = : task_name AND workload_name = : workload_name;
- See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR (command, 1,30) AS command
FROM user_advisor_actions WHERE task_name = : task_name ORDER BY rec_id, action_id;
- See what the actions are using sample procedure. SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm (: task_name);
9 단계 : 권장 사항을 구현하기위한 스크립트 생성
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_name)
'ADVISOR_RESULTS', 'Example1_script.sql');
18.2.9.2 작업 템플릿을 사용하여 최선의 생성
다음 예제에서는 템플릿을 만들고이를 사용하여 작업을 만듭니다. 다음에이 작업을 사용하여 "사용자 정의 작업에 대한 권장 사항" 과 마찬가지로 사용자 정의 테이블에서 권장 사항이 생성됩니다.
CONNECT SH / SH;
VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2 (255);
1 단계 : 템플릿 MY_TEMPLATE 만들기
EXECUTE : template_name : = 'MY_TEMPLATE'; EXECUTE DBMS_ADVISOR.CREATE_TASK (-
'SQL Access Advisor', : template_id, : template_name, is_template => "TRUE ');
2 단계 : 템플릿 매개 변수 설정
권장되는 인덱스 및 구체화된 뷰 이름 지정 규칙을 설정합니다.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'INDEX_NAME_TEMPLATE', 'SH_IDX $ $ _ <SEQ>'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'MVIEW_NAME_TEMPLATE', 'SH_MV $ $ _ <SEQ>');
- Set default owners for recommended indexes / materialized views. EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_INDEX_OWNER', 'SH'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_MVIEW_OWNER', 'SH');
- Set default tablespace for recommended indexes / materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_INDEX_TABLESPACE', 'SH_INDEXES'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (-
: template_name 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
3 단계 : 템플릿을 사용하여 작업 만들기
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2 (255); EXECUTE : task_name : = 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK (-
'SQL Access Advisor', : task_id, : task_name, template => "MY_TEMPLATE ');
- See the parameter settings for task SELECT parameter_name, parameter_value FROM user_advisor_parameters
WHERE task_name = : task_name AND (parameter_name LIKE '% MVIEW %' OR parameter_name LIKE '% INDEX %');
4 단계 : SQL 튜닝 세트 MYWORKLOAD 만들기
EXECUTE : workload_name : = 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET (: workload_name 'test_purpose');
5 단계 : 사용자 정의 테이블 SH.USER_WORKLOAD에서 SQL 튜닝 세트로드
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR / * a sqlset cursor variable * / BEGIN
OPEN sqlset_cur FOR SELECT
SQLSET_ROW (null, sql_text, null, null, username, null, null, 0,0,0,0,0,0,0,0,0, null, 0,0,00) AS row
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET (: workload_name, sqlsetcur); END;
6 단계 : 작업 및 작업 사이의 링크 만들기
EXECUTE DBMS_ADVISOR.ADD_STS_REF (: task_name, : workload_name);
7 단계 : 작업 수행
EXECUTE DBMS_ADVISOR.EXECUTE_TASK (: task_name);
8 단계 : 스크립트 생성
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_name) -
'ADVISOR_RESULTS', 'Example2_script.sql');
18.2.9.3 인덱스 및 구체화된 뷰를 현재 사용 평가
이 예제에서는 SQL 액세스 관리자를 사용하여 기존의 인덱스 및 구체화된 뷰를 사용을 평가하는 방법을 보여줍니다. "사용자 정의 작 업에 대한 권장 사항" 절에 설명되어있는 것처럼 작업을 USER_WORKLOAD 테이블에로드합니다. 특정 작업에 사용되는 인덱스 및 구체화 된 뷰는 SQL Access Advisor의 권장 사항 RETAIN 작업으로 표시됩니다.
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2 (255); VARIABLE workload_name VARCHAR2 (255);
1 단계 : SQL 튜닝 세트 WORKLOAD 만들기
EXECUTE : workload_name : = 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET (: workload_name 'test_purpose');
2 단계 : 사용자 정의 테이블 SH.USER_WORKLOAD에서 SQL 튜닝 세트로드
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR / * a sqlset cursor variable * / BEGIN
OPEN sqlset_cur FOR SELECT
SQLSET_ROW (null, sql_text, null, null, username, null, null, 0,0,0,0,0,0,0,0,0, null, 0,0,0,0)
AS ROW
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET (: workload_name, : sqlsetcur); END;
3 단계 : 작업 MY_EVAL_TASK 만들기
EXECUTE : task_name : = 'MY_EVAL_TASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( 'SQL Access Advisor', : task_id, : task_name);
4 단계 : 작업 및 작업 사이의 링크 만들기
EXECUTE DBMS_ADVISOR.ADD_STS_REF (: task_name, : workload_name);
5 단계 : EVALUATION ONLY 작업을 나타내는 작업 매개 변수 설정
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (: task_name 'EVALUATION_ONLY', 'TRUE');
6 단계 : 작업 수행
EXECUTE DBMS_ADVISOR.EXECUTE_TASK (: task_name);
7 단계 : 평가 결과보기
- See the number of recommendations and the status of the task. SELECT rec_id, rank, benefit
FROM user_advisor_recommendations WHERE task_name = : task_name;
- See the actions for each recommendation.
SELECT rec_id, action_id, SUBSTR (command, 1,30) AS command, attr1 AS name
FROM user_advisor_actions WHERE task_name = : task_name
ORDER BY rec_id, action_id;
18.3 고속 재생 및 쿼리 재작성위한 구체화된 뷰 조정
고속 재생 및 쿼리 재작성 위해 최적화된 구체화된 뷰를 만들려면 여러 DBMS_MVIEW 절차가 유용합니다. EXPLAIN_MVIEW 시저는 Materialized View 빠르게 재생 가능한지, 또한 일반 쿼리 재작성을 수행할 수 있는지 여부를 나타냅니다. EXPLAIN_REWRITE 은 쿼리 재 작성을할지 여부를 나타냅니다. 그러나 이러한 절차는 고속 재생이나 쿼리 재작성를 실행하는 방법은 제공되지 않습니다.
Materialized View를 더욱 사용하기 쉽게하기 위해 TUNE_MVIEW TUNE_MVIEW 시저는 CREATE MATERIALIZED VIEW 구문을 최적화하는 방 법과 빠른 재생 및 일반 쿼리 재작성위한 추가 요구 사항 (Materialized View 로그와 다시 등가화된 관계 등)를 만족시키는 방법이 표시 됩니다. TUNE_MVIEW 하여 CREATE MATERIALIZED VIEW 구문 분석 및 처리되고, 2 개의 출력 (Materialized View의 구현과 CREATE MATERIALIZED VIEW 작업 취소)이 생성됩니다. 이 2 세트의 결과는, 뷰 또는 SQL 액세스 관리자에서 생성되는 외부 스크립트 파일을 통 해 액세스할 수 있습니다. 이러한 외부 스크립트 파일을 실행하면 구체화된 뷰를 구현할 수 있습니다.
관련 항목 :
18.3.1 DBMS_ADVISOR.TUNE_MVIEW 절차
이 단원에서는 다음 항목에 대해 설명합니다.
18.3.1.1 TUNE_MVIEW 구문과 조작
TUNE_MVIEW 의 구문은 다음과 같습니다.
DBMS_ADVISOR.TUNE_MVIEW (
task_name IN OUT VARCHAR2, mv_create_stmt IN [CLOB | VARCHAR2)
TUNE_MVIEW 시저는 task_name 과 mv_create_stmt 라는 2 개의 입력 매개 변수가 사용됩니다. task_name 는 사용자 지정 작업 식별자, 출력에 액세스하는 데 사용됩니다. mv_create_stmt 은 튜닝을하는 완전한 CREATE MATERIALIZED VIEW 글입니다. 입력된 CREATE MATERIALIZED VIEW 문장 REFRESH FAST 절이나 ENABLE QUERY REWRITE 어구의 한쪽 또는 양쪽 모두가 포함되지 않으면 TUNE_MVIEW
의 기본이다 REFRESH FORCE 어구 및 DISABLE QUERY REWRITE 어구를 사용하여 문장을 조정 가능 경우 빠른 재생, 그렇지 않으면 완전
히 새로 고침이 수행됩니다.
TUNE_MVIEW 시저는 내부에 어떤 쿼리 정의를 가질 수있는 다양한 CREATE MATERIALIZED VIEW 구문이 처리됩니다. 이 쿼리 정의는 단순 한 SELECT 문장이다 경우에도 집합 연산자와 인라인 뷰를 가진 복잡한 질의가있을 수 있습니다. Materialized View 질의 정의 REFRESH FAST 절이 포함된 경우 TUNE_MVIEW 에서 해당 쿼리를 분석하고, 고속 재생이 가능한지 확인합니다. 고속 재생이 가능한 경우, "구체화 된 뷰는 이미 적합하며, 더 이상 튜닝 수 없습니다"라는 메시지가 리턴됩니다. 그렇지 않으면 TUNE_MVIEW 시저가 특정 구문에 대한 튜 닝 작업을 시작합니다.
TUNE_MVIEW 시저는 FAST REFRESH 가 가능하도록 필수 집계 라인 등 새로운 라인을 추가하거나 Materialized View 로그 수정해서 쿼리 정의를 수정하는 출력 문장을 생성할 수 있습니다. 복잡한 쿼리 정의 경우 TUNE_MVIEW 프로 시저는 해당 쿼리를 분해하여 여러 개의 고 속 재생 가능한 구체화된 뷰를 생성하거나 고속 재생 요구 사항을 최대한 충족하는 방법으로 구체화된 뷰를 다시 작성 수 있습니다. TUNE_MVIEW 시저는 다음과 복잡한 쿼리 구조를 가진 쿼리 정의를 지원합니다.
ENABLE QUERY REWRITE 절을 지정하는 경우 TUNE_MVIEW 는 REFRESH FAST 와 비슷한 프로세스를 사용하여 문장의 수정도합니다. 이 절 차는 최대한 많은 확장 형식의 쿼리 재작성이 가능하도록 구체화된 뷰를 다시 정의합니다.
TUNE_MVIEW 시저는 실행 가능 문장으로, 2 개의 출력이 생성됩니다. 1 개의 출력 ( IMPLEMENTATION )는 Materialized View와 고속 재생 이나 쿼리 다시 허용하는 데 필요한 구성 요소 (Materialized View 로그와 다시 등가 화)를 최대한 구현하기위한 것입니다. 또 1 개의 출 력 ( UNDO )는 필요하다고 판단하는 경우 구체화된 뷰를 다시 등가 화를 제거하는 것입니다.
IMPLEMENTATION 프로세스의 출력 문장은 다음과 같습니다.
1 개 이상의 사부마테리아라이즈도보기를 참조하는 중첩된 Materialized View 계층 가 형성됩니다. 이것은 고속 재생이나 쿼리 재작성를 최대한 가능하게하기 위해서입니다. 대부분의 경우 사부마테리아라이즈도보기는 빠른 재생이 가능합니다.
분해하는 경우 쿼리 다시 활성화해야합니다.
분해하여 사부마테리아라이즈도보기 공유 할 수 없게 될 수 있습니다. 즉, 분해의 경우 TUNE_MVIEW 출력은 항상 새로운 사부마테리아 라이즈도보기가 포함됩니다. 기존의 Materialized View는 참조되지 않습니다.
UNDO 프로세스의 출력 문장은 다음과 같습니다.
UNDO 프로세스는 Materialized View 로그를 삭제하는 문장은 포함되지 않습니다. 각종 Materialized View가 Materialized View 로그를 공 유할 수 있습니다. 이러한 로그의 일부는 원격 Oracle 데이터베이스 인스턴스에있는 경우도 있습니다.
18.3.1.2 TUNE_MVIEW의 출력에 대한 액세스
TUNE_MVIEW 의 결과에 액세스하는 방법은 2 가지가 있습니다.
18.3.1.2.1 USER_TUNE_MVIEW 뷰 및 DBA_TUNE_MVIEW 보기
TUNE_MVIEW 을 실행하면 결과는 SQL 액세스 어드바이저 저장소 테이블에 출력되고, 데이터 사전 뷰이다 USER_TUNE_MVIEW 과
DBA_TUNE_MVIEW 에서 액세스할 수 있습니다. 자세한 내용은 Oracle Database Reference를 참조하십시오.
18.3.1.2.2 DBMS_ADVISOR 기능 및 절차의 스크립트 생성
권장 사항의 실행 스크립트를 생성하는 가장 쉬운 방법은 DBMS_ADVISOR.GET_TASK_SCRIPT 시저를 사용하는 것입니다. 아래 간단한 예 제를 보여줍니다. 첫째로, 결과를 저장할 디렉토리를 정의해야합니다.
CREATE DIRECTORY TUNE_RESULTS AS '/ tmp / script_dir'; GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
다음 구현 스크립트와 UNDO 스크립트를 생성하고 각 /tmp/script_dir/mv_create.sql 와 /tmp/script_dir/mv_undo.sql 에 저장합 니다.
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_name) -
'TUNE_RESULTS', 'mv_create.sql');
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_name -
'UNDO'), 'TUNE_RESULTS', 'mv_undo.sql');
TUNE_MVIEW 시저 사용 예제 몇 가지 예입니다.
예 18-1 고속 재생을위한 쿼리 정의 최적화
이 예제에서는 TUNE_MVIEW 의해 쿼리 정의를 빠르게 재생되도록 변경하는 방법을 보여줍니다. CREATE MATERIALIZED VIEW 구문은 변 수 create_mv_ddl 에 정의되어 있습니다. 이에, FAST REFRESH 절이 포함되어 있습니다. 질의 정의 1 개의 쿼리 블록이 있고 그 집계 열 을 SUM(s.amount_sold) 는 고속 재생을 지원하기위한 필수 집계 열이 없습니다. TUNE_MVIEW 문장이 MATERIALIZED VIEW CREATE 글과 함께 실행하면 그 결과 Materialized View 권장 사항은 고속 재생 가능합니다.
VARIABLE task_cust_mv VARCHAR2 (30); VARIABLE create_mv_ddl VARCHAR2 (4000); EXECUTE : task_cust_mv : = 'cust_mv';
EXECUTE : create_mv_ddl : = ' CREATE MATERIALIZED VIEW cust_mv REFRESH FAST
DISABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust id = cs.cust id
GROUP BY s.prod_id, s.cust_id ';
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (: task_cust_mv, : create_mv_ddl);
cust_mv 원래 쿼리 정의는 고속 재생을 가능하게하기 위하여 집계 열을 추가하고, 변경합니다.
TUNE_MVIEW 의 출력은 다음과 같이 최적화된 Materialized View 쿼리 정의가 포함됩니다.
CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE AS
SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM ( "SH" "SALES" "AMOUNT_SOLD") M1, COUNT ( "SH" "SALES" "AMOUNT_SOLD") M2, COUNT (*) M3
FROM SH.SALES, SH.CUSTOMERS
WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;
UNDO 의 출력은 다음과 같습니다.
DROP MATERIALIZED VIEW SH.CUST_MV;
예 18-2 USER_TUNE_MVIEW보기에서 IMPLEMENTATION의 출력에 대한 액세스
SELECT STATEMENT FROM USER_TUNE_MVIEW
WHERE TASK_NAME = : task_cust_mv AND SCRIPT_TYPE = 'IMPLEMENTATION';
예 18-3 스크립트 파일에 IMPLEMENTATION 출력 저장
CREATE DIRECTORY TUNE_RESULTS AS '/ myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_cust_mv) -
'TUNE_RESULTS', 'mv_create.sql');
예 18-4 여러 Materialized View 만들 어서 쿼리 다시 활성화
이 예제에서는 쿼리 재작성에서 지원되지 않는 집합 연산자 UNION 포함 Materialized View 질의 정의를 여러 사부마테리아라이즈도보 기 분해, 쿼리 재작성 가능합니다. 입력 디테일 표는 sales , customers 및 countries 입니다. 이 테이블에는 Materialized View 로그가 없습니다. 첫째로, TUNE_MVIEW 글을 create_mv_ddl 변수로 정의되는 CREATE MATERIALIZED VIEW 글과 함께 실행해야합니다.
EXECUTE : task_cust_mv : = 'cust_mv2'; EXECUTE : create_mv_ddl : = '
CREATE MATERIALIZED VIEW cust_mv
ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs, countries cn
WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id
AND cn.country_name IN ('' USA '','' Canada '') GROUP BY s.prod_id, s.cust_id
UNION
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id ';
Materialized View 쿼리 정의는 일반적인 쿼리 재작성를 지원하지 않는 UNION 집합 연산자가 포함되어 있습니다. 그러나 이렇게 여러 Materialized View 분해하면 쿼리 재작성 수 있습니다. 보통의 질의 재작성를 지원하려면 MATERIALIZED VIEW 의 쿼리 정의를 분해합니 다.
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (: task_cust_mv, : create_mv_ddl);
TUNE_MVIEW 에서 다음 권장 사항은 Materialized View 로그와 여러 Materialized View가 포함됩니다.
CREATE MATERIALIZED VIEW LOG ON "SH" "CUSTOMERS" WITH ROWID, SEQUENCE ( "CUST_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "CUSTOMERS"
ADD ROWID, SEQUENCE ( "CUST_ID") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "SH" "SALES"
WITH ROWID, SEQUENCE ( "PROD_ID", "CUST_ID", "AMOUNT_SOLD")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "SALES"
ADD ROWID, SEQUENCE ( "PROD_ID", "CUST_ID", "AMOUNT_SOLD") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "SH" "COUNTRIES"
WITH ROWID, SEQUENCE ( "COUNTRY_ID", "COUNTRY_NAME") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "COUNTRIES"
ADD ROWID, SEQUENCE ( "COUNTRY_ID", "COUNTRY_NAME") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "CUSTOMERS"
ADD ROWID, SEQUENCE ( "CUST_ID", "COUNTRY_ID") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "SALES"
ADD ROWID, SEQUENCE ( "PROD_ID", "CUST_ID", "AMOUNT_SOLD") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW SH.CUST_MV $ SUB1
REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE
AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM ( "SH" "SALES" "AMOUNT_SOLD")
M1, COUNT ( "SH" "SALES" "AMOUNT_SOLD") M2, COUNT (*) M3 FROM SH.SALES, SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND
(SH.SALES.CUST_ID IN (1012, 1010, 1005))
GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;
CREATE MATERIALIZED VIEW SH.CUST_MV $ SUB2
REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE
AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
SH.COUNTRIES.COUNTRY_NAME C3, SUM ( "SH" "SALES" "AMOUNT_SOLD") M1, COUNT ( "SH" "SALES" "AMOUNT_SOLD")
M2, COUNT (*) M3 FROM SH.SALES, SH.CUSTOMERS, SH.COUNTRIES WHERE
SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID AND (SH.COUNTRIES.COUNTRY_NAME IN ( 'USA', 'Canada')) GROUP BY
SH.SALES.PROD_ID,
SH.CUSTOMERS.CUST_ID, SH.COUNTRIES.COUNTRY_NAME;
CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS (SELECT "CUST_MV $ SUB2" "C1" "PROD_ID", "CUST_MV $ SUB2" "C2" "CUST_ID"SUM ( "CUST_MV $ SUB2" "M3")
"CNT", SUM ( "CUST_MV $ SUB2" "M1") "SUM_AMOUNT"FROM "SH" "CUST_MV $ SUB2" "CUST_MV $ SUB2"GROUP BY "CUST_MV $ SUB2" "C1", "CUST_MV $ SUB2" "C2") UNION (SELECT "CUST_MV $ SUB1" "C1" "PROD_ID", "CUST_MV $ SUB1" "C2"
"CUST_ID"SUM ( "CUST_MV $ SUB1" "M3")
"CNT", SUM ( "CUST_MV $ SUB1" "M1") "SUM_AMOUNT"FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1"GROUP BY "CUST_MV $ SUB1" "C1", "CUST_MV $ SUB1" "C2");
BEGIN
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ( 'SH.CUST_MV $ RWEQ'
'SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs, countries cn
WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id
AND cn.country_name IN ('' USA '','' Canada '') GROUP BY s.prod_id, s.cust_id
UNION
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id '
'(SELECT "CUST_MV $ SUB2" "C3" "PROD_ID", "CUST_MV $ SUB2" "C2" "CUST_ID" SUM ( "CUST_MV $ SUB2" "M3") "CNT"
SUM ( "CUST_MV $ SUB2" "M1") "SUM_AMOUNT" FROM "SH" "CUST_MV $ SUB2" "CUST_MV $ SUB2"
GROUP BY "CUST_MV $ SUB2" "C3", "CUST_MV $ SUB2" "C2") UNION
(SELECT "CUST_MV $ SUB1" "C2" "PROD_ID", "CUST_MV $ SUB1" "C1" "CUST_ID" "CUST_MV $ SUB1" "M3" "CNT", "CUST_MV $ SUB1" "M1" "SUM_AMOUNT"
FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1 ")',- 1553577441)
END;
/;
DROP 의 출력은 다음과 같습니다.
DROP MATERIALIZED VIEW SH.CUST_MV $ SUB1
DROP MATERIALIZED VIEW SH.CUST_MV $ SUB2
DROP MATERIALIZED VIEW SH.CUST_MV DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ( 'SH.CUST_MV $ RWEQ')
cust_mv 원래 쿼리 정의는 cust_mv$SUB1 및 cust_mv$SUB2 라는 2 개의 사부마테리아라이즈도보기에 철거되었습니다.
COUNT(amount_sold) 라는 새 열이 cust_mv$SUB1 추가, Materialized View 빠른 재생이 가능하게되었습니다.
cust_mv 원래 쿼리 정의는 2 개의 사부마테리아라이즈도보기를 연락하실 같이 변경하여 두 사부마테리아라이즈도보기에서 빠른 재생 과 일반 쿼리 재작성 수 있습니다.
필요한 Materialized View 로그가 추가되어 사부마테리아라이즈도보기 빠른 재생이 가능합니다. 디테일 테이블에 대해 2 개의 Materialized View 로그 문장이 생성됩니다. 1 개는 CREATE MATERIALIZED VIEW 문장, 다른 1 개는 ALTER MATERIALIZED VIEW FORCE 글입니다. 이 문장은 CREATE 스크립트를 여러 번 실행할 수 있습니다.
BUILD_SAFE_REWRITE_EQUIVALENCE 문장은 이전 쿼리 정의를 새로운 최상위 Materialized View 쿼리 정의에 결합합니다. 이렇게하면 쿼 리 재작성에서는 질의에 대한 응답에 새로운 최상위 구체화된 뷰를 사용됩니다.
예 18-5 USER_TUNE_MVIEW보기에서 IMPLEMENTATION의 출력에 대한 액세스
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME = 'cust_mv2'
AND SCRIPT_TYPE = 'IMPLEMENTATION';
예 18-6 스크립트 파일에 IMPLEMENTATION 출력 저장
다음 문장은 IMPLEMENTATION 의 출력이 /myscript/mv_create2.sql 에있는 스크립트 파일에 저장됩니다.
CREATE DIRECTORY TUNE_RESULTS AS '/ myscript'
GRANT READ, WRITE ON DIRECTRY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT ( 'cust_mv2'),
'TUNE_RESULTS', 'mv_create2.sql');
18.3.1.3 최적화된 사부마테리아라이즈도보기 고속 재생 활성화
이 예제에서는 TUNE_MVIEW 를 사용하여 고속 재생을 할 수 있도록 Materialized View를 최적화하는 방법을 보여줍니다. 이 예제에서는 집합 연산자를 가지는 Materialized View 질의 정의 1 개의 사부마테리아라이즈도보기와 1 개의 최상위 Materialized View로 변환됩니 다. 원래 쿼리 정의 서브 질의의 형식은 유사하며, 조건은 결합됩니다.
구체화된 뷰 자체가 고속 재생하지 못하도록 Materialized View 질의 정의는 UNION 집합 연산자가 포함되어 있습니다. 그러나 구체화된 뷰를 쿼리 정의 2 개의 하위 쿼리를, 1 개의 연락처로 결합할 수 있습니다.
예 18-7 고속 재생을위한 사부마테리아라이즈도보기 최적화
EXECUTE : task_cust_mv : = 'cust_mv3'; EXECUTE : create_mv_ddl : = '
CREATE MATERIALIZED VIEW cust_mv
REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) GROUP BY s.prod_id, s.cust_id UNION
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs -
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id ';
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (: task_cust_mv, : create_mv_ddl);
TUNE_MVIEW 은 다음 권장 사항을 생성합니다. 2 개의 하위 질의가 결합되어 사부마테리아라이즈도보기를 최적화합니다. 사부마테리아 라이즈도보기는 새로운 최상위 Materialized View에서 참조됩니다.
CREATE MATERIALIZED VIEW LOG ON "SH" "SALES"
WITH ROWID, SEQUENCE ( "PROD_ID", "CUST_ID", "AMOUNT_SOLD") INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "SALES"
ADD ROWID, SEQUENCE ( "PROD_ID", "CUST_ID", "AMOUNT_SOLD") INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH" "CUSTOMERS"
WITH ROWID, SEQUENCE ( "CUST_ID") INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH" "CUSTOMERS" ADD ROWID, SEQUENCE ( "CUST_ID") INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV $ SUB1
REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS
SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM ( "SH" "SALES" "AMOUNT_SOLD") M1,
COUNT ( "SH" "SALES" "AMOUNT_SOLD") M2, COUNT (*) M3
FROM SH.CUSTOMERS, SH.SALES
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS
(SELECT "CUST_MV $ SUB1" "C2" "PROD_ID", "CUST_MV $ SUB1" "C1" "CUST_ID" "CUST_MV $ SUB1" "M3" "CNT", "CUST_MV $ SUB1" "M1" "SUM_AMOUNT"
FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1"
WHERE "CUST_MV $ SUB1" "C1"= 2005 OR "CUST_MV $ SUB1" "C1"= 1020) UNION
(SELECT "CUST_MV $ SUB1" "C2" "PROD_ID", "CUST_MV $ SUB1" "C1" "CUST_ID" "CUST_MV $ SUB1" "M3" "CNT", "CUST_MV $ SUB1" "M1" "SUM_AMOUNT"
FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1"
WHERE "CUST_MV $ SUB1" "C1"= 1012 OR "CUST_MV $ SUB1" "C1"= 1010 OR "CUST_MV $ SUB1" "C1"= 1005)
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ( 'SH.CUST_MV $ RWEQ'
'SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) GROUP BY s.prod_id, s.cust_id UNION
SELECT s.prod_id, s.cust_id, COUNT (*) cnt, SUM (s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id '
'(SELECT "CUST_MV $ SUB1" "C2" "PROD_ID" "CUST_MV $ SUB1" "C1" "CUST_ID"
"CUST_MV $ SUB1" "M3" "CNT", "CUST_MV $ SUB1" "M1" "SUM_AMOUNT" FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1"
WHERE "CUST_MV $ SUB1" "C1"= 2005OR "CUST_MV $ SUB1" "C1"= 1020) UNION
(SELECT "CUST_MV $ SUB1" "C2" "PROD_ID" "CUST_MV $ SUB1" "C1" "CUST_ID"
"CUST_MV $ SUB1" "M3" "CNT", "CUST_MV $ SUB1" "M1" "SUM_AMOUNT" FROM "SH" "CUST_MV $ SUB1" "CUST_MV $ SUB1"
WHERE "CUST_MV $ SUB1" "C1"= 1012 OR "CUST_MV $ SUB1" "C1"= 1010 OR "CUST_MV $ SUB1" "C1"= 1005) ',
1811223110);
cust_mv 원래 쿼리 정의는 사부마테리아라이즈도보기 CUST_MV$SUB1 2 개의 하위 쿼리 조건을 결합하기위한 최적화되어 있습니다. 또 한 필요한 Materialized View 로그도 추가되고 사부마테리아라이즈도보기 빠른 재생이 가능합니다.
DROP 의 출력은 다음과 같습니다.
DROP MATERIALIZED VIEW SH.CUST_MV $ SUB1
DROP MATERIALIZED VIEW SH.CUST_MV DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ( 'SH.CUST_MV $ RWEQ');
다음 문장은 IMPLEMENTATION 의 출력이 /myscript/mv_create3.sql 에있는 스크립트 파일에 저장됩니다.
CREATE DIRECTORY TUNE_RESULTS AS '/ myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT ( 'cust_mv3'),
'oracle11R2' 카테고리의 다른 글
| 11g NF asm (0) | 2011.06.22 |
|---|---|
| Oracle11g (0) | 2011.06.17 |
| [Movie] Grid infra structure and Database Install (0) | 2011.05.29 |
| Oracle Database 11g： SQL Fundamentals (0) | 2011.03.06 |
| Oracle Database 11g： Administration Workshop II (0) | 2011.03.06 |
