Never ending story ...

ORACLE trace는 SQL문의 성능 분석을 위해 사용하는 가장 기본적이며 가장 중요한 도구라고 할 수 있습니다.

ORACLE trace는 sql_trace라는 parameter 값을 변경하거나 dbms_system.set_ev procedure를 이용해 설정할 수 있습니다.

trace의 결과는 user_dump_dest parameter에 지정된 directory에 생성되며 tkprof 명령을 이용해 format할 수 있습니다.

tkprof의 결과로 생성된 파일에는 trace가 설정된 후로부터 trace가 해제되었거나 세션이 종료된 시점까지 수행된 SQL문과 PARSE, EXECUTE, FETCH 단계별로 소요시간 및 소요 자원량이 기록되어 있으며 수행시점의 ACCESS PATH가 기록되어 있습니다.

다음은 특정 SQL문을 수행한 결과 생성된 trace 파일을 tkprof로 format한 결과의 일부분 입니다.

 1.
 select
 o.order_id
 ,o.order_date
 ,oi.line_item_id
 ,p.product_name
 ,oi.quantity
 from
 orders o
 ,order_items oi
 ,products p
 where
 o.order_id = oi.order_id
 and o.customer_id = 160
 and oi.product_id = p.product_id
 order by
 o.order_id
 ,oi.line_item_id

 2.
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.07       0.83          0          0          0           0
 Execute      1      0.01       0.01          0          0          0           0
 Fetch        2      0.00       0.07          0         29          0           2
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total        4      0.08       0.91          0         29          0           2

 3.
 Misses in library cache during parse: 1
 Optimizer mode: CHOOSE
 Parsing user id: 52  (OE)

 4.
 Rows     Row Source Operation
 -------  ---------------------------------------------------
       2  SORT ORDER BY
       2   NESTED LOOPS OUTER
       2    HASH JOIN
       2     HASH JOIN
       1      TABLE ACCESS FULL ORDERS
     665      TABLE ACCESS FULL ORDER_ITEMS
     288     TABLE ACCESS FULL PRODUCT_INFORMATION
       2    TABLE ACCESS BY INDEX ROWID PRODUCT_DESCRIPTIONS
       2     INDEX UNIQUE SCAN (object id 41543)

 5.
 Rows     Execution Plan
 -------  ---------------------------------------------------
       0  SELECT STATEMENT   MODE: CHOOSE
       2   SORT (ORDER BY)
       2    NESTED LOOPS (OUTER)
       2     HASH JOIN
       2      HASH JOIN
       1       TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ORDERS'
     665       TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ORDER_ITEMS'
     288      TABLE ACCESS   MODE: ANALYZED (FULL) OF
                  'PRODUCT_INFORMATION'
       2     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                 'PRODUCT_DESCRIPTIONS'
       2      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'PRD_DESC_PK'
                  (UNIQUE)

위의 결과는 대략 5가지 부분으로 나누어질 수 있으며 각각의 부분은 다음과 같은 의미를 가집니다.

 1. 수행된 SQL문
 이 부분은 실제로 수행된 SQL문이 기록된 부분입니다.

 2. 일량 및 시간 정보
 PARSE - EXECUTE - FETCH 단계별로 기록된 수행횟수, CPU 시간 및 소요시간, Disk I/O, Memory I/O, 처리 건수의 정보를 가지고 있습니다.
 이 정보를 활용해 어느 단계에서 시간이 많이 소요됐는지를 알 수 있으며 SQL문 수행시 얼마나 많은 자원을 사용했으며
 최종 처리 건수가 몇 건인지를 알 수 있습니다.
 주의할 점은 'cpu' 및 'elapsed'에 시간 정보가 나오려면 timed_statistics parameter값이 INSTANCE LEVEL 또는 SESSION LEVEL에서
 true로 설정되어 있어야 한다는 점입니다. 만일 이 값이 false로 설정된 경우 'cpu', 'elapsed' 부분에 모두 0이 출력됩니다.

 3. PARSE 관련 정보
 SQL문 수행 시 Library Cache에 수행하려는 SQL문에 대한 Parse 정보가 존재하지 않아 HARD PARSE를 수행했는지의 여부와
 Optimizer Mode, Parsing을 수행한 User의 ID가 표시되는 부분입니다.

 4. 실행 중 실행계획
 실제 SQL문 수행 중 사용된 실행계획입니다.

 5. tkprof 수행시 실행계획
 tkprof를 이용해 format 작업 시 생성된 실행계획입니다. 대부분의 경우 동일한 실행계획을 가지지만 실행시간과 tkprof 수행 시
 관련 Object가 변경됐거나 bind 변수의 type이 틀리는 경우 서로 다른 실행계획이 나올 수 있습니다.

