HighLight in your life~

AUTOTRACE(P.158~P.160)
1. AUTOTRACE 설치
 - AUTOTRACE 강점은 단순함이다. 일단 DBA가 AUTOTRACE를 설정하면 모두가 이를 이용할 수 있다. 필자는 다음과 같은 방법으로 AUTOTRACE를 설치한다.
 
 1)cd $ORACLE_HOME/rdbms/admin 명령을 수행한다.

 2) CREATE TABLE과 CREATE PUBLIC SYNONYM 권한을 가진 사람(예를 들면, DBA)으로 SQL*Plus에 로그인한다.

 3) (EXPLAIN PLAN에 관한 절의 앞쪽에 기술된 것처럼) PLAN_TABLE을 모두에게 공개한다.

 4) SQL*Plus를 빠져나와 cd $ORACLE_HOME/sqlplus/admin을 실행한다.

 5) SYSDBA로 SQL*Plus에 로그인한다.(sqlplus "/ as sysdba").

 6) SQL>@plustrace을 실행한다.

 7) SQL> grant plustrace to public을 실행한다.

 이와 같은 과정을 거치면 누구라도  SQL*Plus를 사용하여 추적할 수 있으며 누구라도 예외 없이 AUTOTRACE를 이용할 수 있다. 결국 개발자에게 자신들의 코드를 튜닝하지 않아도 될 구실을 주지 않게 된다.(그러나 원한다면 public을 일부 사용자로 대체할 수도 있다.)

2. AUTOTRACE 사용
 - AUTOTRACE는 모든 SQL DML문(예를 들면, INSERT, UPDATE, DELETE, SELECT, 그리고 MERGE)이 실행된 후에 보고서를 생성한다. SQL*Plus에서 다음과 같은 SET 명령을 이용하여 이 보고서를 제어할 수 있다.

 1) SET AUTOTRACE OFF
 - 쿼리는 정상적으로 실행되지만 AUTOTRACE 보고서가 생성되지 않는다. 이것이 기본이다.

 2) SET AUTOTRACE ON EXPLAIN
 - 쿼리가 정상적으로 실행되고 AUTOTRACE 보고서에는 최적화기의 실행 경로만 나타난다.

 3) SET AUTOTRACE ON STATISTICS
 - 쿼리가 정상적으로 수행되고 AUTOTRACE 보고서에는 SQL문의 실행 통계만 나타난다.

 4) SET AUTOTRACE ON
 - 쿼리가 수행되고 AUTOTRACE 보고서에는 최적화기의 실행 경로와 SQL문의 실행 통계가 모두 포함된다.

 5) SET AUTOTRACE TRACEONLY
 - SET AUTOTRACE ON과 유사하지만 사용자의 쿼리 출력(이 있다면 이것)을 인쇄하지 않는다. 이 기능은 굉장히 큰 결과 집합을 클라이언트에 반환하는 쿼리를 튜닝하는 데 유용하다. 1,000개의 행이 인쇄되어 화면에 흐르는 동안(일반적으로 실제 쿼리를 실행하는 시간보다 더 오래 걸린다.) 기다리는 대신 이 표시를 억제할 수 있다.

 6) SET AUTOTRACE TRACEONLY STATISTICS
 - SET AUTOTRACE TRACEONLY와 같지만 쿼리 계획을 표시하지 않으며 실행 통계만을 보여 준다.

 7) SET AUTOTRACE TRACEONLY EXPLAIN
 - SET AUTOTRACE TRACEONLY와 같지만 실행 통계의 표시를 생략하며 쿼리 계획만을 보여 준다. 게다가 SELECT문의 경우에는 실제로 이 쿼리를 실행하지 않고 쿼리를 파싱한 후 설명만 해 준다. 이 모드에서는 INSERT, UPDATE, DELETE, 그리고 MERGE문은 모두 실행되지만 SELECT문은 다르게 처리된다.

http://www.gurubee.net/display/DBSTUDY/AUTOTRACE
 
http://code.google.com/p/arctos/source/browse/arctos/DDL/admin/plustrace.sql?r=17383
 
http://support.dbworks.co.kr/?mid=oracle_tech_Mag&page=3&listStyle=gallery&document_srl=583
 
