Simulating Exadata Storage Performance over Traditional Storage

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

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

4 Responses to Simulating Exadata Storage Performance over Traditional Storage

  1. Hadn’t tried this yet and thrilled to see someone test this out! I’m very curious if once converted to Exadata, if the performance gains are as impressive as the test results?

    Like

    • Vivek says:

      Hi Kellyn,

      I too am curious and needs to validate this on EXADATA box. I stroingly feels that these are fairly accurate as oracle internally runs these queries, using a CELL SIMULATION (which is an underlying intelligence behind EXADATA). The customer is about to do a POC and once done, will share the results.

      Regards
      Vivek

      Like

    • Vivek says:

      Hi Kellyn,

      Due to busy schedule, could not write on the test results. Since it was a customer POC, I am not authorised to share minute details, but the results were really encouraging. Take my words.

      Regards
      Vivek

      Like

  2. Rajat Kumar says:

    I have found a similar post on the same topic by Arup Nanda. Interested people can have a look:

    http://www.oracle.com/technetwork/issue-archive/2011/11-jan/o11exadata-187987.html

    Rajat

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s