trace의 내용은 SQL문의 성능 문제 해결에 있어 필수적인 내용이므로 각각의 부분이 의미하는 내용을 정확히 숙지할 필요가 있습니다.

조금 더 심도있는 내용으로 들어가 보겠습니다.

 call    count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.07       0.83          0          0          0           0
 Execute      1      0.01       0.01          0          0          0           0
 Fetch        2      0.00       0.07          0         29          0           2
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total        4      0.08       0.91          0         29          0           2

위의 일량 및 시간 정보를 보면 우선 Parse 작업에 상당한 시간이 소요됐음을 알 수 있습니다. CPU 시간을 보면 'Parse'에 소요된 시간이 0.07초, 'Execute'에 사용된 시간이 0.01초로 SQL문의 처리 준비를 위한 Parse 시간이 실제 처리하는데 사용된 시간보다 7배나 크다는 것을 알 수 있습니다. 배꼽이 배보다 커도 한참 크다는 말이지요.

순수한 CPU 시간에 각종 WAIT가 추가된 실제 소요시간은 'Parse' 단계에서는 0.83초, 'Execute'+'Parse' 단계를 합쳐 0.08초로 약 10배의 차이를 보이고 있습니다.

'Parse' 시간이 매우 컸고 이런 현상이 지속된다면 SQL문을 작성시 가능한 Bind 변수를 사용해 Shared Pool에서 최대한 공유가 잘 될 수 있는 형태로 만들어야 할 것입니다.

사용한 자원을 보면 'disk'는 'Parse','Execute','Fetch' 단계 모두 0을 표시하고 있어 물리적으로 DISK에서 읽어온 자료는 없다는 것을 의미합니다. 모두 Buffer Cache에서 읽어왔다는 말이지요.

Buffer Cache에서 읽어온 량이 바로 'query'와 'current'의 합인 29 block을 나타내고 있는 것입니다.

참고로 'query'는 'consistent gets'를 'current'는 'db block gets'와 동일한 의미를 갖습니다. 내부적인 의미가 있지만 일반적으로 두가지를 구분할 필요가 없으며 두가지 값을 더해서 Logical Read 또는 MEMORY I/O 개념으로 생각하시면 됩니다.

2건을 처리하는 과정에서 29 block을 읽어왔으므로 1 block당 8KB로 계산하면 1건 처리를 위해 116KB를 읽었다는 것을 알 수 있습니다. 이 값은 몇 가지 판단 기준이 있습니다만 절대적으로 적용하기는 약간의 무리가 있으므로 이 정도만 아시면 될 것 같습니다.

3번을 보면 처음 'Misses in library cache during parse: 1'라는 내용이 보이죠. 이 말이 의미하는 것은 'Parse' 과정 중 library cache에 수행 하려는 SQL문의 정보가 존재하지 않았다는 말입니다. 그래서 새롭게 Parse 작업을 수행했고 그 시간이 0.83초 소요된 것입니다. Parse와 관련된 좀 더 자세한 자료는 아래의 Hard Parsing에 따른 성능 문제와 효과적인 SQL 작성방법을 참고하시기 바랍니다.

4번과 5번은 어느 시점에서 본 ACCESS PATH인지만 다르므로 INDEX 이름까지 제대로 출력된 5번의 내용으로 분석하도록 하겠습니다.

이 부분에서 볼 수 있는 내용은 SQL문의 ACCESS PATH와 각 단계별 처리된 건수 입니다. 어떤 과정을 통해 2건이라는 자료가 출력됐는지를 보여주고 있기 때문에 이 부분의 내용은 상당히 중요하다고 할 수 있습니다.

예제에서는 ORDERS라는 TABLE을 FULL SCAN해서 1건을 가져왔습니다. 이 것이 효율적으로 일한 것인지 아닌지는 다른 과정을 통해 다루도록 하겠습니다.

그리고 ORDER_ITEMS라는 TABLE을 다시 FULL SCAN해서 665건을 가지고 왔으며 그 정보를 HASH JOIN해서 2건의 자료가 생성됐음을 알 수 있습니다.

