Simulating Exadata Storage Performance over Traditional Storage
January 3, 2011 4 Comments
Exadata Server Storage Software plays a crucial role in delivering Extreme Performance and Scalability for all database Application and this includes OLTP, Datawarehouse and mix of both. Performance bottleneck, due to data growth and Increase in the Userbase, the customers are now aware of the fact that they need to adopt this technology. There have been instances when Customers, especially in India, request a POC to measure the performance benefit of EXADATA over Traditional Storage. Starting Oracle 11g R2, Oracle provides certain API’s that can be give a fair amount of accurate performance statistics for the application queries and the way these will behave post-implementing exadata. This can also be done using Oracle Enterprise Manager 11g, but I used Oracle API’s to simulate and get the exadata performance matrix on a production system at a customer site.
One of my customer is evaluating Oracle Exadata and the mandate, in this case, is clearly increasing Market competition and sustenance. While the team were busy preparing a presentation, we felt, it would be a good idea to present the customer with the artifacts of the benefits in the actual production queries from their own production system. Post running these scripts a text file was presented to the customer. The customer was amazed to see the performance benefits.
Before we get into the API’s, a brief explanation on Exadata. EXADATA is a fully loaded Server, that comes in different configuration and is plug-and-play machine. It is basically an storage aware software and this intelligence is built in Storage Cells. These Cells store the data they contain and therefore, it is easier to get into the data block that contain the relevant rows directly. Unlike traditional storage that returns all the blocks to the Database Server, Exadata Storage Software returns only the relevant blocks that contain the data. Therefore, the main performance gain observed is due to the amount of data that travels between the Storage System and Database Server. Usually, the I/O bottleneck is observed in the production system if the amount of data that travels is enormous. With Exadata, these unwanted data is filtered at the storage level itself and relevant data traverses from the I/O subsystem to the Database Server. Further, its also reduces the amount of Latching, that would have otherwise required, to protect the blocks reads from the Storage into the SGA. Reduction in Latch Gets means a Scalable Application.
Following are the Scripts that were executed at the Customer site to simulate Exadata Performance and get the Performance Matrix of Application Queries. As mentioned earlier, Exadata Storage Cells provide the intelligence, these scripts use SQL Peformance Analyzer (SPA) to test cell storage in simulation mode and the matrix that is compared is IO_INTERCONNECT_BYTES. This is the amount of data, in bytes, sent by the Storage to the Database Server.
We start by creating SQL Tuning Sets. Once the STS is created, we need to capture all the Cursors (SQL Queries) in the Cache for a certain period, which is speficied in seconds. In my case, I captured the Queries for 180 Seconds. On a production system, this should be enough as the Shared Pool is usually warm and most of the queries are already found in the cache. If this value is increased, the Analysis (simulation) would take time. Once the Queries are captured, the Simulation is initiated, first with CELL_SIMULATION_ENABLED set to FALSE and then with CELL_SIMULATION_ENABLED set to TRUE. When the simulation is run, the queries are internally executed to generate and capture the io_interconnect_bytes value. Finally, it is time to compare the matrix and generate a TEXT or HTML file.
/* Create Tuning SQLSet */
begin
dbms_sqltune.create_sqlset(
sqlset_name => 'Trad_2_Exadata_Simulation',
description => 'SQL Tuning Set for Exadata Simulation');
end;
/
/* time_limit is the time in number of seconds the Queries will be captured. */
/* In this case, it is 180 Seconds */
begin
dbms_sqltune.capture_cursor_cache_sqlset (
sqlset_name => 'Trad_2_Exadata_Simulation',
time_limit => 180,
repeat_interval => 10
);
end;
/
variable Ret_Val clob
set long 999999
begin
:Ret_Val :=
dbms_sqlpa.create_analysis_task(
sqlset_name => 'Trad_2_Exadata_Simulation',
task_name => 'Trad_2_Exadata_Simulation_SPA');
end;
/
begin
dbms_sqlpa.set_analysis_task_parameter ('Trad_2_Exadata_Simulation_SPA','TIME_LIMIT','UNLIMITED');
dbms_sqlpa.set_analysis_task_parameter ('Trad_2_Exadata_Simulation_SPA','LOCAL_TIME_LIMIT',300);
:Ret_Val := dbms_sqlpa.execute_analysis_task (
task_name => 'Trad_2_Exadata_Simulation_SPA',
execution_type => 'test execute',
execution_name => 'TRADITIONAL',
execution_params => DBMS_ADVISOR.arglist ('cell_simulation_enabled','FALSE'),
execution_desc => 'Exadata simulation disabled'
);
end;
/
begin
:Ret_Val := dbms_sqlpa.execute_analysis_task (
task_name => 'Trad_2_Exadata_Simulation_SPA',
execution_type => 'test execute',
execution_name => 'EXADATA',
execution_params => dbms_advisor.arglist ('cell_simulation_enabled','TRUE'),
execution_desc => 'Exadata simulation enabled'
);
end;
/
begin
:Ret_Val := dbms_sqlpa.execute_analysis_task (
task_name => 'Trad_2_Exadata_Simulation_SPA',
execution_type => 'compare performance',
execution_params=> dbms_advisor.arglist('comparison_metric','io_interconnect_bytes')
);
end;
/
set lines 300
set pages 0
set trimspool on
set long 999999
spool report.txt
select dbms_sqlpa.report_analysis_task ('Trad_2_Exadata_Simulation_SPA','TEXT','TYPICAL','ALL') from dual;
spool off
Oracle also provides with a script tcellsim.sql under $ORACLE_HOME/rdbms/admin. This can also be used to run the simulation. The output from the production enviroment is pasted below. I have trimmed the output to make this short.
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : Trad_2_Exadata_Simulation_SPA SQL Tuning Set Name : Trad_2_Exadata_Simulation
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 1335
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_10626 Started : 12/30/2010 14:34:36
Execution Type : COMPARE PERFORMANCE Last Updated : 12/30/2010 14:38:18
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : 300
Status : COMPLETED Number of Errors : 7
Number of Timeouts : 4
Number of Unsupported SQL : 77
SQL Details:
-----------------------------
Object ID : 3160
Schema Name : F_LEA
SQL ID : 5um8xff95jn6x
Execution Frequency : 266122
SQL Text : SELECT 'x' FROM L_AGREEMENT_DTL WHERE AGREEMENTID =
:b1 AND STATUS IN ( 'A','L','O','X','C' )
Execution Statistics:
-----------------------------
----------------------------------------------------------------------
| | Impact on | Value | Value | Impact |
| Stat Name | Workload | Before | After | on SQL |
----------------------------------------------------------------------
| elapsed_time | | 57.165548 | | |
| parse_time | | .000424 | | |
| cpu_time | | 14.94 | | |
| user_io_time | | 1181.09798 | | |
| buffer_gets | | 1360874 | | |
| cost | 0% | 2 | 2 | 0% |
| reads | | 340101 | | |
| writes | | 0 | | |
| io_interconnect_bytes | 84.13% | 2786107392 | 12337536 | 99.56% |
| rows | | 1 | 1 | |
----------------------------------------------------------------------
SQL Details:
-----------------------------
Object ID : 2839
Schema Name : F_LEA
SQL ID : 1xny80dtnbf6f
Execution Frequency : 157981
SQL Text : SELECT INSTALTYPE,EMI FROM L_AGREEMENT_DTL WHERE
AGREEMENTID = :b1
Execution Statistics:
-----------------------------
---------------------------------------------------------------------
| | Impact on | Value | Value | Impact |
| Stat Name | Workload | Before | After | on SQL |
---------------------------------------------------------------------
| elapsed_time | | 3.045475 | | |
| parse_time | | .000509 | | |
| cpu_time | | .65 | | |
| user_io_time | | 65.463246 | | |
| buffer_gets | | 57237 | | |
| cost | 0% | 2 | 2 | 0% |
| reads | | 54172 | | |
| writes | | 0 | | |
| io_interconnect_bytes | 7.77% | 443777024 | 12243136 | 97.24% |
| rows | | 1 | 1 | |
---------------------------------------------------------------------
From this Simulation, it is clearly visible that the performance benefit using Exadata is tremendous. To make this shoirt, I have pasted only two comparisons, but there were other queries as well that showed a significant amount of performance benefit. In the first query above, the performance benefit on the overall system load is around 85% and the amout of data that traverses between the storage to database server has dropped by 99.56% (from 2.7 GB to 12 MB). This is significant. In the second query as well, since the table is same, the drop is from 443 MB to 12MB, a saving of 97%.