데이터베이스 Oracle

image-20211207094310973

  • https://rastalion.me/oracle-buffer-cache-%ED%8A%9C%EB%8B%9D%EA%B3%BC-multiple-buffer-pool%EC%9D%98-%EC%82%AC%EC%9A%A9/
  • https://docs.oracle.com/cd/E25178_01/server.1111/e25789/memory.htm
  • https://1duffy.tistory.com/20, https://ttend.tistory.com/795?category=391739, https://ttend.tistory.com/796?category=391739
  • https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=ttltweety&logNo=60158891969

어떤 사용자가 SQL 문장을 질의 했을때 Server Process가 가장 먼저하는 일은 Parse 작업을 통해 만들어진 실행계획으로 Database Buffer Cache에 필요한 데이터가 존재하는지 확인하고, 존재하지 않는다면 서버 프로세스가 필요한 데이터 블록을 디스크로부터 Database Buffer Cache로 읽게 됩니다. 한명의 사용자가 사용했을 때는 상관없지만, 다수의 사용자가 동시에 Database Buffer Cache를 접근하려고 할때 읽기 일관성을 유지하기 위해 Latch라는 메모리에 대한 Lock을 확보하여 Latch를 소유하고 있는 사용자만 해당 Block에 대한 작업 수행이 가능하여 다른 사용자가 대기하는 현상이 발생합니다.

디스크에서 읽고 기록하는 속도보다 메모리에서 일곡 기록하는 속도가 더 빠르기 때문에 데이터베이스 성능 향상을 위해 가급적 필요한 데이터를 디스크 I/O없이 메모리에서 읽어오는 것이 좋다. 이를 위해 보통 SGA(System Global Area) 영역 중에서 Database Buffer Cache에 가장 많은 크기를 할당한다.

show sga;

Total System Global Area 18845835264 bytes
Fixed Size 2189448 bytes
Variable Size 4227864440 bytes
Database Buffers 14562623488 bytes
Redo Buffers 53157888 bytes

Database Buffer Cache는 오라클 인스턴스에 접속된 모든 프로세스들이 공유하여 사용하고 자주 사용되는 데이터 블럭에 대한 물리적 I/O를 줄이는데 있습니다.

데이터베이스 버퍼 캐시를 효과적으로 사용하려면 불필요한 자원 소비를 피하기 위해 응용 프로그램에 대한 SQL 문을 튜닝해야합니다. 그러기 위해서는 자주 실행되는 SQL 문과 버퍼에 많은 블럭을 읽어오는 SQL 문이 올바르게 작성되었는지 확인해야합니다.

서버 프로세스는 필요한 데이터 블록의 수가 여러 개 일지라도 하나씩 데이터 버퍼 캐쉬로 읽게 된다. 하지만 SQL에서 전체 테이블 검색을 수행하는 경우 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터에 지정된 데이터 블록의 수만큼 데이터 버퍼 캐쉬로 한번에 읽게 된다. DB_FILE_MULTIBLOCK_READ_COUNT 파라메터의 설정 값은 다음과 같이 확인할 수 있다.

show parameter DB_FILE_MULTIBLOCK_READ_COUNT;

db_file_multiblock_read_count integer 128

show parameter DB_BLOCK_SIZE ;

db_block_size integer 8192

블록 크키가 클 때

장점

메모리에서 데이터 블록 사용 율이 높음

한번의 디스크 I/O로 많은 데이터 추출 가능

데이터 블록이 클수록 많은 데이터를 저장하기 때문에 한번 데이터 버퍼 캐쉬로 테이터 블록을 읽으면 재사용할 확률이 높아지게 된다.

단점

데이터 블록에 대한 경합 발생 가능성 증가

동시에 많은 유저가 동일 데이터 블록을 사용할 확률이 높아지므로 해당 데이터 블록에 대한 경합이 발생할 수 있다.

블록 크키가 작을 때

장점

데이터 블록에 대한 경합 발생 가능성 감소

이와 같이 경합이 발생할 확률은 감소하지만 반면에 같은 양의 데이터를 엑세스하는 경우에 더 많은 디스크 I/O를 발생시킬 확률이 높아지게 된다. 위 그림은 데이터 블록을 4KB로 지정한 예이다. 유저 A와 B가 수행한 SQL의 결과 값을 추출하기 위해서 두 번의 디스크 I/O가 발생하였다.

단점

메모리에서 데이터 블록 사용률이 낮음

한번의 디스크 I/O로 적은 데이터 추출 가능

기타

  • Oracle은 단순 읽을 때는 Lock을 사용하지 않기 때문에 Lock 경합이 적게 발생합니다.

  • 트랜잭션이 너무 길면 롤백 시 많은 시간이 걸릴 수 있고, Undo 영역이 고갈되거나 경합을 유발할 수 있음.

  • 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계해야 함.

  • 불필요한 커밋을 너무 자주 하게 되면 Snapshot too old(ORA-01555) 에러를 유발할 가능성 높아지고,

    LGWR이 로그 버퍼를 비우는 동안 발생하는 log file sync 대기 이벤트 때문에 성능 저하 우려 됨.

    Oracle 10g 부터 비동기식 커밋 기능 활용을 검토할 수 있음.(Commit 옵션에 NOWAIT를 주면 비동기식 커밋을 함.)

  • Q. SID는 세션식별자로 유니크한 값을 가지는데 SERIAL#값까지 갖는 이유는 무엇일까?

    A. SID는 사용자별로 할당되는 값이 아니라 세션마다 할당되기 때문에 재사용됩니다. 만약 10, 12번 세션을 확인 후 KILL SESSION명령을 하기전에 10번 세션이 로그아웃을 하고 바로 다른 세션이 연결되어서 SID가 10번으로 할당되는 경우가 있다고 해봅시다. 이럴경우 SID만 가지고 세션을 KILL시키면 내가 원하는 세션이 아닌 다른 세션을 KILL 시키는 문제가 발생할 수 있습니다. 그래서 SERIAL#값을 하나 더 두어 세션을 구분하는 것입니다.

  • 오라클 모니터링 관련 명령어 모음 - http://dbcafe.co.kr/wiki/index.php/ORACLE_%EB%AA%A8%EB%8B%88%ED%84%B0%EB%A7%81

Oracle에서 세션 캐시 커서 조정

https://smarttechways.com/2020/01/13/session-cached-cursor-tuning-in-oracle/

###

오라클서버의 메모리에 관한 테이블 select * from v$sgastat

cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기 select c.sql_text ,b.SID , b.SERIAL# ,b.machine ,b.OSUSER ,b.logon_time –이 쿼리를 호출한 시간 from v$process a, v$session b, v$sqltext c where a.addr = b.paddr and b.sql_hash_value = c.hash_value and a.spid = ‘675958’ order by c.PIECE

cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text from v$process a, v$session b, v$sqltext c where a.addr = b.paddr and b.sql_hash_value = c.hash_value and a.spid = ‘171’ order by c.PIECE

프로세스 아이디를 이용하여 쿼리문 알아내기 select c.sql_text ,b.SID , b.SERIAL# ,b.machine ,b.OSUSER ,b.logon_time –이 쿼리를 호출한 시간 from v$process a, v$session b, v$sqltext c where a.addr = b.paddr and b.sql_hash_value = c.hash_value and a.spid = ‘1708032’ –1912870/ order by c.PIECE