About DATABASE

출처 : http://scidb.tistory.com/entry/%EC%98%A4%EB%A0%8C%EC%A7%80%EB%82%98-TOAD%EC%97%90%EC%84%9C-Predicate-Information%EC%9D%84-%EC%B0%B8%EC%A1%B0%ED%95%98%EB%8A%94-%EB%B0%A9%EB%B2%95

지인으로부터 아래와 같은 질문을 받았다.

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

       저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
       따라서 오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.

요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다. 

Predicate Information이 뭐지?
Predicate Information이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.
 

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

       e.employee_id, e.first_name, e.last_name, e.email, e.salary

  FROM employee e, department d

 WHERE e.department_id = d.department_id

   AND e.job_id = 'SH_CLERK';

select * from table(dbms_xplan.display);  

-------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |    20 |   860 |     3   (0)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |    20 |   860 |     3   (0)|

|*  2 |   INDEX RANGE SCAN          | EMP_JOB_IX |    20 |       |     1   (0)|

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

   2 - access("E"."JOB_ID"='SH_CLERK')         --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

FILTER와 ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER 와 INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEX를 SCAN에 사용된 컬럼이 하나뿐이고 COL2와 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates와 Filter Predicates의 Visible 항목을 체크하고 OK를 클릭한다.

위와 같이 TOAD에서 Access Predicates와 Filter Predicates가 깔끔하게 출력되었다.

2. 오렌지에서 Predicate Information 보기

을 볼 수 있다.

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...

시 스템 개발·운영시에 다양한 소프트웨어나 솔루션을 도입해 구축하게 된다. 이때 어떠한 솔루션을 도입하느냐에 따라 시스템의 성패가 좌우된다. 또한 다양한 툴의 구매도 그 목적에 따라 해당 업무의 효율성을 높일 수도 있고 효과를 주지 못하는 경우도 있다. 이 글에서는 데이터베이스를 이용하는 개발 프로젝트에서 필수 불가결한 툴인 데이터베이스 클라이언트 툴 가운데 웨어밸리의 Orange for Oracle(이하 오렌지) 제품의 기술적인 기능에 대해 알아보고자 한다. 오렌지 도입시 판단의 기준에 도움이 되길 바란다.

1990년대부터 데이터베이스 시장이 급성장하면서 오라클 개발지원 도구 시장에 먼저 선보인 외산 제품들은 국내 고객에게 쉽게 파고들었다. 하지만 국내 제품이 아닌 탓에 사용자는 사용상의 불편함이나 비용 측면의 불리한 점이 있더라도 눈감고 외산 제품을 사용할 수밖에 없었다. 웨어밸리 오렌지는 국내 기술로 개발된 오라클 개발 지원 및 튜닝 툴로, 사용자의 요구사항에 맞춰 기능을 추가할 수 있다는 점이 가장 돋보이는 매력이다.

뒷방 신세로 전락되는 툴

많은 기업에서 오라클 데이터베이스 지원 툴을 구매하고도 6개월 아니 1년이 지나면 단지 구매 리스트에만 등재돼 있을 뿐 현업에서는 사용하지 않아 사장되는 경우를 종종 볼 수 있다. 이것은 툴의 기능에 대한 잘못된 판단으로 적절하게 구매하지 못한 이유도 있겠지만, 구매 후 잘 활용할 수 있도록 교육이 되지 않는 이유가 크다. 이러한 측면에서 볼 때 오렌지는 지금도 매월 1회씩의 무료 교육을 실시해 사용자들이 오렌지를 업무에 잘 활용하도록 유도한다. 필자의 경우 튜닝 작업이나 시스템 아키텍처를 수립하기 위해 고객과 함께 작업을 할 때, 오렌지의 기본 기능만을 사용하거나 아예 사용하지 않는 고객도 많이 봤다. 이러한 경우 고객에게 몇 가지 유용한 기능을 알려주면 대부분의 고객 반응은 ‘아니, 이런 기능도 있었군요’란 놀란 표현이 나온다. 결국 수 십 가지 기능 중에 사용자가 알고 사용하는 기능은 극히 일부이며, 편리하게 사용할 수 있는 기능이 있는데도 불구하고 적절하게 사용하지 못하고 있어 구매 후 교육 여부도 구매시 꼭 생각해 볼 점이다.

툴 구매시 가장 고려할 점

그럼 툴을 구매하고자 할 때 가장 고려해야 할 점은 무엇일까? 앞서 언급한 사후 교육, 사용자 요구사항 적용 등도 있겠지만 가장 먼저 생각해 볼 수 있는 것은 ‘툴의 기능성이다. 기본적으로 적용하고자 하는 업무에 툴이 갖고 있는 기능이 얼마나 적합한가에 따라 잘 활용되는 도구로써 사용될 가능성이 높아지기 때문이다.

오렌지의 4가지 버전과 특징

오렌지는 2001년 12월 12일 1.0버전 출시 이후 현재 3.1버전까지 오라클 데이터베이스 기반의 개발 및 DBA 툴로 선보이고 있다. 최초 오렌지의 개발자는 DBMS 튜닝 및 디자인을 하는 사람으로, 현업에서 작업 중에 비효율적인 매뉴얼 작업들을 자동화하기 위해 오렌지를 만들었다. 이렇게 만들어진 오렌지는 많은 튜너 및 컨설턴트의 피드백을 받아 사용자 중심의 툴로 설계된 것이 가장 큰 특징이다. 지금의 오렌지 3.1버전은 개발 지원 기능 및 튜닝 지원 툴, 모니터링 툴도 제공된다. 오렌지는 오라클 데이터베이스를 기반으로 하는 시스템을 구축할 경우, 개발자가 SQL을 작성하고 데이터를 조회, 등록, 수정 및 PL/SQL 개발 등을 하는데 많은 기능을 제공한다. 또한 DBA의 주요 작업인 데이터베이스 모니터링을 쉽게 할 수 있는 모니터링 기능도 제공한다(DBA 에디션). 오렌지는 사용 용도별로 크게 4가지로 나눠 구분할 수 있다. 스탠더드 에디션(Standard Edition), DBA 에디션, Reorg 에디션, Trusted 에디션으로 나눠지는데, 2005년 3월에 Reorg 에디션을 추가로 구성했다. 사실 Reorg 에디션이라고 해서 특별히 Table, Index, Tablespace 등의 reorg 작업을 오렌지가 직접 해주는 것이 아니라 오라클의 DBMS_REDEFINITION 패키지를 이용해 온라인 reorg 절차를 간편하게 작업할 수 있도록 지원해 주는 것이다. 또한 Trusted 에디션은 사전 등록된 내부 결제 프로세스를 거친 SQL만을 수행할 수 있도록 하는 기능을 갖춘 사전 보안 감시용이라 할 수 있다. Trusted 에디션의 경우 고객의 업무 프로세스와 연계해 구성되어 사용하는 것이므로 제품 구입시 고려할 사항이 스탠더드 에디션, DBA 에디션과 현격하게 다르기에, 이글에서는 고객이 주로 사용하는 스탠더드 에디션, DBA 에디션만을 설명하도록 하겠다.

개발자 위한 오렌지 스탠더드 에디션

다음의 <표 1>과 같이 스탠더드 에디션은 오라클 데이터베이스를 기반으로 개발할 때 필요한 많은 기능들을 제공한다. 그리고 DBA 에디션은 데이터베이스 시스템의 전반적인 작업 및 모니터링 작업을 수행할 때 필요한 기능들을 갖추고 있다.

<표 1> 오렌지의 개발자 지원 기능

구분 개발자 지원 기능 SQL 툴 다양한 방식의 데이터 조회 및 SQL의 수행 지원, 멀티 탭으로 여러 SQL 동시 작업 가능, 미리 생성된 SQL을 호출해 수행할 수 있는 Named SQL 기능, 다양한 출력, SQL 포맷팅 등을 지원함 Schema Browser 16가지 타입의 데이터베이스 오브젝트에 대한 조회 및 상세 참조 지원, 타입 별로 실행할 수 있는 메뉴 지원 Query Builder GUI 화면으로 Drag & Drop 방식의 SQL의 작성을 지원함 PL/SQL 툴 Package, Procedure, Function의 작성 지원, 다양한 템플릿을 지원하며 PL/SQL 디버깅 기능 포함 Description 툴 데이터베이스 내 오브젝트의 상세 정보를 참조할 수 있는 기능, 또한 오브젝트를 재 생성할 수 있는 스크립트 생성 기능도 지원 Table Editor 테이블의 데이터를 쉽게 조회해 Form 형식으로 변경/삭제할 수 있는 기능 Plan 툴 SQL의 튜닝 작업시 실행계획 및 Trace, trace out 분석 및 테이블, 인덱스의 통계 정보를 볼 수 있는 기능 지원. 특히 오라클의 tkprof를 사용하지 않고 이보다 더 많은 정보를 제공하는 trace 분석 엔진 지원 Trace 툴 Session trace의 output인 trace file을 가독성 있는 포맷으로 보여주는 방식 Database Information 데이터베이스의 system parameter, hidden parameter, system 통계치, sga정보, log file, control file 등의 일반 정보를 확인하는 기능 Network Conf 툴 클라이언트의 SQLNET Service(tnsnames.ora)를 설정, 변경, 테스트하는 기능 Export/Import 툴 오라클 데이터베이스로부터 데이터의 export/import 기능 지원 File Editor 개발자 PC의 텍스트 파일 편집을 지원 Loader Data Loading/Unloading 기능 지원, 엑셀 혹은 텍스트 자료를 손쉽게 Loading/Unoloading. 오렌지 자체 Loader 엔진 이용 Lock/Latch Monitor Database Lock 및 블럭킹된 세션 및 오브젝트 정보 제공, OPS/RAC 지원 Transaction Monitor 수행 중인 트랜잭션의 롤백세그먼트 사용량, DML의 양에 대한 정보 제공 SQL Monitor SQL Area 내의 정보 분석 제공, Buffer gets, buffer gets/exec, execution, disk reads, elapsed time별로 조회 가능. 또한 실제 SQL text를 이용해 검색 가능 Session Monitor 데 이터베이스에 접속되어 있는 모든 세션들의 정보 제공, 세션의 100여 가지 통계정보 중 5개를 선택해 표시하는 옵션, 상세 조사가 필요한 세션에 대핸 trace 생성 기능, Kill session 기능, 현재 사용하는 SQL display 기능 Data Dictionary 툴 오라클 내부의 딕셔너리 테이블을 카테고리 별로 구분해 제공, 카테고리 별로 참고하고자 하는 딕셔너리 테이블을 쉽게 검색하여 참고할 수 있는 기능 ERP Monitor 대표적으로 고객의 요청에 의해 추가된 기능으로 Oracle Applications(ERP)를 사용하는 경우 ERP의 기본적인 부분에 대한 모니터링 기능

