Initialize

나름 프로그램 만들다보면, 성능에 신경쓰지 못하고 그냥 넘어가는 경우가 있다.

그리고 나중에, 어느부분에서 느린지 프로그램도 프로그램이지만 DB에서 찾아야 하는 경우도 있다.

그럴때 아래 쿼리를 이용해서 많은 블록에 접근하여 성능에 영향을 주는 SQL을 찾아낼수 있다.

SELECT /*+ NO_MERGE(v) */
       s.HASH_VALUE
     , s.MODULE
     ,s.last_active_time
     , 
     ( SELECT username 
         FROM DBA_USERS 
        WHERE user_id =s.parsing_user_id
      ) username
      , executions
      , ROUND(elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000),1) elapsed_t
      , ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) buffer_gets_per_exec
      , rows_processed
      , sql_text
  FROM
     ( SELECT /*+ NO_MERGE(V1) */
              MAX(HASH_VALUE) HASH_VALUE
         FROM
            (
              SELECT HASH_VALUE, SQL_TEXT
                FROM V$SQL S
               WHERE 1=1
                 AND ( ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) > 10000
                       OR elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000) >10 )
                 AND (module IS NULL
                      OR (module NOT LIKE 'TOAD%'
                     AND module NOT LIKE 'Orange%'
                     AND module NOT LIKE 'Golden32.exe%'
                     AND module NOT LIKE 'PL/SQL Developer%'
                     AND module NOT LIKE 'T.O.A.D%'
                     AND UPPER(module) NOT LIKE 'SQL*PLUS%')
                     )
                 AND PARSING_USER_ID IN
                ( SELECT user_id
                    FROM DBA_USERS
                   WHERE username NOT IN ('SYS','SYSTEM')
                )
             ) V1
        GROUP BY SUBSTR(SQL_TEXT,1,150)
     ) v,
       V$SQLAREA s
 WHERE v.hash_value=s.hash_value
  AND parsing_user_id IN
    ( SELECT user_id
        FROM DBA_USERS
       WHERE username NOT IN ('SYS','SYSTEM')
    )
  AND s.module NOT LIKE 'SmartSQL%'
ORDER BY buffer_gets;

위 쿼리의 제목은 "쿼리수행시간이 10초이상걸리거나, 10000블록 이상 접근하는 쿼리 " 이 정도가 되겠다.

※아래는 이해를 돕기위한 설명.

- Buffer Gets : 해당 SQL이 수행되면서 액세스한 블록의 수이며 V$SQL 등에서는 Executions가 곱해져서 보여진다.

- Executions : 해당 SQL이 수행된 횟수를 의미하며 변수 처리를 바인 드 변수 처리로 수행하지 않으면 다른 SQL로 집계된다.

- Elapsed Time : 해당 SQL이 수행된 수행 시간이며 V$SQL에서는 1,000,000으로 나눠야 초가 된다. 물론 Executions로 곱해져 있기 때 문에 Executions로도 나눠야 1회 수행 시 수행 시간이 된다.

- (ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,NULL,1, EXECUTIONS),1) > 10000 - 10,000 Block 이상 액세스한 SQL

- ELAPSED_TIME/(DECODE(EXECUTIONS,0,1,NULL,1,EXECUT IONS)*1000000) >1- 수행시간 1초 이상

- order by BUFFER_GETS; - Block 액세스에 수행 횟수를 곱한 순으로 결과 추출

프로그램(DB) 유지보수에 큰 도움이 되길 ..

프로그램에서 업무를 수행하는 모듈(기능)은 다양하게 개발하여 처리할수 있다.

예를들어 뷰에서 자바스크립트로 만들어서 처리할수도 있고, 백단에서 자바등으로 만들어서 처리할수 있으며, 

DB의 프로시저 및 펑션등으로도 할수 있다.

만약 특정업무를 수행하는 기능에서 DB작업시 테이블명이나 WHERE조건이 변경되어야 할때

SQL을 정의해둔 XML이나 JSP등의 경우엔 IDE에서 통합검색으로 충분히 찾아서 조치할수 있지만

