Amazon Aurora
Aurora 사용 설명서 (API 버전 2014-10-31)

실행 계획 유지 관리

쿼리 계획 관리는 실행 계획을, 추가, 유지 관리 및 개선할 수 있는 기술과 함수를 제공합니다.

계획 성능 평가

최적화 프로그램이 계획을 미승인 계획으로 캡처한 후 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 실제 성능에 따라 계획을 비교합니다. 성능 실험의 결과에 따라 승인되지 않음에서 승인됨 또는 거부됨으로 계획의 상태를 변경할 수 있습니다. 대신에 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 요구 사항을 충족하지 않을 경우 계획을 일시적으로 비활성화하도록 결정할 수 있습니다.

더 나은 계획 승인

다음 예제에서는 apg_plan_mgmt.evolve_plan_baselines 함수를 사용하여 관리형 계획의 상태를 승인됨으로 변경하는 방법을 보여줍니다.

SELECT apg_plan_mgmt.evolve_plan_baselines ( sql_hash, plan_hash, min_speedup_factor := 1.0, action := 'approve' ) FROM apg_plan_mgmt.dba_plans WHERE status = 'unapproved'; NOTICE: rangequery (1,10000) NOTICE: Baseline [ Planning time 0.761 ms, Execution time 13.261 ms] NOTICE: Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms] NOTICE: Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms NOTICE: Unapproved -> Approved evolve_plan_baselines ----------------------- 0 (1 row)

출력에 파라미터 바인딩이 1과 10,000인 rangequery 문에 대한 성능 보고서가 표시됩니다. 새로운 미승인 계획(Baseline+1)이 이전에 승인된 계획 최상의 계획(Baseline)보다 더 낫습니다. 새 계획이 승인되었는지 확인하려면 apg_plan_mgmt.dba_plans 보기를 확인합니다.

SELECT sql_hash, plan_hash, status, enabled, stmt_name FROM apg_plan_mgmt.dba_plans; sql_hash | plan_hash | status | enabled | stmt_name ------------+-----------+----------+---------+------------ 1984047223 | 512153379 | Approved | t | rangequery 1984047223 | 512284451 | Approved | t | rangequery (2 rows)

이제 관리형 계획에 설명문의 계획 기준인 승인된 계획 두 개가 포함됩니다. 또한 apg_plan_mgmt.set_plan_status 함수를 호출하여 계획의 상태 필드를 'approved', 'rejected', 'unapproved' 또는 'preferred'로 직접 설정할 수도 있습니다.

느린 계획 거부 또는 비활성화

계획을 거부하거나 비활성화하려면 'reject' 또는 'disable' apg_plan_mgmt.evolve_plan_baselines 함수에 작업 파라미터로 전달합니다. 이 예제에서는 해당 설명문에 대해 최상의 승인된 계획보다 최소 10% 더 느린 캡처된 모든 미승인 계획을 비활성화합니다.

SELECT apg_plan_mgmt.evolve_plan_baselines( sql_hash, -- The managed statement ID plan_hash, -- The plan ID 1.1, -- number of times faster the plan must be 'disable' -- The action to take. This sets the enabled field to false. ) FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved' AND -- plan is Unapproved origin = 'Automatic'; -- plan was auto-captured

또한 계획을 거부됨 또는 비활성화됨으로 즉시 설정할 수도 있습니다. 계획의 활성화 필드를 true 또는 false로 즉시 설정하려면 apg_plan_mgmt.set_plan_enabled 함수를 호출합니다. 계획의 상태 필드를 'approved', 'rejected', 'unapproved' 또는 'preferred'로 즉시 설정하려면 apg_plan_mgmt.set_plan_status 함수를 호출합니다.

계획 검증

apg_plan_mgmt.validate_plans 함수를 사용하여 유효하지 않은 계획을 삭제하거나 비활성화할 수 있습니다.

인덱스나 테이블 같은 종속된 객체가 제거되면 계획이 유효하지 않게 되거나 기한 경과 상태가 될 수 있습니다. 하지만 제거된 객체가 다시 생성되는 경우에는 계획이 실시적으로 잘못될 수 있습니다. 유효하지 않은 계획이 나중에 유효 상태가 될 수 있는 경우 유효하지 않은 계획을 삭제하기 보다는 비활성화하거나 아무 작업도 하지 않는 것이 더 나을 수 있습니다.

유효하지 않고 지난 주에 사용한 적이 없는 모든 계획을 찾아서 삭제하려면 다음과 같이 apg_plan_mgmt.validate_plans 함수를 사용합니다.

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') FROM apg_plan_mgmt.dba_plans WHERE last_used < (current_date - interval '7 days');

