데이터와 사람들 DBA 교육 5일차 정리
14장. Flashback
Flash Back
사용자의실수로특정테이블을 특정시점으로되돌리는경우
테이블의잘못된Delete 또는Update에의한원복에활용
Database 단위로도 진행할수있으나, 주로활용되지않음
※ 운영테이블을특정시점으로되돌리는것은위험 <- 버전Query 활용 ( 이전 쿼리 조회하는 기능 있음 )
Row Level Flashback은Undo를활용하기에Undo_Retention에서지정한기간내에서조회가능
ROW MOVEMENT설정이ENABLE로되어있어야함.
Default 설정은Disable이며, 설정확인은dba_tables조회하여확인가능
DML에 의한 복구
Undo 활용
테이블의row_movement속성이enable로되어있어야Flashback 가능
Flashback 단위는second, minute, hour로기술가능
플래쉬백 명령어 ( 업데이트 딜리트 같은거 할때 )
flashback table edu01.t_cust to timestamp (systimestamp-interval '5' minute);
플래시백은 위험하니까 아래처럼 5분전 데이터를 조회해서 사용자가 직접 원복하는게 훨씬 나음
select cust_no, cust_nm
from t_cust as of timestamp (systimestamp-interval '5' minute)
where cust_no= '01000001';
or
select cust_no, cust_nm from t_cust where cust_no= '01000001'
union all
select cust_no, cust_nm from t_cust as of timestamp (systimestamp-interval '5' minute)
where cust_no= '01000001';
휴지통 조회 ( on으로 설정돼있어야 사용가능 일반적으로 on 상태로 두셈 )
select name, value
from v$parameter where name = 'recyclebin';
드랍된 테이블 확인가능
select owner, object_name, original_name
from dba_recyclebin;
드랍 테이블 플래시백 복구
flashback table edu01.t_custto before drop;
Recyclebin 관리
recyclebin은 일정 시간 경과후Drop 된다른테이블에의해채워지며, 이때복구불가
BIN$ 으로 시작되는테이블
dba_recyclebin 또는 user_recyclebin에 의해 조회 가능
flashback table table_name to before drop;
Drop Table Table_name purge; 명령어로 recyclebin으로 백업되지 않음
sys 스키마의테이블은recyclebin 으로 들어가지않고, 바로삭제
purge recyclebin 명령으로 자기 스키마의 휴지통 삭제가능
purge dba_recyclebin 명령으로 데이터베이스 전체의 휴지통 삭제가능 단, sys 계정으로로그인필요
15장. 옵티마이저 및 라이브러리 캐시
lock -> latch -> mutex 순으로 가볍다. lock은 큐로 돼있고 너무 무거워서 latch가 나오고 11버전부터 mutex를 쓴다고 함.
리터럴 쿼리가 많으면 디비에 스트레스를 많이 줌. ( 문장 의미와 상관없이 Text 그 자체가 다를경우 대소문자, 공백 주석 등 )
Bind Peeking ( 실행계획을 만드는 시점에 바인드 변수를 포함해서 실행계획을 만들거냐를 설정하는 값 )
alter system set “_optim_peek_user_binds” = FALSE ;
최초 바인드 변수가 범위가 엄청 길게설정돼서 풀스캔으로 실행계획을 잡으면 이후 쿼리에서도 실행계획이 소량일 경우에도 풀스캔 타게 돼서 false 조건으로 해야함.
리터럴 쿼리 != 다이나믹 sql
다이나믹 sql은 스트링변수를 바인드 변수로 받는 것
16장. 오브젝트 통계정보 관리
통계정보를 갱신할 때 개악 될 확률이 있으니, 통계정보를 미리 백업받는게 중요함.
테이블에 인덱스가 많을 경우 통계정보가 뒤집힐 확률이 더 높음
그래서 SPM , SQL Profile 기능을 사용함.
히스토그램 = 전체 db 풀스캔해서 통계정보 갱신해야해서 잘 작성하지 않음. 바인드 변수가 포함된 쿼리의 경우 어떤 값을 받을지 모르기때문에 무의미하기도 함. 일부 리터럴 쿼리의 경우는 의미가 있다..
보통 dba들은 스키마 단위로 통계정보를 수집한다.
통계정보 수집 파라미터들은 그냥 훑어보면 됨(STATTAB, STATID, NO_VALIDATE 정도 보셈)
통계정보 갱신하면 DBA들은 밤새 작업함.
Pending 통계 -> 바로 반영하지 않고 임시로 바뀐 통계정보를 갖고있다가 내 세션에서 먼저 확인하고 문제없으면 반영할 때 사용 ( 잘 사용하지 않음 )
통계정보 백업 & 리스토어
17장. SQL 분석 도구
alter session setstatistics_level = all; => cursor한테 sql실행하면서 실행계획 트레이스 정보를 남기라고 명령하는거임.
그래서 진짜 수행한 실행계획을 확인할 수 있음
e-rows => 옵티마이저가 예상한 행의 수
a-rows => 실제 수행된 sql의 행의 수
가장많이사용하는옵션 dbms_xplan.display_cursor (null, null, ‘iostats last -rows’)
10046 trace 수행
alter session set max_dump_file_size = unlimited; -- trace file size 지정
alter session set tracefile_identifier='sik'; -- trace file 이름 끝에 sik 를 붙여라
alter session set events '10046 trace name context forever, level 12';-- trace 시작
select /*+ ordered use_nl(e) index(e) */ e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 3000;
alter session set events '10046 trace name context off';-- trace 종료
Tracefile위치확인
- 9i, 10g, 11g
select name, value from v$parameterwhere name = 'user_dump_dest';
- 12c 이상
select value from v$diag_info where name = 'Diag Trace'
플랜 유형에 따라 용어가 다름 참고!
select * from table (dbms_xplan.display_cursor(null, null, 'iostats last -rows')); -- developer
select * from table(dbms_xplan.display(null, null, 'typical')); --dbver
18장. OWI 및 AWR
Oracle Wait Interface 응답시간 분석방법론을 지원하기 위한 오라클이 제공하는 기능및 Interface 전체
Wait Event 발생 요건
프로세스가요구하는리소스를타프로세스가사용중인경우–latch 등
다른프로세스에게작업을요청하고완료를대기할때–Disk IO 등
프로세스가할일이없을때–client에결과전송후client로부터응답대기등
AWR ( Auto Workload Repository ) = 스냅샷
스냅샷 뜰때 사이 간격에 오버헤드가 발생 -> statspack
10G이전에는 사용자가 지정해서 스냅샷을 떳는데, 10G이후부터는 DBMS가 자동으로 떠줌(ASH : Active Session History )
v$active_session_history 제공하고 과거 정보는 dba_hist_active_sess_history 조회 가능
DB CPU를 제외한각종Wait Event 중 상위10개를제공
1순위가 인덱스 스캔량이고 3위가 배치 패러럴 스캔이니까 OLTP 환경인 것을 알 수 있음.
19장. SPM (SQL Plan Management)
실행계획이 Shared Cursor에서 Invalid 되어, Hard Parsing될 경우 실행계획의 개악 될 위험
실행계획을 고정시켜 실행계획이 개악될 리스크를 헤지하는 용도로 활용
새로운 실행계획이 capture 되어, 현재의 실행계획 보다 우수하다고 판단될 경우 신규 실행계획으로 변경 => 자동화를 믿지마셈 , 검증된 실행계획만 믿을 것..
SQL Plan Baselines에 저장된 Plan을 사용법
alter system set optimizer_use_sql_plan_baselines= true scope = both
OPTIMIZER_INDEX_COST_ADJ = 300 설정
※ 기본은100(%) 이며, 낮출경우 인덱스활용시 실행계획Cost 값을 절반으로 낮춤
즉, 낮을수록 인덱스 스캔에 친화적으로설정, 높으면 Full Scan 친화적설정
SQL_PLAN_BASELINE 등록및SQL 실행계획고정확인
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES
SELECT SQL_ID, PLAN_HASH_VALUE, SQL_PLAN_BASELINE
FROM V$SQL
WHERE SQL_ID = '3ny4ty3g1v79u'