DBMS_INMEMORY_ADVISOR

When you follow the Oracle in-memory / optimizer team, then you have probably seen this post on Monday: Release of Oracle Database In-Memory Advisor. I missed it, but was luckily pointed to it via my former colleague Andre. As you will read in the post, a new performance tool/advisor has seen the light referred to as “Database In-Memory Advisor” or DBMS_INMEMORY_ADVISOR.

Apparently it can be used from database version 11.2.0.3 and onwards to check what the improvements might be in an In-Memory Column Store environment.

As mentioned by Maria in the post:

Today Oracle released the new Oracle Database In-Memory Advisor (In-Memory Advisor). The goal of this advisor is to identify analytic workloads that will benefit from Oracle Database In-Memory.

The In-Memory Advisor identifies analytic workloads by analyzing Active Session History (ASH) and AWR data. It differentiates analytic processing from other database activity, based upon SQL plan cardinality, use of parallel query, and other statistics.

The DBMS_INMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS package procedure will output it recommendations regarding possible improvements using the In-Memory Column Store functionality in a HTML and DDL SQL script:

• imadvisor_{Your Task Name}.html
• imadvisor_sql_{Your Task Name}.html
• imadvisor_object_{Your Task Name}.html
• imadvisor_{Your Task Name}.sql

You are also able to run the advisory method against AWR data from different database(s), imported in your test environment, to see if / what the impact might be of using the In-Memory Column Store functionality in a 12.1.0.2 or above database environment.

Today I gave it a test run on my Oracle 12.1.0.2 Enterprise Edition database.

Be aware!
Please note the Advisor is licensed as part of the Database Tuning Pack and relies on data coming from the Database Diagnostic Pack.

Installation

The post mentions Oracle MOS 1965343.1 (Oracle Database In-Memory Advisor – Doc ID 1965343.1), where you can download the code. Here you can download the code in a zip file (imadvisor.zip) and, also important, an installation guide (twp_oracle_database_in_memory_advisor.pdf).

Most of it is straightforward and shouldn’t be an issue. That said, I hadn’t checked my tnsalias connect string beforehand so I had some hick-ups in between. Luckily the script can be rerun and saves some of the answers given, so second time around, you will only have to [ENTER] through (most) of the questions.

After successful installation you will end up with a new IMADVISOR user, a whole bunch of (global temporary) heap tables, DBMS_INMEMORY_ADVISORY packages, some views and a database directory where trace/logging files where created and probably the place for the HTML reports.

First steps…

The twp_oracle_database_in_memory_advisor.pdf mentions, after successful installation, an example script, “@imadvisor_analyze_and_report”, to create a first report.

The report never finished, due to mentioning the following:

IM Advisor: Adding Statistics..
DECLARE
*
ERROR at line 1:
ORA-20001: Statistics capture failed: no database table activity was detected
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR", line 2742
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR", line 4401
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR", line 4481
ORA-06512: at line 10

Which is very true on my TEST database: “No database table activity was detected”… There is a small problem now though. Based on the fact that the adding statistics part “failed”, the following, automated / scripted steps, now also failed.

imadvisor_IM_ADVISOR.sql
BEGIN DBMS_INMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS ('IM_ADVISOR', directory_na
me=>NULL); END;

*
ERROR at line 1:
ORA-20001: GENERATE_RECOMMENDATIONS cannot be invoked until both ADD_STATISTICS
and EXECUTE_TASK have been successfully invoked
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR", line 6341
ORA-06512: at "IMADVISOR.DBMS_INMEMORY_ADVISOR", line 6640
ORA-06512: at line 1

'Fetching recommendation files for task IM_ADVISOR'
IM Advisor generated report in imadvisor_IM_ADVISOR.html
IM Advisor genreated DDL script in imadvisor_IM_ADVISOR.sql

So apparently there is a proper check in DBMS_IMMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS that:

1. DBMS_IMMEMORY_ADVISOR.ADD_STATISTICS must have completed “successfully” and
2. DBMS_IMMEMORY_ADVISOR.EXECUTE_TASK must also have completed “successfully”

So “successfully” means in this case that there must be at least 1 object that might be considered for possible optimization. The situation after running the script is now that there is already a task created and a re-run will fail, and signals the following due to the existence of a task with the same name (in my case “IM_ADVISOR”)…

ERROR at line 1:
ORA-20001: DBMS_INMEMORY_ADVISOR task IM_ADVISOR already exists
ORA-06512: at “IMADVISOR.DBMS_INMEMORY_ADVISOR”, line 1574
ORA-06512: at line 9

You can solve this via using DBMS_INMEMORY_ADVISOR.DROP_TASK:

