TOAD, DB 관리자의 ‘손과 발’

퀘스트 Toad  인터뷰 │  기술자료 │  구축사례 QuestSoftware TOAD for Oracle, 기능 분석 TOAD, DB 관리자의 ‘손과 발’ 오금석|에이스톰텍 차장 얼마나 효율적으로 DBMS를 관리, 운영할 수 있을지가 DBA의 주요 관심사로 자리 잡으면서 DB 튜닝 방법론과 튜닝 도구에 대한 관심이 높아지고 있다. 튜닝 도구를 얼마나 잘 사용하느냐가 튜닝 및 DB 관리의 성패를 좌우하기 때문이다. 대표적인 오라클 DB 개발 및 관리 툴인 TOAD를 각각 개발자, DBA, 튜너 측면에서 어떻게 활용할 수 있는지 살펴보자. 1990년대 초, DBMS 기술이 국내에 도입된 후 현재 15년이라는 시간이 흘렀다. 지난 10여 년간 IT 분야에선 DBMS 도입과 운영이 주요 관심사였고, 그 결과 현재는 DBMS가 기업의 IT 인프라에 없어서는 안 될 핵심 리소스 및 기술로 굳건히 자리매김했다. 이제 DBMS의 관심사는 단순한 시스템 구축을 넘어, 효율성의 극대화로 변모하고 있다. 지난 10여 년간 데이터베이스의 구축 및 보급에 중점을 두었다면 앞으로의 10년은 급속도로 증가하는 대용량 데이터를 어떻게 효율적으로 관리하고, 얼마나 빠르게 효과적으로 데이터를 검색할 수 있는지 그 방법을 고민하는 시기가 될 것으로 보인다. 튜닝 ‘마술이 아닌 기술’ 그러나 아직도 많은 개발자들이 DBMS의 정확한 원리와 구조를 이해하지 못한 채 DBMS를 운영한다. 자신이 수행하는 SQL문이 어떤 과정을 통해 실행되는지에 대한 원리를 알지 못하고, 결과를 구현하기 위한 단순한 문법 위주의 코딩만을 하고 있는 것이 현실이다. 데이터베이스의 튜닝은 마술이 아니다. 데이터베이스 구조에 대한 이해와 SQL문이 실행되는 원리를 정확히 이해한 후, 성능에 문제가 되는 SQL문을 기본 원리에 의해 실행될 수 있도록 하는 것이 튜닝의 기본 원리이다. 다양한 활용 가이드 등을 통해 튜닝 기법과 방법론을 익히지만, 성능이 저하되는 문제를 찾을 수 있을 정도의 DBMS 이해력이 갖춰지지 않았다면 완전한 의미의 튜닝을 수행할 수 없다. 또한 성능 저하 문제는 어느 한 가지 이유로만 발생하는 것이 아닌, 매우 다양한 원인으로 인해 발생하는 것이 현실이기 때문에 더욱 원인 발견에 어려움이 있다. 따라서 문제가 발생한 SQL문에 대한 튜닝을 시도할 때 육감과 경험만을 믿고 튜닝을 시도한다면 좋은 결과를 얻을 수 없다. 이러한 흐름으로 인해 DB 관리 및 튜닝 툴이 각광을 받고 있다. 특히 TOAD는 쉽고 간편하게 튜닝 할 수 있는 다양한 튜닝 도구를 제공한다. 튜닝 도구를 얼마나 잘 사용하고 결과를 잘 분석하느냐가 튜닝의 성패를 좌우할 수 있다. 이 글을 통해 개발자, DBA, 튜너 측면에서 각각 TOAD를 어떻게 활용할 수 있는지를 설명하겠다. <표> TOAD 제품 구성 제 품 구성 요소 요구 기능 TOAD for Oracle Standard TOAD + Formatter Plus Oracle 전문가가 코드를 개발하고, database object를 구축 및 관리하는데 필요한 기능 제공(DBA 모듈 별도) TOAD for Oracle Professional TOAD for Oracle Standard + (Debugger, Knowledge Xpert for PL/SQL Dev) 저장된 데이터베이스 코드의 트러블슈팅(troubleshooting)을 위한 통합 PL/SQL Debugger 추가(DBA 모듈 별도) TOAD for Oracle Xpert TOAD for Oracle Professional + (Xpert Client, Quest SQL Optimizer) Xpert SQL 튜닝 기능 추가 TOAD for Oracle Suite TOAD for Oracle Xpert + (Xpert Option, DBA Module, Knowledge Xpert for Oracle Admin, Qdesigner Physical Architect, Data Factory, Benchmark Factory) Oracle을 이용, 애플리케이션 구축부터 관리까지 할 수 있는 통합 버전이다. Knowledge Xpert 모듈, 테스트 데이터를 데이터베이스로 빠르게 적용시키는 DataFactory Developer 에디션, 데이터베이스 코드의 로드 테스팅을 수행하는 Benchmark Factory, 데이터베이스 스키마를 정의, 디자인, 실행하는 QDesigner Physical Architect가 추가, 포함된다. TOAD의 Optional Module • Debugger Module : 응용 프로그램 오류를 제거하고 대용량 RDB 프로젝트에 개발 및 테스트 시간을 제한하지 않도록 지원한다. 프로시저 편집 세션에서 개발자는 프로그램과 Debugger를 서버에서 실행하면서 라인별로 코드화하고 디버깅 할 수 있다. 또한 모든 변수에 대해 ‘watches’를 수행하고 실행 중에 변경될 때 값을 표시한다. • Xpert Module : TOAD의 Xpert 튜닝 모듈은 Oracle 전문가의 조언을 통해 튜닝 기술을 지원한다. 선택한 SQL 문과기존 데이터베이스 환경에 대해 검사한 후 성능을 향상시키는 여러 가지 옵션을 권장한다. 그러면 사용자는 조언 이면의 로직을 보고 권장 사항을 구현하고, 데이터베이스에 적용해 결과를 볼 수 있다. 변경 사항에 영향을 주는 필요한 SQL을 모두 자동으로 생성한다. • DBA Module : 매일 DB 관리를 자동화하도록 TOAD의 기능과 융통성이 향상된다. 데이터베이스 스키마 비교 및 동기화, 공간 관리, 보안 관리, 배치 작업 일정 조정, NLS(NTL언어 지원) 매개 변수 수정 등 개발자가 일반 관리 작업을 처리할 수 있게 해준다. 이 모듈에는 Oracle 가져오기와 내보내기 마법사가 포함되어 있다. • PL/Formatter : 코딩 스타일이 충돌하지 않고 이 코드의 기존 논리적 구조를 나타내어 기존 PL/SQL, SQL*Forms, Oracle Forms, SQL*Plus 소스 코드를 재포맷한다. • Knowledge Xpert for PL/SQL : 코딩 방법, 최적의 학습 방법, 다이어그램, 재사용 가능한 예제와 함께 PL/SQL의 전체 개발 주기를 다룬다. 1400개 이상의 항목은 데이터베이스에 즉시 접속해, 시간 테스트 경험을 직접 데스크톱에 전달한다. • Knowledge Xpert for Oracle Administration : 관리 기술 자료로 매일 기술적인 질문에 대해 TOAD 내에서 70～80% 직접 응답에 접속한다. 백업 및 복구 문제에서 네트워크 구성 및 데이터베이스 튜닝에 이르기까지 2400개 이상의 기본 설정 방법을 찾고, 이 작업을 빠르고 정확하게 수행할 수 있게 해준다. 개발자를 위한 TOAD TOAD는 Oracle 애플리케이션 개발자들이 GUI 환경에서 보다 적은 시간과 노력을 들이고도 빠르고 쉽게 SQL과 PL/SQL 문장을 개발, 테스트, 디버깅 할 수 있게 하는 다양한 기능을 제공한다. 프로시저 에디터를 통해 모든 데이터베이스 오브젝트를 신속하게 찾아, 정확하게 오브젝트에 접근할 수 있도록 지원한다. TOAD를 통해 쿼리의 생성 및 실행, 데이터베이스 오브젝트의 생성 및 변경을 수행하면서 SQL 및 PL/SQL 코드를 개발하고, 디버깅을 통해 데이터베이스 관련 개발자의 업무 효율을 높여준다. 또한 데이터 입/출력, 스키마 비교/분석, 통계치 업데이트 같은 일상 업무도 쉽고 빠르게 처리할 수 있다. 특히 통합 SQL 튜닝, 자동화된 PL/SQL Code Review 기능은 초보자나 숙련자 모두에게 유용하게 사용되는 기능이다. 최근 발표된 TOAD 8.6에는 새로운 개발 지원 기능들이 추가됐다. 새롭게 추가된 CodeXpert 기능은 룰 셋(rule set)시 저장된 프로시저를 분석해 정확한 어드바이스를 제공하며 보완된 프로시저 에디터는 핫키(Hot key), 자동 수정(Auto Correct), Type-Ahead, Syntax-Highlighting과 PL/SQL, 자바, HTML에 대한 버전 컨트롤 기능이 추가돼 효율성을 높여준다. 또한 SQL 에디터를 통해 사용자가 쉽고 빠르게 쿼리를 테스트하고, 데이터 확인 및 편집과 DDL 문장을 수행할 수 있도록 지원하며, 멀티 탭 형태의 스키마 브라우저는 한 번의 클릭만으로 오브젝트의 구체적인 상황과 데이터, DDL의 기본 설정에 즉시 접속, 데이터베이스 오브젝트 전반을 그래픽으로 볼 수 있게 해준다. PL/SQL 디버거 모듈의 라인 단위 코드 디버깅과 브레이크포인트 세팅, 변수 확인, 편집 등을 지원하는 ‘Watches’ 기능을 통해 코딩 작업시의 에러를 최소화 시켜준다. 이와 함께 협업 환경의 개발을 가능하게 하는 팀 코딩(Team Coding) 기능이 이전 버전에 비해 보완돼, 지속적으로 업데이트 되는 컨트롤 시스템의 통합을 유도함으로써 규모가 큰 프로젝트 수행시 팀 작업 분배를 원활하게 해준다. SQL 에디터 향상된 SQL 에디터는 사용이 쉬운 인터페이스를 통해 사용자들이 신속하게 쿼리를 테스트하고 실행할 수 있게 해주며 쿼리 결과를 확인하고 편집, 튜닝할 수 있도록 지원한다. 또한 DDL 명령 수행이 가능하고 결과 값 세팅(Result Set)과 트레이싱, DBMS_OUTPUT,Plan이 통합돼 보다 쉽게 편집과 테스팅을 진행할 수 있다. 업무 생산성 차원에선 동시에 여러 개의 파일에서 작업이 가능하고 SQL, PL/SQL, HTML, 자바, 텍스트 등 서로 다른 파일에서도 작업을 수행할 수 있도록 개선됐다. <그림 1> TOAD 8.6의 SQL 에디터 프로시저 에디터 프로시저 에디터는 프로시저, 패키지, 트리거, 타입 같은 PL/SQL 오브젝트들을 작성, 수정, 실행하며, PL/SQL 디버거 기능을 통해 라인(Line) 단위의 디버깅, 브레이크포인트 세팅, 코드탐색, 변수 점검 및 수정 등을 통해 사용자가 직접 코딩시에 발생하는 에러를 최소화 해준다. 또 여러 개의 오브젝트를 한 번에 컴파일 할 수 있으며, 종속된 오브젝트도 컴파일 할 수 있다. <그림2> TOAD 8.6의 프로시저 에디터 스키마 브라우저와 SQL 모델러 스키마 브라우저는 데이터베이스 오브젝트를 유형별로 다양하게 분류할 수 있고 사용자가 오브젝트를 선택하면 이에 따른 세부사항이나 DDL 구조를 그래픽 기반에서 직관적으로 보여준다. TOAD의 SQL 모델러는 쿼리를 쉽고 빨리 만들어 주는 역할을 수행한다. 간단하게 테이블을 드래그하고 SQL 모델러로 클릭하면, 자동으로 SQL 명령문을 생성해준다. 또 자동 생성된 SQL 명령문을 테스트하고, 쿼리 결과를 보여주고, 명령문을 저장 또는 에디터로 복사하는 등의 기능을 수행해, 초보 개발자라도 Oracle 전문가처럼 DB 개발 작업을 진행할 수 있다. <그림3> SQL 모델러 DBA를 위한 TOAD TOAD의 DBA 모듈은 다수의 인스턴스나 다량의 데이터베이스를 관리하는 DBA에게 유용하다. 데이터베이스의 성능 관리 화면을 자동으로 업데이트 해주기 때문에 GUI 기반의 브라우저로 손쉽게 관리할 수 있기 때문이다. DBA 모듈이 제공하는 기능에는 데이터베이스 스키마 비교, 동기화 스페이스 및 컨트롤 파일, 리두(Redo) 로그 관리, 보안 관리, 배치 잡 스케줄링 뿐 아니라 Oracle 익스포트/임포트 마법사, 데이터베이스 생성 마법사 등이 포함되어 있다. Oracle SQL 로더 유틸리티(Loader Utility)와 호환이 용이해 양쪽 데이터의 탑재 및 삭제가 쉽고 간편하다. 데이터의 생성, 변경, 삭제 등 DBA가 관리하는 모든 오브젝트 작업은 스키마 브라우저 인터페이스로 간단하게 지원받을 수 있다. 데이터베이스 모니터 데이터베이스 성능 정보를 그랙픽하게 모니터링하는 데이터베이스 모니터 기능은 논리적/물리적 I/O, 이벤트 웨이트, 세션, 요청률(Call Rates), 누락률(Miss Rates), SGA 메모리 사용량, 쉐어드 풀(Shared Pool), 인덱싱 쿼리률(Indexed Query %) 등의 정보를 보여준다. 또한 SGA 트레이스 기능을 통해 인스턴스에서 실행됐던 SQL 문장들에 대해 쉐어드 풀에서 트레이스를 처리할 수 있다. <그림4> 데이터베이스 모니터링 화면 데이터 접근 분석 FGAC(Fine-Grained Access Control)와 FGA(Fine-Grained Audit)의 차이는 사전에 데이터에 대한 접근을 제한하느냐 아니면 데이터가 접근된 이후의 정보를 감지하는가 이다. FGAC는 사용자나 애플리케이션에서 꼭 필요로 하는 데이터만을 공개하고 접근 가능하도록 하며 하나의 테이블에 하나 이상의 FGAC가 지정되면, row는 공개되기 전의 요구 조건을 반드시 충족해야 한다. 몇 개의 접근제어 패키지가 하나의 동일한 기초 테이블 제어를 가능하게 할 수 있다. 또한 FGA 기능을 통해서는 어떤 경로를 통해 사용자에게 데이터가 접근되는지를 분석할 수 있도록 해준다. FGA는 데이터가 아닌 SQL문을 포착하도록 해 확장 가능한 침입감지 기능을 제공한다. 그 예로 SELECT문의 WHERE조건을 갖는 테이블이나 뷰에 대해 감사정책을 덧붙이는 것을 들 수 있다. 또 AUDIT COLUMN 기능을 두어 감사 조건에 대한 실패를 줄이도록 한다. 감사 기록은 DBA_FGA_AUDIT_TRAIL에 저장된다. <그림5> Audit 기능 ADDM 보고서 생성기 ADDM은 오라클 데이터베이스 튜닝을 위한 가장 중요한 요소 중의 하나다. ADDM은 오라클 데이터베이스 내의 병목현상을 자동으로 감지한다. 또한 다른 관리 요소들과 결합해 이러한 병목을 해결하기 위한 제안들을 제공한다. ADDM은 모든 구성요소와 정보를 공유해 매일 세부적인 실시간 분석이 된 정보를 제공해 준다. 이러한 실시간 분석 정보와 통계 값들을 AWR(Automatic Workload Repository)이라는 리포지토리에 저장한다. 60분이라는 고정된 시간 간격으로 데이터베이스 내의 각종 통계 정보들을 보관하는 데이터베이스 내의 리포지토리다. <그림6> ADDM 보고서 생성 화면 Tunner를 위한 TOAD TOAD for Oracle Xpert에 들어 있는 Xpert 튜닝 모듈은 개발자의 Oracle 튜닝 기술을 보완하면서 애플리케이션 튜닝 실력을 향상시켜준다. 또한 선택된 SQL 문장과 데이터베이스 환경의 기본 설정을 점검해 성능을 개선시킬 수 있는 방안을 제시해 준다. 특히 Auto Tune 기능이 생성하는 SQL Rewrite는 자동으로 튜닝을 실행해 사용자의 SQL 문장 실력을 높여준다. 이와 함께 Expert Advice가 제공하는 팁과 예제를 통해 개발자와 DBA는 데이터베이스의 성능을 높일 수 있다. 4가지 SQL 튜닝 방법 TOAD는 SQL 에디터를 통해 현재 작성한 SQL 문의 실행 결과와 실행 계획을 확인하고 Auto Tune 기능을 수행할 수 있다. TOAD의 SQL 튜닝은 4가지 방법으로 수행된다. SQL 텍스트와 튜닝 시나리오 비교, 시나리오 익스플로러, 튜닝 시나리오 권고 등이 그것이다. 먼저 SQL 텍스트 튜닝은 SQL 에디터나 프로시저 에디터에서 수행중인 SQL문장을 드래그 해 실행 계획과 해당 SQL문장에 나타난 오브젝트의 정보를 확인할 수 있다. 또 실행 계획을 통해 현재 SQL문장이 어떻게 수행될 것이지 예측할 수 있으며, 해당 테이블에 생성되어 있는 인덱스나 컬럼의 정보를 확인할 수 있다. <그림7> SQL 텍스트 튜닝 두 번째, 튜닝 시나리오 비교(compared Scenario)는 현재 SQL문장을 자동으로 튜닝해주는 Auto Tune 기능과 비숙련자도 쉽게 사용할 수 있는 Advice 기능으로 구성된다. Advice 기능은 환경에 적합한 어드바이스를 제공m 튜닝 솔루션을 찾아 실행할 수 있게 해준다. Advice에서 선택한 사항을 토대로 기존의 SQL문장과 추천된 SQL문장의 실행 계획을 비교해 보여준다. <그림8> 튜닝 시나리오 비교 화면 세 번째, 시나리오 익스플로러를 통해 실행 과정이 모두 완료된 후 기존의 SQL문장과 권고된 SQL문장을 그래픽하게 비교해주며 이에 따라 사용자가 최적의 시나리오를 선택, 적용할 수 있다. <그림9> 시나리오 익스플로러 화면 <DBA를 위한 TOAD 8.6 신기능> - Fine Grained Audit Control - ADD Report Generator - Data Pump Export/Import - Log Miner <튜닝을 위한 TOAD 8.6 신기능> - Index Expert - SQL Scanner - SQL Inspector - Optimization Intelligence and Best Practices - ToolBar Buttons and Test for Scalability 통합 관리 툴로도 손색없는 TOAD TOAD for Oracle은 단순 개발 툴이라고 치부하기엔 너무 많은 기능을 가지고 있는 툴이다. 특히 최근 출시된 8.6을 비롯, TOAD 8.x 버전의 경우 튜닝 기능과 DBA 지원 기능이 대폭 개선돼, DB 관리 툴로도 손색이 없다. 그러나 제공하는 다양한 기능들이 실제 고객사에서 활발하게 활용되지 못하고 있는 점이 아쉽다. TOAD에서 제공되는 기능에 비해 그간 기술 지원이나 교육 지원이 부족했기 때문이라고 생각된다. 최근 한글화 문제나 시스템 리소스 과사용 등의 문제를 해결한 새로운 버전이 출시됐고, 오라클 10g의 새로운 기능이 지원된 유일한 솔루션인 만큼 현업에서의 활용 폭은 더욱 커질 것으로 기대된다.