[Oracle] Shared Pool(공유 풀)

봄·2025년 8월 20일

오라클 관리

목록 보기
13/163

이론1. 평상시 dba가 하는 일

💡 db에서 발생하는 문제들을 해결하는 역할


이론2. SGA 영역에서 가장 큰 메모리 영역

💡 Shared pool


이론3. 공유풀(shared pool)의 역할?

💡 파싱된 SQL의 결과물을 올려놓는 메모리 영역


이론4. SELECT 문의 처리과정 3가지를 설명하세요.

select ename, sal from emp where ename=’SCOTT’;

  1. 파싱(parsing)
  • 문법검사(syntex check)
    : SQL이 문법적으로 문제가 있는지 확인
  • 의미검사(symentic check)
    : emp 테이블이 database에 있는지 확인

    파싱 결과물 3가지? 1. SQL문장 2. 실행계획 3. 파스트리 (parse tree: 실행가능한 코드)

    위의 3가지를 공유풀에 올립니다.

    🔸 왜 올릴까요? 다음에 똑같은 문장이 실행되면 파싱과정 생략하려고
    파싱작업은 cpu 를 과도하게 사용하는 무거운 작업
  1. 실행(execute)
    : 옵티마이져가 생성해준 실행계획으로 실행 데이터를 검색하는 작업
  2. 패치(fetch)
    : 서버 프로세서 ———— 결과 데이터 ——————> 유져 프로세서

실습1. 아래의 SQL을 실행하고 공유풀에 아래의 SQL이 파싱되서 들어있는지 확인하시오. 먼저 SCOTT으로 접속해서 다음의 SQL을 작성하고 실행합니다.

실습2. SYS 유져로 sqldeveloper 로 접속해서 공유풀에 위의 SQL이 있는지 조회하시오

select sql_id, child_number, sql_text
 from v$sql
 where sql_text like '%emp%' AND sql_text NOT LIKE '%v$sql%'
 order by last_load_time desc;

💡 똑같은 문장이 다음번에 실행되어야지만 파싱과정을 생략할 수 있습니다. 그럼 똑같은 문장이란?

1. **대소문자 구분**

예: select ename, sal from emp;
      SELECT ENAME, SAL FROM EMP;

2. **공백과 들여쓰기 구분**

예: select ename,sal from emp;
     select    ename,   sal  from  emp;

실습3. 이번에는 대문자로 아래의 SQL을 작성하고 실행합니다.

실습4. sys 유져에서 다음과 같이 확인합니다.

똑같은 문장이 다음번에 실행되어야지만 파싱과정을 생략할 수 있습니다. 그럼 똑같은 문장이란?

  1. 대소문자 구분

예: select ename, sal from emp;
SELECT ENAME, SAL FROM EMP;

  1. 공백과 들여쓰기 구분

예: select ename,sal from emp;
select ename, sal from emp;

  1. 리터럴 SQL을 구분 (★)

예: select empno, ename, sal from emp where empno = 7788;
select empno, ename, sal from emp where empno = 7902;

실습5. 터미널 창을 열고 top명령어를 치고 준비를 하고 있습니다.

실습6. 리터럴 SQL을 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 .. 1000
 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;
/

select sql_id, child_number, sql_text
from v$sql
where lower(sql_text) like '%select object_name%' AND sql_text NOT LIKE '%v$sql%'
order by last_load_time desc;

💡 오라클 공유풀 메모리 영역을 리터럴 SQL로 가득 채웠습니다. 이 영역은 위의 sql 말고도 다른 공유받아야할 중요한 sql이 있어야할 공간인데 쓸데없이 리터럴 SQL로 가득차버렸습니다.

--> 해결방법: 바인드 변수

실습7. 위의 상황을 해결하시오

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

💡 회사에 PL/SQL 개발자(DB프로그래밍 개발자)들에게 DBA가 리터럴 SQL을 바인드 변수로 변경해서 프로그래밍하라고 권고해줘야합니다.

문제1. 공유풀(Shared Pool)이란?

파싱을 최소화 하기 위해서 SQL을 공유하는 메모리 영역


문제2. 공유풀에 부하를 주는 주요 SQL은?

리터럴 SQL


문제3. top 명령어로 확인한 cpu를 과도하게 사용하는 프로세서가 오라클에서 어떤 작업을 하고 있는지 확인하는 방법은?

5702

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

프로세서 5702 번호의 유져가 DB에서 어떤 SQL을 수행하는지 확인하는 방법

-- SPID를 통해 세션 정보와 현재 SQL 확인
SELECT s.sid, s.serial#, s.username, s.program, s.machine,
       s.status, s.sql_id, t.sql_text
FROM v$session s, v$sqltext t, v$process p
WHERE s.paddr = p.addr
 AND p.spid = 5702
  AND s.sql_id = t.sql_id(+)
  AND t.piece = 0
ORDER BY s.sid;

alter system kill session '9,32834' immediate;

문제4. 지금까지 만든 스크립트들을 dba.sh 쉘에 정리하시오

#!/bin/bash

echo -e "
           dba 작업을 자동화하는 쉘 스크립트
