78 SGA, Shared Pool, Select

■ SGA : System Global Area


■ Shared Pool
- Library Cache : SQL,PL/SQL문이 저장되어 있는 메모리
- Data Dictionary Cache : 딕셔너리 정보들이 저장되어 있는 메모리
- session 정보 : v$session
- Shared Server 환경을 사용할 경우 UGA(User Global Area)가 포함된다.
- shared_pool_size


select employee_id, last_name, salary
from hr.employees where employee_id = 100;
EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000

> 실행계획이 없는 상태에서는 절대 SQL문 수행하지 않는다.
> 실행계획
1. 데이터 처리 결정
    - rowid scan
    - by user rowid
    - by index rowid (primary key, unique key, ..)
    - full table scan
2. 조인 방법
    - hash
    - nested loop
    - sort merge
3. 조인 순서
    - from절에 나열되어있는 테이블의 갯수만큼 순서를 결정해야 한다.
    - 만약에 조인수행해야할 테이블의 갯수가 3! 경우수만큼 결정
      예) 대소문자, 공백 무조건 일치

---

■ SELECT 처리 단계

1. Parse
1) 문법체크, 의미분석 체크 (유저, 테이블, 컬럼), 권한 체크

2) 동일한 SQL문이 shared pool 메모리 내 library cache 안에 있는지 조회
   왜? 동일한 SQL문이 있으면 실행계획을 공유하기 위해서 library cache latch를 잡고 검색해야한다.
    latch : library cache wait event 발생한다
>> 경합이 발생한다고 무조건 안좋은건 아님
>> 너무 과도하게 발생하면 메모리만 보지말고 문장을 의심하자

2) soft parsing이 실패 즉 동일한 SQL문이 shared pool 메모리 안에 library chache 존재하지 않습니다. 이때가 바로 hard parsing 발생할 때

3) hard parsing이 발생하면 실행계획을 새롭게 생성해야한다.
   - 메모리 공간 확보
        latch : shared pool wait event 발생
    만약에 프리공간이 확보 안될 경우 ORA-04031 오류 발생할 수 있다.
    예를들어 프리 조각이 7k, 10k, 1k, 100byte 이렇게 나있을 경우
    내가 필요한 프로 공간은 8k 프리공간이 필요한 경우 프리로 있는
    10k 프리 공간을 8k로 확보하고 2k 프리 공간으로 남겨놓는다.
    7k, 2k, 1k, 100byte 이런 프리 공간이 있을 경우 8k 프리 공간을 확보 하기 위해서 latch를 잡고 프리검색을 하는데 없어서 ORA-04031 오류 발생한다. ->flush

- 프리를 확보한 후 LCO(Library Cache Objetc)를 생성해야한다.
- LCO를 생성하기 위해서 library cache lock (exclusive) 획득해야한다.
  LCO가 생성되는 library cache lock (null) 모드로 변환된다.
- library cache pin (exclusive) 모드로 변환 후 실행계획을 생성한다.
- 실행계획이 생성이 끝난 경우 execute 단계로 간다.

하드파싱 과다 발생? -> SQL 문장 의심하자 : 문장 튜닝
하드파싱 발생 > 랫츠 잡고 > 공간확보

LCO에 따른 종속관계 정보
실제 셀렉트 문장에 대한 정보
child cursor

version count 만들어짐


프로그램,child cuser,실행계획



2. Bind (옵션)
- SQL문에 변수 처리가 되어 있을 경우 변수에 실제값이 입력되는 단계



3. Execute
- library cache lock 과 library cache pin shared 모드로 변환하고 SQL문을 실행한다.
- 블록 I/O 발생, Data Buffer Cache, 영역을 본다.



4. Fetch
- library cache lock null 모드로 변환하고 library cache pin 해제한다.
- active set 결과를 user process에 전달한다.

---

# Shared Pool Flush : Shared Pool 안에 내용 clear
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;


