Oracle Database In-Memory Advisor

Oracle Database In-Memroy Advisor는 Workload Repository (AWR), Active Session History (ASH) 및 기타 메타 데이터를 이용해서 Database In-Memory를 썼을 때 어떤 오브젝트에 적용할 경우 성능 효과가 있을지 미리 추정할 수 있게 해 줍니다. 오라클 데이터베이스 11.2.0.3 부터 사용할 수 있으며, 아래 MOS 문서에서 관련 자료들을 추가로 확인할 수 있습니다.

Oracle Database In-Memory Advisor (Doc ID 1965343.1)

 

In-Memory Advisor 설치

먼저 위 MOS 문서에서 제공되는 In-Memory Advisor 설치파일 (imadvisor.zip)을 데이터베이스 호스트에 업로드하고 설치파일을 oracle 유저 소유로 변경합니다.

Multitenant 환경에서 In-Memory Advisor 설치는 CDB, PDB 레벨에서 모두 가능합니다만, 여기에서는 CDB에서 설치를 했습니다. CDB에 설치를 하는 경우, Root Conatiner에 “C##IMADVISOR” 유저가 만들어지지만, PDB 또는 non-Multitenant 환경인 경우에는 “IMADVISOR” 유저가 생성됩니다.

 

먼저 oracle 유저로 설치파일의 압축을 해제한 다음, sys 유저로 CDB에 접속하여 “instimadv.sql” 스크립트를 실행합니다.

[root@singledb opc]# su - oracle
Last login: Wed Jun 23 04:25:23 UTC 2021
[oracle@singledb ~]$ unzip imadvisor.zip
Archive:  imadvisor.zip
  inflating: instimadv.sql
  inflating: imadvisor_commoncode.sql