http://arctos.googlecode.com/svn-history/r17383/arctos/DDL/admin/plustrace.sql

오라클 성능 고도화 원리와 해법

p.324

 - 인덱스 구성전략만으로 튜닝이 되지 않을 때는 옵티마이저 힌트를 사용해야 하는데, 조건절이 동적으로 바뀐다면
힌트를 함부로 사용할 수 없다. 그때는 할 수 없이 Static SQL을 사용해야 하며, 인덱스 구성과 컬럼 분포, 자주 사용되는 액세스 유형들을 고려해 SQL을 통합하고 힌트를 기술할 수 있는 형태로 재작성해야만 한다.

 - 원칙은 Static SQL로 작성하는 것이며, 방법이 없거나 SQL이 너무 복잡할 때만 Dynamic SQL을 꺼내 들려고 노력해야 한다.

p.325
 (4)선택적 검색 조건에 사용할 수 있는 기법 성능 비교
A. OR 조건을 사용하는 경우
B. LIKE 연산자를 사용하는 경우
C. NVL 함수를 사용하는 경우
D. DECODE 함수를 사용하는 경우
E. UNION ALL을 사용하는 경우

*p.325 요약
1) not null 컬럼일 때는 nvl, decode를 사용하는 것이 편하다.
2) null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이라면 union all을 사용해 명시적으로 분기해야 한다.
3) 인덱스 액세스 조건으로 참여하지 않는 경우. 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면 (c: is null or col = :c) 또는 (c like :c || '%') 어떤 방식을 사용해도 무방하다.

ORA-27100 Shared Memory Realm Already Exist

'ORA-27100 Shared Memory Realm Already Exist' 오류가 나타나면 sqlplus로 DB에 접속이 안된다.
alter log를 확인해보면 'ORA-27100 Shared Memory Realm Already Exist' 오류가 발생했음을 알 수 있다. 위의 문제는 DB가 open된 상태(spfile을 사용하여 startup함) spfile을 삭제하고 다시 connecttion을 시도했을 때 발생한다. 이러한 경우 DB를 강제 종료하고 pfile로 부터 spfile을 만들고 다시 startup을 해주면 된다.

C:> sqlplus /nolog
SQL> conn sys/manager as sysdba
SQL> startup

--- startup 이후 shutdown 이전에
--- 누군가가 spfile을 삭제함
--- spfile 파일 삭제 이후에 conn try 시도하면
--- 'ORA-27100 Shared Memory Realm Already Exist' Error가 발생함

'SQL> shutdown immediate' 가 작동하지 않으면
'SQL> shutdown abort'로 종료

C:> sqlplus /nolog
SQL> conn sys/manager as sysdba
SQL> CREATE spfile FROM pfile='C:\oracle\product\10.2.3\orcl\database\initSID.ora'
SQL> startup

P.677 참조 내용
- DBLS(데이터베이스 ls 또는 Windows 사용자의 데이터베이스 dir)은 THOMAS KYTE의 핵심 스크립트 중의 하나라고 한다.