계획을 직접 활성화하거나 비활성화하려면 apg_plan_mgmt.set_plan_enabled 함수를 사용합니다.

pg_hint_plan을 사용하여 계획 수정

쿼리 최적화 프로그램은 모든 설명문에 대한 최적 계획을 찾도록 설계되었으며, 대부분의 경우 최적화 프로그램은 좋은 계획을 찾아냅니다. 하지만 경우에 따라 최적화 프로그램에서 생성된 것보다 훨씬 더 나은 계획이 존재한다는 것을 알게 될 수도 있습니다. 최적화 프로그램을 통해 원하는 계획을 생성하기 위한 두 가지 권장 방법은 PostgreSQL에서 pg_hint_plan 확장을 사용하거나 Grand Unified Configuration(GUC) 변수를 설정하는 것입니다.

  • pg_hint_plan 확장 – PostgreSQL의 pg_hint_plan 확장을 사용하여 플래너의 작동 방식을 수정하려면 "힌트(hint)"를 지정합니다. pg_hint_plan 확장을 설치하고 사용하는 방법은 pg_hint_plan 설명서를 참조하십시오.

  • GUC 변수 – 하나 이상의 비용 모델 파라미터 또는 다른 최적화 프로그램 파라미터(예: from_collapse_limit 또는 GEQO_threshold)를 재정의합니다.

이러한 기술 중 하나를 사용하여 쿼리 최적화 프로그램에서 계획을 사용하도록 지정할 경우 쿼리 계획 관리를 사용하여 새 계획을 캡처하고 사용하도록 설정할 수도 있습니다.

pg_hint_plan 확장을 사용하여 SQL 문의 조인 순서, 조인 방법 또는 액세스 경로를 변경할 수 있습니다. 특수 pg_hint_plan 구문과 함께 SQL 설명을 사용하여 최적화 프로그램에서 계획을 생성하는 방식을 수정할 수 있습니다. 예를 들어 문제의 SQL 문에 양방향 조인이 있다고 가정해 보겠습니다.

SELECT * FROM t1, t2 WHERE t1.id = t2.id;

또한 최적화 프로그램에서는 조인 순서(t1, t2)를 선택하지만 조인 순서(t2, t1)가 더 빠름을 알고 있다고 가정해 보겠습니다. 다음 힌트는 최적화 프로그램에서 더 빠른 조인 순서(t2, t1)를 사용하도록 지정합니다. 최적화 프로그램에서 SQL 문에 대한 계획을 생성하되 해당 설명문을 실행하지 않도록 EXPLAIN을 포함합니다. (출력이 표시되지 않음)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

다음 단계에서는 pg_hint_plan을 사용하는 방법을 보여줍니다.

최적화 프로그램의 생성된 계획을 수정하고 pg_hint_plan을 사용하여 계획을 캡처하려면

  1. 수동 캡처 모드를 켭니다.

    SET apg_plan_mgmt.capture_plan_baselines = manual;
  2. 관심 SQL 문에 대한 힌트를 지정합니다.

    /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

    이 실행 후 최적화 프로그램이 apg_plan_mgmt.dba_plans 보기에서 해당 계획을 캡처합니다. 캡처한 계획은 특수 pg_hint_plan 설명 구문을 포함하지 않습니다. 쿼리 계획 관리 기능이 선행 설명을 제거하여 설명문을 정규화하기 때문입니다.

  3. apg_plan_mgmt.dba_plans 보기를 사용하여 관리형 계획을 확인합니다.

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline FROM apg_plan_mgmt.dba_plans;
  4. 계획의 상태를 preferred로 설정합니다. 그러면 최적화 프로그램에서 최소 비용 계획이 아직 approved 또는 preferred가 아닐 때 승인된 계획 세트에서 선택하는 대신 해당 계획을 실행합니다.

    SELECT apg_plan_mgmt.set_plan_status(<sql-hash>, <plan-hash>, 'preferred' );
  5. 수동 계획 캡처를 끄고 관리형 계획을 사용하도록 설정합니다.

    SET apg_plan_mgmt.capture_plan_baselines = false; SET apg_plan_mgmt.use_plan_baselines = true;

    이제는 원본 SQL 문이 실행될 때 최적화 프로그램이 approved 또는 preferred 계획을 선택합니다. 최소 비용 계획이 approved 또는 preferred가 아니면 최적화 프로그램은 preferred 계획을 선택합니다.

계획 삭제