inflating: catimadv.sql
…
[oracle@singledb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jun 23 04:42:45 2021
Version 21.1.0.0.0
…
SQL> @instimadv.sql

 

In-Memory Advisor 유저 “C##IMADVISOR”가 사용할 테이블스페이스와 템프 테이블스페이스를 지정하면 설치가 끝납니다.

…
User C##IMADVISOR requires both a permanent and temporary tablespace.
Available tablespaces:

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM (default permanent tablespace)
TEMP (default temporary tablespace)
UNDOTBS1
USERS

Enter value for permanent_tablespace: SYSTEM
Permanent tablespace to be used with C##IMADVISOR: SYSTEM

Enter value for temporary_tablespace: TEMP
Temporary tablespace to be used with C##IMADVISOR: TEMP

No errors.
No errors.
No errors.
…
All done!

DBMS_INMEMORY_ADVISOR installation successful.

Users who will use the DBMS_INMEMORY_ADVISOR package must be granted
the ADVISOR privilege.

DBMS_INMEMORY_ADVISOR installation and setup complete.

To uninstall:

SQL> @catnoimadv.sql

Disconnected from Oracle Database 21c EE Extreme Perf Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

 

In-Memory Advisor 실행

이제 In-Memory Advisor를 실행해서, In-Memory 영역 사이즈에 대한 권고치, 리포트를 만들어 보겠습니다. CDB에 sys 유저로 접속해서 “imadvisor_recommendations.sql” 스크립트를 수행합니다. 스크립트 프롬프트에서 PDB 이름을 입력하게 되어 있으므로 미리 확인한 다음 스크립트를 수행합니다.

[oracle@singledb ~]$ sqlplus "/as sysdba"
…
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DB0621_PDB1                    READ WRITE NO
SQL> @imadvisor_recommendations.sql

This script creates and runs an In-Memory Advisor task that analyzes
your workload to determine an optimal In-Memory configuration.
…

 

Advisor 작업을 수행할 타스크 이름을 입력합니다. 입력하지 않으면 오라클이 생성한 이름이 사용됩니다. 또한 분석하고자하는 PDB 명을 입력합니다.

…
Default task_name (new task): im_advisor_task_20210623045712
Enter value for task_name: my_task1

Advisor task name specified: my_task1

New Advisor task will be named: my_task1...

Analyzing and reporting on a live workload on this database (DBID=1823495188)...
Enter value for pdb_name: DB0621_PDB1

DB0621_PDB1
…

 

분석하고자하는 In-Memory 사이즈를 입력합니다. 별도 입력없이 <ENTER>를 치면 특정 메모리 사이즈가 아닌 Advisor가 분석한 성능 개선 추정치를 보여줍니다.

…
If you already know the specific In-Memory size you wish, please enter
the value now.  Format: nnnnnnn[KB|MB|GB|TB]

Or press <ENTER> to get performance estimates first.
Enter value for inmemory_size: <ENTER>

The In-Memory Advisor will display performance benefit estimates after analysis.
…

 

분석 구간을 설정합니다. 여기서는 시작 시간을 SYSDATE로 부터 240분 전으로 지정했습니다. Duration은 엔터를 쳐서 디폴트(SYSDATE-begin_time)를 적용했습니다.

…
Default begin time: -60
Enter value for begin_time: -240

Report begin time specified: -240

Enter duration in minutes starting from begin time:
(defaults to SYSDATE - begin_time)

Enter value for duration: <ENTER>

Report duration specified:

Using 2021-JUN-23 01:01:26.000000000 as report begin time
Using 2021-JUN-23 05:02:07.000000000 as report end time
…

 

분석 대상 오브젝트를 지정합니다. 여기서는 SH 스키마 전체에 대해 분석을 할 것이므로 “SH.%”로 입력했습니다. 별도 입력없이 <ENTER>를 치면 모든 오브젝트가 분석 대상이 됩니다.

…
You may optionally specify a comma separated list of object owner
and name patterns to be considered for In Memory Placement.
Example:

GEEK_SUMMARY.%,%.GEEK_%
Press ENTER to consider all objects.

Enter value for consider_objects_like: SH.%
Considering only objects matching these patterns for In Memory placement:

SH.%

In-Memory Advisor: Adding statistics...
In-Memory Advisor: Finished adding statistics.
In-Memory Advisor: Analyzing statistics...
In-Memory Advisor: Finished analyzing statistics.
…

 

분석이 완료되면 아래와 같은 각 In-Memory Column Store 사이즈별 성능 기대 효과를 확인할 수 있습니다.

…
________________________________________________________________________
|                                                                      |
|                                    ESTIMATED      ESTIMATED          |
|                                    ANALYTICS      ANALYTICS          |
|                                    PROCESSING    PROCESSING          |
|                    PERCENTAGE         TIME       PERFORMANCE         |
|         IN-MEMORY  OF MAXIMUM      REDUCTION     IMPROVEMENT         |
|           SIZE      SGA SIZE       (SECONDS)*      FACTOR*           |
|         ---------  ----------  ----------------  -----------         |
|           3.159GB       23           5501          3.4X              |
|           3.001GB       22           5494          3.4X              |
|           2.843GB       21           5494          3.4X              |
|           2.686GB       20           5494          3.4X              |
|           2.528GB       19           5494          3.4X              |
|           2.370GB       18           5494          3.4X              |
|           2.212GB       16           2246          1.4X              |
|           2.054GB       15           2246          1.4X              |
|           1.896GB       14           2246          1.4X              |
|           1.738GB       13           2246          1.4X              |
|           1.580GB       12           2246          1.4X              |
|           1.422GB       11           2246          1.4X              |
|           1.264GB       9            2246          1.4X              |
|           1.106GB       8            2246          1.4X              |
|           970.6MB       7            2246          1.4X              |
|           808.8MB       6            2246          1.4X              |
|           647.1MB       5            138           1.0X              |
|           485.3MB       4            138           1.0X              |
|           323.5MB       2            138           1.0X              |
|           161.8MB       1            138           1.0X              |
|                                                                      |
| *Estimates: The In-Memory Advisor's estimates are useful for making  |
|  In-Memory decisions.  But they are not precise.  Due to performance |
|  variations caused by workload diversity, the Advisor's performance  |
|  estimates are conservatively limited to no more than 10.0X          |
|  faster.                                                             |
|                                                                      |
|______________________________________________________________________|
…

 

끝으로 최적화하고자하는 In-Memory 사이즈를 지정할 수 있습니다. 여기서 지정된 값을 기준으로 리포트가 생성됩니다. 여기서는 엔터를 쳐서 디폴트 값을 적용했습니다. 스크립트 실행이 끝나면, 앞서 지정한 타스크명을 포함하는 html 리포트 파일과 DDL SQL문 파일이 만들어져 있습니다.

Choose the In-Memory size you wish for optimization (default=3.159GB): <ENTER>

The Advisor is optimizing for an In-Memory size of 3.159GB...
Fetching recommendation files for task: my_task1
Placing recommendation files in: the current working directory

Fetched file: imadvisor_my_task1.html
Purpose:      recommendation report primary html page

Fetched file: imadvisor_my_task1.sql
Purpose:      recommendation DDL sqlplus script

You can re-run this task with this script and specify a different an In-Memory
size.  Re-running a task to optimize for a different In-Memory size is faster
than creatng and running a new task from scratch.
SQL> !ls imadvisor_my_task1*
imadvisor_my_task1.html  imadvisor_my_task1.sql

 

생성된 리포트는 아래와 같은 형태로 되어 있습니다. 아래는 리포트의 일부 내용들을 발췌한 스크린샷입니다. SQL파일에는 리포트 하단에서 권고한 압축 방식 변경 DDL문을 포함하고 있습니다.

 

끝