PRODUCT_INFORMATION라는 TABLE을 FULL SCAN해서 288건을 가지고 왔으며 위에서 도출된 2건과 HASH JOIN한 결과 2건의 자료가 생성되었습니다.

그 2건의 자료가 PRD_DESC_PK INDEX를 이용해 PRODUCT_DESCRIPTIONS TABLE을 거쳐 역시 2건의 최종 결과가 출력되었습니다.

실제 Performance Tuning 시 위와 같은 내용들에 대한 분석이 이루어지고, 이에 대한 분석을 기초로 보다 최적의 실행계획을 만들어내는 작업을 할 수 있게 됩니다.

출처 : http://blog.daum.net/warmfeel/92

Stored Procedure VS JAVA App.

Conclusion

1.    I think it is necessary to use. However, before using SPl, We must consider whether only once business or not, whether business is not likely to expand, whether business focus on DB for efficiency. After we consider efficiency method, we can use SP. However, If we can get similar performance without SP, we should not use SP. Using SP should be optional, not mandatory.

2.    SP does not support all of RDMS and SP is not compatible with each other well. (I herd that AXA will be not change DB vender(Oracle) )  It is reason that we use J2EE and comply their agreement. If we don’t consider porting, we can not use J2EE.

3.    Regarding maintenance, We know that SP is easy language. Point of view is when occur an error, we ensure Rapid response for Reliability so we have to equip the tools for test and development.

when occur an error, we should examine all tables with relation to SP. However java can development in parses and ensure reliability with unit test and rapid response is possible than SP.

Of course, We can manage SP more easily via validation code. However, validation code is  involved in each SP so duplicate validation codes are occurred every SP for duplicate purpose

In that case, we will get bad performance although we use SP for performance. Moreover, Validation codes are not managed easily.

4.    In the past  from the viewpoint of maintenance, C includes a SQL in the CS environment(Client and Server) generally so Sp is more useful than to check the parsing error as using C.

These days, Web-Service-Architecture is different from past environment

5.    If our system architecture target work toward 3-tiers, OOP, maintenance and loose coupling, We must not use SP as possible but we get the best performance as SP so if some business has heavy traffic, in that case, we can use SP for performance partially.

출처 : http://onecellboy.tistory.com/25

*. 상황설명

서버쪽 리스너와 클라이언트쪽 tnsnames.ora의 설정을 맞게 했음에도 클라이언트에서 아래의 에러가 뜨면서 접속이 안된다.

참고로 클라이언트에서 tnsping orcl 을 날리면 잘 날아간다.

*. 분석

1. 서버쪽의 lsnrctl status 명령을 날려 리스너의 상황을 보았다.

아래서 두번째 줄 처럼 The listener supports no services의 상태다. 인스턴스가 떠 있어야 함에도 그러질 못하고 있는 상황이다.

2. 로그를 확인해 보았다.

리스너의 HOST에 IP로 적어줬음에도 로그에는 sg.net으로 찍혀있다.

*. 의심

서버쪽의 /etc/hosts 상황을 확인해 보았다.

-- /etc/hosts 상황

큰 문제 없어 보였다.

애매한 점이 있어서 아래와 같이 편집후 OS 재부팅까지 해 보았다.

OS 재부팅후 DB startup을 해보았다.

위의 에러가 나면서 실패.

없다고 나온다. 아무래도 문제의 원인인 듯 싶다.

*. 조치

/etc/hosts의 내용을 아래와 같이 편집했다.(서버의 아이피 : 192.168.56.101)

핑을 날려보니 잘 날아간다.

DB도 startup 완료.

리스너를 실행하고 status를 확인 했다.

드디어 인스턴스가 뜬다.

클라이언트에서 접속 시도해보자.

접속성공.

오렌지에서도 접속 시도 : 문제없이 접속 완료

*. 결론 

서버쪽 /etc/hosts에 아래와 같이 서버의 실제 아이피가 hostname으로 잡혀 있어야 함.

로컬호스트에 hostname이(리스너에서 사용한 이름) 물려 있으면 에러난다.

*. 아래는 참고할 현재 상황

출처 : http://dinggur.tistory.com/264

동적SQL

반복적으로 사용되는 SQL 문법을 저장해 두었다가 필요할때 조건절만 사용자로부터 입력받아 실행시킬수 있는데  이런유형의 SQL을 동적(Dymamic)SQL 이라 한다.