> parse > 딕셔너리 캐시 초기화 - 데이터버퍼캐시 체크 - 없으면 OS read

select * from hr.employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER
----------- -------------------- ------------------------- ------------------------- --------------------
HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
--------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567
17-JUN-03 AD_PRES         24000                                      90


select sql_id,sql_text,parse_calls,loads,executions from v$sql
where sql_text like '%hr.employees%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100           1          1          1



select * from hr.employees where employee_id = 100;
select * from hr.employees where employee_id = 101;
select * from hr.employees where employee_id = 102;
select * from hr.employees where employee_id = 103;
select * from hr.employees where employee_id = 104;
select * from hr.employees where employee_id = 105;


select sql_id,sql_text,parse_calls,loads,executions from v$sql
where sql_text like '%hr.employees%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
82mnzcywm53rs select * from hr.employees where employee_id = 101           1          1          1
czmhpuznkxr1r select * from hr.employees where employee_id = 102           1          1          1
dfhvs4gks9tk5 select * from hr.employees where employee_id = 104           1          1          1
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100           2          1          2
gdy73zzyafm4f select * from hr.employees where employee_id = 105           1          1          1
crmr8navwm6mf select * from hr.employees where employee_id = 103           1          1          1



# 실행계획 보기
select * from table(dbms_xplan.display_cursor('2sgjc8u8ha0m4'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2sgjc8u8ha0m4, child number 0
-------------------------------------
select * from hr.employees where employee_id = 100

Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=100)
        -- 필터가 나올 경우  : 걸러야한다 = Full Back

19 rows selected.





# 무효화 발생
- 통계수집
- 실행계획의 변경
- 인덱스 변경
- 테이블의 구조 변경





alter system flush shared_pool;

select * from hr.employees where employee_id = 100;


#  같은 실행계획을 사용 중
select sql_id,sql_text,parse_calls,loads,executions,plan_hash_value from v$sql
where sql_text like '%hr.employees%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
PLAN_HASH_VALUE
---------------
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100           1          1          1
     1833546154


select plan_hash_value,count(hash_value) from v$sql
group by plan_hash_value having count(hash_value) >= 2 order by 2 desc;
PLAN_HASH_VALUE COUNT(HASH_VALUE)
--------------- -----------------
      903671040                 4
     1395584798                 3
     2909482823                 3
      427385382                 2
     2874733959                 2
      723397186                 2
     3214763678                 2

7 rows selected.





alter system flush shared_pool;

var b_id number

execute :b_id := 101

select * from hr.employees where employee_id = :b_id;
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER
----------- -------------------- ------------------------- ------------------------- --------------------
HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
--------- ---------- ---------- -------------- ---------- -------------
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568
21-SEP-05 AD_VP           17000                       100            90


select plan_hash_value, count(hash_value) from v$sql
group by plan_hash_value having count(hash_value) >= 2 order by 2 desc;
PLAN_HASH_VALUE COUNT(HASH_VALUE)
--------------- -----------------
      903671040                 3
     2874733959                 2
      723397186                 2


select sql_id,sql_text,parse_calls,loads,executions,plan_hash_value,hash_value
from v$sql where sql_text like '%hr.employees%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
PLAN_HASH_VALUE HASH_VALUE
--------------- ----------
gdh3251n9spny select * from hr.employees where employee_id = :b_           1          1          1
              id
     1833546154 1755076254

>> 로드 수 증가? 






alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;


create table hr.emp as select * from hr.employees;


select last_name, salary from hr.emp where employee_id = 100;
LAST_NAME                     SALARY
------------------------- ----------
King                           24000


select sql_id,sql_text,parse_calls,loads,executions,plan_hash_value,hash_value,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
PLAN_HASH_VALUE HASH_VALUE INVALIDATIONS
--------------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe           1          1          1
              e_id = 100
     2466118986 4137362573             0

- PARSE_CALLS, EXECUTIONS : 보통 같이 증가 함
- LOADS : 증가하지 않는게 좋다
- invalidations : 무효화


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

- BLOCKS      테이블이 사용하고 있는 블럭 수
- AVG_ROW_LEN 한행의 바이트 값   > 값에 따라 조인의 종류가 달라진다

>> 통계정보가 없다. > 실행계획을 못만든다


select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  8qhp0dmv9q94d, child number 0
-------------------------------------
select last_name, salary from hr.emp where employee_id = 100

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    40 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPLOYEE_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)
        >> 실행계획을 만들고자 동적으로 표본을 추출하여 통계수집

