Oracle Database In-Memory Advisor
11666 ワード
概要
本文はこの白書の読書ノートです.
Database In-Memory以下DBIMと略す.DBIMアドバイザ以下は推奨器と略す.
DBIMは12.1である.0.2でパブリッシュされた、つまり12 cがパブリッシュされたばかりのときにはこの特性はありません.DBIMのリリースの少なくとも一部はSAP HANA向けです.推奨の役割:DBIMが分析に利益をもたらすかどうかを評価 OLT P負荷に対してインデックスを削除できるかどうか 評価に必要なメモリ量 オブジェクト構成DBMとして生成されたスクリプト インストール
SYSユーザーを使用します.推奨器はCDBまたはPDBにインストールできます.この例では後者です.一時ディレクトリを新規作成し、解凍します.
インストールを開始するには数分かかります.唯一入力する必要があるのはシステム表領域SYSTEMと一時表領域TEMPです.
うんてん
推奨器はAWRなどのデータに基づいているので、実行前にテスト負荷を1つ先に走ることができます.
自動的に生成されるSQLスクリプトは次のとおりです.
別の実行:
SQL推奨事項:
リファレンスOracle Database In-Memory Advisor(文書ID 19653433.1) https://www.oracle.com/technetwork/database/manageability/inmemory-advisor-2412222.html http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html
本文はこの白書の読書ノートです.
Database In-Memory以下DBIMと略す.DBIMアドバイザ以下は推奨器と略す.
DBIMは12.1である.0.2でパブリッシュされた、つまり12 cがパブリッシュされたばかりのときにはこの特性はありません.DBIMのリリースの少なくとも一部はSAP HANA向けです.推奨の役割:
SYSユーザーを使用します.推奨器はCDBまたはPDBにインストールできます.この例では後者です.一時ディレクトリを新規作成し、解凍します.
unzip imadvisor.zip
インストールを開始するには数分かかります.唯一入力する必要があるのはシステム表領域SYSTEMと一時表領域TEMPです.
$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 24 11:26:34 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @instimadv
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 C##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 C##IMADVISOR using your preferred
method. Add no objects or grants to the C##IMADVISOR schema. Then run
this installation script again.
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.
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 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
うんてん
推奨器はAWRなどのデータに基づいているので、実行前にテスト負荷を1つ先に走ることができます.
$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 24 11:31:23 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
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_.html
This script also generates a sqlplus DDL script to implement the
recommendations: imadvisor_.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_20190824113137 2019-AUG-24 11:32:42
im_advisor_task_20190824130450 2019-AUG-24 13:06:18
im_advisor_task_20190824131244 2019-AUG-24 13:13:03
Default task_name (new task): im_advisor_task_20190824131613
Enter value for task_name:
Advisor task name specified: im_advisor_task_20190824131613 (default)
New Advisor task will be named: im_advisor_task_20190824131613...
Analyzing and reporting on a live workload on this database (DBID=2785980613)...
Enter value for pdb_name: orclpdb1
orclpdb1
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 to get performance estimates first.
Enter value for inmemory_size:
The In-Memory Advisor will display performance benefit estimates after analysis.
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:
Report begin time specified:
Enter duration in minutes starting from begin time:
(defaults to SYSDATE - begin_time)
Enter value for duration:
Report duration specified:
Using 2019-AUG-24 12:16:22.000000000 as report begin time
Using 2019-AUG-24 13:16:24.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.
Enter value for consider_objects_like: D18.%
Considering only objects matching these patterns for In Memory placement:
D18.%
In-Memory Advisor: Adding statistics...
In-Memory Advisor: Finished adding statistics.
In-Memory Advisor: Analyzing statistics...
In-Memory Advisor: Finished analyzing statistics.
The Advisor estimates the following performance benefits:
________________________________________________________________________
| |
| ESTIMATED ESTIMATED |
| ANALYTICS ANALYTICS |
| PROCESSING PROCESSING |
| PERCENTAGE TIME PERFORMANCE |
| IN-MEMORY OF MAXIMUM REDUCTION IMPROVEMENT |
| SIZE SGA SIZE (SECONDS)* FACTOR* |
| --------- ---------- ---------------- ----------- |
| 99.97MB 31 189 5.6X |
| 94.98MB 29 |
| 89.98MB 28 |
| 84.98MB 26 |
| 79.98MB 25 |
| 74.98MB 23 |
| 69.98MB 22 |
| 64.98MB 20 |
| 59.98MB 19 |
| 54.99MB 17 |
| 49.99MB 15 |
| 44.99MB 14 |
| 39.99MB 12 |
| 34.99MB 11 |
| 29.99MB 9 |
| 24.99MB 8 |
| 19.99MB 6 |
| 15.00MB 5 |
| 9.997MB 3 |
| 4.999MB 2 |
| |
| *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. |
| |
|______________________________________________________________________|
Choose the In-Memory size you wish for optimization (default=99.97MB):
The Advisor is optimizing for an In-Memory size of 99.97MB...
Fetching recommendation files for task: im_advisor_task_20190824131613
Placing recommendation files in: the current working directory
Fetched file: imadvisor_im_advisor_task_20190824131613.html
Purpose: recommendation report primary html page
Fetched file: imadvisor_im_advisor_task_20190824131613.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スクリプトは次のとおりです.
[oracle@oracle-12201-vagrant ~]$ cat imadvisor_im_advisor_task_20190824131613.sql
Rem Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
ALTER TABLE "D18"."TAB01" INMEMORY NO MEMCOMPRESS;
別の実行:
Choose the In-Memory size you wish for optimization (default=23.11MB):
200MB
The Advisor is optimizing for an In-Memory size of 200MB...
Fetching recommendation files for task: im_advisor_task_20190825110517
Placing recommendation files in: the current working directory
Fetched file: imadvisor_im_advisor_task_20190825110517.html
Purpose: recommendation report primary html page
Fetched file: imadvisor_im_advisor_task_20190825110517.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推奨事項:
Rem Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
ALTER TABLE "D18"." " INMEMORY MEMCOMPRESS FOR QUERY LOW;
リファレンス