[Oracle] DB에서 수행되는 SQL을 로그파일로 생성하기

특정 시간, 또는 특정 주기마다 Oracle 에 어떤 문제가 발생을 하는데
프로그램상으로는 별다른 쿼리를 날리지 않는데 DB서버에 부하가 많이 증가하는 경우

Oracle 내부적으로 다른 쿼리를 수행하는지 확인하기 위해 아래와 같이 10분 간격으로 수행되는 쿼리를 로그파일로 저장을 시켰다.

 과거 수행된 내역의 SQL을 추출하는 쿼리가 있기는 하지만..
 시간(구간)을 지정해야 하고 한눈에 보기에 편하지가 않아 별다른 솔루션이 없다면 이와 같은 방법으로 구축을 해 놓으면 한눈에 보기 편하게 된다.
 단점으로는 10분간격(변경가능)으로 수행중인 쿼리이다보니 그 사이에 수행된 쿼리는 로그에 쌓이지 않는다.

1. DB서버에 쉘을 만들어 Cron 등록

[id@SERVER_NAME ~]$ crontab -l
# logging SQL Query
*/10 * * * * /home/id/sqlStatic.sh
10 0 * * * /home/id/sqlStatic_log.sh
[id@SERVER_NAME ~]$

2. sqlStatic_log.sh 파일 내용
 (전날의 로그파일에 log 라는 확장자를 붙여준다. - 매일 0시 10분에 수행)

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic_log.sh
#!/bin/sh
mv /home/id/sqlStat/sqlStatic_$(date -d '1day ago' +%y%m%d) /home/id/sqlStat/sqlStatic_$(date -d '1day ago' +%y%m%d).log
[id@SERVER_NAME ~]$

3. sqlStatic.sh 파일 내용
  (현재 수행되는 SQL Query 의 Logging을 남긴다. - 매 10분마다 수행)
1004lucifer

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic.sh
#!/bin/sh

# 환경설정
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=orcl
PATH=$PATH:/oracle/app/11.2.0/grid/bin
export ORACLE_HOME
export ORACLE_SID

# 용량을 위해 log 확장자가 붙은 파일을 압축한다.
gzip /home/id/sqlStat/*.log 2> /dev/null

# sqlStatic.sql 파일을 수행한다. (/home/id/sqlStat/spool.lst 파일에 수행중인 query가 저장된다. )
sqlplus dbId/dbPw@host/orcl @/home/id/sqlStatic.sql > /dev/null

# 로그파일에 현재 수행된 시간을 추가한다.
echo $(date +%y\.%m\.%d\ %H\:%M\:%S) >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)

# 현재 수행되는 query 를 로그파일에 추가한다.
cat /home/id/sqlStat/spool.lst >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)

# 날짜 가독성을 위해 빈줄을 로그파일에 추가한다.
echo -e \\n\\n\\n >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)
[id@SERVER_NAME ~]$

4. sqlStatic.sql 파일 내용
  (현재 수행중인 query를 뽑아내기 위한 쿼리 - /home/appadmin/sqlStat/spool 파일에 query 내용이 저장된다.)

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic.sql
-- set define off;
set null @
set pages 0
set colsep '^'
set trimspool on
set lines 30000
set termout off
set feed off
spool /home/id/sqlStat/spool
SELECT a.osuser,
       a.SID,
       a.serial#,
       a.status,
       b.sql_text
  FROM v$session a,
       v$sqlarea b
 WHERE a.sql_address = b.address
       AND status = 'ACTIVE'
;
spool off
exit
[id@SERVER_NAME ~]$

5. 파일 생성 결과
  생성된 임시파일(spool.lst)과 로그파일(sqlStatic_160913)
1004lucifer

[id@SERVER_NAME ~]$ ls -l /home/id/sqlStat/
합계 52
-rw-r--r-- 1 id id  4500  9월 13 17:00 spool.lst
-rw-r--r-- 1 id id 42899  9월 13 17:00 sqlStatic_160913
[id@SERVER_NAME ~]$

확인

 sqlStatic.sql 파일의 내용을 보면 각각의 컬럼에 대해서 구분자로 '^' 문자가 들어가 있음을 볼 수 있다.
 파일에 cvs 확장자를 붙여서 엑셀로 띄운 뒤 '^' 문자로 구분자를 변경하여 열면 한눈에 보기가 좋게 표시가 된다.