데이터베이스 유지 관리
유지관리 point
- Self Management (자동화된 작업)
- Proactive Mgt (Alert)
- Reactive Mgt (ADR) ; adrci
AWR
- 위 관리를 하기 위한 토대를 마련해주는 것
- 성능 정보에 대한 내장 Repository
- 각 오라클 데이터베이스에 내장된 Reporsitory
- 데이터 수집, 분석 및 권장 해결 방안을 위한 Insfrastructure
- v$sysstat... ex) 'Physical reads' 1000 (인스턴스 기동 후의 누적값) =>
- utlbastat, utlestat =>
- statspace : spcreate spreport =>
- AWR
- 주요통계 및 workload 정보의 스냅샷을 sysaux 테이블스페이스에 저장
- 모니터링
- 진단
- 진단한 결과를 가지고 해결책 까지 제공
- Baseline
- 언제부터 언제까지의 스냅샷을 비교하겠다
- 비교하는 과거의 관련 기간을 묶어 놓은 것
- emctl로 AWR 확인, 수정, 생성이 가능하다.
에서 Statistics Mnagement 의 Automatic Workload Repository
에서 Edit로 설정 가능
수정
Baseline과 Snapshot을 옆의 후레쉬 버튼을 누르면서 확인 할 수 있다. 또한 기간을 설정해주고 Generate Report를 눌러줌으로써, 기간을 설정 할 수 있다.
옵티마이져
- SQL을 효율적으로 빠르게 수행할 수 있는 최적의 처리 경로를 선택해주는 DBMS 핵심엔진
- 옵티마이져 통계정보
- 옵티마이져가 최적의 실행계획을 생성할 수 있도록 제공해주는 테이블(객체) 및 시스템 분석정보
- 객체통계
- 수동
- exec dbms_stats.gather_table_stats('SH','SALES');
- analayze table
SCOTT@orcl2> create table emp2 as select * from emp;
Table created.
SCOTT@orcl2> select table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from user_tables
where table_name = 'EMP2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- ----------
EMP2
-- 현재 통계정보가 없는 것을 확인할 수있다.
SCOTT@orcl2> alter table emp2 add constraint emp2_pk primary key(empno);
Table altered.
-- 인덱스 생성
SCOTT@orcl2 > select table_name, index_name , blevel, leaf_blocks, clustering_factor, num_rows,
distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, sample_size, last_analyzed
from user_indexes
where table_name ='EMP2';
SCOTT@orcl2> analyze table emp2 compute statistics;
Table analyzed.
-- estimate statistics
cf) analyze table emp2 list chained rows;
SCOTT@orcl2> select table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from user_tables
where table_name = 'EMP2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- ----------
EMP2 14 4 4 7450 0 41
SCOTT@orcl2>
select *
from user_tab_columns
where table_name = 'EMP2';
-- distinct한 column의 갯수
SCOTT@orcl2>
select * from user_tab_col_statistics
where table_name = 'EMP2';
- dbms_stats
SCOTT@orcl2> exec dbms_stats.delete_table_stats('SCOTT','EMP2');
PL/SQL procedure successfully completed.
SCOTT@orcl2> exec dbms_stats.gather_table_stats('SCOTT','EMP2');
PL/SQL procedure successfully completed.
SCOTT@orcl2> select table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
from user_tables
where table_name = 'EMP2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- ----------
EMP2 14 4 0 0 0 39
- analyze와 dbms_stats의 차이점?
- dbms_stats는 옵티마이저가 실행계획을 작성할 때 필요한 것만 수집한다.
- empty_blocks, avg_space, chain_cnt => 0 , 이 정보가 필요하면 analyze하라!
- 자동
- 밤 10시에 자동으로 수집
- cost = (io time (sbio + mbio) + cpu time) / sbio time
- 시스템통계
- NoWorkload - 작업량을 근거로 측정한 것이 아니다.(오라클이 랜덤으로 I/O를 일으켜서 CPU속도를 측정하는 것이다)
- Workload - 작업량을 기준으로 측정한 것
- IO , CPU 성능 같은 하드웨어적 특성을 측정한 것
- CPU 속도(CPUSPEEDNW)
- IOSEEKTIM
- IOTFRSPEED -- Noworkload(이 값은 단지 상수값으로 생각하면 된다)
- 우리의 것을 제대로 검사하기 위해서는 시스템 통계를 수집하라고 해야한다.
- 아니면 이 값을 직접 넣어주어야 한다.
- 평균 SBIO time SREADTIM -- 이하는 Workload 1/1000 초 ms
- 평균 MBIO time MREADTIM
- 평균 MBRC
- CPUSPEED
- MAXTHR IO 서브시스템의 최대처리량 -초당 바이트
- SLAVETHR 병렬슬레이브의 평균처리량
SYS@orcl2> select sname, pname, pval1, pval2
from sys.aux_stats$;
-- 시스템 통계정보의 확인
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-13-2009 23:35
SYSSTATS_INFO DSTOP 08-13-2009 23:35
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2696.05568
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
- 특정 시점의 스냅샷(마지막으로 분석한 그 시점의 스냅샷)이라는 특징에 대한 실습
SYS@orcl2> CREATE USER user01 IDENTIFIED BY oracle ;
User created.
SYS@orcl2> grant dba to user01;
Grant succeeded.
USER01@orcl2> CREATE TABLE emp AS SELECT * FROM scott.emp ;
Table created.
USER01@orcl2> CREATE INDEX deptno_ix ON emp(deptno) ;
Index created.
USER01@orcl2> SELECT empno, ename, sal, deptno FROM emp ;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 5000 20
7499 ALLEN 1584 30
7521 WARD 1375 30
7566 JONES 5000 20
7654 MARTIN 1375 30
7698 BLAKE 3135 30
7782 CLARK 2695 10
7788 SCOTT 2000 20
7839 KING 5500 10
7844 TURNER 1650 30
7876 ADAMS 5000 20
7900 JAMES 2000 30
7902 FORD 5000 20
7934 MILLER 1430 10
14 rows selected.
USER01@orcl2> SELECT table_name, num_rows, blocks, avg_row_len FROM user_tables ;
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP
==================================================================================================
-- 통계정보가 없는데 어떻게 판단할까?
-- Optimizer_mode은 Rule과 Choose가 있지만 10g 이후로부터는 Chooose만 사용한다.
USER01@orcl2> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
-- 통계 정보가 없으면 optimizer_dynamic_sampling을 통해 강제로라도 진행한다.
USER01@orcl2> show parameter dynamic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
-- 2(default) 는 파싱할때 코스트를 계산할 수없으면, 그때 통계 정보를 만든다는 것이다(DB에 저장하지 않고, 바로 버린다).
==================================================================================================
USER01@orcl2> SELECT table_name, column_name, num_distinct, density, num_buckets, histogram
FROM user_tab_col_statistics ;
no rows selected
USER01@orcl2> SET AUTOTRACE ON EXPLAIN
-- 실행계획만 보여줘
USER01@orcl2> SELECT empno, ename, sal FROM emp WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2695
7839 KING 5500
7934 MILLER 1430
Execution Plan
----------------------------------------------------------
Plan hash value: 870210623
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 138 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPTNO_IX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
***************통계정보가 없기 때문에 dynamic sampling을 한 것이다.
USER01@orcl2> SET AUTOTRACE OFF
USER01@orcl2> show parameter dynamic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
--통계정보를 만들어보자. cascade는 모든 인덱스의 통계정보도 같이 만들라는 뜻이다.
USER01@orcl2> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER01','EMP',CASCADE=>TRUE) ;
PL/SQL procedure successfully completed.
USER01@orcl2> SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
where table_name = 'EMP';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP 14 4 39
USER01@orcl2> SELECT table_name, column_name, num_distinct, density, num_buckets, histogram
FROM user_tab_col_statistics
WHERE table_name ='EMP';
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ --------------- ------------ ---------- ----------- ---------------
EMP EMPNO 14 .071428571 1 NONE
EMP ENAME 14 .071428571 1 NONE
EMP JOB 5 .2 1 NONE
EMP MGR 6 .166666667 1 NONE
EMP HIREDATE 13 .076923077 1 NONE
EMP SAL 9 .111111111 1 NONE
EMP COMM 4 .25 1 NONE
EMP DEPTNO 3 .035714286 3 FREQUENCY
8 rows selected.
USER01@orcl2> INSERT INTO emp
SELECT e.*
FROM emp e, (SELECT level
FROM dual
CONNECT BY level <= 100000);
1400000 rows created.
USER01@orcl2> commit;
Commit complete.
USER01@orcl2> select table_name, num_rows, blocks
from user_tables;
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
EMP 14 4
-- 스냅샷이 걸려있기 때문이 14로 나온다.
USER01@orcl2> SET AUTOTRACE OFF
USER01@orcl2> set autot on exp
USER01@orcl2> select empno, ename, sal
from emp
where deptno = 10;
EMPNO ENAME SAL
---------- ---------- ----------
...............
7934 MILLER 1430
7782 CLARK 2695
7839 KING 5500
7934 MILLER 1430
300003 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 870210623
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 51 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPTNO_IX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
-- Histogram이 3으로 되어있기때문에 3으로 출력된다.
USER01@orcl2> set autot off
USER01@orcl2> EXEC DBMS_STATS.GATHER_TABLE_STATS('USER01','EMP',CASCADE=>TRUE) ;
PL/SQL procedure successfully completed.
USER01@orcl2> select table_name, num_rows, blocks
from user_tables;
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
EMP 1400014 9080
-- 통계 정보가 만들어졌다.
USER01@orcl2> set autot trace exp
USER01@orcl2> select table_name, num_rows, blocks
from user_tables;
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
EMP 1400014 9080
USER01@orcl2> set autot trace exp
USER01@orcl2> select empno, ename, sal
2 from emp
3 where deptno = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 306K| 5081K| 2478 (1)| 00:00:30 |
|* 1 | TABLE ACCESS FULL| EMP | 306K| 5081K| 2478 (1)| 00:00:30 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
-- 재대로 된 통계정보를 주어졌더니, 실행계획이 정상적으로 출력되었다.
USER01@orcl2> set autot off
USER01@orcl2> drop table emp;
Table dropped.
- Histogram
- Data 분포에 대한 통계
- frequence
- 막대그래프와 같은것
- ditinct의 값 각각을 buckets으로 만들어 놓은 것(도수분포표)
- 통계 수집 환경 설정
- CASCADE
- 인덱스 통계가 테이블 통계 수집의 일부로 수집되는지 여부
- DEGREE
- 통계 수집시 사용되는 병렬도
- ESTIMATE_PERCENT
- 올바른 통계를 얻기 위해 샘플링할 행 수 결정
- NO_INVALIDATE
- 커서를 무효화할지 여부
- METHOD_OPT
- 히스토그램을 생성하던지 안하던지
- GRANULARITY
- 수집할 통계의 세분화 정도
- INCREMENTAL
- 파티셔닝된 테이블의 글로벌 통계를 Incremental 방식으로 수집하는데 사용
- PUBLISH
- 통계를 딕셔너리에 게시할지, 전용 영역에 저장할지 결정
- STALE_PERCENT
- 객체의 통계가 오래된 것인지를 판단할때 기준으로 삼을 레벨을 결정(default : 10)
SYS@orcl2> select dbms_stats.get_prefs('STALE_PERCENT')
from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT')
--------------------------------------------------------------------------------------
10
SYS@orcl2> save get
Created file get.sql
SYS@orcl2> select table_name from dict
where table_name like'%PREFS%';
TABLE_NAME
------------------------------
USER_TAB_STAT_PREFS
ALL_TAB_STAT_PREFS
DBA_TAB_STAT_PREFS
SYS@orcl2> select * from dba_tab_stat_prefs;
no rows selected
SYS@orcl2> save tab_prefs
Created file tab_prefs.sql
SYS@orcl2> exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');
PL/SQL procedure successfully completed.
-- 통계 수집을 했을때 13퍼센트 이상 변경됬을시에 통계정보를 만들어라.
SYS@orcl2> @tab_prefs
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
-------------------- -------------------- ------------------------------ --------------------
SH SALES STALE_PERCENT 13
SYS@orcl2>
select dbms_stats.get_prefs('stale_percent') sp,
dbms_stats.get_prefs('method_opt') mo,
dbms_stats.get_prefs('cascade') ca,
dbms_stats.get_prefs('no_invalidate') ni,
dbms_stats.get_prefs('estimate_percent') ep
from dual;
SP
--------------------------------------------------------------------------------------------------
MO
--------------------------------------------------------------------------------------------------
CA
--------------------------------------------------------------------------------------------------
NI
--------------------------------------------------------------------------------------------------
EP
--------------------------------------------------------------------------------------------------
10
FOR ALL COLUMNS SIZE AUTO
DBMS_STATS.AUTO_CASCADE
DBMS_STATS.AUTO_INVALIDATE
DBMS_STATS.AUTO_SAMPLE_SIZE
SYS@orcl2> exec dbms_stats.delete_table_prefs('SALES','STALE_PERCENT');
SYS@orcl2> exec dbms_stats.reset_table_prefs('SALES','STALE_PERCENT');
-- 이런식으로 설정
AWR 관리
SYS@orcl2> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
** SQL 튜닝을 할 경우 value를 ALL로 하여야한다.
ADDM
- 스냅샷을 찍을 때마다 ADDM이 돌아간다.
오라클에 문제가 생겼을 때 그 문제를 진단하기 위한 성능 레포트 3가지
- AWR report
- 과거에 특정 구간에 어떠한 성능상의 문제가 있었는지를 확인
- ADDM report
- 과거의 특정 구간에 생긴 문제에 대한 해결방법을 알려줌
- 수동으로 사진찍기
- DB에 부하를 준다
- 수동으로 사진을 찍는다
- ADDM report를 생성한다
SYS@orcl2> exec dbms_workload_repository.create_snapshot;
-- 스냅샷을 수동으로 하나 찍자
SYS@orcl2> alter system flush shared_pool;
System altered.
SCOTT@orcl2> set serveroutput on
SCOTT@orcl2>
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 .. 1000 loop
open l_rc for
'select /* LEE_CHECK */ 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;
/
SYS@orcl2>
select sql_text, executions
from v$sql
where sql_text like '%LEE_CHECK%';
-- DB에 부하를 준다.
SYS@orcl2> exec dbms_workload_repository.create_snapshot;
-- 스냅샷을 수동으로 하나 찍자
SYS@orcl2>!cat ORACLE_HOME/rdbms/admin/addmrpt.sql
-- ADDM report를 생성후 확인하자.
==================================해결책을 따라 bind 변수를 사용한다.================================
SYS@orcl2> exec dbms_workload_repository.create_snapshot;
-- 스냅샷을 수동으로 하나 찍자
SCOTT@orcl2>
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 .. 1000 loop
open l_rc for
'select /* LEE_CHECK */ object_name from all_objects where object_id = :x' using 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;
/
-- DB에 부하를 소프트 파싱 할 수 있도록 바인드 변수로 바꾸어 스크립트를 돌린다.
SYS@orcl2> exec dbms_workload_repository.create_snapshot;
-- 스냅샷을 수동으로 하나 찍자
SYS@orcl2>!cat ORACLE_HOME/rdbms/admin/addmrpt.sql
-- ADDM report를 생성후 해결 되었는지 확인하자.
SYS@orcl2>
select sql_text, executions
from v$sql
where sql_text like '%LEE_CHECK%';
- ASG report
- 최근에 발생한 악성 SQL과 해당 세션에 대한 정보를 보는 레포트
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]Backup & Recovery (0) | 2019.04.10 |
|---|---|
| [Oracle DBA]성능 관리와 데이터 이동 (0) | 2019.04.10 |
| 복습문제 (0) | 2019.04.05 |
| [Oracle DBA]데이터 동시성 관리 (0) | 2019.04.03 |
| [Oracle DBA]유저 보안 관리 (0) | 2019.04.01 |