Secret

이런 기능이 있다는 것은 오래 전부터 알고 있었는데,

이해도 잘 안되고 사용할 일도 없을 것 같아서 잊고 지내다가,

최근에 읽고 있는 책을 통해 필요성을 인식하게 되었습니다.

읽고 있는 책이 Oracle9i를 기준으로 한 책이라

original export/import utility를 사용한 방법에 대해서 설명되어 있는데,

여기서는 Oracle10g부터 제공되기 시작한 pump export/import utility를 사용한 방법도

같이 소개해 보고자 합니다.

Oracle10g이상을 사용하고 있음에도 불구하고,

여전히 original export/import utility를 단지 익숙하다는 이유로 사용하고 계신 분이 많을텐데,

최근에 등장한 Oracle11gR2에도 original export/import utility가 지원되지만,

언젠가는(3,4년 후 정도 될 것 같습니다) original utility는

pump utility에 의해 완전히 대체될 예정이므로,

하루라도 빨리 pump export/import utility를 익혀두는 편이 좋습니다.

우선 transportable tablespace기능의 장단점에 대해 간단히 소개하고자 합니다.

일반적으로 tablespace전체를 <A> database에서 <B> database로 복사하는 경우,

<A>의 해당 tablespace안에 담겨있는 schemas를 export한 후,

<B>의 datasbase에 import하는 방식을 사용합니다.

다들 아시다시피 import는 export에 비해 훨씬 많은 시간(5배정도)이 걸립니다.

만약 이 tablespace가 20~30GB정도 되는 대용량 tablespace인 경우,

import에 많은 시간이 걸리게 될 것입니다.

transportable tablespace기능을 사용하게 되면,

tablespace에 들어있는 data를 넣다/뺐다(export/import) 할 필요가 없고,

오직 필요한 것은 meta data만을 export/import하면 되기 때문에,

훨씬 시간을 절약할 수 있습니다.

대신, export/import을 수행할 경우, data가 깨끗하게 들어가게 되므로,

tablespace내에서 발생하고 있던 기존의 fragmentation을 해소시킬 수 있지만,

transportable tablespace기능을 사용할 경우, fragmentation은 그대로 남아 있게 됩니다.

이제 구체적인 작업을 설명하겠습니다.

1.  이동시키고자 하는 tablespace(target database)가 transportable한지

     체크합니다.