Step-In 기능으로 직관적 정보 분석이 가능

오렌지는 개발부터 가장 가볍고 빠르게 수행될 수 있어야 한다는 전제 하에 제작됐으며, 성능이 느린 화면은 개발시부터 자체 튜닝 대상이 되어 집중적으로 관리됐다. 오렌지의 개발 언어는 Visual C++를 이용했고, 오라클 OCI 인터페이스를 사용한다. 또한 디스크 공간도 매뉴얼을 포함해 약 20MB 정도만 사용할 뿐이다. 다만 오렌지의 빠른 성능을 보장하기 위해서 오렌지에서 사용하는 뷰(view)를 데이터베이스에 생성해야 하는데, 이 작업을 하는 것은 ‘Orange Configuration Admin’을 통해서 쉽게 생성할 수 있다. 하지만 이 작업은 오라클 데이터베이스의 내부 사용 User인 SYS user로 접속해서 수행해야 하기 때문에 데이터베이스 관리자의 허가 하에 작업할 수 있다. 처음 오렌지를 사용할 때는 이것이 다소 제약사항이지만, 스탠더드 에디션인 경우에는 ‘Orange Configuration Admin’을 돌리지 않아도 대부분의 기능은 사용이 가능하다. 화면 간의 작업 및 동작은 멀티 쓰레드 방식으로 구현돼 여러 개의 창에서 동시에 작업할 수 있으며, 오래 수행되는 쿼리를 기다리지 않고 다른 작업을 할 수 있다는 것이 장점이다. 또한 데이터베이스로의 접속도 여러 개의 세션을 맺을 수 있어서 많은 작업을 한 번에 할 수 있게 디자인됐다. 게다가 많은 컨설턴트들의 오랜 노하우가 담겨 있어 개발자, DBA는 작업시 편리한 작업환경에서 오렌지를 사용할 수 있다. 이러한 예는 오렌지 곳곳에서 찾아 볼 수 있다. SQL monitor에서 특정 SQL을 찾은 후, 현재 이 SQL을 사용하는 세션을 찾는 Step-In 기능은 현업에서 시스템 분석시 많이 사용하는 방법이다. 또한 DBA 에디션의 Health Check 기능은 간단하면서도 기본적으로 점검해야 하는 부분을 많은 컨설턴트가 아이디어 및 개인의 노하우를 집약시킨 것이다. 물론 Report & Graph 기능에 이미 정의되어 있는 스크립트 구성을 보면 노하우를 쉽게 느낄 수 있을 것이다. 그리고 <표 1>에서 나열한 많은 툴 간에 Step-In 작업이 가능해 직관적인 정보 분석이 가능하다. 관심 있는 정보에서 더블 클릭이나 오른쪽 버튼을 누르면 상세 정보 창으로 전이되어 빠르게 자세한 정보를 확인할 수 있다. <화면 1>은 오렌지가 제공하는 툴 간의 Step-In을 보여주고 있다. 이러한 Step-In 기능은 실제 업무에서 작업하는 경우 참고 정보를 확인하기 위한 최적의 툴로 연동하는 기능이다.

<화면 1> 오렌지 툴 간의 Step-In 기능

고객 맞춤형 툴로 변신 가능한 ‘오렌지’

마지막으로 오렌지의 일반적인 측면 중 가장 돋보이는 것은 국내에서 개발한 툴이므로 사용자의 요구사항을 적극적으로 수용할 수 있다는 점이다. 여타 다른 툴들은 외산이므로 제공되는 기능만을 사용할 뿐 추가적인 요구 사항을 요청할 방법이 없다. 다만 오렌지의 경우에는 사용자가 필요한 기능을 개발자와 협의해 점차 고객에게 편리한 툴로 만들 수 있다는 것이다. 사실 오렌지를 개발한 웨어밸리 홈페이지(www.warevalley.com)에는 고객과 개발자의 통신 채널이 있으며 활발하게 Q&A를 진행하고 있다. 그 중 한 예가 ERP 모니터 기능으로 오라클 애플리케이션(ERP)을 사용하는 고객의 요청사항에 의해 만들어진 기능이다. 이는 ERP 관리 작업을 할 때 기본적으로 모니터링을 해야 하는 항목에 대한 화면을 항상 보게 되는데, 오렌지를 이용한 모니터링과 함께 하게 되므로 불편할 사항이 있었다. 이에 대해 고객이 적극적인 요청을 해서 오렌지에 상기 기본 모니터링을 할 수 있는 ERP 모니터 기능을 추가하게 된 것이다. 그러므로 고객이 꼭 필요하다고 느끼고 생각하는 기능에 대해서는 향후에도 추가적인 Add-on이 될 수 있다.

오렌지 탄생 실화 오렌지 개발의 핵심 목표는 가볍고, 빠르고, 쉽게 사용할 수 있는 툴을 만든다는 목표 아래 만들어 졌다. 상상을 해보라. 오렌지의 instance monitor(9개의 그래프)는 단 하나의 SQL로 구현됐다. 오렌지의 탄생은 처음 툴을 만들려고 만들어진 것은 아니다. 데이터베이스 컨설팅을 하던 초기 개발자가 자신이 작업하는 것을 자동화하기 위해 2000년부터 2001년 사이 약 1년간 ‘오렌지’라는 이름으로 만들어 졌다. 그 이후 2001년 12월 정식 ‘오렌지’라는 제품으로 탄생했다. 오렌지라는 이름은 그가 오렌지를 그냥 좋아했기 때문이란다. 그 후 웨어밸리의 한 개발자가 오라클 연동한 클라이언트 툴을 혼자 만들어 보기 위해 ‘토마토’라는 것을 만들었다. 결국 토마토는 오렌지의 기능을 강화하기 위해 오렌지에 흡수됐고, 그 이름을 Report & Graph 툴이라고 했다. 가볍고, 빠르고, 쉽게 사용할 수 있는 툴로 개발될 수밖에 없는 이유는 국내 개발 환경을 잘 아는 개발자의 땀이 묻어 있기 때문이다.

개발자 측면의 유용한 기능들

이제까지는 오렌지의 일반적인 구조에 대해 알아봤다. 그럼 개발자 측면에서의 오렌지 기능들에 대해 살펴보자. 개발자가 주로 하는 작업은 SQL의 작성, 데이터 로딩, 데이터 확인, 데이터 수정, PL/SQL 프로그램 작성 등으로, 오렌지는 이러한 작업 활동을 수행할 수 있는 기능들을 제공한다. SQL 툴에서 지원되는 기능으로 SQL의 조회 결과를 엑셀로 저장하는 기능은 기본적으로 지원되며 그리드 출력, 텍스트 출력을 지원한다. 그리고 멀티 탭을 지원해 여러 SQL 작업을 동시에 할 수 있다. 이 SQL 툴에서는 필자가 자주 사용하는 기능으로는 SQL 툴의 Action 메뉴에 Recall Named Script 기능인데, 자주 사용되는 SQL 스크립트를 선택해서 수행할 수 있다. 핫 키로 Ctrl+R을 누르면 쉽게 저장된 SQL을 수행할 수 있다(필자의 경우에는 Named Script를 종류별로 잘 정리해 약 200여개의 스크립트를 업무에 필요시 바로바로 사용한다). 이 SQL 툴에서 특별히 지원되는 기능으로 SQL 포맷 기능이 있는데, 이는 정돈되지 않는 SQL을 가지런하게 정돈해주는 기능이다. 특히 이 기능은 튜닝 작업을 할 때 SQL Area에서부터 악성 SQL을 추출해 작업하는 경우에 더욱 편리하다. SQL Area에서 추출한 SQL들은 주로 작성된 SQL의 형태가 아닌 무작위로 보여주는데, 이 SQL 포맷 기능은 버튼 한 번의 클릭으로 보기 어렵게 되어 있는 SQL을 잘 정돈해준다. SQL*Navigator에도 이러한 기능이 있는데 비교해 본다면 오렌지가 그다지 훌륭하게 포맷되지는 않지만 무료로 제공된다는 데에서 매력이 있다. SQL 툴에서 SQL을 작성하다 보면 여러 번의 수정 및 테스트를 하게 된다. 이때 여러 번 수정된 것을 취소하고 싶을 때는 Undo 기능을 이용해 변경 전의 값으로 돌리는데, 오렌지의 경우는 무한 undo가 지원된다. 이는 SQL을 처음 작성한 시점까지 undo할 수 있다는 것으로. undo가 제한적으로 지원되는 경우(예, MS-Word)에는 undo를 하다가 더 이상 안 될 때 난처할 경우가 발생하는 단점을 없애준다. 개발자들이 많이 사용하는 또 다른 기능 중에 하나는 로더(Loader)이다. 프로젝트의 개발 단계에서 레거시 데이터를 오라클 데이터베이스로 로딩할 때 사용하게 된다. 또한 현재 오라클 데이터베이스에서 데이터를 Un-loading하는 기능도 지원된다. 로더를 이용해 데이터를 로딩할 수 있는 포맷으로는 .csv, .xls, .txt file 등을 지원하므로, 다양한 소스의 데이터 로딩이 가능하다. 반대의 경우인 Unload 역시 상기 지원되는 포맷으로 Un-loading이 가능하다. 개발자들이 SQL을 작성하거나 PL/SQL을 작성할 때 모든 구문을 알고 작업하는 것이 아니므로, 자주 오라클 매뉴얼을 참조해봐야 하는데, 이러한 불편을 없애기 위해 오렌지에서는 DML, DDL, PL/SQL, PL/SQL Control structure, Pseudo column, SQL Function 등의 템플릿을 지원해 매뉴얼 참조 없이 손쉽게 SQL이나 PL/SQL의 작성을 도와준다.

PL/SQL 툴의 독특한 기능 ‘Source Control’

