buffer pinning, SQL TRACE

■ Buffer Pinning
1) 정의: 버퍼를 읽은 후에 해당 버퍼의 pin을 해제하지 않고 유지하는 기능으로, 동일한 블록을 반복적으로 읽어갈 때 성능을 향상시키는 메커니즘.

2) 정점

- Latch의 점유 시간 감소: 버퍼를 pinning하여 여러 트랜잭션이 동시에 동일한 버퍼를 접근할 때 발생하는 경합(latch contention)을 감소시킴.

- Block I/O 감소: 동일한 블록을 반복적으로 읽을 때, 버퍼를 유지하면 추가적인 디스크 I/O를 발생시키지 않고 메모리 상에서 작업을 수행할 수 있어 I/O 비용을 줄일 수 있음.

3) Index Range Scan: 인덱스의 범위 스캔 시 leaf block을 다시 접근하는 I/O를 감소시킴. Index에서 찾은 RowID를 사용하여 동일한 블록을 다시 접근할 때 I/O를 최소화함.

4) Buffer Pinning은 동일한 블록을 반복적으로 접근하는 작업에서 효과적이며, 특히 Index Range Scan 등에서 성능 향상을 기대할 수 있음.
5) 다만, 과도한 Buffer Pinning은 메모리 리소스를 소비할 수 있으므로 신중하게 사용해야 함.
6) 데이터베이스 엔진은 자동으로 Buffer Pinning을 관리하며, 개발자가 명시적으로 설정할 필요가 없음.

 

---

■  SQL TRACE 
1) 정의

- SQL문의 수행에 대한 세부 정보를 추적하여 trace 파일을 생성하는 기능. 이를 통해 SQL문의 각 단계에서 소요되는 시간, 통계 정보 등을 파악할 수 있음.

2) 레벨

- Instance Level: 전체 데이터베이스 인스턴스에 대한 SQL TRACE 를 수행.

- Sessionc Level: 특정 세션에 대한 SQL TRACE를 수행.

3) 장점

- 성능분석: SQL TRACE를 통해 SQL문의 구문 분석, 실행, fetch 단계에서 어느 부분에서 시간이 오래 걸리는지 세밀하게 파악 가능.

- 통계분석 : 세션 또는 인스턴스 전체의 통계 정보를 확인하여 성능 튜닝에 도움을 줌.

- Server 유저 확인: 서버에서만 유저들이 확인할 수 있도록 구성되어 있어 보안 측면에서 유용.

4) 사용 예시

- SQL TRACE를 사용하여 특정 세션 또는 전체 인스턴스에서 실행되는 SQL문의 성능을 측정하고, 병목 현상을 해결하거나 최적화할 수 있음.

- 특정 유저 또는 세션의 SQL TRACE를 활용하여 성능 저하의 원인을 파악하고 개선할 수 있음.

5) 주의 사항

- SQL TRACE는 성능 분석 및 튜닝을 위한 도구로, 실제 운영 환경에서는 필요에 따라 주의하여 사용해야 함.

- TRACE 파일은 세션 또는 인스턴스가 종료될 때 생성되며, 해당 파일을 분석하여 성능 개선에 활용할 수 있음.

- Oracle 데이터베이스에서는 특별한 권한 없이도 `ALTER SESSION SET SQL_TRACE=TRUE;`와 같은 SQL문을 사용하여 세션 레벨의 SQL TRACE를 활성화할 수 있음.

show parameter user_dump_dest

#) 나만의 고유 식별자 생성

alter session set tracefile_identifier = 'test';

#) 내 session에서 trace를 진행하겠다

alter session set sql_trace = true;

#) test 해보기

select * from hr.employees where employee_id = 100;

#) trace 종료

alter session set sql_trace = false;

#) 확인해보기
[oracle1@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/
[oracle1@oracle trace]$ ls *test.trc
ora11g_ora_15463_test.trc

#) 리포트 작업 
[oracle1@oracle trace]$ tkprof ora11g_ora_15463_test.trc ex01.txt
[oracle1@oracle trace]$ vi ex01.txt
-> SQL문장 확인

[oracle1@oracle trace]$ tkprof ora11g_ora_15463_test.trc ex02.txt sys=no
[oracle1@oracle trace]$ vi ex02.txt

#) HR 계정에서 해보기 (즉, dba 계정이 아닌 user session 에서도 가능.) 

alter session set tracefile_identifier = 'hr';

alter session set sql_trace = true;

select * from hr.employees where employee_id = 100;

alter session set sql_trace = false;

[oracle1@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_15613_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_15613_hr.trc hr01.txt sys=no

TKPROF: Release 11.2.0.4.0 - Development on Tue Feb 13 15:56:38 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle1@oracle trace]$ vi hr01.txt
...

select 문 => Fetch 라인에서 disk, query 본다
DML => Execute 라인에서 disk, current 본다

#) 설명
1) Parse : SQL문을 parsing 하고 실행계획을 생성하는데 대한 통계 정보 | SELECT문, DML 작업
2) Execute : DML 문에 대한 통계정보 | DML 작업, disk+current
3) Fetch : SELECT문에 대한 통계정보 | SELECT문,  disk+query 

4) count : 실행 횟수
5) cpu : Operation의 처리 했을 경우의 소요되는 시간(초)
6) elapsed : 처리에 소요되는 총 경과 시간(초) + 대기시간 포함
7) disk : 물리적 block 읽은 수
8) query : 일관성 있는 (consistent) 읽기를 위한 논리적 buffer 읽기 수(select 작업 시 본다)
9) current : 현재 모드에서 논리적 buffer 읽기 수(dml 작업 시 본다)
10) rows : 처리한 행의 수

11) cr : consistent 블록 읽기 수 (query의 매핑해서 보기)
12) pr : 물리적 블록 읽은 수 (current의 매핑해서 보기)
13) pw : 물리적 블록 쓰기 수
14) time : 수행 단계에서 소요된 시간(microsecond 값)

#)  hr계정 - 부서번호 = 20

alter session set tracefile_identifier = 'hr1';
alter session set sql_trace = true;
select * from hr.employees where department_id = 20;
alter session set sql_trace = false;

[oracle1@oracle trace]$ ls *hr1.trc
ora11g_ora_16019_hr1.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_16019_hr1.trc dept01.txt sys=no
[oracle1@oracle trace]$ vi dept01.txt
...