Running Oracle Database In-Memory Advisor Offline

Introduction

Oracle Database In-Memory is one of the simplest and most effective ways to achieve dramatic query performance gain on any Oracle Database. It does not really make a difference if the database with In-Memory enabled is an OLTP or Data Warehouse database. After all, Database In-Memory targets SQL queries with analytical functions.

With Oracle Database 19c onwards, it is possible to assess Database In-Memory without really procuring the option. This is facilitated via the “Base Level Feature”, that allows using Database In-Memory with a maximum of 16 GB allocated for Database In-Memory out of the SGA. Hence, it is now possible to annswer the question: how will my database benefit from Database In-Memory? Since day 1, Database In-Memory had its own Advisor that provides an Database In-Memory assessment for any Oracle Database. The way to run Database In-Memory Advisor on a production database can be found here. It must be noted that the Advisor can generate recommendations for an Oracle Database that is 11.2.0.3 onwards.

However, some people maybe reluctant to run the Advisor on their production databases, knowing that it has its own data dictionary tables and views that must be run via a supplied set of *.sql and *.plb files.
Also – depending on the database – the Advisor may run for a long period of time, consuming some of the database available resources.

In this blog we are going to explore the following:

Exporting DBiM Advisor Required Files From The Database In Question
Importing Onto a Staging Database
Generating the Advisor Recommendations
Dropping Older Data

Exporting DBiM Advisor Required Files From The Database In Question

There are 2 .dmp files that need to be generated

AWR Extract Dump File
In-Memory Advisor Augmented AWR File

AWR Extract Dump File

The AWR extract dump file is generated by running the file @?/rdbms/admin/awrextr.sql agaisnt the database in question. This .sql file is part of any Oracle Database default installation.

Below is a sample output:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 2 12:38:40 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default database id is the local one: '1058798813'.  To use this
database id, press <return> to continue, otherwise enter an alternative.
Enter value for dbid: 1058798813
Using 1058798813 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 14  
<<<Note: the AWR report must be run for an extended period.>>>

Listing the last 14 days of Completed Snapshots
DB Name        Snap Id    Snap Started
------------ --------- ------------------
CDB1              2884 25 Sep 2022 08:00
                  2885 25 Sep 2022 09:00
                  ...
                  3057 02 Oct 2022 13:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2884
Begin Snapshot Id specified: 2884

Enter value for end_snap: 3057
End   Snapshot Id specified: 3057
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -----------------------------------
DATA_PUMP_DIR                  /home/oracle/
...

Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_2884_3057.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: <RET>
Using the dump file prefix: awrdat_2884_3057
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /home/oracle
|   awrdat_2884_3057.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /home/oracle
|   awrdat_2884_3057.log
|
End of AWR Extract
SQL>

Note: always check the expdp log file for any errors.

In-Memory Advisor Augment AWR File

This file exports dictionary data required by the IM Advisor. The file name is imadvisor_awr_augment_export.sql.
It is part of the downloadable In-Memory Advisor distribution. For more information, please check here.

Below is a run of the file:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 2 12:38:40 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> @/home/oracle/advisor/install/imadvisor_awr_augment_export.sql

*********************************************************************
* This script will create for you an Automatic Workload Repository
* augment that will supply additional data required by the Database
* In-Memory Advisor.  Without this data augment, you cannot use 
* an AWR export with the In-Memory Advisor.  (But with it, you can!)
*
* Note: While the AWR on this database holds data imported from other
* databases, only AWR data from this local database (DBID=1058798813)
* can be used with the AWR augment you are about to create.
*
* Please make sure your AWR export is for DBID=1058798813.
*
* Also note: It is best to capture an AWR augment in the same timeframe
* (preferably after) the corresponding AWR export.
***********************************************************************
You may optionally include one the following SQL Tuning Set(s) as part 
of the AWR augment.
Enter the sqlset_owner and sqlset_name in the prompts below to include
a SQL Tuning Set.
Otherwise, press ENTER for both sqlset_owner and sqlset_name if you 
do not wish to include any SQL Sets.
SQLSET_OWNER                                     SQLSET_NAME
------------------------------------------------ ---------------------
SYS                                              SYS_AUTO_STS
Enter value for sqlset_name:
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------------------------------
DATA_PUMP_DIR                  /home/oracle/
...
 
Please enter the Oracle directory for export (default=DATA_PUMP_DIR)?
Using directory DATA_PUMP_DIR...

The default AWR augment dump file name prefix is imadvisor_awr_augment.
Please press <return> to use this name prefix; otherwise, enter 
an alternative name prefix?

Using "imadvisor_awr_augment" as the AWR augment file name prefix...

Setting up the AWR agument staging schema...
No errors.
old 942:dmp_name := NVL('&&dump_file_name_prefix', :dmp_name_default);
new 942:dmp_name := NVL('imadvisor_awr_augment', :dmp_name_default);

PL/SQL procedure successfully completed.

Exporting AWR agument data...
IMADVISOR_AWR_AUGMENT_EXPORT Data Pump status: SUCCESS

PL/SQL procedure successfully completed.