개발자 관련 오렌지 툴 중의 PL/SQL 툴은 독특한 기능이 있다. 이는 오라클 내의 PL/SQL을 작성할 때 편리하게 작업할 수 있는 환경을 지원한다. 이 PL/SQL 툴의 독특한 기능으로 Source Control이 있는데, 기존의 PL/SQL을 수정할 때 다른 개발자와의 동시 수정을 예방하고 어떤 사용자가 현재 어떤 PL/SQL을 수정하고 있는지 알 수 있는 checkout list를 지원한다. Checkout list는 다른 개발자가 동일 PL/SQL을 Check-In한 정보를 보여 준다. <화면 2>는 checkout list 버튼을 눌렀을 때, 현재 PL/SQL을 수정하고 있는 사용자의 IP-address와 컴퓨터 이름이 나타난다. 만약 자신이 수정하고자 하는 PL/SQL이 이미 다른 개발자로부터 checkout이 됐다면, PL/SQL 툴에서는 이 PL/SQL을 수정할 수 없다. 만약 일반적인 툴이라면 이러한 제약사항 없이 사용하게 되어 두 명의 개발자가 동시에 소스를 수정해 혼란이 발생하게 될 것이다. 만약 다른 개발자가 동시에 작업하고 있다면 IP-address 및 컴퓨터 이름을 통해 어떤 개발자인지 알 수 있고, 전화 등의 통신 채널을 통해 작업 정지나 잔업 협의를 통해 계속 진행할 수 있을 것이다.

<화면 2> Checkout list

PL/SQL 툴의 다른 큰 특징으로는 디버거(Debugger) 기능이다. 일반적인 컴파일러와 유사한 형태로 PL/SQL(procedure, function) 등을 수행 스텝 별로 디버깅할 수 있다. 이 디버거 기능도 다른 외산 제품들은 고가로 팔고 있으나 오렌지는 기본으로 제공하고 있으니 이 또한 큰 장점이라 할 수 있다. 물론 이 PL/SQL 디버거 기능이 C++ 등의 디버거처럼 화려한 기능을 제공하지는 않지만, 기본적은 기능(변수 참조, Step into, Step over, Breakpoirt 등)은 지원한다. PL/SQL 툴에서 procedure, function을 컴파일할 때 컴파일 에러가 발생하게 되면 status 창에 오라클 에러 번호와 에러 메시지를 보여주는데, 이 에러 번호를 더블 클릭하면 그 에러가 발생한 PL/SQL의 부분으로 커서(cursor)를 이동시킨다. 이 기능은 개발자가 빠르게 에러의 위치를 파악하고 수정할 수 있도록 한다. 개발자의 데이터베이스 관련 툴을 사용하는 목적 중의 하나는 데이터베이스 내의 오브젝트를 참조하는 경우이다. 어떤 테이블이 있는지 해당 테이블에 어떤 컬럼이 있는지, 또한 제약사항, 인덱스 등을 확인하는 경우가 있다. 이렇듯 개발자가 SQL 작성시 참조하는 DB 오브젝트의 정보를 손쉽게 확인할 수 있는 스키마 브라우저 기능은 약 16개의 오브젝트를 윈도우의 탐색기와 같이 참조할 수 있도록 지원해 동시에 창을 열고 쉽게 다른 오브젝트를 참고할 수 있다. 물론 스키마 브라우저에서 16개 오브젝트를 검색하며 오른쪽 버튼을 누르면 그 오브젝트에 대한 작업을 할 수 있는 메뉴가 나타난다. 만약 constraint tab에서 오른쪽 버튼을 누르게 되면 ‘Describe, Add constraint, Enable Constraint, Disable Constraint, Drop object’ 등의 연관된 메뉴를 보여준다. 이는 각각의 오브젝트 타입에 따라 필요한 작업을 바로 할 수 있게 하는 기능이다.

DBA 측면의 유용한 기능들

DBA를 위한 기능은 스탠더드 에디션보다 DBA 에디션에 많다. DBA 에디션은 <표 1>과 같이 스탠더드 에디션의 모든 기능에 Space Manager, Security Manager, Instance Monitor, Graph & Report, Health Check, Analyze Manager, Log Miner가 있다. 스탠더드 에디션과 DBA 에디션의 차이는 DBA 기능은 주로 데이터베이스 전체 범위의 모니터링이나 점검, 전체 레벨의 작업을 지원하는 툴들이 대부분이라는 것이다. 가장 대표적인 DBA 지원 툴은 ‘Instance Monitor’이다. 이 툴은 데이터베이스 전반적인 상태를 표시해 주는데, 크게 9가지 부분으로 나눠져 있다. Buffer Cache Hit Ratio, Library/Dictionary Cache Hit Ratio, Connection 수, Active Transactions, Shared Pool Size usage, I/O block per Second, Redo size, Parse/Execution, Wait event 등을 한 눈에 볼 수 있게 주기적으로 그래프를 그려준다. 이 9가지 그래프들은 서로 시간적으로 연결돼 있어 한 그래프의 위치를 선택하면 나머지 8개의 그래프에 시간을 함께 보여줌으로 같은 시간의 정보를 비교해 점검해 볼 수 있다. 이전의 오렌지 2.0 버전에서는 로깅 기능이 없어서 현재 상태의 정보만을 볼 수 있었고 오렌지를 정지했다가 다시 사용할 때는 이전에 모니터링 결과는 무시되고 새로이 모니터링을 해야 했는데, 3.0 버전부터는 로깅 기능도 생겨서 정상적일 때와 부하가 있을 때의 상태를 비교해 볼 수 있다. 이 인스턴스 모니터는 데이터베이스의 전체적인 뷰를 확인하고자 할 때 사용한다. 특별히 이 인스턴스 모니터로부터 다른 Action은 취할 수 없고 현상만 보게 되지만 간단히 시스템의 상태를 보기에는 무리가 없다. 특히 wait event는 옵션 쪽에서 사용자가 특별히 관심 있는 오라클 wait event를 지정할 수 있어 사용자의 편의성을 갖추고 있다.

<화면 3> Instance Monitor의 사용화면

두 번째로 소개할 DBA의 툴 중에 Space Manager가 있다. 이것은 데이터베이스의 테이블 스페이스, 데이터 파일들의 사용량, redo log의 현황, 롤백 세그먼트의 현황을 확인하는 기능을 제공한다. <화면 4>는 각각의 오브젝트가 물리적으로 데이터 파일의 어떤 위치에 몇 개의 extent로 구성되어 있는지 맵 형식으로 한 눈에 보여준다. 이 화면을 통해서 데이터 파일(테이블 스페이스)이 얼마나 조각화가 됐는지 손쉽게 파악할 수 있으며 오브젝트의 실제 저장 위치를 확인 할 수 있다. 다만 블럭의 맵을 그릴 때 약간의 시간이 걸린다는 아쉬움이 남는다(여러 번 튜닝을 했다고는 하는데, 작업량 자체가 많은 관계로 쉽게 빨라지지 않는 것 같다).

<화면 4> Space Manager 데이터 파일 맵

상세한 정보를 제공하는 세그먼트 인포 탭

그리고 <화면 5>와 같이 세그먼트 인포 탭을 보게 되면 해당 오브젝트가 할당 받은 공간 중에 얼마만큼의 블럭을 사용하고 있고, 얼마만큼의 여유 블럭이 있는지 확인할 수 있다. 세그먼트 Usage 바 차트를 보면 진한 파란색, 파란색, 연한 파란색, 하얀색 등으로 구분되어 있는데, 블럭의 사용 정도에 따라 색깔을 달리해 정확한 정보를 시각적으로 보여준다. 이 정보를 이용해 이미 할당 받은 공간의 내부 사용량을 점검함으로써 추가적인 공간 할당이 언제쯤 필요하게 될지 판단하는데 도움이 된다. 보통은 이미 할당한 공간은 모두 사용하는 것으로 간주하고 테이블 스페이스의 공간 할당 계획을 수립하게 되는데, 좀 더 자세한 정보를 이용해 상세한 공간 할당 계획을 수립할 수 있다. 이러한 기능은 여타 다른 툴에서는 찾아보기 어려운 상세한 정보를 제공하는 기능이다.

<화면 5> Space Manager 세그먼트 인포

DBA 에디션에서 빈번히 사용되는 기능 중 하나로 Health Check 기능을 꼽을 수 있는데, 이는 데이터베이스의 전반적인 점검 부분들을 한 번에 점검해 HTML 형식의 리포트를 생성해 준다. 크게 General, SGA, Wait Event, I/O, Space, Access Type, MTS, OPS(RAC)의 8가지로 나뉘며, 각각의 부문별로 점검해야 하는 통계량 및 데이터베이스 운영 내역을 검토해 점검 리포트를 생성한다. 리포트에는 각각의 점검 내역에 대한 자세한 가이드도 제공되어, 문제점이 발견됐을 경우 어떠한 조치를 취해야 하는지 DBA가 쉽게 알 수 있다. 이 Health Check 기능에서 점검하는 점검 리스트들은 데이터베이스 튜닝 및 컨설팅 경험이 있는 여러 명의 노하우가 집약되어 만든 것이다. 그러므로 Instance Monitor와 Health Check 기능을 통해서 데이터베이스 관리자는 관리하고 있는 데이터베이스 상태와 일일 점검 작업을 손쉽게 수행할 수 있다. 다만 오렌지에서 제공되는 기능은 고객의 환경 및 상황에 만족되지 않을 수 있다. 즉, 오렌지에서 이미 정의된 내용이 아닌 고객의 환경에 관련한 점검이 필요한 경우에는 Instance Monitor와 Health Check 기능으로도 점검이 되지 않는다. 이러한 고객의 특화된 부분을 위해 Graph & Report 툴을 제공한다. 이 Graph & Report는 개발 초기에 ‘토마토’라고 불리었고, 오렌지와는 별도로 개발되다가 오렌지의 한 개 툴로서 통합됐다. Graph & Report에서는 Health Check에서 수행되지 않는 다양한 점검 스크립트가 미리 정의돼 있으며, 사용자가 직접 새로운 스크립트를 정의할 수 있는 User Define Group을 통해 원하는 스크립트를 생성해 모니터링하고 이를 차트로 표현될 수 있도록 제공한다. DBA 에디션에서 제공되는 Log miner 툴은 오라클의 log miner 기능을 이용해 이미 수행된 작업들에 대한 상세 내역을 편리하게 확인할 수 있다. 이 log miner 사용자에게 오라클 log miner package의 사용법을 숙지하지 않더라도 손쉽게 데이터베이스 작업 이력을 확인해 볼 수 있다.

<표 2> 오렌지의 DBA 지원 기능

구분 DBA 지원 기능 Space Manager Tablespace, Datafile의 물리적인 저장 공간 할당 정보 제공. 테이블스페이스 추가, 변경, 데이터 파일 추가 변경 지원, 특히 세크먼트 인포에서는 할당된 익스텐트 내부의 블럭들의 사용 정도를 상세하게 보여줌 Security Manager User, Role, Privilege, Profile 등의 권한 관리 기능 제공 Instance Monitor 데이터베이스의 기본적인 SGA 모니터링 기능 제공, 9개의 기본적인 차트를 지원하며 특히 wait event는 사용자가 선정해 구성 가능하다. 히스토리 관리를 위해 로깅기능 제공 Graph & Report 특정 SQL의 반복 수행을 이용한 그래프 작성 제공, User Define SQL의 수행 및 그래프 작성 제공 Health Check 10여명의 컨설턴트에 의해 선별된 데이터베이스의 기본적인 Health Check 및 Report 제공, OPS, RAC, MTS 관련 지원 Analyze Manager Table, Index에 대한 통계치 생성 및 관리 기능 제공, 특히 대량의 통계치 생성 작업시 테이블의 크기나 건수에 따라 완전분석(compute)이나 예측분석(estimate)이 가능 Log Miner Redo log/ Archive log에 대한 Log miner 분석을 쉽게 할 수 있는 기능 제공

