Skip to Main Content

LiveLabs & Workshops

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Jasmin FluriMar 8 2022

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?

This post has been answered by Andy Rivenes-Oracle on Mar 11 2022
Jump to Answer

Comments

Joerg.Sobottka

Hi Jasmin,
Database In-Memory Guide (0 Bytes)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.
Keine Fehler.
Keine Fehler.
Keine Fehler.
Keine Fehler.
Keine Fehler.
Keine Fehler.
Keine Fehler.
Keine Fehler.
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

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
Answer

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.

Marked as Answer by Jasmin Fluri · Mar 25 2022
raphi

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

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

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

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


Andy Rivenes-Oracle

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

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
.....
user997432212

Hello

I have the exact same problem on 19.21 DB. I have opened the SR 3-37967061281 but unfortunately they can not provide me the fixe.

1 - 11

Post Details

Added on Mar 8 2022
11 comments
554 views