PL/SQL에서 동적 SQL을 실행할 때는 EXECUT IMMEDIATE 명령어를 사용한다. 또한 동적 SQL에서 정의된 조건값을 전달할 때에는 USING 절을 사용한다.  

예제 1)

set serveroutput on

create or replace procedure dsql_test
  (v_empno number)

is
sql_stmt varchar2(200);
emp_rec emp%ROWTYPE;

begin
sql_stmt :='SELECT * from emp where empno= :id';        -- 자주사용돠는 sql문을 변수로 저장

execute immediate sql_stmt into emp_rec using v_empno;  

                                                     -- 변수에 저장된 sql문을 호출. v-empno 가  :id로 저장

    DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.empno );       
    DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.ename );       
    DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.hiredate ); 

execute immediate sql_stmt into emp_rec using v_empno;    -- 변수에 저장된 sql 문 호출
    DBMS_OUTPUT.PUT_LINE( '**********************************************');
    DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.empno );       
    DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.ename );       
    DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.hiredate );  
    DBMS_OUTPUT.PUT_LINE( '월급 : ' || emp_rec.sal ); 

end;
/

execute dsql_test(7369)

*************************************************************************

예제 2)

DROP TABLE BONUS;

DECLARE
  dept_id    NUMBER(2)  := 50;
  dept_name  VARCHAR2(14) := 'PERSONAL';
  emp_id     NUMBER(4) := 7934;
  emp_rec    EMP%ROWTYPE;
  location   VARCHAR2(13) := 'DALLAS';
  sql_stmt   VARCHAR2(200);
  salary     NUMBER(7,2);

BEGIN
     EXECUTE IMMEDIATE 'create table bonus (id number, amt number)';

  sql_stmt := 'insert into dept values (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

 sql_stmt := 'select * from emp where empno = :id';
    EXECUTE IMMEDIATE sql_stmt  INTO emp_rec USING emp_id;

 sql_stmt := 'update emp set sal = 2000 where empno = :1 returning sal into :2';
    EXECUTE IMMEDIATE sql_stmt USING emp_id   RETURNING INTO salary;

END ;
/

DESC BONUS;
SELECT * FROM DEPT;
SELECT * FROM EMP WHERE EMPNO=7934;
ROLLBACK;

*************************************************************************

예제 3 - 동적 sql의 명시적 커서

DECLARE 
emp_rec emp%rowtype;
sql_stmt varchar2(200);
my_job varchar2(15) := 'CLERK';

TYPE empcurtyp is ref cursor;      -- 커서의 선언
emp_cv empcurtyp;                     -- 커서를 재사용하기 위해서 재 정의 했다

begin 
  sql_stmt := 'SELECT * FROM emp WHERE job= :j';
  OPEN emp_cv for sql_stmt using my_job;               -- 커서 시작

    LOOP   
        FETCH emp_cv into emp_rec;                         -- 조건값 인출
           exit when emp_cv%notfound;
           dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename);
   end loop; 
  close emp_cv;                                                    -- 커서 종료
end;
/

**********************************************************

             PL/SQL에서 동적 SQL 사용
                               
                                         작성일 : 2002/08/24
                                         작성자 : 오범석
                                         E-MAIL : refreshman@chollian.net
                                         
                                         
오라클의 PL/SQL에서 동적 SQL을 사용하기 위한 방법에 대해 설명한다.
우선 동적 SQL이란 무엇을 말하는 것일까?

보통 PL/SQL에서 CURSOR사용을 위해서는 선언부에 CURSOR를 선언하고 
CURSOR를 생성할 SQL을 선언한다.

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

CREATE OR REPLACE PROCEDURE call_me(ICALL_ID CHAR) AS

    --커서 선언부...
    CURSOR cur1 IS
        SELECT CALL1, CALL2, CALL3 
          FROM CALL_TABLE
         WHERE CALL_ID = ICALL_ID;
   
BEGIN

.............나머지 생략 .................

END;
/
SHOW ERRORS  

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

위에서의 코드에서 볼수 있듯이 CURSOR 선언시 해당 SQL을 지정하는데.
입력값인 ICALL_ID 를 WHERE 절에 조건으로 사용하는것은 가능하다.

문제는 조건에 따라 필드명이나 테이블이 바뀌거나 SQL문이 전혀 다르게
생성된다면 어떻게 할까?

이런 문제에 봉착했을때 동적 SQL을 사용한다.

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

