Exadata Simulation Workflow hands-on

Preamble

I thought I would never ever work on this subject but we are currently studying the replacement of one of our on premise database server by an Exadata machine. This database server is running the application supporting one of our biggest manufacturing site. This database server is also running an OLAP database of those figures for reporting purposes.

And this site is planned to double its size in few years (if everything goes as expected) so our study on how to cope with this increased activity from IT perspective…

While working with Oracle on Exadata my teammates had few question on the capability of Exadata to handle efficiently the ad-hoc queries of our OLAP system. Oracle mentioned the Exadata Simulation Workflow that is in fact an option of SQL Performance Analyzer (SPA) that I have tested a while back.

It’s a pity that this Exadata Simulation Workflow is part of Real Application Testing (RAT) paid option and that Oracle does not make it free. At the end we are checking that we would benefit from Exadata hardware before buying isn’t it ?

What’s funny is that Oracle has mentioned this Exadata Simulation Workflow tool along with Documentum queries that can drastically be improved by Exadata. And we have a 19c database running Documentum internally so a good candidate for my testing !

Our Documentum shared database server is running Red Hat Enterprise Linux Server release 7.3 (Maipo) with Oracle Database 19c Enterprise Edition Release 19.15. The server has 16 cores and 256GB of memory.

Exadata Simulation Workflow execution

The first step for STA is to create a SQL Tuning Set (STS). I’d like to have the first ten SELECT SQL that are doing the most physical I/O per execution of my Documentum schema (the criteria on execution is to avoid division by 0). I have generated this query from the V$SQL view:

SELECT a.*,a.disk_reads/a.executions
FROM v$sql a
WHERE a.parsing_schema_name='SDMDBA'
AND UPPER(a.sql_text) LIKE 'SELECT%'
AND a.executions!=0
ORDER BY a.disk_reads/a.executions DESC
FETCH FIRST 10 ROWS only;

Since my last testing in 11gR2 I noticed Oracle made available a new package called DBMS_SQLSET (but apparently you have exact equivalent in DBMS_SQLTUNE, it looks anyway clearer to use it for STS):

-- create the tuning set
SQL> EXEC DBMS_SQLSET.CREATE_SQLSET(sqlset_name => 'sts01', description => 'Exadata Simulation Workflow STS');
 
PL/SQL PROCEDURE successfully completed.
 
-- populate the tuning set from the shared SQL area
DECLARE
  cursor01 DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  OPEN cursor01 FOR SELECT VALUE(p)
  FROM TABLE(
    DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filter => 'parsing_schema_name=''SDMDBA'' and upper(sql_text) like ''SELECT%'' and executions!=0',
    result_limit => 10, ranking_measure1 => 'disk_reads/executions')
  ) p;
  DBMS_SQLSET.LOAD_SQLSET(sqlset_name => 'sts01', populate_cursor => cursor01);
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 200
SQL> col description FOR a35
SQL> col owner FOR a10
SQL> col name FOR a10
SQL> SELECT * FROM dba_sqlset;
 
        ID   CON_DBID NAME       OWNER      DESCRIPTION                         CREATED   LAST_MODI STATEMENT_COUNT
---------- ---------- ---------- ---------- ----------------------------------- --------- --------- ---------------
         2 3457912370 sts01      YJAQUIER   Exadata Simulation Workflow STS     07-SEP-22 07-SEP-22              10

Create the task with:

DECLARE
  sts_task VARCHAR2(64);
BEGIN
  sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'sts01', task_name => 'Task01', description => 'Task Exadata Simulation Workflow');
END;
/

The previous command will probably end-up with below error:

ERROR at line 1:
ORA-00438: Real Application Testing Option not installed
ORA-06512: at "SYS.PRVT_SMGUTIL", line 84
ORA-06512: at "SYS.DBMS_SQLPA", line 290
ORA-06512: at line 4

This is simply because the Real Application Testing (RAT) option is not activated on your database:

SQL> SET lines 200
SQL> col VALUE FOR a20
SQL> col PARAMETER FOR a25
SQL> SELECT parameter,VALUE FROM v$option WHERE parameter LIKE 'Real Application Testing';
 
PARAMETER                 VALUE
------------------------- --------------------
REAL Application Testing  FALSE

From the Oracle libraries you can use to see if RAT is activated or not:

[orasdm@server01 ~]$ ar -tv $ORACLE_HOME/rdbms/lib/libknlopt.a | grep kec
rw-r--r-- 59848/500   5232 Apr 17 07:23 2019 kecnr.o
[orasdm@server01 ~]$ ar -tv $ORACLE_HOME/rdbms/lib/libknlopt.a | grep -c kecwr.o
0