select object_type, object_name,
decode(status,'INVALID', '*', '') status,
decode(object_type,'TABLE', (select tablespace_name from user_tables where table_name = object_name),
'TABLE PARTITION', (select tablespace_name from user_tab_partitions where partition_name = subobject_name),
'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
'INDEX PARTITION', (select tablespace_name from user_ind_partitions where partition_name = subobject_name),
'LOB', (select tablespace_name
from user_segments
where segment_name = object_name), null)
tablespace_name from user_objects;

 
 오늘은 아침 일찍 일어 났다. 오전 6시에 일어나 7시에 집을 나섰다. 오라클 데이 세미나가 삼성동에서 열리기 때문이다. 8시 30분 부터 샌드위치랑 커피도 주고 점심에는 호텔에서 나온 도시락도 주었다. 그런데 이번 세미나의 요약은 상품 광고였던 것 같다.

 미국에서 일주일(?) 열렸던 진짜 오라클 데이의 여러 세션 중에서 가장 중요하다고 생각한 것들을 요약해서 발표한 세미나였다. 이번 오라클 데이의 핵심 키워드는 Engineered System, Big Data, Cloud였다. 그래서 조기 연설은 미국 본사 부사장인 인도계분이 하셨다. 우리 회사 인도 개발자들과는 많이(?) 상이한 발음을 하셨다. 미국식 발음으로 원활하고 최대한 쉽게 설명하신 것 같다. Big Data와 관련해서 4V이 있다고 한다. 

 Volume
 Velocity
 Variety
 Value
 
 천천히 강조할 부분은 강조하시고....ㅎㅎㅎㅎ 그 다음 세션에서 한국분이 하셨는데, 너무 빨리 말씀을 하셔서 주의가 분산되었고, Engineer 출신이 아니실 것으로 추측됐다. 

 Engineered System은 Oracle이 Sun을 인수하고 나온 것 같다. 아니면 인수하기 이전부터 준비를 했던 사항인 것 같다. 네트워크에서 말하는 OSI 7 Layer와 비슷하게 Oracle 7 Layer를 제시하였다.

 아래는 6개 Layer인데 중간에 뭐가 빠졌는데, 갑자기 생각이 안난다..ㅎㅎㅎㅎ

Application
Middle Ware
Operating System
Virtual Machine
Server
Storage

위 Layer를 기반으로 Full Package 상품을 소개하였다. 

SPARC 블라 블라 패키지를 제시했다. 우선 가로 6개 세로 4개 도합 24개의 노드로 구성된 서버 시스무리한 exadata가 있다. 24개의 노드 중에서 4개는 Redo log와 비슷한 역할을 하고 나머지 20개는 Datafile과 비슷한 역할을 하는 것같다. 정확하게 말하면 instance와 DB가 installation될 드라이브라고 생각해도 될 것 같다. exadata는 기본적으로 SOLARIS와 UNIX기반으로 작동하며, ASM으로 구성된다. exadata는 Storage, Server,Virtual Machine, Operation System를 모두 package로 이야기하는 것 같다.exalogic은 exadata와 WAS(weblogic) 사이 커뮤니케이션을 특유의 프로토콜을 진행한다. 여기서, exalogic은 Middle Ware Layer를 의미하는 것 같다. 그런데 여기서 중요한 사실은 bandwidth가 기존의 SAN을 사용했을 때 8G가 정도였지만 이제는 Infiniband라고 해서 40G까지 지원 가능하다고 한다. 내부 통신 속도가 약 5배 정도 증가하였다고 한다.

 모두 아시겠지만, Oracle이 과거 인수했던 Sun microsystem은 서버와 Solaris OS, Java 유명한 회사였다. Sun의 인수로 인하여 Oracle은 DB(S/W)와 서버 및 스토리지(H/W)를 함께 하는 아주 막강한 회사되었다. 현재도 DB 시장 점유율 1위인데, Apple의 iphone과 같이 H/W에 S/W 최적화가 가능하게 되었다. H/W와 S/W의 협업을 기반으로, 빠르면서 비싼 DB를 구축할 수 있는 기반을 마련한 것 같다. 한 마디로 사다리 걷어차기가 시작된 것 같다. 몇 년 안에 거의 독점시장을 구축할 것 같기도 하다.

 말이 Engineered System이지 아주 비싼 장비를 만든 것 같다. S/W와 H/W가 별도 구매가 힘들다... 별도 구매하면 최적의 성능이 나오지 않기 때문이다. H/W만 사서 구성을 못 한다. 그렇게 하면 기존 서버와 별만 차이점이 없을 것 같다. 어떻게 보면 면도기 판매회사와 비슷하게 변하고 있는 것 같다. 면도기 대는 그렇게 비싸지 않다. 하지만 면도날은 상당히 비싸다. 가끔 마트에 가보면 면도날 6개 들은 상품과 거기에 면도기 대도 추가된 것이 가격이 비슷한 경우를 종종 볼 수 있다. 결국 면도기 회사는 면도날을 판매함으로써 이득을 보는 것과 같다. 

 오라클도 H/W는 타사와 비슷하게 넘기고 S/W를 비싸게 판매하여 수익을 남길 것으로 예상이된다. 한마디로 DB계의 Apple이 될 것으로 예상이된다. 

 참, exadata는 기존 Oracle Database architecture에서 Redo log 부분을 아주 크게 확장시킴으로 인해 대용량 Batch 작업에 더욱 효율적으로 구성한 것 같다. Database에서 commit이 이루어 질 때, Datafile에 기록하는 것이 아니라, Redo log file에 기록이 이루어 질 때 commit이 발생한다고 한다. 추후에 내부 Batch 작업을 통해 Datafile에 Data가 Write된다고 한다.  

 Database Appliance는 SPARC 블라 블라 패키지의 약간 하위 버젼인 것 같다. 결국은 통합 서비스 제공이 목표인 것 같다. 

 마지막으로 DBA와 네트워크 엔지니어 사이의 벽이 점점 없어지는 것 같다. Oracle이 제시한 way의 DBA는 OS, Network, Application 등 모두를 아우르는 Enterprise Manager가 목표인 것 같다. 