CREATE OR REPLACE PROCEDURE call_me(ICALL_TYPE CHAR) AS
 
 
    our_cursor INTEGER;  --커서변수선언
    status  INTEGER;  --커서의 ID를 저장
    sql_str  VARCHAR2(100); --질의어 저장

    --바인딩 변수 선언
    v_CALL1  VARCHAR2(30);
    v_CALL2  VARCHAR2(30);
    v_CALL3  VARCHAR2(30);
 
BEGIN

    IF ICALL_TYPE = 'ME' THEN
        sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
                   '   FROM CALL_ME             ';
    ELSIF ICALL_TYPE = 'YOU' THEN
        sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
                   '   FROM CALL_YOU            '; 
    ELSIF ICALL_TYPE = 'WE' THEN
        sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
                   '   FROM CALL_WE             '; 
  
    END IF;
 
    --동적 쿼리 사용을 위한 커서 생성
    our_cursor := DBMS_SQL.OPEN_CURSOR;
 
    --SQL문 파싱
    DBMS_SQL.PARSE(our_cursor,sql_str,DBMS_SQL.V7);
 
    --각 컬럼을 바인딩한다.
    --인자 : (커서, 필드순번, 바인딩변수, 값의 길이)
    DBMS_SQL.DEFINE_COLUMN(our_cursor,1,v_CALL1,30);
    DBMS_SQL.DEFINE_COLUMN(our_cursor,2,v_CALL2,30);
    DBMS_SQL.DEFINE_COLUMN(our_cursor,3,v_CALL3,30);
 
    --커서 ID를 저장한다.
    status := DBMS_SQL.EXECUTE(our_cursor);
 
    --자료를 출력한다.
    LOOP
        IF DBMS_SQL.FETCH_ROWS(our_cursor) > 0 THEN
  
            --데이터를 변수에 할당한다.
            DBMS_SQL.COLUMN_VALUE(our_cursor,1,v_CALL1);
            DBMS_SQL.COLUMN_VALUE(our_cursor,2,v_CALL2);
            DBMS_SQL.COLUMN_VALUE(our_cursor,3,v_CALL3);
  
            DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
            DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
            DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
   
        ELSE
            EXIT; --LOOP를 빠져나간다.
        END IF;
     END LOOP;
 
    --커서를 닫는다.
    DBMS_SQL.CLOSE_CURSOR(our_cursor);
 
    --에러 처리
    EXCEPTION
    WHEN OTHERS THEN
        --사용자 정의 핸들러.
        errhandle(SQLERRM);
  
 
END;
/
SHOW ERRORS

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

위의 코드에서 보는것과 같이 다음과 같은 순서를 통해 동적 SQL을 실행한다.

1. 선언부에 커서, 질의어저장변수, 커서 ID, 값을 바인딩할 변수를 선언한다.
2. 로직을 통해 SQL문을 생성한다.
3. 동적쿼리 사용을 위한 커서 생성한다.]
4. SQL을 파싱하여 커서에 할당한다.
5. 해당 컬럼을 변수에 바인딩 시킨다.
6. 커서의 SQL을 실행한다.
7. 커서를 패치하면서 바인딩한 변수에 해당 필드값을 설정한다.

출처 : http://blog.naver.com/swinter8/130000770771

spool c:\export.xls;
set markup html on;
set pagesize 9999;
select * from emp;
set markup html off;
spool off;

지정된 디렉토리에 조회한 내용을 담은 파일이 생성된다.

◈ Oracle Flashback 기능 이란?
 
DB 관리중에 실수로 데이타를 삭제하거나 데이타의 값을 잘못 변경 하는 실수가 가끔 발생을 합니다.
 
이러한 오류를 바로 인식할 경우는 Rollback이라는 명령으로 바로 전에 수행한 작업을 원상복귀시킬 수 있지만, COMMIT을 한 이후 시점이나, 한참 시간이 지난 후에 알았다면 간단하게 복구하기가 난감 합니다.
 
이러한 경우에 특정한 시간 또는 시점으로 되돌릴 수 있는 기능이 Oracle Flashback 기능 입니다.
 
간단하게 말해서 Flashback 기능은 특정한 과거시점의 질의를 실행할 수 있게 해 줍니다.
데이타베이스에 구조적인 변화를 가하지 않고 과거 일정 시점의 데이타 상태를 확인할 수 있는 기능 입니다..
 
일종의 오라클에서 지원하는 타임머신이라고 할 수 있죠...
 
 
 
