LiveLabs & Workshops

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

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.

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

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.

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

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.

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.

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

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.