해당 소프트웨어가 프로시저와 펑션을 사용하는 업무가 있다면 IDE에서는 찾기가 어렵다.

찾기가 어렵다는건, 유지보수에서 놓치게 된다는 것이고 또다시 버그가 발생할거란 소리다.

이런경우를 최대한 막기위해선 프로시저와 펑션의 내부를 검색하여 찾아내야한다.

바로 아래의 방법들로 가능하다.

/*
1. PROCEDURE 내용 검색하기
*/
SELECT *
FROM  USER_SOURCE
WHERE  TYPE = 'PROCEDURE' --패키지나 다른 부분을 검색 하고 싶다면 대문자로 입력한다.
AND TEXT LIKE  '%INS_ID%' --찾고자 하는 내용을 입력한다.
;

/*
2. FUNCTION 내용 검색 : 위와 동일한 방법으로 사용한다.
*/
SELECT *
FROM  USER_SOURCE
WHERE TYPE = 'FUNCTION' 
AND TEXT LIKE  '%REG%'  
​;

/*
번외로, 테이블 생성시 중복되는 테이블명은 없는지? 
아니면 유지보수시에 이름이 비슷하면서 유용한 함수는 없는지 찾아보고 싶을때 쓰면 괜찮은 방법이다.
*/
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE '%FN_GET_%' --찾고싶은 테이블명 또는 함수 명 등등
AND OBJECT_TYPE = 'TABLE' -- TABLE, INDEX,FUNCTION, PROCEDURE 다양하게 검색할 수 있다.
;

생각보다 유용하게 쓸수있다.
눈으로 보기보단, 위에 입력된 쿼리들을 직접 입력해보길 권장한다.

보통 개발자들은 DB까지 다룬다.
전문적인 DBA가 아니다 보니, 바쁘게 개발하면서 실수로 데이터를 날려먹거나
UPDATE를 잘못쳐서 잘못 변경되는 일이 많다.
개발DB에 붙여서 쿼리실행한줄 알았더니, 알고보니깐 운영DB던가...

다른 큰 기업들은 개발자들과 DBA가 정확하게 나뉘어서 
개발자들이 DB까진 컨트롤하진 않지만, 그게 아닌 사람들은 DB까지 많이 다룬다.
(지식만 있어서는 안되고, 실제 쿼리작성 및 실행함.  단, 모든 중소기업이 다 그렇진 않음)

어찌됐든, 실수로 UPDATE나 DELETE해버린 내역을 복구하기 위한 방법이다.
(오라클에 한함, 다른DBMS는 모르겠음)

먼저 테이블 명이 SAMPLE_TABLE이라는게 있다고 하자.
여기서 내가 WHERE조건이 ABC라는 키값의 값을 실수로 DELETE했다고 치자, 그 다음에 아래 쿼리를 조회하면?

SELECT  *  
FROM SAMPLE_TABLE
WHERE  = 'ABC'

당연히 위 쿼리의 결과값은 당연히 아무것도 나오지 않을것이다.

하지만, FROM 절의 테이블명 뒤에 아래 구문을 붙여준다면?

AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)

(▼붙여서 실행)

SELECT  *  
FROM SAMPLE_TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)
WHERE  = 'ABC'

내가 삭제하기 전의 값이 추출된다.(10분 이전의 데이터) 물론 10분만 되는것은 아니고 그 이상 가능하다. 
단위는 SECOND, MINUTE, HOUR, DAY로 바꿔 쓸 수 있다.

복구를 하려면 , 아래쿼리 처럼 하면 되겠다.

INSERT INTO SAMPLE_TABLE 
SELECT  *  
FROM SAMPLE_TABLE  AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)  
WHERE ID = 'ABC'​

*애스터리스크를 사용한건 같은테이블에 순서대로 변경없이 모든컬럼에 넣기 위함이다.

컬럼별로 값을 다르게 하거나, 지정하려면 컬럼명을 기재해야한다. 다른테이블에 넣을때도 마찬가지다.
다른 테이블에 넣을땐, INSERT INTO 다른테이블명 .... 을 써서 INSERT한다.

실수로 날려먹었다고 당황하지말자.