Dropping the AWR agument staging schema...
Directory path for Data Pump dump and log files:
/home/oracle/
Data Pump dump file: imadvisor_awr_augment.dmp
Data Pump log file: imadvisor_awr_augment_export.log

PL/SQL procedure successfully completed.

All done.
SQL>

Importing On a Staging Database

At this stage, there should be 2 dump files generated: AWR snapshot data, and AWR augment data.
The next step is to import these 2 files onto a staging database from which the In-Memory Advisor recommendations report can be generated.

Importing AWR Snapshot Data

Below are the steps:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 04:10:42 2022 
Version 19.16.0.0.0 

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

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

SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ ---------------------------------------
DATA_PUMP_DIR                  /home/oracle/
...

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_8339_8493
<<< Note: Do not enter the file etension >>>

Loading from the file name: awrdat_8339_8493.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /home/oracle/
|   awrdat_8339_8493.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /home/oracle/
|   awrdat_8339_8493.log
|

End of AWR Load

Importing AWR Augment Data

Below is a sample run:

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 04:10:42 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.

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

SQL> @/home/oracle/advisor/install/imadvisor_awr_augment_import.sql

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------
DATA_PUMP_DIR                  /home/oracle/
...

Please enter the Oracle directory object to use for import (default=DATA_PUMP_DIR)?

Using directory DATA_PUMP_DIR...

The default IM Advisor AWR augment dump file name prefix is imadvisor_awr_augment.
Please press <return> to use this name prefix; otherwise, please enter an 
alternative name prefix?

Using "imadvisor_awr_augment" as the AWR augment dump file name prefix...

Gathering information about the AWR agument...
IMADVISOR_AWR_AUGMENT_MASTER Data Pump status: SUCCESS

Setting up the AWR agument schema...

Importing AWR agument data...
IMADVISOR_AWR_AUGMENT_IMPORT Data Pump status: SUCCESS

Granting access on the AWR augment schema to DBMS_INMEMORY_ADVISOR...

All done.

SQL>

Generating the Advisor Recommendations

Finally, this step generates the In-Memory Advisor report:

SQL> @/home/oracle/advisor/install/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
------------------------------ -----------------------------
mmapps1_2                      2022-SEP-25 08:06:22          
<<< For this database a previous run of the Advisor was made. 
    It is possible to re-run using the same task so that 
    the Advisor generates recommendations with different inmemory_size. 
    However, we are going to provide a new task as we are running against 
    a different database. >>>

Default task_name (new task): im_advisor_task_20221003042147
Enter value for task_name: mytask

Advisor task name specified: mytask
New Advisor task will be named: mytask...

By default, the Advisor runs against a live workload on this database.
This database also has imported, augmented AWR workloads.

Press ENTER or respond NO to run against a live workload.
Respond YES to run against an augmented AWR workload.

Enter value for run_against_augmented_awr: y
<<< Note: "y" must be specified to tell the Advisor that a run against 
    an already loaded AWR information is needed.>>>

The Advisor can use the following augmented AWR imports:

Augmented AWR Import DBID
-------------------------
*******288

Enter value for dbid: *******288
<<< Note: The DBID must be entered here. >>>

Analyzing and reporting on an augmented AWR workload with DBID=1733873288...
Enter value for pdb_name: mmapps

<<< Note: this is the PDB of the loaded database. Please check below of 
    how to obtain the PDB name from loaded AWR data. >>>

Choose one of the following instance numbers for this workload:

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

Enter value for instance_number: 
<<< Note: you may choose any instance number displayed above. >>>

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: 16g
<<< Note: here the inmemory_size is specified that the Advisor will 
    do the recommendations for. >>>

The In-Memory Advisor will optimize for this In-Memory size: 16g

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: 09/14/22 23:00:20
Enter value for begin_time: 
<<< Note: enter the begin date and time that you need the Advisor to riun against. 
    Leave the value empty to select all values. >>>

Report begin time specified:

Enter duration in minutes starting from begin time:
(defaults to <latest-snapshot-end-time> - begin_time)

Enter value for duration: 
<<< Note: enter the duration that you need the Advisor to riun against. 
    Leave the value empty to select all values. >>>

Report duration specified:

Using 2022-SEP-14 23:00:20.000000000 as report begin time
Using 2022-SEP-21 09:00:06.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: 
<<< Note: if the requested schema owner is known, please provide it. 
    Otherwise, leave it empty to select all available schemas. >>>

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 16g...
Fetching recommendation files for task: mytask
Placing recommendation files in: the current working directory

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

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

The Advisor generates 2 files:

imadvisor_mytask.html: includes all recommendations and DDL needed to copy the objects into the Columnar Store.
imadvisor_mytask.sql: includes the DDL needed to copy the objects into the Columnar Store alone.

Obtaining the PDB Name

As indicated above, the Advisor asks for the PDB name of the loaded data.
To get the loaded PDB name, please create a file called get_im_pdb.sql with the following contents:

set pages 0 lines 132 head off feed off term off echo off
spool temp.sql
select 'select pdb_name from ' || username ||
'.IMADVISOR_PDBS where pdb_name != '|| '''PDB$SEED'';'
from dba_users
where username like 'C##IMADVISOR_%';
spool off
set term on
@temp.sql

Below is a sample run:

SQL> @get_im_pdb.sql
MMAPPS

Dropping Older AWR Data

Once the In-Memory Advisor ereport is generated, you can delete the 3 sets of data that were uploaded above.
Also, to be able to run the Advisor for newer data, both AWR Data and AWR Augment Data should be dropped.

There are 3 sets of data that need to be cleaned-out:

AWR Data
AWR Augment Data
In-Memory Advisor Tasks (Optional)

Unregistering a Database

To drop the loaded AWR snapshots, we need to unregister the database id.
Create a file called unregister_dbid.sql with the following contents:

set pages 0 lines 132 head off feed on serveroutput on
declare
        dbid number;
begin
        select distinct dbid into dbid
                from WRM$_SNAPSHOT
                        where dbid not in (select dbid from v$database);
        dbms_swrf_internal.unregister_database(dbid);
exception
        When no_data_found then null;
end;
/

Below is a sample run of that file:

SQL> @unregister_dbid 
PL/SQL procedure successfully completed.

Dropping AWR Augment Data

To drop the created AWR Augment data, please create a file called drop_augment_awr.sql with the following contents:

set pages 0 lines 132 head off feed off term off echo off
spool temp.sql
select 'drop user ' || username || ' cascade;'
from   dba_users
where  username like 'C##IMADVISOR_%';
spool off
set term on
@temp.sql

Below is a sample run:

SQL> @drop_augment_awr
drop user C##IMADVISOR_632B16D3_6758CE88 cascade;

1 row selected.

User dropped.

Dropping In-Memory Advisor Tasks (Optional)

An Advisor Task can be re-used to assess the In-Memory workload with a different inmemory_size.
For example, you may want to check how your database would behave with the Columnar Store set to say, 256GB.

Once you’ve exhausted all tests, then you may want to drop that task.
To drop previously created In-Memory Advisor Tasks, please create a file called drop_im_advisor_tasks.sql with the following contents:

set pages 0 lines 132 head off feed off term off echo off
spool temp.sql
select 'exec dbms_inmemory_advisor.drop_task(''' || task_name|| ''');' 
from C##IMADVISOR.WRI$_IMA_TASKS;
spool off
set term on
@temp.sql

Below is a sample run:

SQL> @drop_im_advisor_tasks.sql
exec dbms_inmemory_advisor.drop_task('mmapps1_2');
exec dbms_inmemory_advisor.drop_task('mytask');

2 rows selected.

PL/SQL procedure successfully completed.

Extracting Full SQL Text From Loaded AWR Data

The In-Memory Advisor trucates the SQL statements for queries in question.
Sometimes you may want to view the full SQL text.
To do that, create a file called get_sql_text.sql with the following content:

set pages 0 head off lines 132 long 2000
spool sql.csv
select sql_id, sql_text from WRH$_SQLTEXT
where dbid not in (select dbid from v$database);
spool off

Below is a sample run:

SQL> @get_sql_text
.
a63yan528cvy2 SELECT TEXT FROM SYS.VIEW$ WHERE ROWID = :1
43w0r9122v7jm select max(bytes) from dba_segments
cz8wbmy7k5bxn begin sys.dbms_aq_inv.internal_purge_queue_table(:1, :2, :3, :4, :5, :6, :7, :8
              , FALSE);end;
4y1y43113gv8f delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3

575 rows selected.

References

When and How to use Oracle Database In-Memory Advisor
How to Export and Import the AWR Repository From One Database to Another (Doc ID 785730.1)
How to Remove Workload Repository Information Related to an old DBID From the Automatic Workload Repository (Doc ID 1251795.1)

Running Oracle Database In-Memory Advisor Offline

Introduction

Exporting DBiM Advisor Required Files From The Database In Question

AWR Extract Dump File

In-Memory Advisor Augment AWR File

Importing On a Staging Database

Importing AWR Snapshot Data

Importing AWR Augment Data

Generating the Advisor Recommendations

Obtaining the PDB Name

Dropping Older AWR Data

Unregistering a Database

Dropping AWR Augment Data

Dropping In-Memory Advisor Tasks (Optional)

Extracting Full SQL Text From Loaded AWR Data

References

Alaa Fahmy

Autonomous JSON Black Belt

Innovate Your Business with Data Analytics & Business Intelligence Solution

Wieso entwickeln mit Oracle Database?

Running Oracle Database In-Memory Advisor Offline

Introduction

Exporting DBiM Advisor Required Files From The Database In Question

AWR Extract Dump File

In-Memory Advisor Augment AWR File

Importing On a Staging Database

Importing AWR Snapshot Data

Importing AWR Augment Data

Generating the Advisor Recommendations

Obtaining the PDB Name

Dropping Older AWR Data

Unregistering a Database

Dropping AWR Augment Data

Dropping In-Memory Advisor Tasks (Optional)

Extracting Full SQL Text From Loaded AWR Data

References

Authors

Alaa Fahmy

Autonomous JSON Black Belt

Innovate Your Business with Data Analytics & Business Intelligence Solution

Wieso entwickeln mit Oracle Database?