장시간 사용하지 않았거나 더 이상 관련이 없는 계획을 삭제합니다. 최적화 프로그램이 계획을 실행하거나 해당 계획을 설명문에 대한 최소 비용 계획으로서 선택할 때마다 최적화 프로그램이 업데이트하는 last_used 날짜가 각 계획에 지정되어 있습니다. 계획이 최근에 사용되었고 여전히 관련이 있는지 여부를 확인하려면 last_used 날짜를 사용합니다.

예를 들어 다음과 같이 apg_plan_mgmt.delete_plan 기능을 사용할 수 있습니다. 이렇게 하면 최소 비용 계획으로 선택하지 않았거나 31일 이상 실행하지 않은 모든 계획이 삭제됩니다. 하지만 이 예제에서는 명시적으로 거부된 계획을 삭제하지 않습니다.

SELECT SUM(apg_plan_mgmt.delete_plan(sql_hash, plan_hash)) FROM apg_plan_mgmt.dba_plans WHERE last_used < (current_date - interval '31 days') AND status <> 'rejected';

더 이상 유효하지 않으며 다시는 유효 상태로 전환되지 않을 것으로 생각되는 계획을 모두 삭제하려면apg_plan_mgmt.validate_plans 함수를 사용합니다. 자세한 내용은 계획 검증 단원을 참조하십시오.

계획 삭제를 위한 자신만의 고유 정책을 구현할 수 있습니다. 현재 날짜인 last_usedapg_plan_mgmt.plan_retention_period 파라미터의 값(기본값은 32일)보다 크면 계획이 자동으로 삭제됩니다. 더 긴 간격을 지정하거나, delete_plan 함수를 직접 호출하여 고유한 계획 보조 정책을 구현할 수 있습니다.

중요

계획을 정리하지 않을 경우 결국에는 쿼리 계획 관리용으로 별도 보관되어 있는 공유 메모리가 부족해질 수 있습니다. 관리형 계획에 사용할 수 있는 메모리의 양을 제어하려면 apg_plan_mgmt.max_plans 파라미터를 사용합니다. DB 인스턴스 수준 파라미터 그룹에서 이 파라미터를 설정하고 DB 인스턴스를 다시 시작하여 변경 내용을 적용합니다. 자세한 내용은 apg_plan_mgmt.max_plans 파라미터를 참조하십시오.

계획 내보내기/가져오기

관리형 계획을 내보내고 다른 DB 인스턴스로 가져올 수 있습니다.

관리형 계획을 내보내려면

권한 있는 사용자는 apg_plan_mgmt.plans 테이블의 일부를 다른 테이블에 복사한 후, pg_dump 명령을 사용하여 저장할 수 있습니다. 다음은 예제입니다.

CREATE TABLE plans_copy AS SELECT * FROM apg_plan_mgmt.plans [ WHERE predicates ] ; % pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar DROP TABLE apg_plan_mgmt.plans_copy;

관리형 계획을 가져오려면

  1. 내보낸 관리형 계획의 .tar 파일을 계획이 복원되어야 할 시스템으로 복사합니다.

  2. pg_restore 명령을 사용하여 tar 파일을 새 테이블로 복사합니다.

    % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar
  3. 다음 예제와 같이 plans_copy 테이블과 apg_plan_mgmt.plans 테이블을 병합합니다.

    참고

    어떤 경우에는 한 버전의 apg_plan_mgmt 확장에서 덤프하여 다른 버전으로 복원할 수 있습니다. 이 경우 계획 테이블의 열은 다를 수 있습니다. 다른 경우에는 SELECT *를 사용하는 대신에 열에 명시적으로 이름을 지정합니다.

    INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy ON CONFLICT ON CONSTRAINT plans_pkey DO UPDATE SET status = EXCLUDED.status, enabled = EXCLUDED.enabled, -- Save the most recent last_used date -- last_used = CASE WHEN EXCLUDED.last_used > plans.last_used THEN EXCLUDED.last_used ELSE plans.last_used END, -- Save statistics gathered by evolve_plan_baselines, if it ran: -- estimated_startup_cost = EXCLUDED.estimated_startup_cost, estimated_total_cost = EXCLUDED.estimated_total_cost, planning_time_ms = EXCLUDED.planning_time_ms, execution_time_ms = EXCLUDED.execution_time_ms, estimated_rows = EXCLUDED.estimated_rows, actual_rows = EXCLUDED.actual_rows, total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;
  4. 관리형 계획을 공유 메모리에 다시 로드하고 임시 계획 테이블을 제거합니다.

    SELECT apg_plan_mgmt.reload(); -- refresh shared memory DROP TABLE plans_copy;