OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.18.0.0(메인), 19.15.0.0
방법 : 오라클 19c Memoptimize pool 테스트(memoptimize for read)
memoptimize pool 이란?
memoptimize pool은 MEMOPTIMIZE FOR READ로 지정된 힙 구성 테이블에 대한 버퍼 및 관련 구조를 저장함
이 구조는 SELECT * FROM cust WHERE cid = 10과 같은 key 기반 쿼리에 대해 높은 성능과 확장성을 제공함
엔드 투 엔드 응답 시간을 줄이기 위해 클라이언트는 요청된 버퍼를 네트워크를 통해 SGA에서 직접 끌어와 CPU 및 OS의 오버헤드를 방지함
애플리케이션은 코드를 변경할 필요 없이 memoptimize pool의 이점을 누릴 수 있음
memoptimize pool은 두 부분으로 구성되어 되어있음
Memoptimize buffer area
디스크 I/O를 방지하기 위해 데이터베이스는 테이블이 NO MEMOPTIMIZE FOR READ로 변경되기 전까지
memoptimize pool에 존재하는 MEMOPTIMIZE FOR READ 테이블에 대한 버퍼를 영구적으로 잠금
memoptimize 버퍼는 db buffer cache의 버퍼와 동일한 구조를 사용함
그러나 memoptimize pool의 버퍼는 db buffer cache와 완전히 분리되어 db buffer cache 크기에 반영되지 않음
memoptimize buffer area는 memoptimize pool의 75%를 차지함
Hash index
Hash index는 비지속적인(non-persistent) 세그먼트 데이터 구조임
데이터베이스는 Hash index를 여러 비연속 메모리 단위로 할당함
각 단위에는 여러 개의 해시 버킷이 포함되어 있음
별도의 맵 구조는 메모리 단위를 기본 키와 연관시킴
해시 인덱스는 memoptimize pool의 25%를 차지함
memoptimize pool을 활성화하려면 MEMOPTIMIZE_POOL_SIZE 파라미터를 정수 값으로 설정해야함 (기본적으로 비활성화 상태)
이 값은 memoptimize pool에 할당할 SGA의 양을 지정함
MEMOPTIMIZE_POOL_SIZE 값은 SGA_TARGET에 포함되지만 데이터베이스는 memoptimize pool을 자동으로 늘리거나 줄이지 않음
예를 들어 SGA_TARGET이 10GB이고 MEMOPTIMIZE_POOL_SIZE이 1GB이면 memoptimize pool 이외의 SGA 메모리에 총 9GB를 사용할 수 있음
memoptimize pool의 크기를 변경하려면 수동으로 MEMOPTIMIZE_POOL_SIZE 값을 변경해야 하고 인스턴스를 다시 시작해야 함
alter system set ~ scope=both 명령을 사용해서 pool 크기를 동적으로 변경할 수 없음
DBMS_MEMOPTIMIZE 패키지를 사용하면 테이블을 memoptimize pool에 명시적으로 올릴 수 있음
SQL> execute dbms_memoptimize.populate('IMSI','MEM_TBL');
(명령어 수행시 처음으로 해시 인덱스가 자동으로 생성되고 블록이 메모리에 캐시됨)
Memoptimized Rowstore - Fast Lookup (빠른 조회)이란?
Oracle Database 18c의 뉴피처로 key-value 형태의 쿼리에 매우 빠른 검색을 제공하기 위한 것임
이 기능은 매우 빠른 키 조회를 위해 기존의 B-트리 인덱스 대신에 메모리 내에 있는 해시 인덱스를 사용함
Memoptimized Rowstore Fast Lookup 기능은 Database In-Memory의 일부가 아님
하지만 Oracle Database의 In-Memory 기술 중 하나이고 Database In-Memory와 같은 그룹에서 개발했다고함
기존 기능인 Database In-Memory는 inmemory 컬럼 형식 데이터에 액세스하고 분석 쿼리에 훨씬 더 나은 성능을 제공하여 분석 워크로드를 대상으로 하였다고함
하지만 Memoptimized Rowstore Fast Lookup 기능은 약간 다름
이 기능의 목표는 로우(행) 기반 쿼리의 속도를 높이는 것이라고 함
Fast Lookup 기능은 pk 값에 대한 SELECT 쿼리만 지원함
이렇게 하면 완전히 캐시된 인덱스 및 데이터베이스 블록 조회보다 더 빠르게 액세스할 수 있음
따라서 Fast Lookup 기능은 매우 높은 빈도(high-frequency reads)의 읽기가 필요한 애플리케이션에 매우 유용할 수 있음
Fast Lookup을 위해 활성화된 테이블과 연결된 데이터베이스 블록은
SGA(System Global Area)의 일부인 새로운 memoptimize pool에 고정됨(pinned)
이렇게 하면 키와 데이터 값 모두에 대한 순수 메모리 내 액세스가 보장됨
memoptimize pool 기능 그림
memoptimize pool 을 사용할 때 pk 조회속도가 얼마나 빨라지는지 확인해봄
먼저 memoptimize pool 활성화
|
1
2
3
|
SQL> alter system set memoptimize_pool_size = 500m scope=spfile;
System altered.
|
재기동
|
1
2
3
|
SQL>
shutdown immediate
startup
|
파라미터 확인
|
1
2
3
4
5
6
7
8
|
SQL>
col name for a30
col value for a20
select name, value from v$parameter where name = 'memoptimize_pool_size';
NAME VALUE
------------------------------ --------------------
memoptimize_pool_size 536870912
|
파라미터 확인2
|
1
2
3
4
5
6
7
8
9
|
SQL>
col component for a30
select component, current_size, user_specified_size, granule_size
from v$sga_dynamic_components
where component like 'memopt%';
COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE
------------------------------ ------------ ------------------- ------------
memoptimize buffer cache 536870912 536870912 16777216
|
일반 샘플 테이블 생성 및 pk 생성
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
drop table normal_tbl purge;
create table normal_tbl
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
);
alter table normal_tbl add constraint normal_tbl_pk primary key (id1);
|
일반 샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF normal_tbl%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..1000000 INSERT INTO normal_tbl VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
memoptimize pool 테스트용 샘플 테이블 생성 및 pk 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
drop table mem_tbl purge;
create table mem_tbl
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
segment creation immediate
--memoptimize for read
;
alter table mem_tbl add constraint mem_tbl_pk primary key (id1);
|
테이블 memoptimize pool 설정 변경
|
1
2
3
|
SQL> alter table mem_tbl memoptimize for read;
Table altered.
|
memoptimize pool 테스트용 샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF mem_tbl%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..1000000 INSERT INTO mem_tbl VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
용량 및 memoptimize 기능 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200
col owner for a10
col segment_name for a20
select a.segment_name, b.memoptimize_read, sum(a.bytes)/1024/1024 "MB"
from dba_segments a, dba_tables b
where a.segment_name = b.table_name
and a.segment_name in ('NORMAL_TBL','MEM_TBL')
group by a.segment_name, b.memoptimize_read;
SEGMENT_NAME MEMOPTIM MB
-------------------- -------- ----------
NORMAL_TBL DISABLED 96
MEM_TBL ENABLED 96
|
테이블이 memoptimize pool에 올라갔는지 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col name for a30
col value for 999999999
select n.name, s.value
from v$sysstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'memopt r rows populated';
NAME VALUE
------------------------------ ----------
memopt r rows populated 0
|
아직 올라가지 않음
memoptimize pool 테스트용 테이블을 메모리로 로딩
(명령어 수행시 처음으로 해시 인덱스가 자동으로 생성되고 블록이 메모리에 캐시됨)
|
1
2
3
|
SQL> execute dbms_memoptimize.populate('IMSI','MEM_TBL');
PL/SQL procedure successfully completed.
|
테이블이 memoptimize pool에 올라갔는지 재확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col name for a30
col value for 999999999999
select n.name, s.value
from v$sysstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'memopt r rows populated';
NAME VALUE
------------------------------ -------------
memopt r rows populated 1000000
|
일반 테이블 데이터 조회 쿼리 실행
|
1
2
3
4
5
6
7
8
|
SQL>
select /*+ gather_plan_statistics memtest1 */ *
from normal_tbl
where id1 = 123456;
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ------------------------------ -------- -------- --------- ------------- ---------- ---------- ---------- ----- ----
123456 1.2346E+12 1DBZL 20121009 20211125 15-OCT-20 010-6870-5707 3000 4 1234 SQLP A781
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col sql_text for a80
select sql_id, sql_text
from v$sql
where sql_text like '%gather_plan_statistics memtest1%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
dt24tv5nd1zkd select /*+ gather_plan_statistics memtest1 */ * from normal_tbl where id1 = 123456
d5s4wm57razuj select sql_id, sql_text from v$sql where sql_text like '%gather_plan_statistics memtest1%'
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('dt24tv5nd1zkd', NULL, 'ADVANCED ALLSTATS LAST');
Plan hash value: 838361682
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 1 |00:00:00.01 | 4 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| NORMAL_TBL | 1 | 1 | 109 | 0 (0)| 1 |00:00:00.01 | 4 | 4 |
|* 2 | INDEX UNIQUE SCAN | NORMAL_TBL_PK | 1 | 1 | | 0 (0)| 1 |00:00:00.01 | 3 | 3 |
-------------------------------------------------------------------------------------------------------------------------------------
|
일반적으로 PK로 INDEX UNIQUE SCAN을 함
memoptimize pool 테이블 데이터 조회 쿼리 실행1
|
1
2
3
4
5
6
7
8
|
SQL>
select /*+ gather_plan_statistics memtest2 */ *
from mem_tbl
where id1 = 123456;
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ------------------------------ -------- -------- --------- ------------- ---------- ---------- ---------- ----- ----
123456 1.2346E+12 FQRTN 20110905 20210414 13-NOV-15 010-4875-4792 3000 7 1234 SQLP A276
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col sql_text for a80
select sql_id, sql_text
from v$sql
where sql_text like '%gather_plan_statistics memtest2%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
b75ux4dhm651s select /*+ gather_plan_statistics memtest2 */ * from mem_tbl where id1 = 123456
9a24cx740fdj9 select sql_id, sql_text from v$sql where sql_text like '%gather_plan_statistics memtest2%'
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('b75ux4dhm651s', NULL, 'ADVANCED ALLSTATS LAST');
Plan hash value: 679280371
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 1 |00:00:00.01 | 4 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| MEM_TBL | 1 | 1 | 109 | 0 (0)| 1 |00:00:00.01 | 4 | 3 |
|* 2 | INDEX UNIQUE SCAN | MEM_TBL_PK | 1 | 1 | | 0 (0)| 1 |00:00:00.01 | 3 | 3 |
----------------------------------------------------------------------------------------------------------------------------------
|
이상하게 memopimize pool을 이용하지 않음
memoptimize pool 테이블 데이터 조회 쿼리 실행2
gather_plan_statistics 힌트 없이 실행
|
1
2
3
4
5
6
7
8
|
SQL>
select *
from mem_tbl a
where id1 = 654321;
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ------------------------------ -------- -------- ------------ ------------- ---------- ---------- ---------- ----- ----
654321 6.5432E+12 1DKST 20150517 20211217 25-NOV-15 010-3127-4965 4000 3 1234 SQLP A942
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
col sql_text for a80
select sql_id, sql_text
from v$sql
where sql_text like '%654321%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
ak2mr9c4jxbfc select sql_id, sql_text from v$sql where sql_text like '%654321%'
47j1q820qvzds select * from mem_tbl a where id1 = 654321
|
xplan display_cursor로 커서에 올라간 플랜 조회(gather_plan_statistics는 안씀)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('47j1q820qvzds', NULL, 'ADVANCED ALLSTATS LAST');
Plan hash value: 679280371
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID READ OPTIM| MEM_TBL | 1 | 80 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN READ OPTIM | MEM_TBL_PK | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
|
gather_plan_statistics 힌트를 사용하지 않으면 예상 실행계획에
TABLE ACCESS BY INDEX ROWID READ OPTIM, INDEX UNIQUE SCAN READ OPTIM 플랜이 나옴
oracle blog에 있는 쿼리로 재시도
테이블생성
|
1
2
3
4
5
6
|
SQL>
drop table FAST_LOOKUP purge;
create table FAST_LOOKUP (
id number(5),
test_col varchar2(15))
segment creation immediate;
|
pk 설정 및 memoptimize for read 설정
|
1
2
3
|
SQL>
alter table FAST_LOOKUP add constraint fast_lookup_pk primary key (id);
alter table FAST_LOOKUP memoptimize for read;
|
gather_plan_statistics 힌트 없이 조회
|
1
|
SQL> select test_col from fast_lookup where id = 10;
|
xplan.display_cursor 조회
|
1
2
3
4
5
6
7
8
9
|
SQL> select * from table(dbms_xplan.display_cursor());
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID READ OPTIM| FAST_LOOKUP | 1 | 22 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN READ OPTIM | FAST_LOOKUP_PK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
|
동일하게 gather_plan_statistics 힌트를 사용하지 않으면 예상 실행계획에
TABLE ACCESS BY INDEX ROWID READ OPTIM, INDEX UNIQUE SCAN READ OPTIM 플랜이 나옴
gather_plan_statistics 힌트 넣고 조회
|
1
|
SQL> select /*+ gather_plan_statistics test1 */ test_col from fast_lookup where id = 10;
|
xplan.display_cursor 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
Plan hash value: 3956077887
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 0 |00:00:00.01 | 1 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| FAST_LOOKUP | 1 | 1 | 22 | 0 (0)| 0 |00:00:00.01 | 1 1 |
|* 2 | INDEX UNIQUE SCAN | FAST_LOOKUP_PK | 1 | 1 | | 0 (0)| 0 |00:00:00.01 | 1 1 |
--------------------------------------------------------------------------------------------------------------------------------------
|
여전히 실제 플랜 확인시 READ OPTIM 실행계획을 볼수 없음
xplan 이 문제인가 싶어 10046 트레이스로 다시 확인해봄
10046 트레이스로 확인
|
1
2
3
4
5
|
SQL>
alter session set tracefile_identifier='10046_20230215_TEST1';
alter session set events '10046 trace name context forever, level 8';
--run query(쿼리 실행 하는 부분 생략)
alter session set events '10046 trace name context off';
|
10046 트레이스 결과
gather_plan_statistics 사용시
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ vi test1.trc
select /*+ gather_plan_statistics memtest19.18_1 */ *
from mem_tbl
where id1 = 123456;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 3 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID MEM_TBL (cr=4 pr=3 pw=0 time=248 us starts=1 cost=3 size=80 card=1)
1 1 1 INDEX UNIQUE SCAN MEM_TBL_PK (cr=3 pr=3 pw=0 time=223 us starts=1 cost=2 size=0 card=1)(object id 153120)
|
gather_plan_statistics 미사용시
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ vi test2.trc
select /*+ memtest19.18_2 */ *
from mem_tbl a
where id1 = 123456;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 3 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID MEM_TBL (cr=4 pr=3 pw=0 time=497 us starts=1 cost=3 size=80 card=1)
1 1 1 INDEX UNIQUE SCAN MEM_TBL_PK (cr=3 pr=3 pw=0 time=448 us starts=1 cost=2 size=0 card=1)(object id 153120)
|
10046에선 두 쿼리 모두 READ OPTIM 실행계획은 나오지 않음
19.12 버전부터 non-exadata에서도 사용가능하다고 oracle blog에 나와있는데
왜 실행계획에 안나오는건지 정확히 알수없음
신규버전이 나오면 다시한번 테스트 해봐야할듯함
dbms_sqltune sql monitor에서 될까 싶어서 테스트해봄
monitor 힌트 테스트(19.15)(19.18에서 안되서 19.15에서 확인)
현재 세션 sid 확인
|
1
2
3
4
5
|
SQL> select sid "this sess sid" from v$mystat where rownum<=1;
this sess sid
-------------
57
|
쿼리 실행
|
1
2
3
4
5
6
7
8
|
SQL> select /*+ monitor memtest19.15_n5 */ *
from mem_tbl
where 1=1
and id1 = 223456;
2 3
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ---------- -------- -------- --------- ------------- ---------- ---------- ---------- ----- ----
223456 2.2346E+11 02RYU 20151120 20210612 19-SEP-18 010-1299-3855 3000 2 1234 SQLP A215
|
확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
SQL>
set long 1000000
select dbms_sqltune.report_sql_monitor(session_id =>57,report_level=>'ALL',type=>'TEXT') from dual;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor memtest19.15_n5 */ * from mem_tbl where id1 = 652143
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (495:22904)
SQL ID : ag9q7pwjx4bxg
SQL Execution ID : 16777216
Execution Started : 02/16/2023 15:50:50
First Refresh Time : 02/16/2023 15:50:50
Last Refresh Time : 02/16/2023 15:50:50
Duration : .000523s
Module/Action : sqlplus@TEST (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@TEST (TNS V1-V3)
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.00 | 0.00 | 0.00 | 1 | 4 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=679280371)
====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | TABLE ACCESS BY INDEX ROWID READ OPTIM | MEM_TBL | 1 | 3 | 1 | +0 | 1 | 1 | | |
| 2 | INDEX UNIQUE SCAN READ OPTIM | MEM_TBL_PK | 1 | 2 | 1 | +0 | 1 | 1 | | |
====================================================================================================================================================
|
여기서도 READ OPTIM 실행계획을 볼수있음
참조용
기존 테이블 memoptize 옵션 설정
|
1
|
SQL> alter table imsi.mem_tbl memoptimize for read;
|
테이블을 메모리로 올림(명령어 수행시 처음으로 해시 인덱스가 자동으로 생성되고 블록이 메모리에 캐시됨)
|
1
|
SQL> execute dbms_memoptimize.populate('IMSI','MEM_TBL');
|
테이블을 메모리에서 제거함
|
1
|
SQL> execute dbms_memoptimize.drop_object('IMSI','MEM_TBL');
|
테이블 memoptimize for read 취소 방법
|
1
|
SQL> alter table sh.products no memoptimize for read;
|
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MEMOPTIMIZE_POOL_SIZE.html#GUID-D33BB2FE-94A7-475F-B8C8-CC9AC61B502F
https://blogs.oracle.com/in-memory/post/memoptimized-rowstore-fast-lookup
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-4434D082-4748-47C3-A410-B7E2B443DD16
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/Chunk520365104.html#GUID-EE5E8238-C900-46DB-A688-6A5D0BDFEA7C
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-CFADC9EA-2E2F-4EBB-BA2C-3663291DCC25
https://asktom.oracle.com/pls/apex/asktom.search%3Ftag%3Dora-01034-oracle-not-available
https://blogs.oracle.com/in-memory/post/memoptimized-rowstore-available-non-exadata
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c ORA-04036 에러를 발생시키는 방법 (0) | 2023.04.30 |
|---|---|
| 오라클 19c 파티션 테이블 멀티 truncate 테스트 (0) | 2023.03.06 |
| 오라클 19c db_files 파라미터 과다 설정시 pga 사용량 비교 (0) | 2023.02.10 |
| 오라클 19c invisible index 테스트 (0) | 2023.02.09 |
| 오라클 11gR2 dbca silent mode 설치시 dbca에 나오는지 확인 (0) | 2023.02.02 |