Im-memory开启

操作步骤：

1. 对RAC节点1RRD1安装In-memory
2. 在第一个节点，分别设置1G，5G推荐内存，时长分别为300，7200分钟，查看生产的推荐文件。
3. 使用脚本awrddrpt.sql对比设置in_memory前后系统各项指标对比情况。
4. 运行负载sql进行对比。
5. 选取时间2020.8.31 和2020.9.1 时间段 9：00-10：00的对比。

 

   

SQL> @imadvisor_recommendations


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

This script then generates an HTML recommendation report file in the
current working  directory: imadvisor_<task_name>.html

This script also generates a sqlplus DDL script to implement the
recommendations: imadvisor_<task_name>.sql

NOTE: You may specify one of your existing tasks if you wish to optimize for a
different In-Memory size.

Using an existing, executed task is faster than a new task since a new task
requires statistics gathering and analysis.

But if you wish to analyze a different workload or use a different statistics
capture window or add a SQLSET, you must specify a new task.

The following is a list of your existing tasks:


TASK_NAME                      DATE_CREATED
------------------------------ -----------------------------
im_advisor_task_20200831142346 2020-AUG-31 14:24:08
5GIM_advisor20200831           2020-AUG-31 14:36:14



Default task_name (new task): im_advisor_task_20200831144254
Enter value for task_name: 5G_7200_20200831

Advisor task name specified: 5G_7200_20200831

New Advisor task will be named: 5G_7200_20200831...


Analyzing and reporting on a live workload on this database (DBID=2007374147)...





Choose one of the following instance numbers for this workload:



INSTANCE_NUMBER DEFAULT_INSTANCE
--------------- ----------------
              1        *
              2
              3


Enter value for instance_number: 1

The Advisor will analyze the workload from instance number 1.




The In-Memory Advisor optimizes the In-Memory configuration for a specific
In-Memory size that you choose.

After analysis, the In-Memory Advisor can provide you a list of performance
benefit estimates for a range of In-Memory sizes.  You may then choose the
In-Memory size for which you wish to optimize.

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: 5G

The In-Memory Advisor will optimize for this In-Memory size: 5G


Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Default begin time: -60
Enter value for begin_time: -7200

Report begin time specified: -7200


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

Enter value for duration: 7200

Report duration specified:   7200

Using 2020-AUG-26 14:43:36.000000000 as report begin time
Using 2020-AUG-31 14:43:36.000000000 as report end time


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.



Considering all objects for In Memory placement.

In-Memory Advisor: Adding statistics...

In-Memory Advisor: Finished adding statistics.

In-Memory Advisor: Analyzing statistics...

In-Memory Advisor: Finished analyzing statistics.

The Advisor is optimizing for an In-Memory size of 5G...
Fetching recommendation files for task: 5G_7200_20200831
Placing recommendation files in: the current working directory

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

Fetched file: imadvisor_5G_7200_20200831.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.

多个节点advisor 任务共享，因此不必多个节点分别运行advisor脚本

SQL> @imadvisor_recommendations


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

This script then generates an HTML recommendation report file in the
current working  directory: imadvisor_<task_name>.html

This script also generates a sqlplus DDL script to implement the
recommendations: imadvisor_<task_name>.sql

NOTE: You may specify one of your existing tasks if you wish to optimize for a
different In-Memory size.

Using an existing, executed task is faster than a new task since a new task
requires statistics gathering and analysis.

But if you wish to analyze a different workload or use a different statistics
capture window or add a SQLSET, you must specify a new task.

The following is a list of your existing tasks:


TASK_NAME                      DATE_CREATED
------------------------------ -----------------------------
im_advisor_task_20200831142346 2020-AUG-31 14:24:08
5GIM_advisor20200831           2020-AUG-31 14:36:14
5G_7200_20200831               2020-AUG-31 14:43:40



Default task_name (new task): im_advisor_task_20200831151127
Enter value for task_name: ^C

3、分析并设定In-memory的开启相关参数

alter system set parallel_degree_policy=auto scope=spfile;

根据推荐报告，将PRD库的In-memory设置为1G

SQL> ALTER SYSTEM SET INMEMORY_SIZE=1G SCOPE=SPFILE;

SQL> ALTER SYSTEM SET INMEMORY_SIZE=1G SCOPE=SPFILE;

System altered.


alter system set parallel_degree_policy=auto scope=spfile;

重启数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                  7654304 bytes
Variable Size            1.3690E+10 bytes
Database Buffers         7.0867E+10 bytes
Redo Buffers              260780032 bytes
In-Memory Area           1073741824 bytes
Database mounted.
Database opened.
SQL> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     4
inmemory_query                       string      ENABLE
inmemory_size                        big integer 1G
inmemory_trickle_repopulate_servers_ integer     1
percent
memory_max_target                    big integer 0
memory_target                        big integer 0
optimizer_inmemory_aware             boolean     TRUE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_memory_address                integer     0

根据advisor设置对象

重启各实例查看生效结果

运行sql负载，运行23分23秒

SELECT *
        FROM apps.CUX_AR_TRX_CASH_V T
       order by t.k_number, t.seq;

再次运行in_memory advisor,并执行推荐sql,再次运行负载sql,耗时16分49秒，说明in-memory起到了相当作用。