◈ Flashback을 사용하기 위한 요구조건
   
 - 자동 언두 관리 시스템을 사용해야 합니다.  (UNDO_MANAGEMENT 파라미터를 AUTO로 설정)
   .UNDO_MANAGEMENT = AUTO
 
 - 이전의 어느 시점까지의 언두(UNDO)정보를 보유하여 Flashback Query를 수행할것인지
   UNDO_RETENTION 파라미터를 설정해야 합니다.
   .ALTER SYSTEM SET UNDO_RETENTION=1800
 
 - 일반사용자가 Flashback 기능을 이용하기 위해서 DBMS_FLASHBACK패키지에 대한 EXECUTE권한이 있어야 합니다.
 
 
 
◈ Flashback 사용하기
 
Flashback의 사용 방법은 과거시점의 특정 시간으로 사용하는 방법과 SCN(System Change Number)을 사용하는 방법이 있습니다.
 
 
 - 과거시점의 시간 사용: DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);
 - SCN 사용 : DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);
 
 
과거시점의 시간을 지정하여 Flashback 기능을 사용 할 경우 오라클은 내부적으로 이를 SCN으로 전환하여 처리 합니다. 시간 정보를 SCN으로 Mapping하는 시간이 필요한데 통상 5분 주기로 이루어 집니다.
 
따라서, 시간으로 지정할때는 현재보다 5분이상 차이가 나는 과거시점을 지정해야 합니다.
 
 
또한 Flashback 기능은 무한대로 이전의 데이터를 조회할 수 있는 기능이 아니고,
관리자가 UNDO_RETENTION 파라미터를 통해서 정해준 시간(초) 동안의 데이터를 조회할 수 있습니다.
 
 - 디폴트 UNDO_RETENTION 시간은 10800(3시간) 입니다.
 
그리고  Flashback data를 참고하는 경우엔 DML, DDL등의 작업을 직접 수행 할 수 없습니다.


◈ Flashback 사용예제


C:\>SQLPLUS /NOLOG
 
 
▒▒▒▒▒▒ Flashback 사용을 위한 환경설정 시작 ▒▒▒▒▒▒
 
-- SYSDBA 권한으로 접속
SQL>CONN / AS SYSDBA
 
 
-- UNDO MANAGEMENT MODE 확인
SQL>SHOW PARAMETER UNDO;
NAME                                 TYPE        VALUE
--------------------------- ----------- ---------
undo_management                 string       AUTO
undo_retention                       integer      10800
undo_suppress_errors            boolean     FALSE
undo_tablespace                    string        UNDOTBS1
 
   
-- undo_management가 MANUAL로 되어있을경우 아래와 같이 변경하고 UNDO 테이블스페이스를 생성하고 지정합니다..
SQL>ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;
 
-- UNDO 테이블 스페이스 생성
SQL>CREATE UNDO TABLESPACE UNDOTBS2
    DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf' SIZE 100M;
 
-- UNDO 테이블 스페이스 지정
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2
 
 
-- UNDO_RETENTION 시간을 변경하시면 실제 적용을 위해 5분정도 기다려야 합니다.
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800
 
 
-- scott유저에게 DBMS_FLASHBACK EXEUCTE 권한 부여
SQL>GRANT EXECUTE on DBMS_FLASHBACK TO SCOTT;
 
▒▒▒▒▒▒ Flashback 사용을 위한 환경설정 끝 ▒▒▒▒▒▒
 
 
-- 테스트를 위해서 scott 유저에 접속을 합니다.
SQL>CONN scott/tiger
 
 
-- emp 테이블 14건의 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다...
 
 
-- 데이터 삭제하기전의 날짜를 확인 합니다.
-- Flashback을 이용하여 이 시점에서 데이터를 조회, 복구 할 것입니다.
SQL>SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
 
TO_CHAR(SYSDATE,'YY
-------------------
2006-01-21 17:16:51
 
 
-- 데이터를 삭제 합니다.
SQL>DELETE FROM emp;
14 행이 삭제되었습니다.
 
 
-- commit 수행
SQL>COMMIT;
 
 
-- 데이터 확인
SQL>SELECT * FROM emp;
선택된 레코드가 없습니다.
 
 
-- Flashback 과거시점의 시간사용 모드 Enable로 데이터 확인
SQL>EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))
PL/SQL 처리가 정상적으로 완료되었습니다.
 
 
-- 데이터를 확인 할 수 있습니다.
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
 
 
-- Flashback Disable로 변경
SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL 처리가 정상적으로 완료되었습니다.
 
 
-- Flashback Disable로 변경하면 데이터를 확인 할 수 없습니다.
SQL>SELECT * FROM emp;
선택된 레코드가 없습니다.
 
 
Flashback Disable 상태에서도 이전데이터를 보기위해서는 데이터 복구 작업을  진행해야 합니다.
아래는 삭제된 데이터 복구 예제 입니다.
 
 
 