튜너 측면의 유용한 기능들

오렌지의 태생이 튜너의 작업 지원을 위해서 개발됐다는 것은 이미 앞에서 언급했다. 즉, 튜너의 작업시 필요한 많은 기능이 오렌지에는 포함되어 있다.

SQL 튜닝에 강력한 기능을 갖고 있는 '오렌지'

오라클 데이터베이스 관련 튜닝이라고 하면 크게 두 가지로 나눌 수 있는데, 하나는 데이터베이스 튜닝이고 나머지는 SQL 튜닝이라 할 수 있다. 오렌지는 후자의 SQL 튜닝에 강력한 기능들을 많이 갖고 있으며, 전자인 데이터베이스 튜닝 기능도 보유하고 있다. 앞서 설명한 개발자 기능, DBA 기능에도 데이터베이스 튜닝을 위한 점검 기능이 포함되어 있으며, 특히 SQL Monitor, Instance Monitor, Session Monitor, Transaction Monitor, Lock Monitor는 데이터베이스 튜닝시에 자주 사용되는 기능이다. SQL Monitor에서는 데이터베이스에 다양한 옵션을 이용해 부하를 많이 주는 SQL을 찾을 수 있으며, Instance Monitor에서는 주된 병목 현상이 어떤 부분에서 발생하는지 쉽게 확인할 수 있다. Session Monitor에서는 데이터베이스에 접속한 세션들의 작업 내역을 데이터베이스 통계치 기준으로 자원을 많이 사용하는 세션들을 편리하게 점검할 수 있다. 여기에서는 SQL을 튜닝하는 것을 중점적으로 설명하겠다.

대표적 튜닝 지원 기능 ‘Plan 툴’

대표적인 튜닝 지원 기능으로는 ‘Plan 툴’이라고 할 수 있다. Plan 툴은 개별 SQL의 수행 계획(Execution Plan)을 보여주며, 수행 계획의 각 단계별로 위치하는 오브젝트의 정보를 손쉽게 확인해 볼 수 있다. 단지 더블 클릭만으로 테이블의 컬럼 정보, 인덱스 정보, 그리고 테이블의 통계치 등을 확인해 볼 수 있다. 또한 하나의 SQL에 포함되어 있는 모든 테이블과 인덱스 정보를 수집할 수 있다. 물론 이러한 static 정보만을 갖고 정확한 튜닝 결과를 도출 할 수 없다. 이에 Plan 툴에서는 실시간 SQL Trace 기능을 지원한다. 오라클에서 SQL trace를 생성해 이 trace file을 실시간으로 읽기 편한 포맷으로 분석해 사용자에게 제공한다. 10046 이벤트를 이용한 여러 레벨의 trace를 지원하며, SQL 내부에 있는 bind 변수 값, wait event 및 실제 수행되는 시간을 정확하게 분석할 수 있다.

오라클의 tkprof 없이 자체 엔진으로 trace file을 분석한다?

오렌지를 사용하지 않는 경우에는 SQL의 trace를 걸어서 trace file을 확인하고, 오라클에서 제공하는 tkprof를 이용해 포맷팅을 해서 trace 결과를 확인하는 5∼6단계의 작업이 필요하나, plan 툴의 실시간 trace 기능을 사용하면 버튼 하나로 모든 작업이 완료돼 빠르게 튜닝 작업을 할 수 있다. 여기서 오렌지의 특이한 점은 trace file을 분석하기 위해서 오라클의 tkprof를 사용하지 않고 자체적으로 엔진을 만들어 trace file을 분석하는 것이다. 이렇게 자체적으로 엔진을 만들어서 사용하는 까닭은 오라클 서버의 tkprof를 이용해 분석하는 경우, 서버에 tkprof를 수행할 수 있도록 셸 프로그램 등을 구성해야 하는 구조적인 문제(오렌지는 서버에 에이전트가 없는 가벼운 구조이다)와 클라이언트 반응 시간, Wait Event 정보와 같은 더 많은 분석 정보를 주기 위해서다. SQL 튜닝을 하는 과정에 하나의 SQL을 여러 방법으로 수정해 보고 작업하게 되는데, Plan 툴에서는 멀티 탭 기능을 이용해 여러 개의 SQL 버전을 만들어서 튜닝 작업을 할 수 있다. 물론 이런 변형된 SQL들을 각각 trace를 할 수 있으며, 이러한 모든 결과는 Workspace 개념으로 저장 및 로드할 수 있다(PTD 파일로 저장이 된다). 이 Workspace 개념은 개발자와 튜너와의 통신 수단으로 사용되곤 한다. 개발자가 개발한 SQL의 성능이 나오지 않을 경우에는 1차적인 Trace 및 통계정보를 workspace로 저장해 튜너에게 튜닝 요청을 하게 되면, 튜너는 이 workspace를 로드해 바로 튜닝 작업을 진행할 수 있으며 튜닝이 완료되면 수정된 SQL과 이에 따른 trace 정보를 다시 개발자에게 workspace로 저장해 전달할 수 있다.

<화면 6> Plan 툴

이 Plan 툴에서는 Run time execution plan을 보여준다는 것도 특징이다. SQL이 수행되는 과정은 크게 『파싱 | 바인딩 |수행』으로 나뉘는데, 지금까지는 실행 계획을 파싱 단계에서 수립해 수행했다. 그런데 오라클 데이터베이스 9i 버전부터는 bind peeking이라는 기능이 있어서 바인딩 단계에서 변수 값을 이용해 새로이 실행 계획을 생성하는 것이다. 그러므로 수행 계획만을 체크했을 때와는 달리 실제 수행 계획이 달라지는 경우가 종종 있다. 이러한 차이점은 개발자나 튜너를 황당하게 만드는데, 이렇게 차이가 나는 실행 계획은 확인시켜 줄 수 있다. Plan 툴의 내부 기능인 Trace를 독립적인 Trace 툴로 제공하고 있는데, 이는 Trace를 오라클 전체적인 방법으로 수행하거나, 이미 trace가 생성되어 있는 경우 이 파일을 분석하는 형태로 사용하기 때문에 추가된 것이다. 또한 포맷팅된 결과로 볼 수도 있지만 종종 로우 파일(Raw file) 형태의 참조가 필요한 경우를 대비해 로우 파일의 참조도 지원한다.

미래의 오렌지에 대한 기대

지금까지 오렌지가 개발자, DBA, 튜너에게 어떠한 기능을 제공하는지에 대해 알아봤다. 이 글에서 언급한 내용이 오렌지의 모든 기능은 아니지만, 자칫 매뉴얼처럼 될 것을 우려해 오렌지가 갖고 있는 기능 중에 사용자들이 가장 많이 사용하고 많은 도움을 주는 기능 위주로 설명해 오렌지를 선택하고자 하는 고객에게 객관적인 정보가 됐으면 하는 바람이다. 오렌지 역시 버전이 업그레이드되며 사용자 중심의 기능들이 많이 추가됐다. 하지만 앞으로 좀 더 해야 할 일이 많은 것 역시 현재의 숙제이다. 오라클 9i, 오라클 10g, 그리고 2005년 8월 말에 출시될 오라클 10g R2의 새로운 기능들을 사용자들이 손쉽게 접근하고 활용할 수 있는 그러한 오렌지로 변신해야 할 것이다. 아직까지는 오라클 8, 오라클 8i, 그리고 오라클 9i를 지원하는데 머물러 있기 때문에, 향후 1∼2년 이내에 오라클 10g의 장점을 표현해 주지 못한다면 시장에서의 입지는 좁아질 것이다. 오라클 10g에서 제공되는 기본 기능에는 AWR(Automatic Workload Repository), ADDM(Automatic Database Diagnostic Manager) 등이 있다. 이 기능들은 데이터베이스의 운영 상태, 문제점, 병목 현상의 원인 등을 모니터링 툴이 없어도 될 정도의 데이터를 제공한다. 이렇게 풍부한 데이터를 어떻게 효율적으로 사용자에게 제공하느냐가 다음 버전 오렌지의 발전에 계기가 될 것이다.

출처 :

오라클 데이터베이스 환경에서 쉽고, 빠르게 개발의 효율성과 생산성을 높이고, 성능의 최적화를 구현하는 어플리케이션 개발 및 성능 튜닝 툴

패밀리 제품 Intel Cluster Math kernel Library for Linux Orange for Oracle DBA (라이센스) Orange for Oracle Reorg (라이센스) Orange for Oracle STD (라이센스)

