0219
■ 오라클 관리 수업 복습
1장: 오라클 아키텍쳐 (5문제) --------> 오라클 구조 암기(A4지 3~4장)
2장: 오라클 소프트웨어 설치 (1문제) ----> 기본기
3장: dbca 를 이용해서 db 생성 (1문제) ----> 기본기
4장: 오라클 인스턴스 관리 (파라미터 파일) (3문제) -->오라클 설정 변경할 일
5장: ASM 인스턴스 관리 (3문제)
6장: 오라클 네트워크 관리 ---> 일하러 나가서 첫날 셋팅해야할 일
7장: 테이블 스페이스 관리 (3문제) ---> DBA 가 일상적으로 하는일
8장: 유져 보안 관리 (2문제) ---> si형 DBA 가 일상적으로 하는일
9장: 데이터 동시성 관리 (lock) ---> si형, sm형 DBA 가 일상적으로 하는일
10장: 언두 데이터 관리 -----> si형, sm형 DBA 가 일상적으로 하는일
11장: 오라클 감사(audit) ---> 의심스러운 작업을 모니터링 하고 개인 정보보호법에 의해서
우리 회사의 데이터를 지키는 기술적인 방법
12장: 데이터베이스의 자동화 기능
오라클 아키텍쳐 중에 백그라운드 프로세서 하나 설명해봐라 ~ 하면 할 수 있어야지 !
언두 테이블 스페이스가 풀나면 아무것도 못해
데이터 마이그레이션 전에 ?
언두 테이블스페이스에 여유공간이 있는지 확인 !!!!
매일 밤 10시에 자동으로 수행되는 3가지 ?
- 통계정보 자동 수집
- 여유 공간 확보
- 자동 sql 튜닝 ---> 그날 하루에 발생했던 악성 SQL TOP 10개를 골라서 자동으로 튜닝
▣ 예제94. 과거의 DB 에 어떠한 이슈가 있었는지를 분석하는 세가지 레포트를 생성할 수 있어야 해요.
AWR
메모리 내용에 대해 사진을 찍음 - 그 스냅샷은 테이블 스페이스에 SYSAUX 테이블스페이스 에 들어있음.
mmon 이라는 오라클 백그라운드 프로세서가 1시간에 1번씩 메모리의 성능 정보를 수집해서
awr 레퍼지토리에 이것을 8일간 보관한다.
이 주기는 변경 가능합니다. 1시간을 30분으로 변경할 수 있고 8일을 30일로 변경할 수 있습니다.
아래의 report 를 dba 가 활용해야 하는 때 ?
월요일 아침에 출근했는데 새벽에 수행되었던 배치 프로그램이 아침 6시 전에 다 끝나는데
아직도 돌고 있다고 하면서 어떤 프로그램이고 어떤 sql 인지 알아내달라고 요청이 들어오면 ?
AWR 레포지토리의 성능 데이터를 분석하는 3가지 레포트 ?
- awr report : 과거의 성능 이슈를 확인
SQL> @?/rdbms/admin/awrrpt.sql
- addm report : 성능 이슈에 대한 해결 방법을 오라클이 직접 알려줌
SQL> @?/rdbms/admin/addmrpt.sql
- awr 비교 report : 정상적인 시간대의 db 상태와 문제가 되는 시간대의 db 상태를 비교
SQL> @?/rdbms/admin/awrddrtp.sql
■ 실습1: awr report 생성하기
- 수동으로 사진을 찍습니다.(메모리의 성능정보 수집)
PROD(SYS) > exec dbms_workload_repository.create_snapshot;
PROD(SYS) > ed snap.sql
PROD(SYS) > @snap.sql
- 악성 sql 을 수행합니다.
PROD(SCOTT) > select count(*) from emp, emp, emp, emp, emp, emp;
- 수동으로 사진을 찍습니다.
- awr report 를 생성합니다.
PROD(SYS)> @?/rdbms/admin/awrrpt.sql
■ 실습2: addm report 생성하기
해결 방법을 알려주는 레포트
- sys 유져에서 다음과 같이 addm report 를 생성하는 스크립트를 수행합니다.
PROD(SYS)> @?/rdbms/admin/addmrpt.sql
문제1. 아래의 부하를 일으키고 addm report 를 떠서 부하의 원인과 해결방법을
알아내시오 !
- ADDM 레포트 사용방법
- 수동으로 사진을 찍는다
SQL> exec dbms_workload_repository.create_snapshot;
-
db 에 부하를 준다
하드파싱을 과도하게 유발하는 스크립트를 돌린다.
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 10000
loop
open l_rc for
'select object_name from all_objects where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line ( round( (dbms_utility.get_time - l_start)/100, 2) || 'seconds');
end;
/
3.. 수동으로 사진을 찍는다
SQL> exec dbms_workload_repository.create_snapshot;
- ADDM report 를 생성한다.
SQL> @?/rdbms/admin/addmrpt.sql
바인드 변수를 써야되는데 메모리에서 다시 파싱하고 파싱하고... 공유가 안된다 ... ? 이게 무슨말이에요
메모리에 있는 내용을 보고싶다면
prod(sys) > select sql_text
from v$sql
where sql_text like 'select object_name%';
위의 2개의 sql 은 서로 다른 SQL 이라서 수행할 때 마다 다시 파싱합니다.
파싱은 SQL 을 기계어로 변경하는 작업인데 이 때 CPU를 많이 사용합니다.
그래서 오라클은 Parsing 을 최소화 하려고 shared pool 이라는 메모리를 두고서 여기에 sql 을 올려놓고
다음번에 똑같은 sql 이 들어오면 파싱과정을 생략하고 바로 실행하게 합니다
그런데 아래와 같이 작성하면 다시 파싱해야 합니다. (리터럴 sql)
select empno, ename, sal from emp where empno=7788;
select empno, ename, sal from emp where empno=7902;
다시 작성하면 해결될 수 있습니다.
select empno, ename, sal from emp where empno=:v_empno using i;
바인드 변수(:v ) 를 사용하여 해결한다.
리터럴 sql -> 바인드 변수 로 튜닝 !
바인드변수 사용하니까 시간이 줄어든 것을 확인
문제4. cursor_sharing 을 force 로 변경했으므로 오라클이 알아서 literal SQL 을 바인드 변수로 변경했는지 확인하시오
connect scott/tiger
select empno, ename, sal from emp where empno = 7788;
select empno, ename, sal from emp where empno = 7902;
select sql_text
from v$sql
where sql_text like 'select empno, ename%';
select empno, ename, sal from emp where empno = :"SYS_B_0"
설명: 리터럴 SQL을 오라클이 알아서 바인드 변수로 변경했습니다.
그러면서 SQL이 공유되게 했습니다.
결론적으로 하드 파싱을 유발하지 않게 되었습니다.
문제5. 여러분들이 직접 리터럴 SQL 을 2개 작성해서 실행하고 오라클이 알아서 바인드 변수로 변경했는지 확인해보세요
select ename, sal from emp where sal = 3000;
select ename, sal from emp where sal = 5000;
select sql_text
from v$sql
where sql_text like 'select ename, sal%';
문제6. 아까 26초 넘게 수행되었던 리터럴 sql 을 1000번 수행하는 PL/SQL 을 수행해보시오 !
짱 빨리 됨.
문제7. 그럼 다시 cursor_sharing 파라미터를 exact 로 변경하고 디비를 내렸다 올리세요
cursor_sharing 파라미터를 exact 으로 변경한다는 것은 오라클이 리터럴 sql 을 바인드 변수로 변경 안하는 것 입니다.
그냥 리터럴 sql 자체로 수행되게 하는 것 입니다.
▣ 예제95. 현재 발생하고 있는 이슈에 대해서 확인하려면 ash 레포트를 생성할 수 있어야 해요)
ASH( Active Session History) 는 현재 발생하고 있는 악성 sql 과 성능 이슈에 대한
정보를 ASH 메모리 영역에 저장하고 성능 분석 시 사용하는 기능입니다.
SQL> @?/rdbms/admin/ashrpt.sql
■ 실습.
- scott 유져에서 아래의 악성 SQL을 수행합니다.
select count(*) from emp, emp, emp, emp, emp, emp, emp, emp;
- 다른 터미널 창에서 sys 유져에서 ash report 를 생성합니다.
PROD(SYS)> @?/rdbms/admin/ashrpt.sql
현재라서 스냅샷 안찍어도 됨
▣ 예제96. 사진을 찍는 주기 와 성능 정보를 저장하는 기간을 변경할 수 있어요
그림 설명: 11g 를 기준으로 mmon 이라는 백그라운드 프로세서가 1시간에 한번씩 성능 정보를 수집하고
8일 동안 awr repository 에 저장합니다.
이 주기는 변경 가능합니다.
현장에서 EM 잘 안쓴다.
주로 명령어로 주기 관리 한다..
■ 실습. 성능 수집 주기와 저장 기간 변경하기
#1. 현재 세팅되어있는 성능 수집 주기를 확인합니다.
select snap_interval, retention
from dba_hist_wr_control;
1시간
8일 임을 확인
#2. 수집 주기를 1시간에서 10분으로 변경합니다.
exec dbms_workload_repository.modify_snapshot_settings(interval=>10);
#3. 잘 변경되었는지 확인합니다.
select snap_interval, retention
from dba_hist_wr_control;
#4. 보관 주기를 8일이 아니라 30일로 변경합니다.
exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);
#5. 변경되었는지 확인합니다.
select snap_interval, retention
from dba_hist_wr_control;
1시에 snap
1시 이후에 작되어 30분에 악성 sql 완료되면
2시에 snap
--- > awr 레포트에 안나옴.
▣ 예제97. 사진을 찍지 않게 설정하거나 더 많은 성능정보가 수집되게 설정할 수 있어요
■ 실습.
- 현재 스냅샷 설정 값을 확인합니다.
show parameter statistics_level
- 사진을 찍지 않게끔 설정 합니다.
alter system set statistics_level=basic scope=spfile;
db 내렸다가 올리기
- 더 많은 성능 정보가 수집되게끔 설정합니다.
PROD(SYS) > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
PROD(SYS) > startup
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
위와 같이 파라미터 값을 잘못 변경해서 에러가 발생하면서 db 가 안올라오면 다음과 같은 순서로 조치한다
- spfile 을 가지고 pfile 을 생성합니다.
create pfile from spfile;
- pfile 을 열어서 내용을 원래대로 수정합니다.
exit;
dbs
vi initPROD.ora
statistics_level=typical
- prfile 을 가지고 spfile 만들기
sys> create spfile from pfile
- startup 합니다.
show parameter statistics_level
※ memory_target 값 또는 sga_target 값이 설정되어있으면 statistics_level 을 basic 으로 변경못합니다.
PROD(SYS) > show parameter sga_target
NAME TYPE VALUE
sga_target big integer 256M
- 더 많은 성능정보가 수집되겠금 설정 합니다.
alter system set sga_target = 512m;
문제1. statistics_level 을 all 로 설정해서 더 많은 성능정보가 수집되게끔 설정하시오 !
show parameter spfiile
PROD(SYS) > show parameter spfile
NAME TYPE VALUE
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
show parameter statistics_level
PROD(SYS) > show parameter statistics_level
NAME TYPE VALUE
statistics_level string TYPICAL
alter system set statistics_level = all scope=spfile;
shutdown immediate
startup
show parameter statistics_level
PROD(SYS) > show parameter statistics_level
NAME TYPE VALUE
statistics_level string ALL
설명: 위와 같이 statistics_level 을 all 로 변경해놓으면 사진을 찍을 때 더 많은 성능정보가
수집이 되어지고 앞으로 실제 실행계획을 보기 위해서 힌트로 gather_plan_statistics를
sql에 힌트로 안써도 됩니다.
▣예제 98. 오라클의 각종 advice 기능을 이용하면 더 풍부하게 데이터 베이스를 관리할 수 있어요
select ename, sal from emp
where sal = 3000;
이라고 할 경우
sql 튜닝 어드바이저에게 튜닝 부탁을 하면 - sal 에 인덱스를 생성하라고 권고 함.
만약 위의 sql 을 access 어드바이져에게 튜닝을 부탁하면 - materialized view 를 생성하라고 권고함.
튜닝 어드바이저와 access 어드바이저의 차이가 뭐길래 ???
sal 에 인덱스를 생성 하면 위의 sql 이 빨라질 순 있지만 다른 sql 이 느려질 수도 있음.
materialized view는 서로서로 빨라짐.
문제5. 아래의 sql 을 sql 튜닝 어드바이져에게 튜닝해달라고 요청하시오 !
select /+ no_index(emp emp_deptno) / ename, deptno, job
from emp
where deptno = 10;
답:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select /+ no_index(emp emp_deptno) / ename, deptno, job
from emp
where deptno = 10';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_3',
description => 'Task to tune a query on a specified table' );
end;
/
SQL>
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK (
task_name => 'my_sql_tuning_task_3' );
end;
/
- SQL튜닝 어드바이져가 권고해준 레포트를 확인합니다.
set long 70000
set lines 100
select dbms_sqltune.report_tuning_task('my_sql_tuning_task_3')
from dual;
▣ 예제99. db 의 문제가 생겼을 때 바로 그 문제를 확인할 수 있는 쿼리문이 있어요
실습3. 아래의 설정 내용을 변경해서 tablespace 의 사용량에 대한
경고 메세지와 위기 메세지를 출력하는 테이블 스페이스 사용량
임계치를 설정하시오 !
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '20',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '50',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'PROD',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'TS50'
);
END;
/
SYS> select reason, message_level from dba_outstanding_alerts;
SCOTT> truncate table emp50;
SYS> select reason, message_level from dba_outstanding_alerts;
테이블 스페이스에 대해서 조정한 임계치 확인하는 쿼리문 :
문제1. ts5000 이라는 테이블 스페이스를 사이즈 5m 로 생성하시오
PROD(SCOTT)>
create tablespace ts5000
datafile '/home/oracle/ts5000.dbf' size 5m;
문제 2. ts5000 테이블 스페이스의 공간에 대해서 경고는 70%, 위기는 90% 일 때
발생하게 alert 를 설정하시오 !
PROD(SCOTT)>
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '70',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '90',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'PROD',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'TS5000'
);
END;
/
PROD(SYS) > select reason, message_level from dba_outstanding_alerts;
문제3. ts5000 테이블 스페이스가 full 나게 ts5000 테이블 스페이스에 emp5000테이블을 생성하고
데이터를 중복해서 막 입력 하시오
SCOTT>
create table emp5000
( empno number(10),
ename varchar2(10) )
tablespace ts5000;
create table emp5000
tablespace ts5000
as
select * from scott.emp;
insert into emp5000 values(1111,'aaa');
insert into emp5000
select *
from emp5000;
문제4. 스냅샷을 한번 찍고 dba_outstanding_alerts 를 조회해서 현재 이슈를 확인하시오
PROD(SYS) > select reason, message_level from dba_outstanding_alerts;
▣ 예제100. 과거에 발생했던 이슈를 확인하는 쿼리문
" dba_alert_history "
현재 발생하고 있는 이슈는 dba_outstanding_alerts에 들어가는데
과거에 발생했던 이슈는 dba_alert_history 에 다 저장되어있음.
■ 실습
- dba_alert_history 를 db 에버에서 조회해서 과거의 이슈를 확인하시오 !
SELECT * FROM dba_alert_history
ORDER BY creation_time desc;
문제1. 위의 쿼리문을 실행했을때 reason 에 한글이 ? 로 나오면서 제대로 보이지 않을 때 어떻게 해야하나 ?
alter session set nls_language='KOREAN';
alter session set nls_territory='KOREA';
alter session set nls_characterset='AL32UTF8';
SELECT * FROM dba_alert_history
ORDER BY creation_time desc;
오늘의 마지막 문제
데이터 베이스의 템프 테이블 스페이스에 대해서 공간이 꽉차면 ~ 경고 메세지와 심각 메세지를
temp 라는 테이블스페이스
create tablespace temp
datafile '/home/oracle/temp.dbf' size 5m;
create table emp444
tablespace temp
as
select * from scott.emp;
insert into emp444 values(1111,'aaa');
insert into emp444
select *
from emp444;
PROD(SCOTT)>
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '60',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '80',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'PROD',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'TEMP3'
);
END;
/
select reason, message_level from dba_outstanding_alerts;
drop tablespace temp INCLUDING CONTENTS AND DATAFILES;
