Forum Stats

  • 3,836,963 Users
  • 2,262,207 Discussions
  • 7,900,157 Comments

Discussions

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

Jasmin Fluri
Jasmin Fluri Member Posts: 3 Blue Ribbon

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?

Best Answer

Answers

  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 598 Bronze Trophy
    edited Mar 8, 2022 3:54PM

    Hi Jasmin,

    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
    Andy Rivenes-Oracle Member Posts: 7 Employee

    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
    Andy Rivenes-Oracle Member Posts: 7 Employee
    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.

    Joerg.Sobottka
  • raphi
    raphi Member Posts: 26 Bronze Badge

    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
    Andy Rivenes-Oracle Member Posts: 7 Employee

    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
    raphi Member Posts: 26 Bronze Badge
    edited Mar 25, 2022 12:46PM

    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
    Andy Rivenes-Oracle Member Posts: 7 Employee

    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 Member Posts: 7 Employee

    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
    raphi Member Posts: 26 Bronze Badge

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