Is the Oracle Database In-Memory Advisor 12c also available for 19c?

Jasmin Fluri · Mar 8, 2022 12:47PM

The MOS Note (Document 1965343.1 (oracle.com)) says there is an Oracle In-Memory Advisor available for Oracle Database 11.2.0.3 and above. It doesn't say up to which version it is compatible or available.

I tried to install the Oracle In-Memory Advisor (Document 1965343.1 (oracle.com)) on an Oracle Database 19c (19.14) according to the documentation attached in the MOS notes and got the following error (no matter if I installed it in the CDB or the PDB and no matter which tablespace I used):

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_IMADVISOR_AWR_AUGMENTTABS' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at line 3

From this I assume it's not compatible with 19c.

Is there an Oracle Database In-Memory Advisor available for 19c?

Or is there another possibility to install the 12c In-Memory Advisor on 19c?

Andy Rivenes-Oracle · Mar 11, 2022 4:55PM

Hi Jasmin,

I have spoken with the Developers and this is a bug. They have a workaround but we cannot send you the code fix directly. If you open an SR and send me the SR number I can fast track you the workaround fix.

Joerg.Sobottka · Mar 8, 2022 3:50PM

Hi Jasmin,

https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/intro-to-in-memory-column-store.html#GUID-8BAFE308-549E-4D81-AF93-5B82C6DBC9E4

states your support note.

I have installed it now on 19.11 on MS Windows - this is the output of the installation:

Verbunden mit:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

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

Geben Sie einen Wert f³r permanent_tablespace ein: SYSAUX

Permanent tablespace to be used with C##IMADVISOR: SYSAUX

Geben Sie einen Wert f³r temporary_tablespace ein: TEMP

Temporary tablespace to be used with C##IMADVISOR: TEMP

Keine Fehler.

Verbindung zu Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0 beendet

----------------------------------------------

This is fine.

If I want to let it run, I do get an error that SYS.DBMS_INMEMORY_ADVISOR Package BODY is missing (and it is really not in DBA_OBJECTS)...

For me it sounds like the package is corrupt OR an essential part in the document is missing...

Andy Rivenes-Oracle · Mar 9, 2022 5:28PM

Hi Jasmin,

I was able to install the In-Memory Advisor in a 19.14 database but it is not a multitenant database. I will have to find a multitenant database and try to see if I can replicate your error, but the In-Memory Advisor is supported on 19c databases as well as 21c.

Andy Rivenes-Oracle · Mar 11, 2022 4:55PM

Hi Jasmin,

I have spoken with the Developers and this is a bug. They have a workaround but we cannot send you the code fix directly. If you open an SR and send me the SR number I can fast track you the workaround fix.

raphi · Mar 22, 2022 1:27PM

Hello,

I am working with Jasmin in the same team and after receiving the fix in the SR I could finally install the advisor in 10 different 19.14c PDBs. I could also generate a report on 9 of those DBs, unfortunately on one DB I am getting the following error, no matter what time window I chose (24h, 12h, 4h):

@/imadvisor_recommendations
Enter value for awr_store: PDB
Report duration specified:
Using 2022-MAR-22 10:17:28.000000000 as report begin time
Using 2022-MAR-22 14:17:29.000000000 as report end time
Considering all objects for In Memory placement.

In-Memory Advisor: Adding statistics...
DECLARE
*
ERROR at line 1:
ORA-20001: Internal error:
ORA-01426: numeric overflow
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6214
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6214
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6870
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6947
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 2868
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT", line 6960
ORA-06512: at "SYS.DBMS_INMEMORY_ADVISOR", line 46
ORA-06512: at line 11

Any idea what might be causing this or how to debug the error?

Thanks & kind regards,

raphael

Andy Rivenes-Oracle · Mar 22, 2022 3:22PM

Hi Raphi,

From your example above I would say that a 1 second duration is not going to work:

Enter value for begin_time: 03/21/22 15:31:10

Report begin time specified: 03/21/22 15:31:10

Enter duration in minutes starting from begin time:

(defaults to SYSDATE - begin_time)

Enter value for duration: 0:01

Report duration specified: 0:01

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 25

In my case the report still ran, but was not useful. You say that you used longer intervals, did you check that there were AWR snapshots covering the periods you were analyzing?

If you still get that message then you should open an SR so that we can capture your information and identify what the error is.

raphi · Mar 25, 2022 12:45PM

Hi Andy,

not sure I understand you, in my example I took a snapshot of 4 hours (10:17:28 until 14:17:29), not 1 second, and we have AWR snapshots back until August 2018, taken in 10 mins intervals:

select min(begin_interval_time) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
14-AUG-18 10.00.56.427 AM

But we only had ASH data for about 17h according to oldest_sample_time in v$ash_info, which is why I tried with lower intervals.

However, I now tried it with 1 hour and this worked. So with new found courage I tried a 6 hour interval and this worked as well. I don't know what is different today compared to 3 days ago, it's the same DB and same time of day.