P.S.
 - Cloud 시스템 구축의 필수요소(?)인 Enterprise Manager 12c는 그냥 이런 것이 있다 정도로 우선 충분할 것 같다. 그리고 이번 세미나는 11g Release 2를 기반으로 설명을 진행했다.
 - 현재 NT서버에 Datafile기반으로 작동하는 환경에서는 아직 SPARC 적용이 힘들다. 우선 OS를 Solaris 혹은 Unix로 갈아타고 Datafile 기반에서 ASM 기반으로 migration을 진행해야 가능할 것 같다.
 - 중견기업 이상의 환경에서 cost 대비 effective할 것 같다. 소기업들은 아직 ASM을 비롯하여 SPARC 환경으로 가기에는 어려운 것 같다. 너무 비싸고 소량의 데이타를 갖고 있기 때문이다. SPARC은 TERA급 이상의 데이터를 갖고 있는 Production에서 effecient할 것 같다.
 - 이번 세미나를 통해 가을판 Oracle 잡지가 어느 정도 구독 가능해 질 것 같다. ㅎㅎㅎㅎ

*DDL 추출하기

1) USING DBMS PACKAG

C:\> sqlplus /nolog

SQL> conn sys/manager as sysdba

SQL> desc all_db_links

SQL> select * from all_db_links;

SQL>

set long 2000000000

SQL> SELECT dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM dual;
SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','TBS_SCOTT') FROM dual;

SQL> SELECT dbms_metadata.get_ddl('DB_LINK','LINK_SCOTT','PUBLIC') FROM dual;
SQL> SELECT dbms_metadata.get_ddl('DB_LINK','LINK_SCOTT','PUBLIC') DDL_QUERY FROM dual;

2) USING export (rows=no 포함 시에 DDL만 export함)

가) 전체 export
C:\> exp system/manager file=full_dp.dmp log=full_dp.log feedback=10000 buffer=102400000 rows=no

나) scott_d_p만 export
C:\> exp scott_d_p/scott_d_p file=dp_ddl.dmp log=dp_ddl.log owner=scott_d_p feedback=10000 buffer=102400000 rows=no


http://www.koug.net/xe/3569

http://www.oracleclub.com/lecture/1560

http://www.koug.net/xe/3569

http://lazyjin.tistory.com/16

http://surachartopun.com/2008/02/using-dbmsmetadatagetddl.html

[출처] [펌] NT상에 오라클 8i 제거.|작성자 말의씨

 

http://blog.naver.com/PostView.nhn?blogId=ilmare8619&logNo=70009922447

네. 오라클을 설치를 해 보셨으니 삭제하는 방법도 아셔야지요..오라클 삭제의 방법은 여러가지 이겠지만..저는 확실하고 완벽하게 제거하는 방법을 택했습니다.그럼 설명을 드리지요.

 

먼저 오라클 서비스를 멈추어야 합니다.NT4의 경우는 제어판 -> 서비스를 보시면 되며윈도2000 서버의 경우는 관리도구 -> 서비스 를 보시면 됩니다.

그럼 이렇게 서비스가 보통 되어 있으실 겁니다.  - 현재 윈도 2000 서버 환경입니다.이것덜중...  시작됨 이라고 되어 있는 녀석들을 모두 멈추시구요..

 