To activate it shutdown the database and use chopt:

[orasdm@server01 ~]$ chopt enable rat
 
Writing to /ora_sdm/software/install/enable_rat_2022-09-14_17-34-40PM.log...
/usr/bin/make -f /ora_sdm/software/rdbms/lib/ins_rdbms.mk rat_on ORACLE_HOME=/ora_sdm/software
/usr/bin/make -f /ora_sdm/software/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/ora_sdm/software

Once you restart the database the RAT option will be activated. Again please note that this RAT option is not free. The create analysis task should now work properly…

To overcome this problem and not activate the RAT option on our production database we have created a complete copy and we have respected the one month trial period. You can export/import your STS on the newly created database using procedures of DBMS_SQLSET like DBMS_SQLSET.CREATE_STGTAB, DBMS_SQLSET.PACK_STGTAB, Export/import Data Pump and DBMS_SQLSET.UNPACK_STGTAB. In my case we restored the backup after I had created the STS…

Then after, taking idea from $ORACLE_HOME/rdbms/admin/tcellsim.sql script, you have two options to set your task as an Oracle Exadata simulation workflow task:

dbms_sqlpa.set_analysis_task_parameter(… parameter => ‘CELL_SIMULATION_ENABLED’…)
dbms_sqlpa.execute_analysis_task(… execution_params => dbms_advisor.arglist(‘cell_simulation_enabled’,’TRUE’) …)

Execute the task with Exadata not simulated (cell simulation deactivated):

EXEC dbms_sqlpa.execute_analysis_task(task_name => 'Task01', execution_type => 'execute', execution_name => 'cell_simulation_disabled', execution_params => dbms_advisor.arglist('cell_simulation_enabled','FALSE'));

Execute the task with Exadata simulated (cell simulation activated):

EXEC dbms_sqlpa.execute_analysis_task(task_name => 'Task01', execution_type => 'execute', execution_name => 'cell_simulation_enabled', execution_params => dbms_advisor.arglist('cell_simulation_enabled','TRUE'));

Execute a comparison task:

EXEC dbms_sqlpa.execute_analysis_task(task_name => 'Task01', execution_type => 'compare', execution_name => 'cell_simulation_compare', execution_params => dbms_advisor.arglist('comparison_metric','io_interconnect_bytes'));

Remark:
Even if you can technically compare on various metrics like elapsed_time, buffer_gets or many others (see in official documentation) in this particular Exadata Simulation Workflow simulation the only viable parameter choice is io_interconnect_bytes. My teammate suggested that elapsed_time could be interesting for users. I have tried but no metrics after have been filled. I suppose you really need an Exadata hardware to know how much time the query would take. After all it should depends on number of storage cell you bought…

You can monitor the successful execution and their timing of your tasks using a query like:

SQL> SET lines 200
SQL> col execution_name FOR a25
SQL> col execution_type FOR a20
SQL> col execution_start FOR a25
SQL> col execution_end FOR a25
SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, status
     FROM dba_advisor_executions
     WHERE task_name='Task01'
     ORDER BY execution_start;
 
EXECUTION_NAME            EXECUTION_TYPE       EXECUTION_START           EXECUTION_END             STATUS
------------------------- -------------------- ------------------------- ------------------------- -----------
cell_simulation_disabled  TEST EXECUTE         16-sep-2022 12:17:29      17-sep-2022 12:54:55      COMPLETED
cell_simulation_enabled   TEST EXECUTE         19-sep-2022 09:49:10      20-sep-2022 09:58:52      COMPLETED
cell_simulation_compare   COMPARE PERFORMANCE  20-sep-2022 11:19:40      20-sep-2022 11:19:40      COMPLETED

Remark:
Looking at he execution time greater than 24 hours I might have been a little bit too enthusiastic while selecting this (small) list of ten most physical I/Os queries…

In case of issue you can reset your different task execution trials with:

SQL> EXEC dbms_sqlpa.reset_analysis_task(task_name => 'Task01');
 
PL/SQL PROCEDURE successfully completed.

Exadata Simulation Workflow reporting

Time to generate an html (text mode is also available) report with:

SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool exadata_simulation_workflow.html
SQL> SELECT dbms_sqlpa.report_analysis_task('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off

The report start with a the information used to generate the report (the multiple run with and without cell simulation enabled):

A global summary of the improved SQL statements is displayed, with the impact on database overall workload and the impact of each SQL statements on database workload and how much they would improve individually: