AWR(Automatic Workload Repository)

• Oracle Database 10g부터 구성 요소가 문제 감지 및 자체 튜닝 목적을 위해 통계를 수집, 유지 관리 및 활용할 수 있도록 서비스를 제공하는 infrastructure 입니다.

• MMON(Memory Monitor), MMNL(Memory Monitor Light) 백그라운드 프로세스에 의해 수집되어 SYSAUX tablepsace에 저장된다.

• 수집간격 : 기본값 60분

• Retention 기간 : 기본값 7일(11g 8일)

• 수집 레벨(statistics_level)

  • basic : ADDM(Automatic Database Diagnostic Monitor) 기능 비활성화
  • typical(권장)
  • all

statistics_level

• 데이터베이스의 모든 주요 통계 수집 또는 advisory를 제어한다.
• basic : advisory 또는 통계 수집 되지 않습니다. 모니터와 여러 자동 기능이 비활성화 된다.
• typical : 기본값으로서 전반적으로 데이터베이스 성능을 제공하고 모든 주요 통계를 수집한다.
• all : typical 설정으로 수집되는 모든 advisory 또는 통계외에 시간별 운영체제 통계 및 행 소스 실행 통계가 추가 된다.

ADDM(Automatic Database Diagnostic Monitor)

• CPU 병목 지점
• 부실한 Oracle Net 연결 관리
• lock 경합
• I/O
• Oracle 메모리 구조의 크기
• 로드량이 많은 SQL문
• PL/SQL, JAVA 시간
• 체크포인트

확인

• show parameter statistics_level
  

• select * from dba_hist_wr_control;

  • TOPNSQL : 성능의 문제가 발생한 SQL문 수집하는 수
  • statistics_level = typical(TOPNSQL 30개), all(TOPNSQL 100개)
    

스냅샷 주기 변경

• 스냅샷 주기를 20분으로 보관 기간일은 60일, TOPNSQL의 수는 50개로 변경
  (매개변수는 분 단위로 설정)
• execute dbms_workload_repository.modify_snapshot_settings(interval=>20, retention=>60*24*60, topnsql=>50)
  

스냅샷 확인

select snap_id, instance_number, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by 1;

스냅샷 삭제

• execute dbms_workload_repository.drop_snapshot_range(low_snap_id=>306 ,high_snap_id=>320);
  

AWR DB 보고서 test

drop table hr.emp purge;

create table hr.emp
nologging
as
select rownum emp_id, last_name, first_name, job_id, hire_date, salary, commission_pct,email, department_id
from hr.employees, (select rownum emp_id from dual connect by level <= 10000)
order by dbms_random.value;


select num_rows, blocks, avg_row_len from dba_tables where owner='HR' and table_name='EMP';

<<SYS session>>

select dbms_workload_repository.create_snapshot from dual;

<<HR session>>
- direct path read 방식을 안하겠다는 이벤트

alter session set events '10949 trace name context forever, level 1';

• multi block i/o로 1개의 row만 fetch

select * from hr.emp where emp_id = 100;

<<SYS session>>

• 스냅샷 찍기

select dbms_workload_repository.create_snapshot from dual;

• 문제 되는 내용이 384 ~ 385 스냅샷 안에 담겨있다.

select snap_id, instance_number, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by 1;

• AWR DB 보고서
  @$ORACLE_HOME/rdbms/admin/awrrpt.sql
  

text로 받기

스냅샷 범위로 받기

파일이름

os로 나와서 local에서 확인해보면 만들어진 awr report가 있다.
vi awr_example

physical i/o가 많이 발생한 sql id로 실행계획 확인

select * from table(dbms_xplan.display_awr('fb86gfcy9j69m'));

SQL 튜닝 어드바이저

- 생성하기

declare
   my_task_name varchar2(50);
    my_sqltext clob;
begin
   my_sqltext := 'select * from hr.emp where emp_id = 100';
   my_task_name := dbms_sqltune.create_tuning_task(
                sql_text => my_sqltext, 
                    user_name => 'hr',
                    task_name => 'my_sql_tuning_task');
end;
/

- 실제 수행

execute dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task')

- 리포트 보기

set long 999999
set longchunksize 100000
set linesize 200

select dbms_sqltune.report_tuning_task('my_sql_tuning_task') from dual;

• 인덱스 생성을 추천해준다.
  

• sql tuning advisor, sql access advisor 2개가 있는데, 해당 문장은 문장 자체에는 문제가 없기 때문에 sql tuning advisor가 아니라 sql access advisor가 작동된다. join의 순서를 튜닝해야 하는 경우에는 sql tuning advisor가 작동된다.
  

- 해당 task는 딕셔너리 파일에 저장되어 있기 때문에 지우는 작업도 필요하다.

execute dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task')

active session history

• active session은 cpu를 사용하고 있거나 특정자원(lock, latch)을 획득하지 못해 대기하고 있는 session
• shared pool에 ASH(active session history) 메모리에 저장된다.
• 1초에 한번씩 v$session, v$session_event를 액세스해서 샘플링한 데이터
• 시간별로 active session의 목록을 분석하는 것과 특정 세션이 시간순으로 어떤 상태변화인지를 분석할때 필요하다.
• dba_hist_active_sess_history는 v$active_session_history뷰의 AWR버전, 이뷰는 active session의 10%만 저장

<<SYS session >>

grant execute on dbms_lock to hr;

<<hr session 1>>

create table hr.ash_tab(id number, name varchar2(10));

insert into hr.ash_tab(id,name) values(1,user);

commit;

<<hr session 1>>

begin 
	for i in 1..10 loop
    	update hr.ash_tab set id = 1;
		
        dbms_lock.sleep(1);
        
        commit;
     end loop;
end;
/

<<hr session 2>>

begin 
	for i in 1..10 loop
    	update hr.ash_tab set id = 1;
		
        dbms_lock.sleep(1);
        
        commit;
     end loop;
end;
/

<<SYS session >>
특정 시간 범위 내에서 활성 세션 기록(v$active_session_history)을 조회하여 세션의 상태, 실행 중인 SQL, 이벤트, 및 블로킹 세션 정보를 확인

select sample_time, session_id, session_state, sql_id,
(select sql_text from v$sql where sql_id = h.sql_id) sql_text,
event, blocking_session
from v$active_session_history h
where sample_time between to_date('2024-11-14 14:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2024-11-14 14:26:00','yyyy-mm-dd hh24:mi:ss');