제품목적 SQL과 PL/SQL개발 및 튜닝, 모니터링을 빠르고 쉽게 할 수 있는 인터페이스를 제공함으로써 사용자의 생산성을 향상 시킨다. 사용대상 ORACLE DB를 액세스 하기 위한 SQL(Structured Query Language), PL/SQL을 작성하는 개발자와 SQL 튜닝과 모니터링을 하는 DBA 운영환경 CPU : Pentium 133 이상 메모리: 64 MB 이상 Software Platform: Windows 9x/NT/Me/2000/xp SQL*Net 최소 : 8.0.5, 권장 : 8.1.7 이상 (http://otn.oracle.co.kr/software/) Oracle RDBMS: Oracle 7.3 이상 Disk Space : 10MB Orange 특장점 현업 개발자들이 요구하는 핵심기능에 충실 뛰어난 인터페이스 오라클 경력이 많은 컨설턴트들의 실전을 통해 검증된 방법 및 interface 제공 모든 기능이 통합형으로 제공 순수 국내기술 사용자들의 추가 요구사항에 신속한 대응 한글 매뉴얼 및 HELP 제공 기술축적에 따라 저렴한 가격으로 다양한 DB TOOL software 제공 Architecture 제품종류 Standard Edition Schema Browser Query Builder SQL Tool PL/SQL Tool Description Tool Table Editor Plan Tool Trace Tool Database Information Network Configuration Tool Expert Tool Import Tool File Editor Loader Latch Monitor Transaction Monitor ERP Monitor SQL Monitor Session Monitor Lock Monitor Data Dictionary Tool DBA Edition Space Manager Security Manager Instance Monitor Graph & Report Health Check Analyze Manager Log Miner Reorg Edition Reorg Manager dbms_redefinition 패키지를 사용하여 테이블 온라인 REORG 및 REORG 대상 추출 Trusted Edition DB 사용자가 의도적으로 외부유출을 목적으로 데이터를 액세스하거나 또는 비의도적인 실수에 의하여 문제가 발생하는 것을 사전에 차단하기 위하여 DB 액세스 시 사전 등록된 내부결재 프로세스를 거친 SQL만 실행하도록 하는 기능을 갖춘 사전 보안감시용 툴

SQL * Plus 명령어  & 환경 시스템 변수

                                                                           - By Everekk79 ( Last Updated 2007.11.22 )

sqlplus [ username [ /password [ @database ]]

SQL*Plus 명령어는 한번에 한 라인만 입력할 수 있으며 명령어를 축약하여 사용할 수 있다.

SQL*Plus 명령어는 SQL 버퍼에 저장되지 않으며 세미콜론(;)과 같은 종료 문자를 사용하지 않는다.

RUN : 최근에 실행된 SQL 명령문을 실행한다

         SQL 명령문과 함께 결과를 출력한다

/ : 최근에 실행된 SQL 명령문을 실행한다.

    검색 결과만을 출력한다.

  ==> 최근에 실행된 SQL 명령문은 SQL 버퍼에 저장되어 있 다.

eixt : 정상 종료

  ==> 정상 종료를 해야만 사용자가 데이터베이스에 수정한 내용이 디스크에 영구적으로 저장된다.

▣ 시스템 변수 설정 명령어

 SET System_Variable Value

Set Auto [Commit] { off | on | imm[ediate] | n }

on, immediate : DML 명령문이 성공적으로 실행되면 자동적으로 Commit 명령문 실행

off : DML 명령문 실행 후, 사용자가 직접 Commit 명령문 실행

n : DML 명령문을 n 번 성공적으로 실행 후 자동적으로 Commit 문 실행 

Set Feed[back] { 6 | n | off | on }

Select 문의 실행 결과를 표시하기 위하여 출력 행의 수를 지정하는 시스템 변수

n : 출력 행의 수가 n 이상인 경우만  " n개의 행이 선택되었습니다" 라는 메시지 출력

6 : 기본값

on : feedback 변수 값을 기본값으로 설정한다.

Set Heading { off | on }

off : 컬럼 제목이 출력되지 않는다.

on : 컬럼 제목을 출력한다.

Set Lin[esize] { 80 | n }

한 화면에 표시되는 SQL 명령문의 출력 결과에 대한 행의 크기를 설정한다.

기본값은 80이며 최대값은 시스템에 따라 차이가 남

Set Pages[ize] { 14 | n }

한 화면에 표시되는 SQL 명령문의 실행 결과에 대한 페이지의 크기를 설정하기 위한 시스템 변수

한 페이지에는 칼럼 제목, 데이터의 구분선, 페이지를 구분하기 위한 공백 라인도 포함된다.

기본값은 14이다,

Set Pause { off | on }

SQL 명령문의 실행 결과를 한 화면에 보기 힘든 경우에 한 페이지씩 나누어 출력하기 위한 시스템 변수이다.

on : 사용자가 엔터키를 입력할 때까지 대기하고 있다가 엔터 키를 입력하면 실행 결과를 출력한다.

Set Term[out] { off | on }

SQL 명령문 실행 결과를 화면에 출력할 지 여부를 지정하기 위한 시스템 변수

스크립트(*.sql) 파일에 의해 실행된 여러 개의 SQL 명령문의 출력 결과를 확인 할 필요가 없는 경우 유용하게 사용된다.

off : SQL 명령문의 실행 결과가 화면에 출력되지 않는다.

on : SQL 명령문의 실행 결과를 화면에 출력한다.

Set Ti[me] { off | on }

SQL  프롬프트 앞에 시스템의 현재 시간을 함께 표시하도록 설정하는 시스템 변수

on : SQL 프롬프트 앞에 시간 정보가 함께 표시된다.

off : 시간 정보를 출력되지 않도록 설정한다.

Set Timing { off | on }

Timing  변수는 SQL 명령문을 실행하는데 소요된 시간을 출력하기 위한 시스템 변수이다.

시간은 '시:분:초.밀리초' 형식으로 출력된다.

on : SQL 명령문을 실행하는데 소요된 시간을 출력한다,

off : 소요된 시간 정보가 출력되지 않는다.

Set Und[erline] { - | c | on | off }

컬럼 제목과 데이터간의 구분 기호를 설정하기 위한 시스템변수이다.

c : 모든 문자

on : 구분기호 사용

off : 구분기호 사용하지 않음

Set Verify { on | off }

SQL 명령문이나 PL/SQL에서 &를 이용한 치환 변수등을 사용할 때 치환되기 전 후의 자세한 값을

보일 건지의 여부를 결정한다. 기본값은 On이다

Set Serveroutput { on | off }

PL/SQL 문에서 dbms_output.put_line() 프로시저를 사용

출력을 SQL*Plus 에서 보려면 Set Serveroutput on 을 먼저 실행해야 한다.

Set Colsep { text }

컬럼이 표시될때 컬럼의 구별 문자

열 사이에 출력되는 문자를 설정

Default Value : 공백

▣ SQL*Plus 기타 명령어

Show { system_variable | all }

Show 명령문은 현재 세션에 설정된 시스템 변수를 확인하기 위한 명령어이다.

Show all을 할 경우 현재 설정된 모든 환경 변수값을 확인 할수 있다.

Help 명령어

SQL 명령어를 모를 경우 사용

▣ 형식 명령어

Column { column | alias } [ option ] [ Format ]

Column 명령어는 SQL 명령문의 실행 결과로 출력되는 칼럼 제목이나 컬럼 데이터에 대한 출력 형식을 다양하게 지정하기 위한 명령어이다.

[ option ]

Cle[ar] : 컬럼 형식 해제

For[mat] fromat : 컬럼 데이터의 출력 형식 지정

Hea[ding] text : 컬럼 제목 설정, text 내의 수직(|)바는 컬럼 제목을 여러 줄로 출력 할 경우

                       EnterKey 역활

Jus[tify] { align } : 컬럼 제목을 왼쪽, 가운데 또는 오른쪽 정렬

Nopri[nt] : 컬럼 숨기기

pri[nt] : 컬럼 출력하기

Nul[l] text : null값에 대한 출력 문자 지정

[ Format ]

An : 문자 형식 컬럼의 출력 크기를 n폭으로 설정

9 :  단일 zero-suppression (0억제) 숫자

      example >> 999999 -> 1234

0 : 지정된 길이만큼 숫자 앞에 0 추가

      example >> 009999 -> 001234

$ : 숫자 앞에 달러 기호 삽입

      example >> $9999 -> $1234

L : 숫자 앞에 지역 화폐단위 삽입

      example >> L9999  -> $1234

. :  소숫점 위치 지정

      example >> 9999.99  -> 1234.00

, : 1000 자리마다 ',' 구분자 삽입

      example >> 9,999 -> 1,234

SQL> help variable

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

VAR[IABLE] [variable [type]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

▣ 편집 명령어

A[ppend] text : SQL 버퍼의 현재 라인 끝에 text 추가

C[hange]/old/new : 현재 라인의 old text를 new text로 변경

C[hange]/text/ : 현재 라인에서 text 삭제

Cl[ear] Buff[er] : 모든 라인 삭제

Del : 현재 라인 삭제

Del n : n 번째 라인 삭제

Del m n : m 번째 라인에서 n 라인까지 Text 삭제

I[nput] : 현재 라인 다음에 text 추가

I[nput] text : 현재 라인 다음에 text 추가

L[ist] : 모든 라인 출력

L[ist] n : n번째 라인의 text 출력

L[ist] m n : m번째 라인부터 n 라인까지 text 출력

n : n 번째 라인으로 이동

n text : n 번째 라인 내용으로 text 변경

0 text : 1번째 라인 앞에 text 추가

▣ 파일 조작 명령어

Sav[e] filename : 현재 SQL 버퍼의 내용을 파일에 저장

Get filename :  Save 명령어로 저장한 파일을 SQL 버퍼에 읽어 옴

Sta[rt] filename : 파일을 일고 즉시 실행

@filename : 파일을 읽고 즉시 실행

Ed[it] filename : 저장된 파알 내용을 편집

Spo[ol] [filename | off | on ] : 파일에 출력 결과를 저장

                                            off 는 spool 파일을 닫음

                                            on 은 spool 파일을 닫고 프린터로 파일 전송

출처 : http://applejara.tistory.com

가끔 보면 행이 null인지 아니면 공백인지 구분이 안될때가 있다. 오라클 사용자는 대부분 Toad같은 툴을 사용하기 때문에 Toad를 기준으로 null값을 확인하는 방법을 알아보자.

일단 기본적인 화면에선 null은 표시되지 않는다. 그럼 null값을 표시하는 설정을 확인해 보자. 위치는 view -> toad options -> data grids , visual 이다. 필자는 toad 9.6을 사용하므로 버젼에 따라 조금씩의 차이가 있을 수 있다.

최 하단에 null columns가 있는데 여기에 옵션을 선택할 수 있다. 필자는 노란색으로 표시하기로 했지만, null이란 문자로 출력하게 바꿀수도 있다.

설정 후 null값은 노란색 배경으로 출력됨을 확인할 수 있다.

Oracle DB로 보는

데이터베이스 인터페이스의 발전

많은 개발자들이나 DBA들은 하루에도 몇 번씩 데이터베이스와 대화를 시도한다. 때로는 툴을 쓰기도 하고 때로는 COMMAND 모드로 대화를 시도한다. 대부분 직관적으로 보기 쉽고 접근하기 쉬운 GUI 기반의 툴로 접근하리라 생각된다. 막상 편한 GUI 기반의 툴을 쓰다가 갑자기 커맨드 라인 모드로 데이터에 접근한다면 불편하기 그지없을 것이다. 우리가 익숙하게 쓰는 데이터베이스가 어떤 툴로 변모해 왔으며 앞으로 어떤 방향으로 변해갈지 오라클 데이터베이스를 중심으로 설명하고자 한다.

먼저 데이터베이스에서 유저 인터페이스의 종류를 꼽으면 아래와 같다.

- 커맨드 라인 인터페이스
- GUI 기반 관리 툴
- SQL*DEVELOPER
- 기타 RDBMS에서의 유저 인터페이스

커맨드 라인 인터페이스

SQL*PLUS

오라클 데이터베이스의 유저 인터페이스 가운데 가장 대표적인 커맨드 라인 유틸리티인 SQL*PLUS는 크게 MS-DOS 프롬프트와 같은 명령 프롬프트 기반의 CUI(Character User Interface) 유저 인터페이스와 일부 그래픽 모드를 보강한 GUI 기반의 인터페이스 두 가지를 지원한다.

SQL*PLUS라는 이름의 기원은 Oracle 4 버전 때로 거슬러 올라간다. UFI(User Friendly Interface)라는 이름으로 버전 4까지 포함되어 오다가 이 UFI에 신규 기능들이 추가되어 내부적으로 ‘Advanced UFI’로 명명되었다. 후에 신규 릴리즈가 발표되기 전 공식적으로 SQL*PLUS로 명명해서 발표하게 되었다.

근래 들어서 강력한 사용자 편의 기능을 갖춘 써드파티 툴에 밀려서 개발자나 DBA에게 있어서 그 사용빈도가 줄었다고는 하나 모든 운영체제 시스템에서 사용 가능할 뿐만 아니라 SQL* PLUS만의 특수한 기능으로 인해 그 효율성은 여전히 유효하다고 하겠다. 특히나 유닉스(UNIX) 계열의 서버 작업시에는 없어서는 안 될 중요한 유틸리티라고 할 수 있다.

<화면 1>은 X-Terminal에서 실행한 SQL*plus 커맨드 라인의 예제와 윈도우 GUI 기반의 예제이다.

<화면 1> CUI 기반의 SQL*PLUS와 윈도우 GUI 기반의 SQL*PLUS

ISQL*PLUS

본격적인 인터넷 환경이 활성화되기 시작한 시기에 발표된 Oracle 8i(1998)에서 SQL*PLUS의 웹(web) 기반 버전이 ISQL*PLUS이다. Oracle 9i부터는 웹 브라우저에서 SQL을 실행하는 환경을 지원하게 되었다. 이는 후에 나오는 10g의 웹 기반 Enterprise Manager의 모체가 되었던 존재이기도 하다. 기존의 SQLPLUS가 포맷을 정하기 힘들고 FETCH 사이즈를 지정하기 힘든 문제점을 지녔는데, ISQLPLUS에서는 데이터 GRID 형태를 지원함으로써 이 문제를 어느 정도 해소하게 되었다.

<화면 2> 웹 기반의 ISQLPLUS

접속 방식은 다음과 같이 웹 브라우저에서 URL로 host 서버의 주소와 포트번호를 입력해서 접속한다.

- 형식 : http://machine_name.domain:port/isqlplus
- 예 : http://localhost:5560/isqlplus

9i에서의 ISQLPLUS는 Java Oracle HTTP 서버 기반에서 7777 포트를 대기하고 있다. 반면 10g에서는 ocj4로 동작하는 5560 포트를 대기하고 있다.

SQL*PLUS의 장단점

● 단점
- OUTPUT 출력 값에 대한 데이터 포맷과 사이즈를 일일이 지정하기 힘들다.
: 관련 옵션을 알거나 미리 설정해야 한다.
- 써드파티 툴에서의 ARRAY 사이즈가 디폴트로 지정되어 있다.
: 많은 데이터가 FETCH되는 경우 이를 정지할 수 없으므로 쿼리의 ROWNUM으로써 제어해야 한다.
- COMMAND 지향적인 툴이므로 모든 명령어를 숙지하고 있어야 한다.
: TOAD나 ORANGE와 같은 간편한 인터페이스를 제공하지 않는다.

● 장점
- PL/SQL, SQL 명령어를 구분 없이 사용할 수 있다.
- OS 명령어를 SQL*PLUS 명령어에 포함해서 사용할 수 있다.
: 윈도우의 경우 host, 유닉스의 경우에는 ‘!’를 붙여서 운영체제 명령어를 병행해 사용할 수 있다.
- 미리 작성된 스크립트(script)를 활용하면 더 효율적으로 배치 작업이나 DBA 업무를 수행할 수 있다.
: @script.sql 형태로 해당 스크립트를 실행할 수 있으며 스크립트 파일 내에 @@ 기호를 붙여서 스크립트 파일 안에서 다른 스크립트 파일을 호출할 수 있다.
- AUTOTRACE나 EXPLAIN PLAN 명령어, DBMS_ XPLAN을 통해 각종 통계정보와 실행계획 확인 및 옵티마이저의 동작 방법을 확인해볼 수 있는 강력한 기능을 제공한다. 모든 장점 중에서도 특히 이것이 DBA나 개발자가 반드시 SQL*PLUS에 친숙해야만 하는 가장 큰 이유가 아닐까 생각된다.

그럼 SQL*PLUS와 ISQLPLUS의 미래는 어떨까? 11g에서 그 명암이 갈리게 되었다. SQL*PLUS의 존재는 최소화되었다. 기존 윈도우 GUI 기반의 SQL*PLUS는 더 이상 지원되지 않는다. 그도 그럴 것이 뒤에서 살펴보겠지만 2006년도에 써드파티용 대체 툴로 발표된 SQL Developer 툴이 이 SQL*PLUS의 역할을 수행하면서 11g에서는 GUI용 SQL*PLUS와 자리바꿈을 하게 되었다. 다만 커맨드 유저 인터페이스는 계속해서 지원한다. 또한 ISQLPLUS는 10g에서의 EM 등장으로 인해 11g에서는 그 효용성이 감소함으로써 11g에서는 더 이상 지원되지 않는다.

GUI 기반 관리 툴

자바 콘솔 기반의 9i ENTERPISE MANAGER

Oracle이 GUI 기반의 유저 인터페이스를 Oracle 8i에서 본격적으로 지원한 GUI용 데이터베이스 관리 유저 인터페이스가 Oracle Enterprise Manager이다. 자바 콘솔 기반으로서 당시만 하더라도 구동하기가 매우 무겁다는 의견이 많았다. 따라서 사용자들에게 널리 사용되지 못하고 대신 쉽고 사용자 친화적인 인터페이스(user friendly interface)로 무장한 많은 데이터베이스 관리 툴들이 득세하기도 했다.

하지만 EM(Enterprise Manager) 그 자체만 본다면 상당히 매력적인 기능들이 많았다. 3-tier 기반으로 다수의 서버를 통합적으로 관리 가능했을 뿐 아니라 스케줄링 작업이나 각종 리포트 및 위험 상황이 오면 관리자에게 공지(Notification)하는 유용한 기능들을 제공했다.

<화면 4> Oracle 9i Enterprise Manager의 초기 화면

브라우저 기반의 10g Enterprise Manager

본격적인 그리드(Grid) 컴퓨팅을 표방하고 발표된 Oracle 10g EM에서 가장 크게 바뀐 점은 인터넷에서 사용하는 HTML 기반의 3-tier 아키텍처로 구성되었다는 점이다. 사용자는 브라우저를 통해 OEM에 접속해서 어디에서라도 데이터베이스를 관리할 수 있다. 모바일 디바이스에서도 인터넷에만 접속된다면 OEM에 접속할 수 있다. 뿐만 아니라 자바 콘솔 기반이 아니기 때문에 클라이언트에서는 소프트웨어 설치가 필요하지 않게 되었다.

이 OEM에는 두 가지 타입이 지원된다. 하나는 Oracle 설치 시에 디폴트로 설치되는 Oracle Enterprise Manager Data base Control과 복수의 인스턴스를 가진 RAC(Real Appli cation Cluster) 환경에서 설치할 수 있는 Oracle Enterprise Manager Grid Control을 설치할 수 있다.

<그림 1> HTML 기반의 3-tier 아키텍처

브라우저를 통해 OEM에 접속했다면 이 홈페이지 안에서 정보를 통합해 확인할 수 있고 데이터베이스의 상태를 즉시 확인해볼 수 있다. 직관적인 인터페이스를 제공하므로 마치 웹사이트를 서핑하는 것처럼 링크를 클릭해 확인함으로써 원하는 정보를 간편하고 쉽게 접근할 수 있다. 또한 전체적인 구성은 Drill down 메뉴 구성으로써 전체 문제 -> 세부 문제로 문제의 원인을 재빨리 찾을 수 있다.

<화면 5> 10g의 Enterprise Manager

모바일용 Enterprise Manager

EM의 서브 버전으로서 10g에서는 OEM의 모바일 버전인 EM2GO라는 버전을 제공하며, 모바일 환경에서 데이터베이스를 관리하고 장소에 구애받지 않고 데이터베이스를 관리할 수 있는 환경을 제공한다. EM2GO의 경우에는 OEM과 동일하게 OC4J 인스턴스를 공유함으로써 추가적인 리소스가 필요하지 않다.

<화면 6> PDA를 통한 EM2GO의 로그인

상용 툴의 대체자, SQL*DEVELOPER

2006년 오라클은 기존 사용 툴을 대체할 만한 그래픽 유저 인터페이스 IDE(Integrated Development Environment)용 툴인 SQL DEVELOPER를 발표한다. SQL*PLUS나 ISQL*PLUS 등 Oracle의 유저 인터페이스가 MS SQL의 ENTERPRISE CONSOLE이나 DB2의 DB CENTER에 비해 그 사용자 편의적인 유저 인터페이스가 부족했던 것은 사실이다.

MS SQL에서 Oracle과의 비교우위를 강조할 때 항상 자신 있게 내세웠던 것은 사용자가 쉽게 데이터를 관리하고 접근할 수 있는 쉬운 GUI 인터페이스였다. 따라서 이러한 점 때문에 Oracle을 지원하는 TOAD, Orange, PL/SQL Developer 등의 수많은 데이터베이스 관리용 써드파티 툴들이 득세할 수 있었다. 이에 대한 변화로 오라클은 SQL*DEVELOPER를 발표하게 되었고 11g에서는 GUI용 SQL*PLUS를 대체하게 되었다.

그렇다면 SQL*DEVELOPER가 기타 써드파티 툴과 비교할 때 어떤 특징을 지니고 있을까? 먼저 몇 가지 점을 꼽을 수 있다. 첫 번째, CLIENT 제품 설치 없이 JDK만 설치되어 있는 상태라면 JDBC를 통해 바로 접속할 수 있는 Direct Access를 지원한다. OCI, SQLNET을 통해 접속하기 위해서는 반드시 Oracle CLIENT에 포함된 이런 요소를 설치하고 또한 CLIENT 설치 후에 TNSNAME.ORA 파일에 해당 서비스를 등록해야 이 접속 정보를 참조하여 해당 서버에 접속할 수 있었다. 그러나 이러한 설정이나 설치 없이 SQL DEVELOPER에 포함된 JDBC 드라이버를 이용하면 DB 서버의 주소를 직접 기술해 바로 접속할 수 있다.

기존에도 SQLGATE라는 툴이 SQLNET 설치 없이 접속할 수 있는 방법을 지원했으나 SQL*DEVELOPER처럼 제품 설치 없이 압축만 풀고 해당 실행 파일만 가지고 있다면 툴을 실행할 수 있는 경우는 없었다. 그러나 이와 같이 어느 환경에서나 휴대용 저장장치에 파일을 가지고 다니면서 설치 없이 언제든 실행할 수 있다는 것은 큰 장점이 될 수 있다.

<화면 7> SQL DEVELOPER의 메인 화면

두 번째, 기존의 써드파티 툴은 RDBMS에 종속적인 터라 특정 DB용의 툴만 존재했다. 그러나 SQL*DEVELOPER에서는 이기종 DB 접속을 지원한다. Oracle뿐만 아니라 사이베이스, MS ACCESS, MySQL로의 접속을 지원한다. 따라서 이기종 DB를 관리하거나 이기종 DB로의 마이그레이션을 수행할 때 유용하다.

세 번째는 기존 써드파티 툴들이 일방적으로 정보를 제공하는 역할을 했다면 SQL*DEVELOPER에서는 사용자가 자신만의 리포트를 작성해서 모니터링에 사용할 수 있는 툴을 사용자가 편집 및 커스터마이징할 수 있는 편의 기능을 제공하고 있다. 대표적인 것이 User Define Report이다. 기존의 9I OEM에서 지원하던 사용자 작성 리포트 기능을 그대로 차용한 형식으로 사용자가 지정된 스크립트를 그리드나 차트 형태로 보여줄 수 있는 기능을 말한다.

많은 유용한 모니터링 스크립트를 가지고 있다고 하더라도 구슬도 꿰어야 서말이라는 말처럼 적시에 찾아내지 못한다면 무용지물일 것이다. 이 리포트에 해당되는 스크립트를 정리해서 정리된 폴더로 쌓아놓는다면 강력한 사용자정의 모니터링 툴이 될 수 있다.
아래는 Report 메뉴에서 사용자정의 리포트의 차트 스타일로 지정하고 실행한 예제이다.

● 예제 : 사용자정의 리포트 작성

① 먼저 다음의 Connection 탭을 선택하고 User Define Report를 선택한 후 오른쪽 마우스 버튼 클릭으로 Add Report를 추가한다.

<화면 9> Add Report 추가

② Report명과 해당되는 SQL문을 입력하고 STYLE은 CHART 형식으로 지정한다. 주의해야 할 것은 아래 3개의 measure 값이 항상 들어가야 하며 각각 값들은 그래프의 X축 값, 계열 값, Y축 값으로 나타낸다. 다시 말해 SID 컬럼이 X축, EVENT가 각각의 계열 값, SUM의 계산 값이 Y축의 값을 나타낸다는 것이다.

SQL> SELECT SID,EVENT,SUM(seconds_in_wait)
FROM V$SESSION
WHERE TYPE <> ‘BACKGROUND’
GROUP BY SID,EVENT

<화면 10> STYLE 지정(CHART 형식)

③<화면 10>의 하단 탭 중 Chart Detail을 선택해서 차트의 종류를 설정한다. 여기서는 BAR_VERT_STACK 형태(막대-수평 형태)의 차트를 선택해본다.

<화면 11> BAR_VERT_STACK 형태 선택

④<화면 12>와 같은 그래프가 완성되었다. REFRESH 간격 TIME을 5초로 설정하면 주기적으로 바뀌는 리포트 차트를 볼 수 있다.

<화면 12> SQL*DEVELOPER의 CHART 스타일 리포트

<표 1> 기타 RDBMS와의 비교

지금까지 간략하게나마 데이터베이스 유저 인터페이스의 발전 방향에 대해 살펴봤다. 설명한 내용을 통해 COMMAND, JAVA CLIENT 기반, 웹 기반으로 서서히 변화하고 있으며 좀 더 쉽고 편하면서 언제 어디서나 사용 가능한 방향으로 바뀌고 있음을 알 수 있었다. 앞으로의 유저 인터페이스 진행 방향을 꾸준히 지켜보는 것은 언제나 흥미로운 시도임이 분명하다.

필자소개

김도근 kilgw@naver.com｜OracleAce, OCM, MCDBA. ‘데이터베이스란 OS 위에 올라가 있는 애플리케이션에 불과하다’라는 생각으로 항상 데이터베이스를 생각하면서 데이터베이스를 공부하는 것을 낙으로 삼고 있다. 스터디 및 온라인, 오프라인 기고 등의 활동을 전개 중이다.

출처 : 한국 마이크로 소프트웨어 [2009년 6월호]

제공 : DB포탈사이트 DBguide.net

방 1)

Sql Plus 에서 아래 코드 붙여넣기 하신후 실행하시고

@C:\oracle\ora92\rdbms\admin\utlxplan.sql

toad-view-option-oracle-general 에서 가운데쯤 보시면 Explan Plan Table Name이라는 항목이 있습니다. 거기에 Plan_Table 이라고 기재하신후 사용하면 됩니다.

방 2)

토드에서 explain plan을 볼려면 아래의 스크립트를 실행 시킵니다.
C:\Program Files\Quest Software\TOAD\temps\toadprep.sql

toadprep.sql을 열어보면 toad유저를 생성할 때..
테이블스페이스를 지정하는데 데이타베이스에 존재하는 테이블 스페이스에 맞게 수정해야 합니다.

=============== 아래 부분은 제 오라클에 맞게 수정한 부분입니다. ===================

CREATE USER TOAD IDENTIFIED BY TOAD
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
QUOTA 0K ON SYSTEM;

============================================================================

toadprep.sql스크립트가 에러없이 수행이 되면 오라클에 toad라는 유저가 생성되고...

toad_plan_table, toad_plan_sql 테이블이 생성이 됩니다.

또한 시퀀스, 시노님, 권한부여, 함수가 에러없이 생성이 되면 설치가 다 끝난겁니다.

실행계획을 보는 방법은 우선 SQL을 실행할 유저로 토드를 접속합니다..

그리고 나서 sql 을 실행하고 나면 토드 아래에  explain plan과 autotrace를 보면 됩니다.

아래의 그림은 제 피시에서 실행한 예 입니다..아래의 explain plan이 보이지 ？을 경우에는 토드 메뉴에서 view->explain plan을 선택하거나 아래 그림 맨 오른쪽 세번째 있는 엠브런스차 아이콘을 클릭하면 됩니다..

실행 계획을 보는 방법은 Operation 컬럼에 나온 내용과 아래의 표를 참고해서  보시면 됩니다.

그리고 Explain Plan 오른쪽에   Auto Trace를 보면 Trace정보가 나옵니다..

AutoTrace관련 몇 가지를 설명하면 아래와 같습니다.

SQL Developer

문서화와 모니터링, 그리고 관리

Oracle SQL Developer 1.5의 새로운 기능 활용하기

Oracle SQL Developer 1.5에서는다양한기능들이새롭게선보였다. 처 음에는사소한것처럼보이는기능조차도사용자들의일상적인작업에상 당히유용한도움을줄수있다. 본기사에서는Oracle Database의객체와 스키마를 문서화하고 관리할 수 있도록 돕는 Oracle SQL Developer 1.5 기능에대해다루고있다. 여기에서는다음을배울수있다.

본 기사의 예제는 Oracle SQL Developer 1.5.1을 필요로 한다. Oracle SQL Developer 1.5의 프로덕션 릴리스를 설치했다면, 이를 열고 Help - > Check For Updates를이용하여Oracle SQL Developer 1.5.1로업데 이트한다. 아니면OTN에서 전체 Oracle SQL Developer 1.5.1 설치를 다운로드하여 새로운 빈 폴더로 압축을 해제한다(기존 Oracle SQL Developer 폴더에서압축을해제하면안된다).

설치가진행되는동안Oracle SQL Developer 1.2.x 또는1.5에서Oracle SQL Developer 1.5.1로 데이터베이스 커넥션 및 환경 설정을 마이그레 이션할수있다. 환경설정을마이그레이션하기를원치않는경우, 설치이 후 모든 이전 릴리스에서 데이터베이스 커넥션을 가져올 수 있다. 커넥션 을가져오려면,

1. Oracle SQL Developer의 이전 릴리스를 시작한다.

2. Connections Navigator에서 Connections을 선택한다.

3. 오른쪽 마우스 버튼을 클릭하고 Export Connections을 선택한다.

4. 적절한 위치로 브라우저하고 connections.xml과 같은 파일명을 입력한 다음, Save를 클릭 한다.

5. 이전 릴리스를 닫고 Oracle SQL Developer 1.5.1을 시작한다.

6. Connections Navigator에서 Connections을 선택한다.

7. 오른쪽 마우스 버튼을 클릭하고 Import Connections을 선택한다

8. 파일로 브라우저하고 Open을 클릭한 다음, OK를 클릭한다.

본 기사에서는 Oracle Database 인스턴스 내HR 및OE 예제 스키마에 액세스해야한다.

데이터베이스 문서 작성하기

여러분은 자신만의 리뷰 또는 다른 사람과의 공유를 위해HTML 포맷으 로스키마에대한문서를작성할수있다. 다음과같은단계에따라스키마 문서를작성하고볼수있다.

1. 현재 HR 스키마에 대한 커넥션이 없다면, 하나를 만들어 HR_ORCL라고 명명한다(커넥션을 만드는 데 대한 상세 정보는“http://www.oracle.com/technology/oramag/oracle/08 -sep/o58sql.html#next#next”에서 확인할 수 있으며“, http://www.oracle.com/technology /oramag/oracle/08-may/o38sql.html”에서도 설명하고 있다).

2. HR_ORCL 커넥션을 오른쪽 마우스 버튼을 누르고 Generate DB Doc를 선택한다.

3. 만들어진 파일을 위해 적당한 위치를 선택하거나 \working과 같은 폴더를 만든다. 만들어 진 파일을 다른 사람들과 공유할 계획이라면 공유 파일 서버 위치를 이용한다(이 파일을 이 동하거나 복사할 수 있다).

기본브라우저에index.html 파일이자동으로열리게된다. 자동으로열리 지않으면브라우저에서\working\index.html 파일을탐색해이를연다. HTML 문서내모든데이터베이스객체에대한상세내역을보려면, 상단 좌측에있는스키마패널의객체유형을선택한다. 그러면, 해당유형의모 든 객체 목록이 스키마 패널 아래의 패널에 나타난다. 여기에서 객체를 선 택해해당상세내역을중앙패널에표시한다. 예를들어EMPLOYEES 테 이블의 상세 내역을 디스플레이하고, 최상위 패널에서 Tables와그아래 패널의

EMPLOYEES를선택한다(<그림1> 참조).

보고서를 이용한 모니터링 및 관리

Oracle SQL Developer’s View -> Reports 기능으로 여러 표준 시스템 보고서를선택하여데이터베이스및스키마의상세내역을볼수있다. 또한 손쉽게액세스할수있도록Tools 메뉴와Connections Navigator에서각 각2개의보고서가제공된다. 이들모두SYSTEM 또는SYS와같은권한이 높은(privileged users) 사용자에게 적합하다(일부 제약 조건을 가진HR 와같은권한이낮은-nonprivileged user-사용자로서도실행할수있음). Sessions 보고서는현재의활성및비활성세션에대한상세내역을보여준 다. 다음과같은단계에따라Sessions 보고서를표시할수있다.

1. SYSTEM 사용자를 위해 SYSTEM_ORCL라는 새로운 커넥션을 만든다.

2. Tools -> Monitor Sessions을 선택한다.

3. Select Connection 대화 상자에서 SYSTEM_ORCL를 선택하고 OK를 클릭하여 보고서를 연다.

예를 들어 사용자의 세션이 분명하게 닫히지 않은 경우, 권한이 높은 사용 자는Sessions 보고서에서세션을종료할수있다(기본HR 스키마는세션 을 종료할 수 없다). 만약 실습을 진행하는 동안, HR 커넥션이 계속 활성 상태를유지하면, 금방만든Sessions 보고서내에서HR 세션을선택하여 오른쪽 마우스 버튼을 누르고 Kill Session을 선택한 다음, Apply를클릭 한다.

이 레벨에서 이용할 수 있는 다른 보고서는 Manage Database 보고서다. Connections Navigator 내SYSTEM_ORCL connection을 오른쪽 마 우스버튼으로누리고Manage Database를선택한다. 이보고서는여러분 의데이터베이스테이블스페이스에대한상세내역을표시한다. 이보고서를 SYS connection에서 실행하면, Oracle SQL Developer 내에서 데이 터베이스를닫고재시작할수있다(Shutdown 버튼은권한이낮은사용자 들이이용할수없다).

객체를 새 스키마로 복사하기

여러 스키마를 함께 실행하는 경우 종종 여러 스키마 간에 객체와 그 데이 터를 복제하는 작업이 이루어지게 된다. Oracle SQL Developer 내에서 이를 수행할 수 있는 많은 방법이 있으며 대표적으로 다음과 같은 방법을 들수있다.

다음 예제에서는4가지 메소드를 각각 이용하여 그 강점과 한계를 비교할 것이다.

1. 해당 스키마를 위해 OE_ORCL라는 이름의 새로운 데이터베이스 커넥션을 만든다.

2. OE_ORCL 커넥션을 선택하고 Tables 노드를 확장한다.

3. CATEGORIES 테이블을 오른쪽 마우스 버튼으로 누르고 Export DDL -> Save to Worksheet를 선택한다(<그림 2> 참조).

SQL Worksheet에나타난SQL은OE 스키마이름을포함하고있기때문 에 새로운 스키마 내에서 실행하는 데 적합하지 않다(이 SQL 구문은 DBMS_METADATA 패키지를이용하여수집되며일련의환경설정에 따라실행된다). OE 스키마이름없이SQL을생성하려면, 다음과같은단 계를따른다.

1. Tools -> Preferences를 선택하고 트리 내에서 Database 노드를 확장한 다음 ObjectViewer Parameters를 선택한다.

2. Show Storage 및 Show Schema 옵션의 확인란을 지우고 Show Constraints as Alter의 확인란을 선택한다.

3. OK를 클릭한다.

4. SQL Worksheet를 비우고 이전 단계를 반복한다: CATEGORIES 테이블을 오른쪽 버튼으 로 누르고 Export DDL -> Save to Worksheet를 선택한다. SQL Worksheet 내 SQL 코드 에는 더 이상 OE prefix가 포함되어 있지 않다.

이제 다음과 같은 단계를 거쳐 CATEGORIES 테이블과 그 데이터를 HR_ORCL 스키마로복사한다.

1. SQL Worksheet Connections 목록 내 HR_ORCL 커넥션을 선택하고 Run Script를 클릭 (또는 F5 누르기)하여 HR 스키마 내에 표시된 DDL이 실행되도록 한다.

2. HR_ORCL 노드를 확장하고 새로운 CATEGORIES 테이블을 검토한다. 여기에는 그 어떤 데 이터도 포함되지 않는 점을 유념해야 한다.

3. Connections Navigator에서 OE_ORCL 커넥션의 CATEGORIES 테이블을 오른쪽 마우스 버튼으로 누르고 Export Data -> Insert를 선택한다.

4. Export Data 대화 상자에서 클립보드로 출력물을 전송하고 Apply를 클릭한다.

5. HR_ORCL 사용자를 위해 새 SQL Worksheet를 열고 Ctrl-V를 눌러 클립보드의 내용을 붙 여 넣는다.

6. Run Script를 클릭(또는 F5 누름)하고 SQL을 실행한다.

7. Commit 버튼을 클릭(또는 F11 누름)하고 HR_ORCL 커넥션 내 CATEGORIES 테이블 데이 터를 검토한다.

앞의단계는1개의테이블과그데이터만을복사하기위한것이다. 1개객체 와그데이터를복사하는보다빠른방법은Copy context-menu 명령이다.

1. OE_ORCL 커넥션의 INVENTORIES 테이블을 오른쪽 마우스 버튼으로 누르고 Table -> Copy를 선택한다.

2. Copy 대화 상자에서 HR을 새 테이블 소유자로 선택하고 New Table Name에 INVENTORIES 를 입력한 다음 Include Data의 확인란을 선택한다.

3. Apply를 클릭한다.

4. 새 INVENTORIES 테이블을 보려면 HR_ORCL 커넥션의 Tables 노드의 새로 고침을 실행 한다.

여러테이블과그데이터를위한DDL 코드를만드려면Database Export마법사를이용해야한다. OE 스키마에서HR 스키마로하나의테이블집 합을복사하려면다음과같은단계를따른다.

1. Tools -> Database Export를 선택한다. 적절한 파일 위치에 브라우징하고 기본 파일명을 그대로 둔 다음, export.sql을 내보낸다(Tools -> Preferences를 선택하고 트리 내 Database 노드를 선택한 다음, 환경 설정 내에 익스포트를 저장하는 Select 기본 경로를 설정하는 방식으로 이 파일을 위한 기본 경로를 선택할 수도 있다).

2. Export Wizard에서 OE_ORCL 커넥션을 선택하고 Storage Schema 및 Show Schema 옵 션의 확인란을 지우도록 한다. Include Drop Statement and Automatically Include Dependent Objects의 확인란을 선택한다. Next를 클릭한다.

3. Types to Export 화면에서Toggle All의 확인란을 지우고 Tables and Data의 확인란을 선 택한다. Next를 클릭한다.

4. Specify Objects 화면의 OE 목록에서 Go를 클릭하여 선택할 테이블의 목록(list of tables to select)을 채운다. OE.ORDER_ITEMS 테이블만 우측 패널로 보낸다. Next를 클릭한다.

5. Specify Data 화면에서Go를 클릭하여 테이블의 목록을 채운다. OE.ORDER_ITEMS 테이 블만 우측 패널로 보내고 이를 선택해 해당 테이블을 하이라이트 한다. 다음의 빈 상자에, order_id < 2355을 입력하고 Apply Filter를 클릭한다(<그림 3> 참조). Next, 그 다음 Finish 를 클릭한다.

 

이제SQL Worksheet에나타난export.sql 스크립트는추가테이블을포 함하고 있다. 이는 여기에서 만들어지지 않은 제약 조건이 이들 테이블에 종속되기때문이다. 또한, 제한된데이터집합이반환된다.

SQL Worksheet의 커넥션 목록에서HR_ORCL을 선택하고 스크립트 를 실행한다. 변경을 커밋한 다음, Database Export 마법사가OE_ORCL 스키마에서 복사한 테이블을 보려면HR_ORCL 노드의 새로 고침을 실 행한다.

마지막으로 Oracle SQL Developer 내Database Copy를 이용하는 것은 객체를다른스키마로복사하는데매우효과적인방법이다. insert 문의스 크립트를작성하는대신, Database Copy가백그라운드의새테이블로데 이터를입력한다. Database Copy는또한, BLOBs와CLOBs를새스키마 로복사한다.

이비교실습을완료하려면, Database Copy를이용하여하나의객체집합 을HR 스키마로복사한다.

1. Tools -> Database Copy를 선택한다. OE_ORCL for Source Connection 및 HR_ORCL for Destination Connection를 선택한다. 여러분이 여기에서 선택할 수 있는 것은 새 객체 를 생성하고 기존 객체 내 데이터를 삭제(truncate)하여 새 데이터로 대체될 수 있도록 하 거나 객체를 드롭(및 대체)하는 것뿐이다.

2. Truncate Objects를 선택하고 Next를 클릭한다. Copy Summary는 모든 테이블이 삭제될 것이라는 것을 표시한다. 이는 여러분이 원하는 것이 아니기 때문에 Back을 클릭하고 Create Objects를 선택한 다음, Next를 클릭한다. 이를 통해 기존 객체가 드롭되거나 삭제 되지 않도록 보장하게 된다.

3. Finish를 클릭한다.

4. HR_ORCL 커넥션 내에 생성된 테이블과 데이터를 검토한다.

Database Export와 Database Copy는 2가지 점에서 큰 차이가 있다. Database Export는내보낼객체유형을선택할수있도록하며각카테고 리 내에서 개별 객체를 제한한다. 또한, Database Export를 이용하면 GRANT 문을 생성하고DROP 문을 포함하며INSERT 문을 생성하도 록결정할수있기때문에새로운또는기존스키마를위해반환할수있는 스크립트를원하는대로생성할수있는능력을갖게된다.

결론

본 기사에서는 Oracle SQL Developer 1.5에서 소개된 일부 기능을 검토 하고있다. 여러분은데이터베이스상세내역을공유하고세션을모니터링 및관리하며여러스키마간에데이터베스객체를복사하는새로운방법으 로생산성을높일수있다.

필자소개

Sue Harper는 오라클의 선임 제품 매니저(senior principal product manager)로서 런던에 거주하고 있다.그의 테크니컬 블로그, sueharper.blogspot.com에서는 Oracle SQL Developer 기능 및 특징에 대해 다루고 있다.

출처 : 한국 오라클

제공 : DB포탈사이트 DBguide.net