"
echo -e " ================================= "
echo "                                       "
echo "[1] DB에서 발생한 TX 락을 확인하려면 1번을 누르세요.
[2] DB에서 발생한 악성 SQL을 확인하려면 2번을 누르세요
[3] TOP명령어로 확인한 프로세서 번호로 해당 세션의 정보를 확인하고 싶으면 3번을 누르세요"
echo "                                "
echo -n "원하는 작업번호를 누르세요 "
read aa
echo "                                "
case $aa in
    1) sqlplus -s system/oracle_4U @/home/oracle/lock.sql ;;
    2) sqlplus -s system/oracle_4U @/home/oracle/bad.sql ;;
esac
echo "

문제5. dba.sh 에 3번에 spid 로 해당 프로세서의 정보를 확인하는 sql을 추가하시오

아래의 스크립트를 /home/oracle 밑에 spid.sql 로 저장하세요.

accept  p_spid prompt 'spid 를 입력하세요  ~  '
set verify off

-- SPID를 통해 세션 정보와 현재 SQL 확인
SELECT s.sid, s.serial#, s.username, s.program, s.machine,
       s.status, s.sql_id, t.sql_text
FROM v$session s, v$sqltext t, v$process p
WHERE s.paddr = p.addr
 AND p.spid = &p_spid
  AND s.sql_id = t.sql_id(+)
  AND t.piece = 0
ORDER BY s.sid;

아래의 쉘 스크립트를 dba.sh 로 저장하세요

#!/bin/bash

echo -e "
           dba 작업을 자동화하는 쉘 스크립트
"
echo -e " ================================= "
echo "                                       "
echo "[1] DB에서 발생한 TX 락을 확인하려면 1번을 누르세요.
[2] DB에서 발생한 악성 SQL을 확인하려면 2번을 누르세요
[3] TOP명령어로 확인한 프로세서 번호로 해당 세션의 정보를 확인하고 싶으면 3번을 누르세요"
echo "                                "
echo -n "원하는 작업번호를 누르세요 "
read aa
echo "                                "
case $aa in
    1) sqlplus -s system/oracle_4U @/home/oracle/lock.sql ;;
    2) sqlplus -s system/oracle_4U @/home/oracle/bad.sql ;;
    3) sqlplus -s system/oracle_4U @/home/oracle/spid.sql ;; 
esac
echo "

지금까지 스크립트 총정리

1. lock.sql

col holder for a15
col waiter for a15
select decode(status,'INACTIVE',username || ' ' || sid || ',' || serial#,'lock') as Holder,
       decode(status,'ACTIVE',  username || ' ' || sid || ',' || serial#,'lock') as waiter, sid, serial#, status
    from( select level as le, NVL(s.username,'(oracle)') AS username,
    s.osuser,
    s.sid,
    s.serial#,
    s.lockwait,
    s.module,
    s.machine,
    s.status,
    s.program,
    to_char(s.logon_TIME, 'DD-MON-YYYY HH24:MI:SS') as logon_time
       from v$session s
      where level>1
                or EXISTS( select 1
    from v$session
    where blocking_session = s.sid)
      CONNECT by PRIOR s.sid = s.blocking_session
  START WITH s.blocking_session is null );

!echo "락 홀더 세션을 죽이고 싶으면 아래의 SQL을 참고하세요"
!echo " alter system kill session '136,61515' immediate; "

2. bad.sql

col username for a10
-- 높은 리소스를 사용하는 SQL 세션
WITH resource_intensive AS (
    SELECT
        sql_id,
        sql_text,
        executions,
        disk_reads,
        buffer_gets,
        cpu_time/1000000 AS cpu_seconds,
        elapsed_time/1000000 AS elapsed_seconds,
        CASE
            WHEN executions > 0 THEN ROUND(disk_reads/executions, 2)
            ELSE disk_reads
        END AS disk_reads_per_exec,
        CASE
            WHEN executions > 0 THEN ROUND(buffer_gets/executions, 2)
            ELSE buffer_gets
        END AS buffer_gets_per_exec
    FROM v$sqlarea
    WHERE (disk_reads > 100000 OR buffer_gets > 1000000 OR cpu_time > 10000000)
)
SELECT
    s.sid,
    s.serial#,
    s.username,
    SUBSTR(ri.sql_text, 1, 100) AS sql_text,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command
FROM
    v$session s,
    resource_intensive ri,
    v$sqlarea sa
WHERE
    s.sql_address = sa.address
    AND s.sql_hash_value = sa.hash_value
    AND sa.sql_id = ri.sql_id
    AND s.username IS NOT NULL
ORDER BY ri.cpu_seconds DESC, ri.disk_reads DESC;

3. spid.sql

accept  p_spid prompt 'spid 를 입력하세요  ~  '
set verify off

-- SPID를 통해 세션 정보와 현재 SQL 확인
SELECT s.sid, s.serial#, s.username, s.program, s.machine,
       s.status, s.sql_id, t.sql_text
FROM v$session s, v$sqltext t, v$process p
WHERE s.paddr = p.addr
 AND p.spid = &p_spid
  AND s.sql_id = t.sql_id(+)
  AND t.piece = 0
ORDER BY s.sid;
profile
봄
이전 포스트

[Oracle] DBA에게 유용한 스크립트 활용법

다음 포스트

[Oracle] DB Buffer Cache

0개의 댓글