◈  삭제된 데이터 복구
 
Oracle9i Database Release 2이상 버전에서는  SELECT...AS OF 명령을 사용하여 쉽게 Flashback 데이터를 복구 할 수 있습니다.
Oracle9i Database Release1 버전에서는 DBMS_FLASHBACK프로시저를 이용해서 데이터를 복구해야 합니.
 
 
-- 삭제된 데이터 복구(오라클 버전 Release 9.2.0.1.0 실행)
SQL>INSERT INTO emp
    (SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'))
 
 
-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
 
 
 
-- 위에 복구된 데이타를 Rollback으로 지우고 DBMS_FLASHBACK 패키지를 이용해서 복구해 봅니다.
 
-- 삭제된 데이터 복구
SQL>DECLARE
   
     CURSOR emp_cursor is
       SELECT * FROM emp;
     
      v_emp emp%ROWTYPE;
     
    BEGIN
   
     DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'));
     
     OPEN emp_cursor;
     
     -- Flashback을 Disable했지만 커서(test_cursor)는 여전히 과거시점의 데이터를 가지고 있습니다.
     DBMS_FLASHBACK.DISABLE;
                             
     LOOP
        FETCH emp_cursor INTO v_emp;
            EXIT WHEN emp_cursor%NOTFOUND;
            INSERT INTO emp VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
     END LOOP;
     CLOSE emp_cursor;
     COMMIT;
    END;
    /
 
PL/SQL 처리가 정상적으로 완료되었습니다.

-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.

오라클 클라이언트에서 서버로 접속하는 방법

자료제공 : shop-wiz.com[숍위즈 - 폰돌]

oracle 10g 를 기본으로 설명


프로그램 > Oracle-OraDb10g_home1 > Configuration adn Migration Tools > Net Manager 선택
[ 주의 : 오라클 버전마다 틀립니다 ]

“서비스 이름 지정” 을 추가.



네트 서비스 이름이란 서버쪽의 어떤 연결을 클라이언트에서 사용하기 위한 별칭이라고 생각하면 됨.
자기가 사용하고 싶은대로 이름을 선택하면 됨.
Toad 같은 관리프로그램이나 기타 프로그램에서 오라클의 접속들을 구분하는 이름이 됨




서버와 클라이언트가 데이터를 주고 받는 네트워크 프로토콜을 선택해야 함
보통 TCP/IP 프로토콜을 사용, 특수한 목적이 있을때는 다른 프로토콜을 선택하면 됨.
TCP/IP 프로토콜은 기본적으로 1521 번 포트를 사용해서 데이터를 주고 받음.






호스트명 : 서버의 IP Address 또는 도메인명을 입력함.
포트번호 : 기본값 1521, 방화벽을 지나가면서 포트번호가 바뀌거나 서버에서 다른 포트를 사용하는 경우에는 적절한 값을 입력





접속할 오라클 데이터베이스의 SID 를 입력함. 모를 경우에는 관리자에게 문의하여 알아내야 함.






접속이 정상적으로 되는지 테스트를 할 수 있음.
테스트 버튼을 눌러서 테스트 시작










테스트 버튼을 누르면 scott / tiger 로 테스트함.
다른 아이디로 테스트를 하고 싶을때는 “로그인 변경..” 버튼을 선택한 후 사용자명과 암호를 변경한 후 다시 시도.


ORA-12560: TNS:프로토콜 어댑터 오류

set ORACLE_SID=~~

일정횟수 이상 로그인 실패시 10g 일 때 scott계정을 사용 할려면 만나는 오류이다.
=====================================================================
SELECT USERNAME,
       ACCOUNT_STATUS,
           TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE          
FROM DBA_USERS;
=====================================================================

lock의 상태를 확인 하고     ALTER USER 명령을 사용하여 (* LOCK걸린계정 콜론으로 막지말것) 
=====================================================================
ALTER USER LOCK걸린계정 ACCOUNT UNLOCK;
=====================================================================

다시 확인

=====================================================================
SELECT USERNAME,
       ACCOUNT_STATUS,
           TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE          
FROM DBA_USERS
WHERE USERNAME = 'LOCK걸린계정'
=====================================================================

1. 바인드 변수를 사용한다.

오라클은 일단 SQL 문장이 실행되면 이를 메모리에 로드한뒤 처리하는데 가정 먼저 이전에 처리된 것 중

이와 동일한 문장이 있는지 메모리에서 찾게 된다. 만약 동일한 문장이 있으면 이미 이전에 처리한 문장이

므로 파싱 단계에서 하는 문법 검사 그리고 옵티마이저가 수행하는 여러가지 작업을 건너 뛰고 이미 만들어진 실행계획대로 처리하여 결과를 반환하게 된다. 가끔씩 복잡한 복잡한 쿼리를 실행하면 맨 처음 실행할 때는 결과가 나오기까지 시간이 오래 걸리지만 두번째 부터는 결과가 빨리 나오는 경우를 볼 수 있는데,

바로 이러한 처리 방식 떄문이다.

그런데 여기서 말하는 동일한 문장은 대소문자는 물론 공백이 포함되어 있다면 이것까지 정확히 같은 문장을 의미한다. 만약 이전 문장에 대소문자는 물론 공백이 하나 추가되었다면 오라클은 다른 문장으로 인식하게 된다. 따라서 where 조건에 바인드 변수를 사용하면 비교값이 달라지더라도 동일한 문장으로 인식을 하여 처리 하지만 , 바인드 변수를 사용하지 않는다면 모두 다른 문장으로 인식하게 되므로 처리속도가 느려지게 된다.

2. 가급적 where 조건에서는 인덱스 컬럼을 모두 사용한다.

인덱스를 만들어 놓더라도 where 조건을 어떻게 명시하느냐에 따라 옵티마이저가 인덱스를 사용할 수 도 있고 사용하지 않을 수도 있다. 예를 들어 A 와 B라는 컬럼에 인덱스를 만들었는데, where 조건에 A컬럼만 사용한다면 인덱스를 타지 않게 된다.

3. 인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자(=)를 사용하라.

비록 인덱스 컬럼을 where 조건에 모두 명시하였더라도 like와 같은 연산자를 사용하면 인덱스 효율이 떨어진다. like외에도 is null, is not null, not in 등이 사용되었을 경우에도 마찬가지 현상이 발생한다.

4. 인덱스 컬럼은 변형하여 사용하지 않도록 한다.

where 조건에서 인덱스 컬럼을 사용했고, 동등 연산자를 사용했다 하더라도 인덱스 컬럼에 변형을 가하게 되면 인덱스를 사용하지 못한다.

5. OR 보다는 AND를 사용하라.

옵티마이저 OR-Expansion 처리는 OR 연산자로 연결된 쿼리를 UNION ALL로 변환하므로 OR 보다는 AND를 사용해야 성능 좋은 쿼리를 작성할 수 있다.

6. 그룹핑 쿼리를 사용할 경우 가급적 HAVING 보다는 where 절에서 데이터를 필터링 하라.

그룹핑 쿼리에서는 where 조건 뿐만 아니라 HAVING 절에 조건을 주어 반환하는 로우를 필터링 할 수 있다. 그룹핑 쿼리 처리순서는 where 조건이 먼저 처리 되므로 가급적 필터링 할 대상은 where 조건에서 처리할 수 있게 쿼리를 작성하도록 한다. HAVING 절은 이미 where 절에서 처리된 로우들을 대상으로 또 조건을 검사하기 때문에 좋은 성능을 발휘하기가 힘들다.

7. DISTINCT는 가급적 사용하지 않는다.

DISTINCT 키워드는 내부적으로 정렬 작업을 수반하기 때문에 꼭 필요한 경우가 아니라면 사용하지 않는다.

8. IN, NOT IN 대신 EXISTS와 NOT EXISTS를 사용하라.

where 조건에서 in 이나 not in 연산자 보다는 EXISTS나 NOT EXISTS를 사용하는 것이 더 좋은 성능을 발휘한다.

9. SET 연산자 사용시 UNION 대신 UNION ALL을 사용하라.

union 연산자는 연결된 쿼리에서 동일한 로우는 제거하고 한 로우만 반환하게 된다. 따라서 추가적으로 필터링하는 로직이 숨어 있으므로 union all 보다는 비용이 높을 수 밖에 없다.