복습
12장의 주요내용은 오라클 헬스체크 입니다.
✅ 오라클의 성능을 진단하는 3가지 레포트 생성방법
1.awr report< - 가장 많이 활용됨(MMON이 사진을 찍음)
2.addm report
3.ash report<- 최근에 발생했던 db의 악성 SQL과 이슈들↓ active session history 메모리 영역 1. 최근에 발생한 악성 SQL 2. 최근에 발생한 active session 의 정보➡️ dba_outstanding_alrrts 여기에 현재 이슈가 저장됩니다.
1. 테이블 스페이스가 full차는 현상에 대한 경고 메세지
2. snap shot too old 에러 (OCM문제)
3. resumable session (OCM문제)
Alert에 대한 대응
p.12-36. Alert 유형 및 Alert 지우기
✅ 아래는 현재 발생한 db의 이슈이다. 시간이 지나면 사라진다. 그럼 예전거는 못보는걸까??
dba_alert_history 를 확인하면 된다 !
SYS @ orcl > select reason from dba_outstanding_alerts;실습1. 과거에 발생했던 db의 이슈를 확인하기 (주로 인스턴스 올라갔다 내려갔다가 주를 이룬다.)
SYS @ orcl > select reason from dba_alert_history;문제 최근에 snap shot too old 에러가 db에서 언제 발생했는지 확인해보기
select * from dba_alert_history where lower(reason) like '%스냅%';
이런것도 뜬다. em에서 undo advisor 확인 후에 용량 늘리면 됨.
문제 아카이브 로그 파일이 생성되는 flashback_recovery_area 파라미터에 지정된 영역이 꽉 차서 alert가 뜬적이 있었는지 dba_alert_history 에서 확인하기!
select * from dba_alert_history where lower(reason) like '%recovery%';아래 결과 등등이 출력된다.
✅reason: db_recovery_file_dest_size/10737418240바이트는 38.57%이(가) 사용 중이며 6595543040바이트를 사용할 수 있음을 나타냅니다.
✅suggested_action: 복구 영역의 공간 확보를 위한 선택 옵션: 1. RMAN RETENTION POLICY을(를) 변경합니다. Data Guard를 사용 중인 경우 RMAN ARCHIVELOG DELETION POLICY을(를) 변경합니다. 2. RMAN BACKUP RECOVERY AREA 명령을 사용하여 테이프에 파일을 백업합니다. 3. 디스크 공간을 추가한 다음 db_recovery_file_dest_size 매개변수 값을 늘립니다. 4. RMAN DELETE 명령을 사용하여 불필요한 파일을 삭제합니다. OS 명령을 사용하여 파일을 삭제한 경우 RMAN CROSSCHECK and DELETE EXPIRED 명령을 사용합니다.SYS @ orcl > show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 10G
p.12-36 resumable session suspended
❓ resumable session suspended 란 특정 테이블 스페이스에 data를 입력하는데 테이블 스페이스가 full이 나서 db 입력이 실패해버게 되면 1~3시간 작업한 데이터 이행이나 데이터 파이프 라인으로 연결한 입력작업이 실패합니다 ! 그러면 다시 되돌려놓아야 합니다. 3시간 동안 수행한 데이터 입력작업이 거의 끝에 가서 tablespace 공간이 꽉차서 실패하면 테이블 스페이스의 공간을 추가하고 다시 3시간을 돌려야합니다.
예를들어 ts70 테이블 스페이스에 insertfmf 하다가 풀이났을 때 잠시 멈추고 suspend가 select reason from dba_outstanding_alerts;에 reson을 계속 뿌린다. 그러면 끝난게아니고 끝날때까지 대기
실습 스크립트
scott> create tablespace ts05
datafile '/home/oracle/ts05.dbf' size 20m;
scott>
create table emp05
tablespace ts05
as
select * from emp;
scott> insert into emp05
select *
from emp05;
scott> / <---------- 에러 날때까지 반복하시오!
-- 위의 insert 작업을 1시간동안해왔는데 ts05 테이블스페이스의
-- 공간이 부족해서 insert 가 에러났다면 다시 data 를 지우고
-- 다시 작업해야한다.
-- 그래서 resumable session 기능을 이용하면 insert 할때 공간이
-- 부족하면 잠깐 insert 를 멈출수 있다.
-- insert 가 멈춰있는동안 ts05 테이블 스페이스에 공간을 추가하면
-- 계속 insert 가 진행이된다.
truncate table emp05;
alter session enable resumable timeout 300;
insert into emp05
select *
from emp;
insert into emp05
select *
from emp05;
-- 위 작업을 여러번 아까와 동일하게 수행하는데, 이번에는 공간이 부족하다고 에러가 바로 안나고 300초 waiting합니다.
-- 그 때 dba가 select * from dba_outstanding_alerts를 조회하면 resumable session에 대한 정보가 나온다.
-- 그러면 다음과 같이 공간을 추가해주기 ! (300초 설정했으니까 5분 지나기전에 아래 하면 안되던 insert가 들어간다.)
SYS> alter tablespace ts05
add datafile '/home/oracle/ts05c.dbf' size 100m; 
문제 (OCM시험문제) resumable_timeout 파라미터를 시스템 레벨로 1시간 지정하시오
SYS> ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600;📖 13장. 성능관리
이 단원을 마치면 다음을 수행할 수 있습니다.
➡️ Enterprise Manager를 사용하여 성능 모니터
➡️ AMM(자동 메모리 관리) 사용
➡️ Memory Advisor를 사용하여 메모리 버퍼의 크기 조정
➡️ 성능 관련 동적 뷰 보기
➡️ invalid 및 unusable 객체 문제 해결
p.13-4 EM을 활용해서 성능 문제 확인하기 실습
실습1. 아래의 악성 SQL을 준비
SH> select count(*)
from sales s, sales s, sales s, sales s;실습2. EM에서 성능 탭으로 가서 라인 그래프를 조회한다.
✅ 밑에 그래프 누르면 1시간안에 어떤일이 있었는지 드래그 해서 다 볼 수 있다. 악성 SQL, 악성 세션을 모두 볼 수 있고 SQL ID를 클릭하면 어떤 악성 SQL인지도 보인다.
p13-9 메모리 구성 요소 관리
실습1. 자동 메모리 관리 기능을 사용하기 위한 단 하나의 파라미터가 무엇인가?
SQL> show parameter memory_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 1G✅ memory_target 만 지정하면 나머지는 오라클이 알아서 상황에 따라 사이즈 조정을 한다.
1.sga_target: sga 영역의 사이즈 파라미터
2.pga_aggregate_target: pga 영역의 사이즈 파라미터
SYS @ orcl > show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 0 -- 0이면 자동관리중 SYS @ orcl > show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 0 -- 0이면 자동관리중낮시간 : sga > pga
밤시간 : sga < pga
위와 같은 환경이 되려면 memory_target만 설정하면 된다.
- SGA 영역 내의 메모리 컴포넌트들의 사이즈도 알아서 자동 조절합니다.
1. db buffer cache ---------------> SQL문이 활발하게 일어나면 사이즈 커진다. 2. redo log buffer ---------------> DML 문장을 적는 공간인데 사이즈 고정되어있다. 3. shared pool -------------------> 파싱이 활발하게 일어나면 크게 잡고 4. large pool -------------------> 병렬 쿼리문, RMAN 백업과 복구 수행시 사이즈 크게잡기 5. java pool -------------------> 자바 코드가 오라클에서 수행될 떄 크게잡기 6. stream pool -------------------> 데이터 이행과 같튼 작업 수행시 크게잡기✅ 작업에 따라 각각의 사이즈 조정이 필요한데, 이것을 오라클에 의해 자동조절이 되는것.
SQL> select component, current_size/1024/1024 as mb from v$sga_dynamic_components where current_size != 0; COMPONENT MB ---------------------------------------------------------------- ---------- shared pool 360 large pool 20 java pool 4 DEFAULT buffer cache 220
실습1. p13-10에 나오는 화면을 em에서 찾아보기
데이터베이스 home > 맨아래에 중앙원고자 > 메모리 권고자
위 이미지에서 권고사항을 눌러보면 그래프가 나온다.
실습2. p.13-11에 나오는 화면을 emp 에서 찾기
✅ 실습1과 같은 페이지에서 아래쪽에 있다. 레포팅 해야 하는 일이 있다면 이 화면 캡쳐해서 사용해도 된다.
✅ PGA 영역의 사용량 그래프에서 빨간색이 많이 나오면 pga 영역의 사이즈가 작아서 temp tablespace를 사용하는 정렬 SQL이 많아는 것이므로, dba는 memory_target 사이즈를 늘리는 것을 고려한다. (디비를 올렸다 내려야 하므로) 추석때나 설때..
p.13-13 다이나믹 퍼포먼스 뷰 사용방법
💡 데이터 베이스의 성능을 진단하기 위한 데이터가 들어있는 딕셔너리를 다이나믹 퍼포먼스 뷰 라고 합니다.
v$로 시작하는 성능 데이터 ------------------------------> 성능 정보 ↑ SQL, 리눅스 쉘, PL/SQL✅ 우리가 그동안 이 성능정보를 위해 만든 리눅스 쉘이
dba.sh입니다.
실습1. (유용한 쉘 스크립트 생성) 현재 top에서 cpu를 100% 사용하고 있는 세션이 어떤 SQL을 수행하고 있는지 단번에 확인하는 쉘
#악성sql 하나 돌리고
SH> select count(*)
from sales s, sales s, sales s, sales s;
$ top | head -8 | tail -1 | awk '{print $1}'
#위에서 출력되고있는 프로세서 번호를 변수에 담고 echo로 출력하기
pid=`top | head -8 | tail -1 | awk '{print $1}'`
echo $pid
$ vi cpu_top_sql.sh
[orcl:~]$ sh cpu_top_sql.sh
# cpu를 과도하게 사용하는 프로세서의 SQL을 출력하는 쉘스크립트 전체코드
pid=`top | head -8 | tail -1 | awk '{print $1}'`
echo $pid
output=$(sqlplus -s sys/oracle_4U as sysdba <<EOF
Select a.sql_text txt
from v\$sqlarea a, v\$session b, v\$process c
where c.spid = '$pid'
and c.addr = b.paddr
and b.sql_address = a.address
and b.sql_hash_value = a.hash_value;
EOF
)
echo $output
v$로 시작하는 다이나믹 퍼포먼스 뷰에서 얻을 수 있는 dba 성능정보 (2가지)
1. 누적 통계정보 : db에서 수행한 작업들에 대한 수치들을 누적해서 계속 증가하여 저장하는올려주는 딕셔너리
select name, value from v$sysstat where name like '%logical%'; NAME VALUE ---------------------------------------------------------------- ---------- session logical reads 142526 SYS @ orcl > / NAME VALUE ---------------------------------------------------------------- ---------- session logical reads 1441962. 대기 이벤트 정보 : 오라클이 아프면 왜 아픈지 이유를 알려준다. 그 이유가 대기 이벤트로 나타난다.
SYS> select event from v$session_wait order by event;
✅ db file scattered read <- 누군가 full table scan 하면서 대기하고 있구나
p.13-16 부적합한 객체 및 사용할 수 없는 객체
✅ 락파라미터를 사용한 프로시저 생성하기
#입력 파라미터를 사용한 프로시져 생성하기
SCOTT>
set serveroutput on
create or replace procedure pro1
( p_empno number )
is
v_sal number(10);
begin
select sal into v_sal
from emp
where empno=p_empno;
dbms_output.put_line ( v_sal );
end;
/
SCOTT> drop table emp;
SCOTT> select object_name, object_type, status
from dba_objects
where owner='SCOTT';
SCOTT> flashback table emp to before drop;
✅ 상태가 INVALID로 나오는 것은 프로시저내에서 사용되고 있는 테이블이 drop 되었거나 또는 테이블의 컬럼의 길이의 변경이 일어났거나 컬럼이 삭제, 추가 되거나 해서 테이블 구조에 변경이 발생하면 해당 테이블과 관련된 프로시저들이 전부 상태가 INVALID가 됩니다. INVALID가 되면 프로시저 작동이 안되니까 조취를 취해야한다.
이렇게도 볼 수 있다.
✅ flashback을 했는데도 INVALID로 나온다. 이 경우 dba가 해줘야하는 일이있는데, INVALID된 프로시저를 다시 컴파일 하기!SCOTT> alter procedure pro1 compile; SCOTT> select object_name, object_type, status from dba_objects where owner='SCOTT';
✅ 우리는 emp 관련된 프로시저가 1개밖에 없었지만 현업에서는 엄청 많아서 일일이 찾아 컴파일해주면 시간이 많이 걸린다.
문제 INVALID된 프로시저를 compile하는 SQL문을 작성하는 SQL문을 작성하시오
SCOTT> select 'alter procedure ' || object_name || ' compile; '
from dba_objects
where owner='SCOTT' and object_type='PROCEDURE';
'ALTERPROCEDURE'||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter procedure PRO1 compile;
alter procedure MY_PROC compile; 문제 이번에는 위 컴파일 하는 SQL을 실행해버리는 procedure를 생성하기
create or replace procedure pro196 is begin for r in (select object_name, object_type, status from user_objects where status = 'INVALID' ) loop execute immediate ' alter '||r.object_type||' ' ||r.object_name||' compile'; end loop; exception when others then for i in (select object_name from user_objects where status = 'INVALID') loop dbms_output.put_line(i.object_name||'view또는 프로시저에 문제가 생겼습니다. 해당 테이블을 확인하시오.'); end loop; end; / SCOTT @ orcl > exec pro196; PL/SQL procedure successfully completed.
p.13-16 Index가 Invalid 되는 경우 해결방법
💡 테이블에 insert를 하고 update를 하고 delete를 하다 보면, 아래와 같이 테이블의 블럭의 내용이 구선된다.
emp테이블 블럭의 처음 상태 -----------------------------> emp테이블 블럭의 나중상태
↑ 수많은 DML작업
✅ emp테이블을 처음 상태로 만들어주기 위해 DBA, 디비 엔디니어,데이터 엔지니어가 가장 많이하는 작업은 reorg 작업입니다, 다음과같습니다! (db reorg 작업)
alter table emp move tsblespace t500;➡️ 인덱스는 그대로 있는데 위처럼 테이블이 이사를 가버리면 풀테이블 스캔이 나와버린다. drop하고 다시 만들면 되지만 하지않고 해결하는 방법은??
alter index emp_sal rebuild online; #onlinen을 붙여줘야 계속해서 DML작업이 reorg작업중에도 진행된다. 
⭐reorg 할때
1. 테이블 이사시키고
2. 관련 인덱스 모두 rebuild!
index 의 상태가 invalid 하다는것은 인덱스를 통해서
테이블을 엑세스 할수없는 상태라는것이다.
* 실습
SCOTT> @demo.sql
SCOTT> create index emp_sal on emp(sal);
SCOTT> select index_name, status
from user_indexes
where index_name='EMP_SAL';
SCOTT> select table_name, tablespace_name
from user_tables
where table_name='EMP';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP USERS
SCOTT> alter table emp move tablespace example;
SCOTT> select index_name, status
from user_indexes
where index_name='EMP_SAL';
INDEX_NAME STATUS
------------------------------ --------
EMP_SAL UNUSABLE문제. 월급이 3000 인 사원의 이름과 월급을 출력하는데
힌트를 써서 emp_sal 인덱스를 통해서 데이터를 검색
할수 있게 하시오 !
select /*+ index(emp emp_sal) */ ename, sal
from emp
where sal = 3000;
ERROR at line 1:
ORA-01502: index 'SCOTT.EMP_SAL' or partition of such index is in unusable
state
select /*+ full(emp) */ ename, sal
from emp
where sal = 3000;
ENAME SAL
---------- ----------
FORD 3000
SCOTT 3000
alter index emp_sal rebuild online;
SCOTT @ orcl > select index_name, status
from user_indexes
where index_name='EMP_SAL'; 2 3
INDEX_NAME STATUS
------------------------------ --------
EMP_SAL VALID문제 scott이 가지고 있는 모든 테이블을 users에서 example로 move 시키는 SQL 을 생성하는 스크립트를 만들기
SCOTT> select 'alter table ' || table_name || ' move tablespace example; ' from dba_tables where owner='SCOTT'; 'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEEXAMPLE;' -------------------------------------------------------------------- alter table SALGRADE move tablespace example; alter table EMP10 move tablespace example; alter table EMP50 move tablespace example; alter table EMP05 move tablespace example; alter table DEPT move tablespace example; alter table EMP move tablespace example; alter table BONUS move tablespace example; 7 rows selected.➡️ 큰테이블은 맨 뒤로 보내고, 작을테이블부터 move 되게 해야 시간 절약이 된다.
💡 dba를 위한 tip !
문제위의 스크립트를 수정해서 작은 테이블 부터 큰 테이블 순으로 스크립트가 만들어지게 하세요 !exec dbms_stats.gather_schema_stats('SCOTT'); SQL> set pages 400 SQL> select 'alter table ' || table_name || ' move tablespace example; ' from dba_tables where owner='SCOTT' order by num_rows asc; select table_name, num_rows from dba_tables where owner='SCOTT' order by num_rows asc; -- 이렇게 하면 num_rows보임 'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEEXAMPLE;' -------------------------------------------------------------------- alter table EMP50 move tablespace example; alter table BONUS move tablespace example; alter table EMP10 move tablespace example; alter table DEPT move tablespace example; alter table SALGRADE move tablespace example; alter table EMP move tablespace example; alter table EMP05 move tablespace example;
문제 table을 다른 테이블 스페이스로 move한 이후에 UNUSABLE하게 된 인덱스들을 모두 rebuild하는 스크립트를 생성하시오
select 'alter index ' || index_name || ' rebuild online; ' from dba_indexes where status='UNUSABLE' and owner='SCOTT' order by num_rows asc; 'ALTERINDEX'||INDEX_NAME||'REBUILDONLINE;' ----------------------------------------------------------- alter index EMP_SAL rebuild online; desc dba_indexes