SQL> EXECUTE DBMS_INMEMORY_ADVISOR.DROP_TASK( task_name => 'IM_ADVISOR', force => TRUE); 

…or when all fails…”force delete” the corresponding row in table IMADVISOR_TASKS…(I had to do this once up to now, because the DROP_TASK with force => TRUE, didn’t work)

In the end you will notice that only something will happen, at least that is how it seems to be in my database, if you can get past the following point…that is; if there is any work to be done; that is; if you have “C” values in column STATUS of table IMADVISOR_BASIS.

By the way, the “interpretation” of the “C“/”R“values in column status regarding “Considered” and “Rejected”, is my own. I am guessing it might be a valid assumption regarding their intentional meaning.

SELECT SQL_OBJECT_ID,
  OWNER,
  TABLE_NAME,
  CASE
    WHEN STATUS='C'
    THEN '(C)oncidered for IM Optimization'
    WHEN STATUS='R'
    THEN '(R)ejected for IM Optimization'
    ELSE STATUS
  END AS STATUS,
  RATIONALE
FROM IMADVISOR_BASIS ;

SQL_OBJECT_ID  OWNER TABLE_NAME STATUS                         RATIONALE
-------------- ----- ---------- ------------------------------ ------------------------------------------------------------------------------------
104889	                        (R)ejected for IM Optimization Object is either owned by a system user or its type is not elibable for IM placement

AFAIK the steps needed are (in sequential order), with variable “v_task_name” (VARCHAR2()) value ‘MY_IM_TASK’:

• DBMS_INMEMORY_ADVISOR.DROP_TASK( task_name => v_task_name, force=>TRUE); (if task exists)
• DBMS_INMEMORY_ADVISOR.CREATE_TASK( task_name => v_task_name);
• DBMS_INMEMORY_ADVISOR.ADD_STATISTICS( v_task_name, capture_window_start => SYSTIMESTAMP-3600, capture_window_end => SYSTIMESTAMP);
• DBMS_INMEMORY_ADVISOR.EXECUTE_TASK(v_task_name);
• DBMS_INMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS(task_name => v_task_name, directory_name => v_directory_name, inmemory_size => NULL);

I created a small script that (also) details the steps.

DECLARE
  --
  v_task_name      VARCHAR2(4000) := 'MY_IM_TASK';
  v_directory_name VARCHAR2(4000) := 'IMADVISOR_DIRECTORY';
  return_code      VARCHAR2(4000);
  --
BEGIN
  --
  DBMS_OUTPUT.ENABLE (buffer_size => NULL);
  --
  BEGIN
    -- Remove task if it exists, else do nothing during test
    DBMS_INMEMORY_ADVISOR.DROP_TASK( task_name => 'MY_IM_TASK', force=>TRUE);
    COMMIT;
    -- Alternative forced drop the corresponding row in table IMADVISOR_TASKS
    --
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  DBMS_INMEMORY_ADVISOR.CREATE_TASK( task_name => v_task_name);
  DBMS_OUTPUT.put_line('IMADVISOR: Task '||v_task_name||' created');
  --
  DBMS_INMEMORY_ADVISOR.ADD_STATISTICS( v_task_name, capture_window_start => SYSTIMESTAMP-3600, capture_window_end => SYSTIMESTAMP);
  DBMS_OUTPUT.put_line('IMADVISOR: Finished Adding Statistics for task '||v_task_name);
  --
  DBMS_INMEMORY_ADVISOR.EXECUTE_TASK(v_task_name);
  DBMS_OUTPUT.put_line('IMADVISOR: Finished Executing task '||v_task_name);
  --
  DBMS_INMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS (task_name => v_task_name, directory_name => v_directory_name, inmemory_size => NULL);
  DBMS_OUTPUT.put_line('IMADVISOR: Finished Generating Recommendations for task '||v_task_name);
  --
EXCEPTION
WHEN OTHERS THEN
  return_code := ABS(SQLCODE);
  dbms_output.put_line(SQLERRM);
  dbms_output.put_line(dbms_utility.format_error_backtrace);
  RAISE;
END;
/

Code example script: in-memory_advisor.txt

I will follow up on this post, if I fill in the remaining blanks, like creating an actual HTML report…

Additional info

Hyperlinks:

• Oracle Database In-Memory Advisor (Doc ID 1965343.1)
• Oracle Database In-Memory Advisor (Oracle main page)
• Release of Oracle Database In-Memory Advisor (Oracle In-Memory Blog)
• Oracle Database In-Memory Advisor Best Practices (pdf)
• Oracle Database In-Memory Advisor (pdf)