[Oracle] 오라클 성능정보 확인 레포트 (ADDM)

🔎 오라클에 성능상의 이슈가 생겼을 때 확인해야할 4개의 레포트

1. awr 레포트
   : 특정 시간에 발생한 성능상의 이슈 확인 (과거에 발생한 악성 SQL 찾을 때 유용)
2. addm 레포트
   : 성능상의 이슈도 알려주고 해결방법도 알려줌
3. ash 레포트
   : 최근에 발생한 성능상의 이슈들을 알려줌
4. awr 비교 레포트
   : 정상적인 시간과 비정상적인 시간을 서로 비교해서 얼마나 느린지를 상대적으로 비교하게 해줌

---

[실습1] addm 레포트로 성능상의 이슈 확인 및 해결방법 알아내기

 1. 수동으로 사진을 찍는다 

PROD(SYS) > exec dbms_workload_repository.create_snapshot;

 2. db 에 부하를 준다 

   하드파싱을 과도하게 유발하는 스크립트를 돌린다.

PROD(SCOTT) >
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.. 수동으로 사진을 찍는다 

PROD(SYS) > exec dbms_workload_repository.create_snapshot;

 4. ADDM  report 를 생성한다.

PROD(SYS) > @?/rdbms/admin/addmrpt.sql 

---

[실습2] 오렌지에서 addm 레포트를 생성하시오

---

[실습3] 추천해준대로 literal SQL을 바인드 변수로 변경해서 PL/SQL 프로그래밍하시오

리터럴 SQL: select empno, ename, sal from emp where empno = 7788;

바인드 변수 : select empno, ename, sal from emp where empno = :v_empno;

--- 2. 바인드 변수 사용했을 때 

alter system flush  shared_pool;

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 = :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;
/

---

[실습4] 오렌지에서 addm를 생성하세요.

💡 성능상의 이슈가 없어서 아무것도 출력되지 않음

---

개발팀에서 계속 리터럴 SQL을 바인드 변수로 변경 안해주면
cursor_sharing 파라미터를 force 로 변경하면 오라클이 알아서 리터럴 SQL을 바인드 변수로 변경함

---

문제1. cursor_sharing 파라미터를 force 로 변경하고 db를 내렸다 올리시오

SYS @ ora19dw > show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


SYS @ ora19dw > alter system set cursor_sharing=force scope=both;

시스템이 변경되었습니다.

SYS @ ora19dw > startup force
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 2415917880 bytes
Fixed Size                  8899384 bytes
Variable Size             520093696 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SYS @ ora19dw > show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      FORCE


SYS @ ora19dw > connect scott/tiger
연결되었습니다.
SCOTT @ ora19dw >
SCOTT @ ora19dw > select empno, ename from emp where empno=7788;

     EMPNO ENAME
---------- ----------
      7788 SCOTT
      7788 SCOTT
      7788 SCOTT
      7788 SCOTT

SCOTT @ ora19dw > select empno, ename from emp where empno=7902;

     EMPNO ENAME
---------- ----------
      7902 FORD
      7902 FORD
      7902 FORD
      7902 FORD

SCOTT @ ora19dw > select sql_text
  2               from v$sql
  3               where sql_text like 'select empno, ename%';

SQL_TEXT
--------------------------------------------------------------------------------
select empno, ename from emp where empno=:"SYS_B_0"

---

문제2. 아래의 10000번의 하드파싱을 일으키는 PL/SQL을 실행해보시오

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;
/

SCOTT @ ora19dw >
SCOTT @ ora19dw > select sql_text
                  from v$sql
                  where sql_text like 'select object_name%';

SQL_TEXT
--------------------------------------------------------------------------------
select object_name from all_objects where object_id = :"SYS_B_0"

[주의사항] cursor_sharing을 force로 하게되면 모든 리터럴 SQL을 바인드 변수로 변경하기 때문에 아래의 SQL 실행계획이 하나로 고정됨

아래의 ename에 인덱스가 생성되어있는 상태입니다

select count(*) from emp10000 where ename=’scott1’;  — 1건

select count(*) from emp10000 where ename=’scott99’;  — 9999건 

                                             

select count(*) from emp10000 where ename= :v_ename;

---

[실습2] 아래의 환경을 만들고 아래의 SQL의 실행계획을 확인하시오

insert into emp
 select *
    from emp;

SCOTT @ ora19dw > /

3584 행이 생성되었습니다.


SCOTT @ ora19dw > commit;

커밋이 완료되었습니다.


SCOTT @ ora19dw > update emp
  2               set ename='scott99';

7168 행이 업데이트되었습니다.

SCOTT @ ora19dw >
SCOTT @ ora19dw > commit;

커밋이 완료되었습니다.


SCOTT @ ora19dw > update emp
  2               set ename='scott1'
  3              where rownum = 1;

1 행이 업데이트되었습니다.


SCOTT @ ora19dw > commit;

SCOTT @ ora19dw > create index emp_ename on emp(ename);

아래의 sql은 오렌지에서 실행계획을 확인합니다.

select count() from emp where ename='scott1';
select count() from emp where ename='scott99';

---

문제2. 다시 cursor_sharing을 exact 로 변경하고 다시 아래의 실행계획을 확인해보시오

select count(*) from emp where ename='scott99'; 
select count(*) from emp where ename='scott1';