Table of contents
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:
The detail of the first SQL statement, the after column is empty, I guess it is because it cannot be simulated as if really running on an Exadata:
Finally the execution plan before:
And after like if running on Exadata:
References
- Using the Exadata Simulation Workflow
- Configuring an Analysis Task for Exadata Simulation Using APIs
- How to Check and Enable/Disable Oracle Binary Options ? (Doc ID 948061.1)
- Autonomous Dedicated and SQL Performance Analyzer: Through good SQL and bad