22 rows selected.


select sql_id,sql_text,parse_calls,loads,executions,plan_hash_value,hash_value,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
PLAN_HASH_VALUE HASH_VALUE INVALIDATIONS
--------------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe           1          1          1
              e_id = 100
     3956160932 4137362573             0



# 통계 수집
execute dbms_stats.gather_table_stats('hr','emp',no_invalidate=>false)
PL/SQL procedure successfully completed.

- ('소유자','테이블명',
- no_invalidate : True , 기본값, 즉시 무효화 하지마


select num_rows, blocks, avg_row_len from dba_tables where owner = 'HR' and table_name = 'EMP';
  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
       107          5          69


select sql_id,sql_text,parse_calls,loads,executions,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
INVALIDATIONS
-------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe           1          1          1
              e_id = 100
            1






alter system flush shared_pool;


select last_name, salary from hr.emp where employee_id = 100;


select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));


# Unique Index
create unique index hr.emp_idx on hr.emp(employee_id);


alter table hr.emp add constraint emp_id_pk primary key(employee_id);

>> 자동으로 성공? 원래 실패 시 using index 붙여서 사용

alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx;


select * from dba_constraints where owner = 'HR' and table_name = 'EMP';


select last_name, salary from hr.emp where employee_id = 100;


select sql_id,sql_text,parse_calls,loads,executions,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
INVALIDATIONS
-------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe           1          1          1
              e_id = 100
            0

>>> LOADS가 증가되어야 하는데????



# 테이블 구조의 변경

desc hr.emp
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ----------------------------------------
 EMPLOYEE_ID                                                 NOT NULL NUMBER(6)
 FIRST_NAME                                                           VARCHAR2(20)
 LAST_NAME                                                   NOT NULL VARCHAR2(25)
 EMAIL                                                       NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                                         VARCHAR2(20)
 HIRE_DATE                                                   NOT NULL DATE
 JOB_ID                                                      NOT NULL VARCHAR2(10)
 SALARY                                                               NUMBER(8,2)
 COMMISSION_PCT                                                       NUMBER(2,2)
 MANAGER_ID                                                           NUMBER(6)
 DEPARTMENT_ID                                                        NUMBER(4)


alter table hr.emp modify last_name varchar2(30);


select sql_id,sql_text,parse_calls,loads,executions,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
INVALIDATIONS
-------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe           2          1          2
              e_id = 100
            1


select last_name, salary from hr.emp where employee_id = 100;


select sql_id,sql_text,parse_calls,loads,executions,invalidations
from v$sql where sql_text like '%hr.emp%' and sql_text not like '%v$sql%';
SQL_ID        SQL_TEXT                                           PARSE_CALLS      LOADS EXECUTIONS
------------- -------------------------------------------------- ----------- ---------- ----------
INVALIDATIONS
-------------
3cfbnypy847km select * from hr.employees where employee_id = 100           1          1          1

            0

8qhp0dmv9q94d select last_name, salary from hr.emp where employe           1          2          1
              e_id = 100
            1

---

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-shared-pool-and-large-pool.html#GUID-7FD646B3-FCE3-4D3C-B988-C8D138FFD514

Database Performance Tuning Guide

 

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-0EE333AF-E9AC-40A4-87D5-F6CF59D6C47B

PL/SQL Packages and Types Reference