SQL Advisor 및 Memory Advisor 활용

SQL Advisor 소개

오라클 데이터베이스에서 SQL Advisor는 성능이 낮은 SQL 문장의 성능 개선을 위한 권장 사항을 제공하는 강력한 튜닝 도구입니다. SQL Advisor는 통계 점검, 접근 경로 분석, SQL 구조 분석 등 다양한 분석을 수행하여 최적의 실행 계획을 생성하도록 돕습니다. 또한, SQL 프로필 생성을 통해 옵티마이저가 더 나은 계획을 선택하도록 유도합니다.

SQL Advisor 실행 방법

SQL Tuning Advisor는 자동 또는 수동으로 실행할 수 있습니다. 자동 SQL 튜닝은 데이터베이스 유지 관리 윈도우에서 주기적으로 실행되며, 수동 SQL 튜닝은 필요에 따라 SQL Developer 또는 Cloud Control을 통해 수행할 수 있습니다.

예제: SQL Developer를 이용한 수동 SQL 튜닝

SQL Developer에서 특정 SQL 문장을 선택하고 “SQL 튜닝”을 클릭하면, SQL Advisor가 해당 문장을 분석하고 튜닝 권장 사항을 제공합니다. 


-- SQL Developer에서 SQL 튜닝 Advisor 실행 예시
SELECT /*+ gather_plan_statistics */ *
FROM   employees e, departments d
WHERE  e.department_id = d.department_id
AND    e.salary > 5000;

예제: DBMS_SQLTUNE 패키지를 이용한 SQL 튜닝

DBMS_SQLTUNE 패키지를 사용하여 SQL 튜닝 작업을 생성하고 실행할 수 있습니다. 


-- 튜닝 작업 생성
DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := 'my_tuning_task';
  DBMS_SQLTUNE.CREATE_TUNING_TASK (
    task_name   => task_name,
    sql_text    => 'SELECT /*+ gather_plan_statistics */ * FROM employees e, departments d WHERE e.department_id = d.department_id AND e.salary > 5000',
    description => 'Test SQL Tuning Task',
    scope       => 'COMPREHENSIVE',
    time_limit  => 60
  );
END;
/

-- 튜닝 작업 실행
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_tuning_task');
END;
/

-- 튜닝 결과 확인
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') FROM dual;

SQL Advisor 권장 사항

SQL Advisor는 다음과 같은 다양한 권장 사항을 제공할 수 있습니다.

  • 객체 통계 수집: 옵티마이저가 최적의 실행 계획을 생성하는 데 필요한 통계가 오래되었거나 누락된 경우 통계 수집을 권장합니다.
  • 인덱스 생성: 특정 쿼리의 성능을 향상시키기 위해 새로운 인덱스 생성을 권장합니다.
  • SQL 문장 재작성: SQL 문장의 구조를 변경하여 성능을 개선할 수 있는 경우 재작성을 권장합니다.
  • SQL 프로필 생성: 실행 계획에 큰 영향을 미치는 보조 통계를 SQL 프로필로 생성하여 옵티마이저가 더 나은 계획을 선택하도록 돕습니다.

Memory Advisor 소개

Memory Advisor는 오라클 데이터베이스의 메모리 구성 요소(SGA, PGA)의 크기를 최적화하는 데 도움을 주는 도구입니다. Memory Advisor는 시스템의 작업 부하를 분석하여 각 메모리 구성 요소에 적절한 크기를 권장합니다. 이를 통해 데이터베이스의 전반적인 성능을 향상시킬 수 있습니다.

Memory Advisor 활용 방법

Memory Advisor는 Enterprise Manager Cloud Control 또는 명령줄 인터페이스(CLI)를 통해 사용할 수 있습니다.

예제: Enterprise Manager Cloud Control을 이용한 메모리 튜닝

  1. Enterprise Manager Cloud Control에 로그인합니다.
  2. 성능 메뉴에서 “어드바이저 중앙”을 선택합니다.
  3. “메모리 어드바이저”를 선택합니다.
  4. Memory Advisor가 분석을 수행하고 메모리 구성 요소에 대한 권장 크기를 제공합니다.
  5. 권장 사항을 검토하고 적용합니다.

예제: 명령줄 인터페이스를 이용한 메모리 튜닝

다음은 `V$SGA_TARGET_ADVICE` 뷰를 사용하여 SGA_TARGET 초기화 매개변수의 적정 값을 결정하는 예제입니다. 


SELECT SGA_SIZE_FACTOR,
       SGA_SIZE,
       ESTD_DB_TIME_FACTOR,
       ESTD_PHYSICAL_READS
FROM   V$SGA_TARGET_ADVICE
ORDER BY SGA_SIZE_FACTOR;

`V$PGA_TARGET_ADVICE` 뷰를 사용하여 PGA_AGGREGATE_TARGET 초기화 매개변수의 적정 값을 결정할 수도 있습니다. 


SELECT PGA_TARGET_FACTOR,
       PGA_TARGET_BYTES,
       ESTD_PGA_CACHE_HIT_PERCENTAGE,
       ESTD_OVERALLOC_COUNT
FROM   V$PGA_TARGET_ADVICE
ORDER BY PGA_TARGET_FACTOR;

Memory Advisor 권장 사항

Memory Advisor는 시스템의 작업 부하를 분석하여 다음 사항에 대한 권장 사항을 제공합니다.

  • SGA 크기 조정: Shared Pool, Buffer Cache, Large Pool 등 SGA 구성 요소의 크기를 조정하여 SQL 쿼리 및 PL/SQL 코드 실행 성능을 최적화합니다.
  • PGA 크기 조정: 작업 영역 크기(Sort Area, Hash Area 등)를 조정하여 정렬 및 해시 조인 작업 성능을 최적화합니다.

결론

SQL Advisor와 Memory Advisor는 오라클 데이터베이스 성능을 최적화하는 데 필수적인 도구입니다. 이러한 도구를 효과적으로 활용하면 SQL 쿼리 및 PL/SQL 코드 실행 시간을 단축하고, 시스템의 전체적인 응답성을 향상시킬 수 있습니다. 이 가이드에서 제시된 예제와 설명을 바탕으로 여러분의 데이터베이스 환경에 맞게 SQL Advisor와 Memory Advisor를 활용하여 최고의 성능을 경험하시기 바랍니다.

위로 스크롤