I don't think an SR will be able to find this out, if at all then maybe a developer can judge from the line numbers in the error output what went wrong on my first tries. But having a 6 hour report should be enough for now.

General question: does the imadvisor use ASH at all or is it solely relying on AWR snapshots?

Kind regards,

raphael

Andy Rivenes-Oracle · Mar 25, 2022 3:58PM

Hi Raphael,

I was just going by what you had posted:

Report duration specified:
Using 2022-MAR-22 10:17:28.000000000 as report begin time
Using 2022-MAR-22 14:17:29.000000000 as report end time

Enter value for duration: 0:01

Report duration specified: 0:01

In any event, I'm glad you were able to get it to work. It does use ASH data to augment the SQL information.

Andy Rivenes-Oracle · Mar 25, 2022 3:59PM

Andy Rivenes-Oracle · Mar 25, 2022 4:03PM

Whoops, that's weird, it does show a 4 hour period, I'm not sure why I misread that. Is it possible that you didn't have snapshots for that time period? I apologize for not seeing that correctly. That error seems to occur when the Advisor cannot find a proper time range. Again, it seems like you are past that so sorry for not reading that correctly the first time.

raphi · Mar 25, 2022 4:17PM

Okay thanks for clarifying. We are not missing any snapshots and we had 10 min snapshots when I tested it on Tuesday (see below).

Not sure why it couldn't find a correct timerange, the only explaination might be that I first tried a 24 hour interval when we only had 17 hours of ASH data, it's when the error first occured. I then executed the script again (with a new task) with a lower interval and the error persisted. Was something maybe stored internally which prevented future exections on the same day from succeeding?

What's strange though and contradicts that theory, I uninstalled the advisor between runs and tried from the CDB, I got the same error on that particular date with a 4h interval, hmm.

select begin_interval_time from dba_hist_snapshot where BEGIN_INTERVAL_TIME < sysdate-3 order by 1;
22-MAR-22 10.00.47.074 AM
22-MAR-22 10.00.47.075 AM
22-MAR-22 10.10.53.090 AM
22-MAR-22 10.10.53.090 AM
22-MAR-22 10.20.59.231 AM
22-MAR-22 10.20.59.231 AM
22-MAR-22 10.30.04.831 AM
22-MAR-22 10.30.04.832 AM
22-MAR-22 10.40.10.850 AM
22-MAR-22 10.40.10.851 AM
22-MAR-22 10.50.16.800 AM
22-MAR-22 10.50.16.800 AM
22-MAR-22 11.00.22.632 AM
22-MAR-22 11.00.22.632 AM
22-MAR-22 11.10.28.832 AM
22-MAR-22 11.10.28.832 AM
22-MAR-22 11.20.34.653 AM
22-MAR-22 11.20.34.654 AM
22-MAR-22 11.30.40.864 AM
22-MAR-22 11.30.40.864 AM
22-MAR-22 11.40.47.387 AM
22-MAR-22 11.40.47.387 AM
22-MAR-22 11.50.53.089 AM
22-MAR-22 11.50.53.089 AM
22-MAR-22 12.00.59.495 PM
22-MAR-22 12.00.59.495 PM
22-MAR-22 12.10.04.896 PM
22-MAR-22 12.10.04.896 PM
22-MAR-22 12.20.11.105 PM
22-MAR-22 12.20.11.105 PM
22-MAR-22 12.30.17.314 PM
22-MAR-22 12.30.17.315 PM
22-MAR-22 12.40.23.597 PM
22-MAR-22 12.40.23.598 PM
22-MAR-22 12.50.29.611 PM
22-MAR-22 12.50.29.611 PM
22-MAR-22 01.00.35.889 PM
22-MAR-22 01.00.35.889 PM
22-MAR-22 01.10.42.076 PM
22-MAR-22 01.10.42.077 PM
22-MAR-22 01.20.48.543 PM
22-MAR-22 01.20.48.543 PM
22-MAR-22 01.30.54.621 PM
22-MAR-22 01.30.54.621 PM
22-MAR-22 01.41.00.513 PM
22-MAR-22 01.41.00.513 PM
22-MAR-22 01.50.05.729 PM
22-MAR-22 01.50.05.729 PM
22-MAR-22 02.00.11.808 PM
22-MAR-22 02.00.11.808 PM
22-MAR-22 02.10.18.401 PM
22-MAR-22 02.10.18.401 PM
22-MAR-22 02.20.24.482 PM
22-MAR-22 02.20.24.483 PM
22-MAR-22 02.30.30.821 PM
22-MAR-22 02.30.30.821 PM
22-MAR-22 02.40.37.217 PM
22-MAR-22 02.40.37.218 PM
.....

Forum Stats

Quick Links

Categories

Is the Oracle Database In-Memory Advisor 12c also available for 19c?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Is the Oracle Database In-Memory Advisor 12c also available for 19c?

Best Answer

Answers