3.  <A> database상에서 target tablespace의 meta data만을 export합니다.

     (1)  original export를 사용하는 경우

           ⇒ exp system/**** transport_tablespace=y tablespaces=users file=users.dmp ...

     (2)  pump export를 사용하는 경우

           ⇒ expdp system/**** transport_tablespaces=users dumpfile=users.dmp ...

     ※  original export의 경우에는 current directory에 dump file이 생성되지만, pump export의

          경우에는 지정한 directory object가 가리키는 directory에 생성됩니다.

          그러므로 이 directory object는 실행 전에 생성해 놓아야 합니다. 위의 경우에는

          directory object를 지정하지 않았는데, 그런 경우에는 "data_pump_dir"이라는

          directory object가 default로 사용됩니다.

4.  target tablespace에 해당하는 datafile을 <A> host에서 <B> host로

     복사합니다.

     저의 경우, <B> host상에서 "wget" command를 사용해서 ftp protocol를 사용해서 파일을

     전송받았는데, tablespace의 datafile의 크기가 23GB나 되는 관계로 19분15초라는 긴 시간이

     걸렸습니다. 참고로, Windows의 경우, "ocopy"를 사용하도록 합니다.

6. <B> database상에 target tablespace에 속한 schemas 및 해당 schemas와

    관련된 user-defined roles를 생성합니다.

    불행히도 original utility를 사용해도 pump utility를 사용해도

    schemas 및 user-defined roles를 import시에 자동으로 생성해 주지 않습니다.

    왜냐하면 이 정보들은 target tablespace에 종속된 정보가 아니기 때문입니다.

    (1)  original export를 사용하는 경우

          <A> database상에서 dba_users를 select하여, default_tablespace가 target database로

          된 schemas를 파악하고, 해당 schema에 접속하여 session_privs 및 session_roles를

          select 하여, <B> database상에 생성해 준다.

          schemas를 생성하기 전에 target tablespace가 존재하지 않으면 default tablespace로

          설정할 수 없기 때문에 임시로 taget tablespace를 생성한다.

    (2)  pump export를 사용하는 경우

          위와 동일한 방법을 사용해도 전혀 상관없지만, pump import의 "sqlfile"이란 option을

          사용하여 schema생성 및 privilege설정과 관련된 meta data를 SQL*Plus command 형식으로

          얻을 수 있습니다.

          1)   우선 위의 예와 동일한 방법으로 해당 tablespace에 종속된 schemas를 추출합니다.

편하게 보기위해서 제 블로그로 퍼왔습니다. 출저 남길게요~

Export와 Import 툴을 사용할 수 있게 되어 있지 않다면, catexp.sql과 catalog.sql 을 실행합니다. 이것은 한번만 실행해야 합니다. 이 스크립트가 수행하는 작업은 다음과 같습니다.

- 데이터 딕셔너리에 exp와 imp에서 필요한 뷰를 생성합니다.

- EXP_FULL_DATABASE 역할을 생성합니다.

- EXP_FULL_DATABASE와 IMP_FULL_DATABASE역할에 모든 필요한 권한을 할당합니다.

- EXP_FULL_DATABASE와 IMP_FULL_DATABASE를 DBA 역할에 할당합니다.

- 설치된 catexp.sql의 버전을 기록합니다.

exp했을때 데이터를 저장할 충분한 디스크 공간이 없다면 오류가 발생할 것입니다. exp 될 데이터의 양이 얼마나 될지 다음 쿼리로 대충 예측할 수 있습니다. 여기서 계산된 양에 LOG 데이터와 VARRAY 데이터의 양은 포함되지 않습니다.

SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

Export와 Import 툴을 사용하기 위한 권한

사용자에게 CREATE SESSION 권한이 있어야 합니다. 이 권한은 CONNECT 롤에 포함되어 있습니다. 다른 사용자가 소유한 테이블을 exp 하려면 EXP_FULL_DATABASE 역할이 사용 가능해야 합니다. 이 역할은 모든 데이터베이스 관리자(DBA)에게 부여되어 있습니다.

Export와 Import 명령의 실행

명령의 실행을 위해서는 필요한 파라미터를 인자로 주어 실행을 합니다. 파라미터를 주는 방법은 다음과 같습니다.

- 명령행에 인자를 붙여서 실행합니다.

- 파라미터 파일을 만들어서 파일을 인자로 실행합니다.

- 대화식 모드로 실행합니다.

Export 파라미터

- BUFFER : row 패치에 사용되는 버퍼의 크기로 기본값은 운영체제에 의존적입니다. 이 파라미터는 패치된 배열내에 들어갈 최대 행의 수를 결정합니다. 다음식으로 버퍼 사이즈를 계산할 수 있습니다.

buffer_size = rows_in_array * maximum_row_size

이 값이 0 이면 한번에 하나의 행을 페치 합니다. 계산 예를 보겠습니다. 다음과 같은 테이블이 있을때,

CREATE TABLE sample (name varchar(30), weight number);

name 컬럼의 크기는 30 + 2( 크기 + 지시자) 입니다. weight 클럼의 크기는 22 + 2 (크기 + 지시자) 입니다. 그러므로 한번에 100 행을 페치한다면, BUFFER_SIZE는 (30+2+22+2) * 100 = 5600이 되겠습니다.

보통은 이 파라미터를 기본값으로 사용을 합니다. 하지만 데이터량이 많을때 적절한 사이즈를 지정해주면 덤프를 빠르게 수행할 수 있습니다.

- COMPRESS : 이 파라미터는 테이블 데이터를 위한 초기 확장(initial extent)을 어떻게 관리할지를 지정합니다. 기본값을 y 입니다. 이 파라미터를 데이터를 압축하는 파라미터가 아닙니다. Oracle은 초기 extent가 꽉차게 되면 다음 extent를 next extent = next * (1 + PCTINCREASE / 100) 로 계산하여 확장합니다. PCTINCREASE가 파라미터에 의해 다음 확장은 초기보다 더 커지게 됩니다. 이 상황 그대로 export 한 후, 다른 컴퓨터에서 import할 때 충분한 디스크 용량이 없으면 extent 도중에 "Unable to extent next extent" 오류를 만날 수 있습니다. COMPRESS=y 를 사용하면 현재 전체 extent 크기를 하나의 초기 extent로 만들어 줍니다. COMPRESS=n을 사용하면 현재 테이블과 동일한 extent 값을 만들어 줍니다.

- CONSISTENT : SET TRANSACTION READ ONLY 문장을 사용하여 exp가 실행되는 동안 일관성이 유지되도록 합니다. 기본값은 n 입니다.

- CONSTRINTS : 테이블 제약사항을 내보낼지 지정합니다. 기본값은 y 입니다.

- DIRECT : SQL 문장 처리 층을 거치지 않고 export합니다. 기본값은 n 입니다.

- FEEDBACK : 지정된 행수를 내보낼때마다 . 을 출력합니다. 기본값은 0입니다.

- FILE : 덤프 파일명을 지정합니다. 기본값은 expdat.dmp 입니다.

- FILESIZE : 덤프 파일 크기를 지정합니다. 지정된 크기가 넘으면 다음 파일에 저장되고, 더이상 지정된 파일이 없으면 새파일 이름을 입력하라는 메세지가 나옵니다. 지정하지 않으면 하나의 파일이 덤프 됩니다.

exp scott/tiger FILE=dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048

- FLASHBACK_SCN : SCN(System Change Number)를 사용하여 flashback를 활성화 하고, export 동작은 지정된 scn으로 일관된 데이터를 수행합니다. 기본값은 없습니다.(none)

※ 참고 

FLASH BACK 은 commit 해서 rollback 할 수 없는 데이터에 대해서 조회하거나 복구할 수 있는 기능 입니다. SCN(System Change Number) 타임스탬프를 사용하여 복구 지점을 선택할 수 있습니다.

- FLASHBACK_TIME : 타임스탬프를 사용하여 flashback 지점을 지정하여 export 합니다. 기본값은 none 입니다.

- FULL : 전체 데이터베이스를 export 합니다. 전체 데이터베이스를 복제할 수 있는 좋은 방법입니다. 하지만 오류를 방지하기 위해 주의가 필요합니다. 기본값은 n 입니다.

모든 테이블 공간과 데이터 파일 목록을 만들어 두면 좋습니다. 모든 테이블 스페이스가 작성된 것이 확실하지 않으면 둘 이상의 데이터베이스가 있는 시스템에서 전체 import 를 수행하지 마십시오. 전체 가져오기는 export한 데이터베이스와 동일한 데이터 파일명을 사용하여 정의되지 않은 모든 테이블 공간을 작성합니다. 이로 인해 충돌이 발생할 수 있습니다.

- GRANTS : 권한 정보를 export 할지 지정합니다. 사용자 모드에서는 사용자가 부여한 권한 정보만 export 됩니다. 기본값은 y 입니다.

- HELP : 도움말을 출력합니다. 기본값은 none 입니다.

- INDEXES : 인덱스를 내보낼지 지정합니다. 기본값은 y 입니다.

- LOG : 메세지를 로그 파일에 기록합니다.  (예, export.log)

- OBJEXT_CONSISTENT : 개개의 객체를 read only 트랜잭션으로 export 합니다. CONSiSTENT 옵션은 하나의 read only 트랜잭션만 있습니다. 기본값은 n 입니다.

- OWNER : 사용자 모드를 지정합니다. 지정된 사용자만 백업됩니다. 기본값은 none 입니다.

- PARFILE : 파라미터 파일을 지정합니다.

- QUERY : 테이블 데이터중 조건에 맞는 일부말 백업할 수 있습니다.

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

exp scott/tiger TABLES=emp,bonus QUERY=\"WHERE job=\'SALESMAN\' and sal\<1600\"

- RECORDLENGTH : 파일 레코드의 길이를 지정합니다. 기본값은 운영체제에 따라 다릅니다.

- RESUMABLE : 공간 할당에 resumable 을 사용할 것인지 표시합니다. y 로 설정하게되면  RESUMABLE_NAME과 RESUMABLE_TIMEOUT을 지정해야 합니다. 기본 값은 n 입니다.

※ 참고 

resumable 은 데이터 입력작업중 공간이 부족할 경우 rollback 하지 않고 공간이

확보될때까지 기다리게 하는 옵션 입니다. 데이터 입력 작업에서 resumable 로 작업하면

alter session enable resumable;

오류로 멈추지 않고 데이터 파일을 추가 등의 작업으로 공간을 확보하고 계속 진행할 수 있습니다.

- RESUMABLE_NAME : resume 가능한 문장을 식별합니다. 기본값 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID' 입니다.

- RESUMABLE_TIMEOUT : 재개 가능한 시간을 지정합니다. 지정된 시간안에 오류가 수정되지 않으면 실행이 종료됩니다. 기본값은 7200초 (2 시간)입니다.

- ROWS : 테이블 데이터의 행을 export 할지 지정합니다. 기본값은 y 입니다.

- STATISTICS : import 할때 옵티마이저 통계를 생성할 방식을 지정합니다. ESTIMATE, COMPUTE, NONE 중에 하나를 지정합니다. 기본값은 ESTIMATE 입니다.

- TABLES : export할 테이블을 지정할 수 있습니다. 기본값은 none 입니다.

- TABLESPACES : 지정된 테이블 스페이스의 모든 테이블이 export 됩니다. 기본값은 none 입니다.

- TRANSPORT_TABLESPACE : Transportable Tablespace메타데이터를 export합니다. 기본값은 n 입니다.

※ Transportable Tablespace란 서로 다른 DB간에 Tablespace단위로 Datafile을 이동해서 바로 사용할 수 있는 기능입니다. exp로 메타데이터만 옮기면 데이터파일은 복사해 넣으면 됩니다.

- TRIGGERS : 트리거를 export합니다. 기본값은 y 입니다.

- USERID (username/password) : export를 사용할 사용자의 아이디와 비밀번호를 입력합니다. 비밀번호를 빼면 프롬프트를 보여줍니다.

Import 파라미터

- BUFFER : row 패치에 사용되는 버퍼의 크기로 기본값은 운영체제에 의존적입니다. export 파라미터 참조

- COMMIT : y로 지정하면 각 배열을 입력한 후에 commit 됩니다. 기본값은 n 으로 테이블 단위로 commit 합니다.

- COMPILE : 패키지, 프로시저, 함수를 컴파일할지 지정합니다. 기본값은 y 입니다.

- CONSTRAINTS : 제약조건을 가져올지 지정합니다. 기본값은 y 입니다.

- DATAFILES : Transportable Tablespace를 사용할 때 데이터 파일을 지정합니다.

- DESTORY : 기존 데이터파일을 재사용할지를 지정합니다. y를 지정하면 기존의 정보로 데이터파일을 생성합니다. 기본값은 n 입니다.

- FEEDBACK : 지정된 행 만큼 입력시 마다 . 표시 합니다.

- FILE : 덤프 파일 을 지정합니다. 기본값은 expdata.dmp 입니다.

- FILESIZE : 다중 export 파일을 지정합니다. 운영체제에 의존적입니다.

- FROMUSER : import 할 스키마를 지정합니다. 기본값을 none 입니다.

- FULL : 덤프 파일 전체를 import 합니다. 기본값은 y 입니다.

- GRANTS : 권한을 import 합니다. 기본값은 y 입니다.

- HELP : 도움말을 보여줍니다.

- IGNORE : 오류를 보여줄지 말지 지정합니다. n은 오류를 표시하고, y는 무시합니다. 기본값은 n입니다.

- INDEXES : 인덱스를 가져올지 여부를 지정합니다. 기본값은 y입니다.

- INDEXFILE : 지정되면 인덱스 생성문이 지정된 파일에 저장되고 실제 인덱스가 생성되지는 않습니다.

- LOG : 로고 메세지를 저장할 파일을 지정합니다.

- PARFILE : 파라미터 파일을 지정합니다.

- RECORDLENGTH : 파일 레코드의 길이를 지정합니다. 기본값은 운영체제에 의존적입니다.

- RESUMABLE : 공간 할당을 재시작 가능한지 지정합니다. 기본값은 n 입니다.

- RESUMABLE_NAME : resume 가능한 문장을 식별합니다.

- RESUMAGLE_TIMEOUT : 재개 가능한 시간을 지정합니다.

- ROWS : 테이블 데이터를 import 합니다. 기본값은 y 입니다. 

- SHOW : 덤프파일 내용을 보여주고, 실제 import하지는 않습니다. 기본값은 n 입니다.

- SKIP_UNUSABLE_INDEXES : 색인 사용 불가능 상태 (시스템 또는 사용자에 의해)로 설정된 색인을 건너 뜁니다. 이전에 색인 사용 불가능으로 설정되지 않은 다른 색인은 행이 삽입 될 때 계속 갱신됩니다.

- STATISTICS : import 시 옵티마이저 통계에 사용될 모드를 지정합니다.(ALWAYS, NONE, SAFE, RECALCULATE) 기본값은 ALWAYS 입니다.

- STREAMS_CONFIGUREATION : 스트림 메타데이터를 import 할지 지정합니다.(복제 관련) 기본값은 n입니다.

- STREAMS_INSTANTIATION : 스트림 인스턴스 메타데이터를 import 할지 지정합니다.(복제 관련) 기본값은 n입니다.

- TABLES : 지정된 테이블을 import 합니다.

- TABLSPACES : 지정된 테이블 스페이스만 import 합니다.

- TOUSER : import할 target 유저를 지정합니다. 유저는 미리 존재해야 합니다.

- TRANSPORT_TABLESPACE : Transportable tablespace 메타메이터를 import 합니다.

- TTS_OWNERS : Transportable tablespace를 소요한 사용자를 지정합니다.

- USERID(username/password) : import를 수행할 사용자의 아이디와 비밀번호를 지정합니다.

※참조 : https://docs.oracle.com/cd/B28359_01/server.111/b28319/exp_imp.htm#CEGFIAGE

출처: http://pentode.tistory.com/152 [쉬고 싶은 개발자]

-- 성능 개선 대상 sql list 추출 스크립트  (튜닝의시작 튜닝방법론에서..)

-- IO/CPU TOP SQL 관련 성능 개선 대상선정
select rownum cnt,
         t1.*
from (
         select t1.parsing_schema_name,
                  t1.module,
                  t1.sql_id,
                  t1.hash_value,
                  t1.substr_sqltext,
                  t1.executions,
                  t1.buffer_gets,
                  t1.disk_reads,
                  t1.rows_processed,
                  t1.lio,
                  t1.elapsed_sec,
                  t1.cpu_sec,
                  round(t1.cpu_time/t1.cpu_time_total*100,1) ratio_cpu,
                  round(t1.elapsed_time/elapsed_time_total*100,1) ratio_elapsed
        from (
                 select parsing_schema_name,
                          module,
                          sql_id,
                          hash_value,
                          substr(sql_text,1,100) substr_sqltext,
                          executions,
                          buffer_gets,
                          disk_reads,
                          rows_processed,
                          cpu_time,
                          elapsed_time,
                          round(buffer_gets/decode(executions,0,1,executions),1) lio,
                          round(elapsed_time/decode(executions,0,1,executions)/1000000,1) elapsed_sec,
                          round(cpu_time/decode(executions,0,1,executions)/1000000,1) cpu_sec,
                          sum(cpu_time) over() cpu_time_total,
                          sum(elapsed_time) over() elapsed_time_total
                  from v$sqlarea s
                  ) t1
        where t1.executions >0
        and    t1.parsing_schema_name not in ('SYS','SYSTEM','SYSMAN')
        order by ratio_cpu DESC       
        ) t1
where rownum <=100

-- DBA_HIST_SQLSTAT  활용 스크립트
select sql_id,
         schema_name,
         module,
         ela_ratio,
         ela_tot,
         cpu_ratio,
         cpu_tot,
         exec_ratio,
         exec_tot,
         lio_ratio,
         lio_tot,
         pio_ratio,
         pio_tot,
         rows_ratio,
         rows_tot
from (
         select sql_id,
                  parsing_schema_name schema_name,
                  nvl(substr(b.module,1,15),'-') module,
                  round(ratio_to_report(sum(b.elapsed_time_delta)) over()*100,1) as ela_ratio,
                  round(sum(b.elapsed_time_delta)/1000000,0) as ela_tot,
                  round(ratio_to_report(sum(b.cpu_time_delta))over()*100,1) as cpu_ratio,
                  round(sum(b.cpu_time_delta)/1000000,0) as cpu_tot,
                  round(ratio_to_report(sum(b.executions_delta))over()*100,1) as exec_ratio,
                  sum(b.executions_delta) as exec_tot,
                  round(ratio_to_report(sum(b.buffer_gets_delta))over()*100,1) as lio_ratio,
                  sum(b.buffer_gets_delta) as lio_tot,
                  round(ratio_to_report(sum(b.disk_reads_delta))over()*100,1) as pio_ratio,
                  sum(b.disk_reads_delta) as pio_tot,
                  round(ratio_to_report(sum(b.rows_processed_delta))over()*100,1) as rows_ratio,
                  sum(b.rows_processed_delta)as rows_tot
         from dba_hist_snapshot a,
                 dba_hist_sqlstat b
         where a.instance_number=1
         and     a.begin_interval_time >= to_date(:b1,'YYYY-MM-DD')
         and     a.end_interval_time <= to_date(:b2, 'YYYY-MM-DD') +0.99999
         and     a.dbid=b.dbid
         and     b.parsing_schema_name not in ('SYS','SYSTEM','SYSMAN')
         and     a.instance_number = b.instance_number
         and     a.snap_id=b.snap_id
         group by b.sql_id,
                      b.parsing_schema_name,
                      b.module
         order by cpu_ratio desc
         )
where rownum <=10;        
        
-- full table 관련 성능 개선 대상 선정

select rownum cnt,
         t1.*
from (                       
         select t1.parsing_schema_name,
                  t1.module,
                  t1.sql_id,
                  t1.hash_value,
                  t1.substr_sqltext,
                  t1.executions,
                  t1.buffer_gets,
                  t1.disk_reads,
                  t1.rows_processed,
                  t1.lio,
                  t1.elapsed_sec,
                  t1.cpu_sec,
                  round(t1.cpu_time/t1.cpu_time_total*100,1) ratio_cpu,
                  round(t1.elapsed_time/elapsed_time_total*100,1) ratio_elapsed
        from (
                 select s.parsing_schema_name,
                          s.module,
                          s.sql_id,
                          s.hash_value,
                          s.address,
                          substr(s.sql_text,1,100) substr_sqltext,
                          s.executions,
                          s.buffer_gets,
                          s.disk_reads,
                          s.rows_processed,
                          s.cpu_time,
                          s.elapsed_time,
                          round(s.buffer_gets/decode(s.executions,0,1,executions),1) lio,
                          round(s.elapsed_time/decode(s.executions,0,1,executions)/1000000,1) elapsed_sec,
                          round(s.cpu_time/decode(s.executions,0,1,executions)/1000000,1) cpu_sec,
                          sum(s.cpu_time) over() cpu_time_total,
                          sum(s.elapsed_time) over() elapsed_time_total
               from v$sqlarea s
               ) t1,
               (
                select distinct hash_value,
                         address
                from v$sql_plan
                where operation = 'TABLE ACESS'
                and options = 'FULL'
                ) x
        where t1.executions > 0
        and x.hash_value = t1.hash_value
        and x.address = t1.address
        and t1.parsing_schema_name not in ('SYS','SYSTEM','SYSMAN')
        order by ratio_cpu DESC
        ) t1
where ROWNUM <=10       

-- Literal SQL 관련 성능개선 대상선정

select rownum rno,
         t1.*
from (
        select max(substr_sqltext) sql_text,
                 max(parsing_schema_name) parsing_schema_name,
                 max(module) module,
                 max(s.sql_id) sql_id,
                 count(s.exact_matching_signature) literal_sql_cnt,
                 round(sum(buffer_gets)/sum(s.executions),2) buffer_avg,
                 round(sum(elapsed_time)/sum(s.executions),2) elapsed_avg,
                 round(sum(rows_processed)/sum(s.executions),2) rows_processed,
                 sum(s.executions) executions,
                 round(sum(cpu_time)/max(cpu_time_total)*100,2) ratio_cpu,
                 round(sum(elapsed_time)/max(elapsed_time_total)*100,2) elapsed_cpu,
                 count(distinct s.plan_hash_value) plan_cnt
        from (                
                 select s.parsing_schema_name,
                          s.module,
                          s.sql_id,
                          s.hash_value,
                          s.plan_hash_value,
                          s.address,
                          substr(s.sql_text,1,100) substr_sqltext,
                          s.executions,
                          s.buffer_gets,
                          s.disk_reads,
                          s.rows_processed,
                          s.cpu_time,
                          s.elapsed_time,
                          s.force_matching_signature,
                          s.exact_matching_signature,
                          round(s.buffer_gets/decode(s.executions,0,1,executions),1) lio,
                          round(s.elapsed_time/decode(s.executions,0,1,executions)/1000000,1) elapsed_sec,
                          round(s.cpu_time/decode(s.executions,0,1,executions)/1000000,1) cpu_sec,
                          sum(s.cpu_time) over() cpu_time_total,
                          sum(s.elapsed_time) over() elapsed_time_total
                from v$sqlarea s
                ) s
         where s.executions > 0
         and s.force_matching_signature <> exact_matching_signature
         and s.parsing_schema_name not in ('SYS','SYSTEM','SYSMAN')
         group by s.force_matching_signature
         having count(s.exact_matching_signature) >=2
         order by ratio_cpu DESC
         ) t1
where rownum <=10                                                                          

-- 배치프로그램 관련 성능 개선 대상선정
-- 인라인뷰 o 부분에 배치 프로그램명을 입력하면 해당 프로그램에서 수행하는 모든 sql에 대한 i/o 발생량,  추출 row수, 응답시간, 수행횟수 등의 정보가 추출된다. 이 정보들을 가지고 수행시간이 오래 소요되는 부분의 sql을 추출하여 개선하면 된다.

-- 특정 program 내에서 수행된 sql 추출 스크립트

select o.object_name,
         s.parsing_schema_name as schema,
         s.module,
         s.sql_id,
         s.hash_value,
         substr(s.sql_text,1,100) as sqltext,
         s.executions,
         s.buffer_gets,
         s.disk_reads,
         round(s.rows_processed/s.executions,1) as "Rows",
         round(s.buffer_gets/s.executions,1) lio,
         round(s.elapsed_time/s.executions/1000000,1) elapsed_sec,
         round(s.cpu_time/s.executions/1000000,1) as cpu_sec,
         round(s.elapsed_time/1000000, 1) as elapsed_time
from ( select object_id, object_name
          from dba_objects
          where object_name = :Procedure_Name) o,
          v$sqlarea s
where o.object_id=s.program_id
order by 14 desc                  
        

-- 1. 배치 프로그램명으로 OBJECT_ID 추출하기
SELECT OBJECT_NAME, OBJECT_ID
FROM DBA_OBJECTS
WHERE OBJECT_NAME IN ('PLSQL_BATH_1','PLSQL_BATCH2'); --배치 프로그램명 입력

-- 2. DBA_OBJECTS에서 추출된 OBJECT_ID 값으로 V$SQLAREA의 PROGRAM_ID와 연결

col substr_text for a30
col module for a15

select substr(sql_text,1,30) substr_text, module, program_id
from v$sqlarea
where program_id in ();

-- 3.sql내역확인

select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets
from (
         select parsing_schema_name Schema,
                  module,
                  sql_id,
                  hash_value,
                  substr(sql_text,1,37) substr_sqltext,
                  executions,
                  buffer_gets,
                  disk_reads,
                  rows_processed,
                  round(buffer_gets/executions,1) lio,
                  round(elapsed_time/executions/1000000,1) elapsed_sec,
                  round(cpu_time/executions/1000000,1) cpu_sec
        from v$sqlarea s
        where s.program_id in ( select object_id
                                            from dba_objects
                                            where object_name in (''))
       order by 7 desc
       )t1
where rownum <=10;

-- 최근가장 많이 수행 된 sql과 수행점유율(수행횟수) ash
select sql_id,
         count(*),
         count(*)*100/sum(count(*)) over() ratio
from v$active_session_history
where sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
and sample_time < to_date(:to_time,'yyyymmdd hh24miss')
group by sql_id
order by count(*) desc;

-- 특정 session이 가장 많이 수행 한 sql과 수행 점유율(수행횟수)

select sql_id,
         count(*),
         count(*)*100/sum(count(*)) over() ratio
from v$active_session_history
where sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
and sample_time < to_date(:to_time,'yyyymmdd hh24miss')
and sessiond_id = :b1
group by sql_id
order by count(*) desc;

-- 특정 구간 이벤트 별 대기 시간
select nvl(a.event,'ON CPU') as event,
         count(*) as total_wait_time
from v$active_session_history a
where sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
and sample_time < to_date(:to_time,'yyyymmdd hh24miss')
group by a.event
order by total_wait_time DESC;

-- 특정 구간 cpu점유율 순 - top sql
select ash.sql_id,
         sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
         sum(decode(ash.session_state,'WAITING',1,0)) -
         sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'USER I/O',1,0),0)) "Wait",
         sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'USER I/O',1,0),0)) "IO",
         sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,
        v$event_name en
where sql_id IS NOT NULL
and en.event#=ash.event#
and ash.sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
and ash.sample_time < to_date(:to_time,'yyyymmdd hh24miss')
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc;

-- 특정 구간 cpu 점유율 순 top session
select ash.session_id,
         ash.session_serial#,
         ash.user_id,
         ash.program,
         sum(decode(ash.session_state, 'ON CPU',1,0)) "CPU",
         sum(decode(ash.session_state, 'WAITING',1,0)) -
         sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",
         sum(decode(ash.session_state, 'WAITING', decode(en.wait_class,'USER I/O',1,0),0)) "IO",
         sum(decode(session_state, 'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,
       v$event_name en
where en.event# = ash.event#
and ash.sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
and ash.sample_time < to_date(:to_time,'yyyymmdd hh24miss')
group by session_id,
             user_id,
             session_serial#,
             program
order by sum(decode(session_state,'ON CPU',1,1)) DESC;

리소스를 많이 차지 하는 sql찾기 
1) 총 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
      FROM V$SQLAREA
      ORDER BY BUFFER_GETS DESC  )
WHERE ROWNUM <= :p_rank

2) 평균 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,trunc(BUFFER_PER_EXEC) BUFFER_PER_EXEC, address, SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,address,
   BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
   SQL_TEXT
       FROM   V$SQLAREA
       ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <=  50

3) 메모리, 디스크 사용량이 일정 기준치를 넘은 SQL
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,BUFFER_GETS/EXECUTIONS,SQL_TEXT
FROM   V$SQLAREA
WHERE  BUFFER_GETS >  :p_val1
OR     DISK_READS > :p_val2
OR     EXECUTIONS > :p_val3

from en-core

白面書生
 

4) 디스크 사용량 많은 SQL
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,trunc(BUFFER_PER_EXEC) BUFFER_PER_EXEC, address, SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,address,
   BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
   SQL_TEXT
       FROM   V$SQLAREA
       ORDER BY disk_reads DESC )
WHERE ROWNUM <=  50

-- address 에 해당하는 sql찾기
select *
from v$sqltext
where address = 'B443F724'
order by piece;

from zerobug

이 패키지를 설정하게 되면 SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행하게 된다.

SNP 백그라운드 프로세스는 선택적인 프로세스로 초기화 파라미터 파일에 아래의 항목을 설정 한 후 재 시작을 하면 실행 된다.

 - job_queue_processes = 1     <-- SNP 프로세스의 개수를 지정한다.

 - job_queue_interval = 60         <-- SNP 프로세스가 Sleep 상태에서 깨어나는 간격을 초로 지정한다.

이 패키지는 아래와 같은 다양한 프로시저들을 가지고 있다.

1. Job 관리하기

이 프로시저는 다양한 파라미터의 항목을 가지고 설정 할 수 있다.

 - 기본문법

 DBMS.JOB.submit (

  job out binary_interger,

 what in varchar2,

 next_date in date default sysdate,

 interval in varchar2 default 'null'

 no_parse in boolean default false

 )

 - 주요 파라미터의 의미 

 (1) 새로운 job 등록 테스트

 위에서 살펴본 프로시저들을 사용해서 새로운 job을 등록하는 테스트를 하겠다. 테스트 내용은 job_test01이라느 테이블을 만든 후 1분에 1번씩 해당 테이블에 데이터를 insert 하는 것이다.

 step 1. 테스트용 테이블과 sequencem procedure를 생성한다. 

 step2. job을 등록할 프로시저를 생성한다. 

 * 참고 : 위 5행의 시간 설정 방법 * 

 step3. job을 등록한다.

 step4. 수행되고 있는 job 내용을 확인한다.

 step5. 실제 테이블에 데이터가 입력되는지 확인한다.

(2) 등록되어 있는 job 삭제하기

 (3) 등록되어 있는 job 수정하기

 수행시간을 1분마다에서 5분마다 1회 수행으로 변경함.

http://ann-moon.tistory.com/45

SMP

상호 연결된 하나 이상의 컴퓨터가 그룹을 이루어 작업을 함께 처리하는 방식

CRS(Cluster Ready Service)는 10g Real Application Cluster의 새로운 기능으로, 모든 플랫폼에 대한 표준화된 클러스터 인터페이스를 제공해 주고, 이전 버전에 없었던 새로운 고가용 서비스를 제공해 줍니다.

CRS는 H/W업체에서 제공하는 Cluster S/W/와 함께 사용 되어질 수도 있으며, H/W 업체의 Cluster S/W없이 CRS자체만으로도 구성할수 있습니다.

 9i RAC환경에서는 H/W업체에서 제공하는 Cluster S/W 사용이 필수요소였지만, 10g RAC로 오면서 H/W 업체의 Cluster S/W의 사용은 선택사항이 되었습니다.

 이와 같은 CRS를 구성하기 위해서는 아래사항을 반드시 확인해야 하며 인지하고 있어야 합니다.

 - CRS 10g RAC를 설치하기 전에 설치되고 실행되어야 합니다.

 - CRS HOME과 ORACLE_HOME은 반드시 다른 디렉토리에 설치되어야 합니다.

 - CRS를 설치하기 전에, VOTING 파일 또는 OCR(Oracle Configuration Repository)파일을 설치 할 수 있는 공유된 

   디렉토리 또는 디바이스가 구성 되어야 합니다. 이를 위한 voting file은 최소 20MB 정도 크기이며, OCR 파일은

   최소 100MB 크기가 되어야 합니다.

 - CRS 및 RAC를 설치하기 위해서는 다음과 같은 네트워크 인터페이스가 구성되어야 합니다.

   Public Interface

   Pravate Interface

   Virtual Interface

 - RAC NODE당 1개의 CRS 데몬만 실행 가능합니다.

 - 네트워크 split이 있을 경우 (NODE간 통신 두절) data corruTESTion을 방지 하기 위해 하나 또는 그 이상의 NODE

   에서 시스템 Rebooting 현상이 발생 할 수 있습니다.

 - 서비스를 중단시키는 올바른 방법은, 장비를 shutdown 시키거나, "init.crsstop" 명령을 실행시키는 것입니다.

CRS를 구성하고 있는 프로세스

 - CRSD

CRS를 구성하고 있는 가중 중요한프로세스로서 RAC에서 담당하는 역활은 아래와 같습니다.

  - HA(High Availability) 작업을 위한 엔진

  - Application 자원 관리

  - Application 자원을 구동, 정지, failover 처리

  - OCR(Oracle Configuration Repository)의 구성 프로파일 관리

  - OCR의 현재 알려진 상태를 저장

  - root 권한으로 실행

 이처럼 CRSD 데몬은 RAC환경에서 가장 기본적인 NODE간의 자원상태를 감지하고 있습니다. 필요한 경우 해당 NODE를 Down 하거나 Rebooting 하는 역활을 수행하게 됩니다. CRS데몬은 장애 발생 시 자동으로 구동되도록 설정되어 있습니다. 만약에 CRS 데몬을 OS명령어로 KILL하게 될 경우 해당 NODE는 Rebooting 될 수 있으니 주의하시기 바랍니다.

 - CSSD

   CSSD는 RAC의 일부로, ASM과 함께 단일 인스턴스를 구성

   NODE 맴버쉽에 대한 액세스를 제공

   그룹 서비스 제공

   기본적인 클러스터 LOCK 기능 제공

   오라클 계정으로 실행

  이처럼 CSSD 데몬은 데이터베이스의 Syncronization 부분을 담당하게 됩니다.

$ORACLE_HOME/network/admin/listener.ora 에서 다음 명령어를 추가해준다.

맨 마지막줄

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

DIAG_ADR_ENABLED_LISTENER=OFF

LOG_FILE_LISTENER=listener_etc.log    <<--- LOG로 남길 파일

LOG_DIRECTORY_LISTENER=/tmp        <<--- LOG가 저장될 위치

wq!

lsnrctl reload

원복방법

위의 3줄 삭제후 저장

ORA-01455 : 열 변환시 정수 데이터 유형이 오버플로우 되었습니다.

이번 작업에서는 데이터가 한 건도 없는 테이블을 EXPORT할때 발생하였습니다.

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

ALTER TABLE <empty table name> ALLOCATE EXTENT;

########################################################

# Listener (stop, start, status)
# srvctl stop listener -n <hostname> -l LISTENER

########################################################

srvctl stop listener -n rac1 -l LISTENER
srvctl start listener -n rac1 -l LISTENER
srvctl status listener -n rac1 -l LISTENER

srvctl stop listener -n rac2 -l LISTENER
srvctl start listener -n rac2 -l LISTENER
srvctl status listener -n rac2 -l LISTENER

########################################################

# Oracle Instance and service(status)
# srvctl status database -d <database_name>

########################################################

srvctl status database -d ORCL

########################################################

# Oracle 11g RAC stop command
# srvctl stop instance -d <database_name> -i ORCL1<instance_name>
# srvctl stop listener -n r<hostname> -l LISTENER
# srvctl stop nodeapps -n <hostname>

########################################################

- RAC1

emctl stop dbconsole

srvctl stop instance -d ORCL -i ORCL1
srvctl status instance -d ORCL -i rac1

srvctl stop listener -n rac1 -l LISTENER
srvctl status listener -n rac1 -l LISTENER

srvctl stop nodeapps -n rac1

- RAC2
emctl stop dbconsole

srvctl stop instance -d ORCL -i ORCL2
srvctl status instance -d ORCL -i ORCL2

srvctl stop listener -n rac2 -l LISTENER
srvctl status listener -n rac2 -l LISTENER

srvctl stop nodeapps -n rac2

########################################################

# Oracle 11g RAC start command
# srvctl start listener -n r<hostname> -l LISTENER
# srvctl start instance -d <database_name> -i <instance_name>

########################################################

- RAC1

srvctl start nodeapps -n rac1
srvctl status nodeapps -n rac1

srvctl start listener -n rac1 -l LISTENER
srvctl status listener -n rac1 -l LISTENER

srvctl start instance -d ORCL -i ORCL1
srvctl status instance -d ORCL -i ORCL1

emctl start dbconsole

- RAC2
srvctl start nodeapps -n rac2
srvctl status nodeapps -n rac2

srvctl start listener -n rac2 -l LISTENER
srvctl status listener -n rac2 -l LISTENER

srvctl start instance -d ORCL -i ORCL2
srvctl status instance -d ORCL -i ORCL2

emctl start dbconsole

########################################################

# All Database (stop, start)
# srvctl start database -d <database_name>

########################################################

srvctl start database -d ORCL
srvctl stop database -d ORCL

 오라클 10g에서 디폴트로 걸려있는 SCHEDULER를 확인.

SQL> connect /as sysdba

SQL> SELECT OWNER, JOB_NAME, JOB_CREATOR, STATE FROM DBA_SCHEDULER_JOBS ;

OWNER                          JOB_NAME                       JOB_CREATOR                    STATE

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

SYS                            PURGE_LOG                      SYS                            SCHEDULED

SYS                            FGR$AUTOPURGE_JOB              SYS                            DISABLED

SYS                            GATHER_STATS_JOB               SYS                            SCHEDULED

SYS                            AUTO_SPACE_ADVISOR_JOB         SYS                            SCHEDULED

 'STATE' 항목에 'SCHEDULED'로 표시된 항목이 현재 SCHEDULER가 걸려있는 것이다. 이를 DISABLED 시키면 자동스케줄러 작업이 해지된다.

자동 Analyze 스케줄러 작업을 해지 시키기

SQL> EXEC DBMS_SCHEDULER.DISABLE('PURGE_LOG');

SQL> EXEC DBMS_SCHEDULER.DISABLE('FGR$AUTOPURGE_JOB');

SQL> EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

SQL> EXEC DBMS_SCHEDULER.DISABLE('AUTO_SPACE_ADVISOR_JOB');

SQL> SELECT OWNER, JOB_NAME, JOB_CREATOR, STATE FROM DBA_SCHEDULER_JOBS ;

OWNER                          JOB_NAME                       JOB_CREATOR                    STATE

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

SYS                            PURGE_LOG                      SYS                            DISABLED

SYS                            FGR$AUTOPURGE_JOB              SYS                            DISABLED

SYS                            GATHER_STATS_JOB               SYS                            DISABLED

SYS                            AUTO_SPACE_ADVISOR_JOB         SYS                            DISABLED