Oracle In-Memory Advisor 部署以及使用

1、mos 下载 imadvisor:Oracle Database In-Memory Advisor (Doc ID 1965343.1)

2、解压、安装：

[oracle@js1u-dba-sitdb ~]$ mkdir imadvisor
[oracle@js1u-dba-sitdb ~]$ ls
imadvisor  imadvisor.zip
[oracle@js1u-dba-sitdb ~]$ unzip imadvisor.zip -d imadvisor
Archive:  imadvisor.zip
   inflating: imadvisor/instimadv.sql 
   inflating: imadvisor/imadvisor_commoncode.sql 
   inflating: imadvisor/catimadv.sql 
   inflating: imadvisor/catnoimadv.sql 
   inflating: imadvisor/dbmsimadv.sql 
   inflating: imadvisor/imadvisor_recommendations.sql 
   inflating: imadvisor/imadvisor_analyze_and_report.sql 
   inflating: imadvisor/imadvisor_ash_sql_coverage.sql 
   inflating: imadvisor/imadvisor_awr_augment_export.sql 
   inflating: imadvisor/imadvisor_awr_augment_import.sql 
   inflating: imadvisor/imadvisor_debug_dump.sql 
   inflating: imadvisor/imadvisor_debug_switch.sql 
   inflating: imadvisor/imadvisor_fetch_recommendations.sql 
   inflating: imadvisor/imadvisor_rationale.sql 
   inflating: imadvisor/imadvisor_version.sql 
   inflating: imadvisor/prvtimadvdep.plb 
   inflating: imadvisor/prvtimadvprvt.plb 
   inflating: imadvisor/prvsimadvint.plb 
   inflating: imadvisor/prvtimadvint.plb 
   inflating: imadvisor/prvtimadv_noncdbroot.plb 
   inflating: imadvisor/prvtimadv_cdbroot.plb 
   inflating: imadvisor/prvsimadvprvt11.plb 
   inflating: imadvisor/prvsimadvprvt12.plb 
[oracle@js1u-dba-sitdb ~]$ ls
imadvisor  imadvisor.zip
[oracle@js1u-dba-sitdb ~]$ cd imadvisor/
[oracle@js1u-dba-sitdb imadvisor]$ ls
catimadv.sql                      imadvisor_debug_dump.sql             prvsimadvprvt11.plb
catnoimadv.sql                    imadvisor_debug_switch.sql           prvsimadvprvt12.plb
dbmsimadv.sql                     imadvisor_fetch_recommendations.sql  prvtimadv_cdbroot.plb
imadvisor_analyze_and_report.sql  imadvisor_rationale.sql              prvtimadvdep.plb
imadvisor_ash_sql_coverage.sql    imadvisor_recommendations.sql        prvtimadvint.plb
imadvisor_awr_augment_export.sql  imadvisor_version.sql                prvtimadv_noncdbroot.plb
imadvisor_awr_augment_import.sql  instimadv.sql                        prvtimadvprvt.plb
imadvisor_commoncode.sql          prvsimadvint.plb
[oracle@js1u-dba-sitdb imadvisor]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 1 10:22:47 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> @instimadv.sql
Welcome to the Oracle Database In-Memory Advisor (DBMS_INMEMORY_ADVISOR)
installation.

DBMS_INMEMORY_ADVISOR uses Active Session History (ASH), Automatic Workload
Repository (AWR) and optionally SQL Tuning Sets (STS) to determine which
tables, partitions and subpartitions to place In Memory for optimized analytics
processing performance.  DBMS_INMEMORY_ADVISOR produces a recommendation report
and a SQLPlus script to implement its recommendations.

DBMS_INMEMORY_ADVISOR users require the ADVISOR privilege.

 

This installation script will create user IMADVISOR and add object
definitions to the schema.  This user is created using the IDENTIFIED BY
password method with a random-generated password.  If you prefer to use either
the IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY method, abort this installation
by pressing ^C.  Then create user IMADVISOR using your preferred
method.  Add no objects or grants to the IMADVISOR schema.  Then run
this installation script again.

User 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: SYSAUX

Permanent tablespace to be used with IMADVISOR: SYSAUX

Enter value for temporary_tablespace: TEMP

Temporary tablespace to be used with IMADVISOR: TEMP

No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.

No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
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 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

3、使用：

以拥有advisor 系统权限的账号登陆，这里直接用sys 登陆，执行imadvisor_recommendations.sql 脚本：

指定任务名称，也可以用默认名称，如果是容器数据库且PDB 没有开启awr快照功能，AWR 存储选择CDB，反之可以选择PDB,这里使用默认值存储在CDB中：

指定数据库inmemory区域的内存大小:

指定分析报告的开始时间，默认一小时之前，这里指定8小时之前:

指定分析的持续时间，这里指定480分钟,8小时:

可以指定分析哪个schema下面的对象，如schema.%,不指定分析所有对象:

分析完成后，产生html 报告、建议的调整sql脚本:

查看分析报告，根据报告的内容，决定哪些table、partition 加载至inmemory 内存区域对查询性能改善最大: