[DB health check]
주기적으로 DB 진단.
1) alert log 분석 : DB 물리적 변경, 에러, 장애 진단.
-- crontab으로 설정해둬서 alertlog 날짜 나누기
cp alert_db1.log alert_db1.log_20241226 ; echo > alert_db1.log2) AWR 분석 : DB 성능 진단(hit ratio, physical/logical read 비율 등)
3) trace file 분석 : 특정 에러에 대한 세부 진단.
[AWR]
oracle 성능 진단 툴.
성능 진단 결과를 html이나 text 파일 형태로 다운 가능.
oracle 설치 시 자동으로 수행.
default snapshot 보관 주기 : 8일(= 8일의 기간동안만 성능 분석이 가능하다는 의미)
1. snapshot 확인
select *
from dba_hist_snapshot
order by snap_id;2. snapshot 주기 확인
select DBID, -- db 식별 id
SNAP_INTERVAL, -- snapshot 생성 주기(default : 1시간)
RETENTION, -- snapshot 보관 주기(default : 8일)
topnsql -- 수행에 제일 오래 걸린 sql
from dba_hist_wr_control;3. AWR report 생성
cd $ORACLE_HOME/rdbms/admin
ll awrrpt*
-rw-r--r--. 1 oracle oinstall 9963 Aug 11 2016 awrrpti.sql -- RAC용
-rw-r--r--. 1 oracle oinstall 7857 Aug 11 2016 awrrpt.sql -- single DB용
> @awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type: html(직접 치거나 그냥 enter 누르기)
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
1780532810 DB1 1 db1 db1
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1780532810 1 DB1 db1 oel7
Using 1780532810 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1 (직접 침. 중간에 shutdown된적이 없는 기간을 입력해야됨)
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
db1 DB1 110 26 Dec 2024 09:54 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 110(시작 snapshot id)
Begin Snapshot Id specified: 110
Enter value for end_snap: 111(종료 snapshot id)
End Snapshot Id specified: 111
지금 스냅샷이 1개뿐이라서 안되는데 아무튼 html파일 생성되면 그거 winscp이용해서 windows로 옮겨서 열어보면 됨.4. AWR report 설정 변경
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>30*24*60, topnsql=>'100');
-- 조회 - 2번에 쿼리
select DBID, -- db 식별 id
SNAP_INTERVAL, -- snapshot 생성 주기(default : 1시간)
RETENTION, -- snapshot 보관 주기(default : 8일)
topnsql -- 수행에 제일 오래 걸린 sql
from dba_hist_wr_control;[ORACLE 스케줄러]
oracle 에서 제공하는 작업 예약 기능(os의 crontab과 유사하지만 더 복잡하고 어렵다).
dbms_job나 dbms_scheduler를 사용하는 2가지 방법이 있다.
1. dbms_job
– pl/sql로 짜여진 프로시저만 등록 가능(os job 등록 불가)
– snp 백그라운드 프로세스에 의해 동작(job_queue_processes 파라미터 통해 확인 가능)
– dbms_job을 사용한 스케줄 등록을 위해서는 create any job 권한 필요.
2. dbms_scheduler
– 더 다양한 형태의 작업을 등록 가능. (작업시간, 작업형태 등)
– os 프로그램에 대한 스케줄링 가능.
[dbms_job]
1. 프로세스 조회
select value
from v$parameter
where name = 'job_queue_processes';
-- 4000개 작업 등록 가능2. dbms_job 기능
1) submit : 작업 등록
2) remove : 작업 제거
3) change : 작업 변경
4) next_date : job에 등록된 작동 시간 변경
5) interval : 작업 주기 설정
6) what : 작업 내용(프로시저명)
7) run : 작업 동작
3. 작업 등록 문법
begin
dbms_job.submit(
job => job_id,
what => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Hello, World!''); END;',
next_date => SYSDATE + 1/24, -- 1시간 후에 실행
interval => 'SYSDATE + 1/24') -- 매 1시간마다 실행
end;
/[실습 – 시퀀스를 사용한 자동 입력]
1. 테이블 생성
create table dbms_job_test1(no number, name varchar2(10));2. 시퀀스 생성
create sequence seq_job_test1; -- 1부터 1씩 증가하는 기본 시퀀스
select *
from dba_sequences
where sequence_name = 'SEQ_JOB_TEST1';3. pl/sql 프로시저 생성
create or replace procedure insert_job_test1
is
begin
insert into dbms_job_test1 values(seq_job_test1.nextval, dbms_random.string('A',6));
commit;
end;
/4. 작업 등록
vi job.sql
begin
dbms_job.submit(
job => :job_id,
what => 'insert_job_test1;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24/60');
end;
/
> variable job_id number;
> @job.sql
PL/SQL procedure successfully completed.
> print job_id
JOB_ID
----------
1
> commit;5. 작업 등록 확인
-- 등록된 작업 확인
select *
from dba_jobs;
-- 테이블 확인
select *
from dbms_job_test1;6. 작업 삭제
exec dbms_job.remove(1);
-- 괄호 안에 숫자는 job_id 입력해주기
commit;
-- 확인
select *
from dba_jobs;7. 작업 수정
지금은 삭제해서 수정 못하지만 만약 한다면 이런 식으로
exec dbms_job.change(2, 'insert_job_test1;', SYSDATE, 'SYSDATE + 1/24');
-- 주기를 1시간으로 변경[실습 – dblink를 사용한 주기적 데이터 적재]
orcl DB의 emp 데이터를 매분 db1 DB의 dbms_job_test1 테이블에 적재
1. 테이블 생성
create table dbms_job_test2
as
select * from scott.emp@dblink_orcl
where 1=2;
select * from dbms_job_test2;2. 프로시저 생성
create or replace procedure insert_job_test2
is
begin
insert into dbms_job_test2
select empno,ename,job,mgr,hiredate,sal,comm,deptno,sysdate
from scott.emp@dblink_orcl;
commit;
end;
/3. 작업 등록
vi job.sql
begin
dbms_job.submit(
job => :job_id,
what => 'insert_job_test2;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24/60');
end;
/
sqlplus system/oracle
variable job_id number;
@job.sql
print job_id
commit;4. 확인
select *
from dba_jobs;
select *
from dbms_job_test2;
-- procedure 확인 쿼리
SELECT OBJECT_NAME AS PROCEDURE_NAME, OWNER
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
and owner = 'SYSTEM'
ORDER BY OWNER, OBJECT_NAME;[dbms_scheduler]
1. dbms_job 사용법
1) dbms_scheduler.create_job() : 작업 등록
2) dbms_scheduler.enable() : 작업 활성화
3) dbms_scheduler.disable() : 작업 비활성화
4) dbms_scheduler.drop_job() : 작업 삭제
2. dbms_job 작업 등록 방법
begin
dbms_scheduler.create_job(
job_name => '',
job_type => '',
job_action => '',
start_date => '',
repeat_interval => ''); -- monthly, ..., secondly
end;
/[실습]
1. table 생성
create table dbms_job_test3(no number, name varchar2(10));2. sequence 생성
create sequence seq_job_test3;3. procedure 생성
create or replace procedure insert_job_test3
is
begin
insert into dbms_job_test3 values(seq_job_test3.nextval, dbms_random.string('A',10));
commit;
end;
/4. 작업 등록
begin
dbms_scheduler.create_job(
job_name => 'job_insert_job_test3',
job_type => 'plsql_block',
job_action => 'begin insert_job_test3; end;',
start_date => systimestamp,
repeat_interval => 'freq=secondly; interval=30');
end;
/5. 작업 활성화
select OWNER,
JOB_NAME,
JOB_ACTION,
start_date,
repeat_interval,
enabled
from dba_scheduler_jobs
where owner='SYSTEM';6. 확인
select * from dbms_job_test3;7. job 비활성화
exec dbms_scheduler.disable('JOB_INSERT_JOB_TEST3');
select * from dbms_job_test3;8. job 삭제
exec dbms_scheduler.drop_job('JOB_INSERT_JOB_TEST3');[process]
1. user process
– 단말기(서비스 요청자)
– 앱이나 웹을 통해 서비스를 요청하는 단말기, orange, sqlplus 접속 세션.
– dbms는 user process의 직접적인 관찰 불가. server process 통해서 간접 모니터링.
2. server process
– user process 를 담당하는 서버 내 프로세스.
– dbms 내부에서 모니터링 가능(v$session)
– 서버에서 ps로 확인 가능
– dedicated vs shared mode 로 구분
** server process mode
1) dedicated mode (전용 모드)
– default.
– 하나의 user process에 하나의 server process를 할당하는 방식.
– session 관리 용이(자원 점유 현황 조회, 세션 정리 등 가능) – 1대1이기 때문에
– 접속자가 많은 환경일 경우 너무 많은 server process가 할당됨 -> 메모리 부족 유발
2) shared mode (공유 모드)
– 여러 user process를 하나의 server process로 할당하는 방식
– user process에 대한 모니터링, 관리가 어려움.
– 접속자가 많은 환경일 경우 메모리 효율적 사용 가능(메모리 과부하 가능성 줄어듬)
– dispatcher에 의해 세션 우선순위 결정
[실습]
1. 프로세스 수 확인
select *
from v$session
where username is null; -- dbms 자체적으로 사용하는 서버 프로세스
select *
from v$session
where username is not null; -- 단말기가 사용하는 서버 프로세스
select *
from v$process; -- 자체적으로 사용하는 서버 프로세스 + 단말기가 사용하는 서버 프로세스2. 최대 프로세스 수 확인
select value
from v$parameter
where name='processes';3. process 수 변경
alter system set processes = 500; -- error, 동적 변경 불가능
alter system set processes = 500 scope = spfile;4. DB 재기동 후 확인
> shutdown immediate
> startup
select value
from v$parameter
where name='processes';[select문 실행 원리]
parse -> bind -> excute -> fetch
1. parse
– syntax check(문법 검사) + sementic check(객체 검사)
– 자주 사용하는 객체 정보를 dictionary cache에 저장.
– 오류가 발생하지 않는 경우 hash함수에 의해 sql 판별. hash value가 같은 경우 같은 sql로 판단 -> 실행계획 공유(cursor sharing)
** hash 함수 판별 규칙
1) 대소문자 구분
select ora_hash('select * from emp') from dual; -- 3677333291
select ora_hash('select * from EMP') from dual; -- 125473356 2) 공백 구분(띄어쓰기, 엔터)
select ora_hash('select * from EMP') from dual; -- 125473356
select ora_hash('select * from EMP') from dual; -- 15891951253) literal sql 구분(상수 구분)
-> 상수처리된 sql들은 각각 다른 hash value 리턴 -> 실행계획 공유 불가
* 해결 방법
– 상수를 bind변수 처리. (where empno = :vempno)
– cursor sharing mode로 변경.
select ora_hash('select * from emp where empno = 1111') from dual; -- 2745642225
select ora_hash('select * from emp where empno = 2222') from dual; -- 737833702. bind
변수에 상수값을 대입하는 단계.
3. excute
필요한 데이터를 먼저 db buffer cache에서 찾고 있으면 사용(logical read).
없으면 datafile에서 불러옴(physical read).
db block size만큼 disk I/O 발생 (default size = 8k)
4. fetch
사용자가 원하는 데이터만을 고르는 과정.
block 단위로 I/O가 발생하는데, 원하는 데이터를 해당 block에서 골라서 리턴해야 하므로 fetch 필요.
[DML문 실행 원리]
parse -> bind -> excute
1. parse
2. bind
3. excute
[cursor sharing]
hash 함수에 의해 리턴되는 hash value가 같은 sql 구문에 대해 library cache에 있는 실행계획을 공유(재사용)하는 과정.
** cursor sharing mode : 실행계획 공유에 대한 규칙 결정하는 모드.
1) exact : 정확히 일치(대소, 공백, 상수항)하는 sql에 대해서만 실행계획 공유
2) similar : 비슷한 sql에 대해 실행계획 공유(12c 이상에서는 deprecated 사용하지 않음.)
3) force : literal sql에 대해 실행계획 공유
select *
from v$parameter
where name = 'cursor_sharing';
-- EXACT (default)[실습 – exact mode일 때 parsing 확인]
1. shared pool flush (초기화)
alter system flush shared_pool;2. 다양한 쿼리 실행
select /* parse_test1 */ * from scott.exam_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9412;
-- hard parsing3. parsing 결과 조회
select /* aaaa */
SQL_TEXT,
sum(EXECUTIONS) as 전체실행횟수,
count(*) as 하드파싱횟수,
sum(EXECUTIONS) - count(*) as 소프트파싱
from v$sqlarea
where SQL_TEXT like '%parse_test1%'
and sql_text not like '%aaaa%'
group by sql_text;[실습 – force mode일 때 parsing 확인]
결과 : 상수값을 바인딩 처리하면서 널널히 봐줌. 대소구분은 여전히 함.
0. cursor sharing mode 변경(동적 변경 가능)
alter system set cursor_sharing=force;
select value from v$parameter where name = 'cursor_sharing';1. shared pool flush (초기화)
alter system flush shared_pool;2. 다양한 쿼리 실행
select /* parse_test1 */ * from scott.exam_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9411;
-- hard parsing
select /* parse_test1 */ * from scott.EXAM_01 where studno=9412;
-- soft parsing3. parsing 결과 조회
select /* aaaa */
SQL_TEXT,
sum(EXECUTIONS) as 전체실행횟수,
count(*) as 하드파싱횟수,
sum(EXECUTIONS) - count(*) as 소프트파싱
from v$sqlarea
where SQL_TEXT like '%parse_test1%'
and sql_text not like '%aaaa%'
group by sql_text;
-- 결과 : 상수값을 바인딩 처리하면서 널널히 봐줌. 즉, 4번만 소프트파싱됨. 대소구분은 여전히 함.
select /* parse_test1 */ * from scott.EXAM_01 where studno=:"SYS_B_0" 2 1 1
select /* parse_test1 */ * from scott.EXAM_01 where studno=:"SYS_B_0" 1 1 0
select /* parse_test1 */ * from scott.exam_01 where studno=:"SYS_B_0" 1 1 04. 원상복구
alter system set cursor_sharing = exact;