그 후 regedit으로 레지스트리를 편집하시면 됩니다.

 

regedit을 실행 시키시고...

 

HKEY_LOCAL_MACHINE->SYSTEM->CurrentControlSet->Services

부분을 보시면 대략 이런 모습으로 되어 있을 겁니다.

많은 녀석덜중 위에 나온 요녀석들..즉.. Oracle 로 시작되는 녀석을 모두 삭제 하시고..

 

다음은

HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE

부분을 보시면..

 

요렇게 생기셨을 겁니다. 요녀석을 살짝 지워버리면 된답니다.그런후 서버를 재부팅 하시고..

윈도 탐색기를 이용해 물리적으로 존재하던 오라클 디렉토리를 지웁니다.

C:\ORACLE 또는 C:\app

C:\Program Files\Oracle

시작 => 프로그램(P) => Oracle - OraClient11g_home1

휴지통 비우시고 reboot하시면 모두 삭제된 상태입니다.

 

 

 

 

중략......중략.... 중략....
중략......중략.... 중략....
중략......중략.... 중략....

위의 빨간 부분만 따로 빼내어, recovery_control_file.sql 을 만들어 돌려 준다.
SQL>@recovery_control_file

P.S. temprory tablespace가 누락 될 수 있니 주의해야 한다. 간혹 control file backup할때 누락되기도 한다. 그럴 경우 위의 마지막 3문장, ALTER TABLESPACE TEMP ADD TEMPFILE 블라 블라를 넣어주어야 한다.

- db_recovery_file_dest_size가 default 값인 2G 일 때 발생하는 Error

기본적인 oracle 환경은 아래와 같다.

C:\>sqlplus /nolog

SQL> conn /as sysdba

SQL>startup

 

 

 

SQL> show parameter p_d 혹은 SQL> show parameter recovery

 

 

 

아래와 같이 select * from v$recovery_file_dest; 를 해보면 default값인 2G가 잡혀있는 것을 알 수 있다. 유사한 구문은 select name, floor(space_limit/1024/1024) "Size MB", ceil(space_used/1024/1024) "Used MB" from v$recovery_file_dest order by name; 이다.

 

 

 

아래의 로그를 분석해보면 다음과 같다.

두번째 online redo log buffer의 11번째 sequence에서 archive를 할 수 없다는 것을 알 수 있다.

 

 

아래의 그림을 보면 recovery_file_dest의 size가 거의 fulll임을 알 수 있다.

 

 

recovery_file_dest의 size가 설정 값의 한계에 다달아 문제가 발생했음을 알 수 있다.

 

아래의 명령문을 쓰면 mount 단계인 것을 알 수있다. SQL> startup open 을 해도 open이 되지 않음을 알 수 있다.

SQL> select status from v$instance;

 

두번째 online redo log buffer의 11번째 sequence에서 archive를 할 수 없어서 그 이후 데이터는 모두 loss가 났음을 알 수 있다. 두번째 redo log파일이 손상되었기 때문에 복구 작업을 진행해야 한다.

 

cancel 기반 recovery를 진행하겠다. 기본적으로 SQL> recover database until cancel; 은 mount 단계에서 진행한다. 현재 mount 단계이기 때문에 복구를 바로 진행하도록 하겠다.

1.     존재하는 archive 파일을 적용한다.

SQL> recover database until cancel;

 

2.     Control files, Redo log files, Data files의 SCN(System Change Number)를 동일하게 맞추어 준다.

SQL> alter database open resetlogs;

 

3.     SQL> archive log list 를 통하여 초기화된 log sequence 번호를 확인해 보자.

 

4.     SQL> alter system switch logfile; 을 통해 switch log가 되는지 확인해 본다.

5.     2G로 잡혀있는 DB_RECOVERY_FILE_DEST_SIZE를 100G로 아주 아주 넉넉하게 변경해 보자.

SQL> shutdown immediate

SQL> startup mount

SQL> show parameter recovery

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 100G;

SQL> startup open

 

 

 

 

P.S. 참고

ORA-16038과 관련, Redo log file이 올라오지 않을 때
 
http://majesty76.tistory.com/54