Simulation Demonstrates Performance

Use Oracle Exadata simulation in SQL Performance Analyzer to help predict how your application will perform on Oracle Exadata.

January/February 2011

The newest disrupter in the database market is Oracle Exadata, which improves the performance of any type of application. But for those considering the move to Oracle Exadata, the big question is probably how your existing applications—running on a traditional database—will perform on Oracle Exadata. How can you know the answer before you actually take the leap to Oracle Exadata? In Oracle Database 11g Release 2, there is a new addition that allows you to simulate and measure the activities of your database under Oracle Exadata without actually investing in an Oracle Exadata appliance. In this article, I will show you how to use this capability to predict the performance you can expect for your applications running on Oracle Exadata.

Oracle Database 11g Release 1 introduced a new tool—SQL Performance Analyzer—as a part of the Oracle Real Application Testing suite (see my Oracle Magazine March/April 2008 article “Performing Through Changes"). This tool allows you to capture SQL statements from any database and replay them in a new environment to uncover potential issues and predict behavior in the new target environment.

The target environment may be structurally different from the current production environment, with more or fewer indexes and materialized views, using different infrastructures, or running under different session parameters. In Oracle Database 11g Release 2, the scope of SQL Performance Analyzer is extended to simulate the functionality of Oracle Exadata.

Of the many ingredients in the secret sauce that makes Oracle Exadata so fast, it’s the intelligence in the storage cells—where the Oracle Exadata storage is located—that has the largest impact. The storage cells know the pattern of the data they store, so they can intelligently return only relevant data, rather than all data. Here is a simple query that illustrates the point:

select sum(order_qty)
from sales
where cust_id = 1000

In a traditional Oracle database, the server process issues the command to retrieve all blocks from the SALES table to the buffer cache (located in the system global area), if they are not there already. Then the session sifts through the accumulated data to identify rows where CUST_ID = 1000. Those rows are copied to the program global area of the server process, the sum of another column—ORDER_QTY—is computed, and, finally, the result is returned to the user. The table could potentially have 1 billion rows, and the number of rows where CUST_ID = 1000 could be just 100, so the database has to get all 1 billion rows to sort through them to find the matching rows. The I/O subsystem will still need to return all the blocks of the table to the server process. The storage in this case does not know the underlying data structure, so it can’t differentiate relevant data from the rest.

In an Oracle Real Application Clusters (Oracle RAC) system, there is yet another issue. Parallel query slaves could be spun off on multiple instances. They send the data they gather to the query coordinator in a different instance for collation over the inter-connect, saturating the interconnect hardware with this traffic. In addition, the process of transferring blocks over the interconnect also consumes CPU and requires latches, which in turn requires further CPU cycles. Therefore, the greater the demand for blocks from the storage system, the greater the load on CPU and I/O and the interconnect bandwidth—and the worse the query performance.

This is exactly where Oracle Exadata works its wonders—it knows the general distribution of data in table columns at the storage cells. When the aforementioned query is issued, the storage cells do not just return all the data from the table—they filter data at the storage level. The server process does not get all the blocks, but rather only those blocks that could potentially satisfy the condition, and then computes the sum from the relevant rows from those blocks. The demand on the storage is reduced from 1 billion rows—the total number of rows in the table—to just 100—the number of rows where CUST_ID = 1000. The I/O waits are reduced, enabling the storage subsystem to service more clients without putting them in queues. But the reduced load on storage is not the only Oracle Exadata benefit: the reduced number of buffers in the buffer cache means less contention for latches and subsequently less CPU consumption as well. In a typically busy system, Oracle Exadata could deliver several orders of magnitude difference in performance for this query and data.

Now that you know how Oracle Exadata speeds up the queries, the next natural question may be, does it work for your application? If your application does a lot of reads, then it exerts pressure on the storage and pushes a lot of buffers through the cluster interconnect. Oracle Exadata will reduce that pressure with significantly fewer reads (because the storage returns fewer blocks to the database nodes), so you can expect a dramatic performance difference. On the other hand, an application that does few reads may see a lesser performance gain. Instead of guessing about your application’s performance change following a move to Oracle Exadata, you can use Oracle Exadata simulation in SQL Performance Analyzer to help you predict performance gains. Note that in this initial offering, only a data warehouse decision-support system workload is simulated.

You can perform the Oracle Exadata simulation in SQL Performance Analyzer in two ways—using Oracle Enterprise Manager and using APIs provided with Oracle Database. I am going to show the process using APIs. (For steps on using Oracle Enterprise Manager, see the Oracle By Example tutorial.) Here are the steps:

1. Create a SQL tuning set (STS). Listing 1 shows how to create an STS named ExadataSimul1.

   Code Listing 1: Creating a SQL tuning set

   begin
     dbms_sqltune.create_sqlset(
       sqlset_name => 'ExadataSimul1',
       description => 'SQL Tuning Set for Exadata Simulation');
   end;

2. With the STS created, add the relevant SQL statements to it. There are several ways to do this. The simplest approach is to create a job that scours the cursor cache in the shared pool and adds the SQL statements found to the STS. Listing 2 shows how to do that every 10 seconds for 120 seconds. The execution of this command will wait on the screen for 120 seconds, so you will have to create a different session for the next step.

   Code Listing 2: Adding SQL statements to the SQL tuning set from the cursor cache

   begin
     dbms_sqltune.capture_cursor_cache_sqlset (
       sqlset_name     => 'ExadataSimul1',
       time_limit      => 120,
       repeat_interval => 10
     );
   end;

3. In a different session, run some example workload that can be captured in the STS. Listing 3 shows a query that performs a considerable amount of I/O and is perfect for this demo. This query is based on the SH sample schema. When this query runs, it will be captured in the ExadataSimul1 STS automatically.

   Code Listing 3: Running an example query

   select prod_name, cust_state_province, channel_desc, sum(amount_sold)
   from sales s, customers c, products p, channels c
   where s.cust_id = c.cust_id
   and s.prod_id = p.prod_id
   and s.channel_id = c.channel_id
   and c.cust_year_of_birth between 1960 and 1970
   group by prod_name, cust_state_province, channel_desc

4. Create an analysis task named ExadataSimulSPA1 to subject the SQL statements in the ExadataSimul1 STS to Oracle Exadata simulation, as shown in Listing 4.

   Code Listing 4: Creating an analysis task

   variable ret clob
   set long 999999
   begin
     :ret :=
        dbms_sqlpa.create_analysis_task(
          sqlset_name       => 'ExadataSimul1',
          task_name         => 'ExadataSimulSPA1');
   end;
   /
   print ret

5. With the STS created and populated, start the simulations. First, execute the analysis task with Oracle Exadata simulation disabled—with the cell_simulation_enabled parameter set to false—as shown in Listing 5. The task will run the SQL statements inside the STS.

   Code Listing 5: Executing a task without Oracle Exadata storage simulation

   begin
      dbms_sqlpa.set_analysis_task_parameter ('ExadataSimulSPA1',
   'TIME_LIMIT','UNLIMITED');
      dbms_sqlpa.set_analysis_task_parameter ('ExadataSimulSPA1',
   'LOCAL_TIME_LIMIT',300);
      :ret := dbms_sqlpa.execute_analysis_task (
         task_name        => 'ExadataSimulSPA1',
         execution_type   => 'test execute',
         execution_name   => 'INITIAL',
         execution_params => DBMS_ADVISOR.arglist ('cell_simulation_enabled','FALSE'),
         execution_desc   => 'Exadata simulation disabled'
       );
   end;

6. Next, run the same execution with Oracle Exadata simulation enabled—with the cell_simulation_enabled parameter set to true—as shown in Listing 6. The task will run the SQL statements inside the STS and simulate the process of running the statements on Oracle Exadata.

   Code Listing 6: Executing a task with Oracle Exadata storage simulation

   begin
      :ret := dbms_sqlpa.execute_analysis_task (
         task_name        => 'ExadataSimulSPA1',
         execution_type   => 'test execute',
         execution_name   => 'EXASIMUL',
         execution_params => dbms_advisor.arglist ('cell_simulation_enabled','TRUE'),
         execution_desc   => 'Exadata simulation enabled'
       );
   end;

7. After both executions, it’s time to perform the comparison. Listing 7 shows how to execute a comparison task where the metric to be compared is the I/O interconnect bytes from each execution.

   Code Listing 7: Executing a comparison task

   begin
     :ret := dbms_sqlpa.execute_analysis_task (
       task_name       => 'ExadataSimulSPA1',
       execution_type  => 'compare performance',
       execution_params=> dbms_advisor.arglist('comparison_metric',
   'io_interconnect_bytes')
     );
   end;

8. Finally, it’s time to generate the comparison report. The best way to visualize the report is in HTML output. Listing 8 outputs the report to an HTML file named exasimul1_rep.html. Now you can open the report in a browser and examine the differences in performance clearly.

   Code Listing 8: Getting the comparison report

   set lines 300 pages 0 trimspool on long 9999
   spool exasimul1_rep.html
   select dbms_sqlpa.report_analysis_task ('ExadataSimulSPA1','HTML','TYPICAL','ALL')
   from dual
   /
   spool off

Figure 1 shows a partial output from the report that compares the execution statistics. The report clearly shows the predicted impact of Oracle Exadata on I/O interconnect bytes. In the first run—with Oracle Exadata simulation disabled—the number of bytes was 25,174,016. In the second run—with Oracle Exadata simulation enabled—the number of bytes was only 2,763,712, or about a tenth of the bytes in the first run—a significant improvement by any count.

This means that on Oracle Exadata, the I/O across the interconnect will be about a tenth of the original value, reducing the bandwidth utilization and resultant CPU usage as well as the number of blocks processed by the global cache mechanism. So the query, with no changes, will likely perform orders of magnitude better on Oracle Exadata.

Similarly, had you seen an improvement in the range of 10 percent in the Exadata simulated workload, then you could surmise that those SQL statements likely would not perform dramatically better in Oracle Exadata. In that case, the most dramatic event would be that you were able to predict the execution improvement reasonably well without actually executing the statements on an Oracle Exadata Database Machine.

The full report (not shown in Figure 1) also shows the execution plan of the queries before and after the Oracle Exadata cell simulation. Migration to Oracle Exadata may not change the query plan, but some execution steps will change to reflect the cell processing that is not available on a regular storage. For example, a TABLE ACCESS FULL execution step in the non-Exadata simulation will become TABLE ACCESS STORAGE FULL under Oracle Exadata and is shown as such in the after-simulation plan. But with that exception, the plan will not change because all the other factors—optimizer environment, session parameters, RDBMS version, and so on—are identical in both simulation runs.

As shown in Listing 8, the last parameter in the call to dbms_spa.report_analysis_task is ALL, which indicates that the report will include details on the individual SQL statements. This helps to present the performance benefit of Oracle Exadata in even greater detail, because not only can you see the overall improvement, but you will be able to see the impact of Exadata simulation on individual statements—some of which may be business critical or are executed much more often in the production environment. The I/O savings in those cases will be highly visible in the report and very valuable to your production operations—a fact that you may want to highlight in the business case for Oracle Exadata.

One of the key ingredients of the superior performance of the Oracle Exadata appliance is the ability of the storage to return only the relevant data blocks—not the entire table. This minimizes the I/O at the database servers and results in less I/O across the interconnect and fewer CPU cycles. But if you want to predict how much I/O is saved on specific SQL statements from your actual production database, you can perform the Oracle Exadata simulation on any Oracle Database 11g Release 2 database with traditional storage and get a fairly accurate estimate of the I/O savings in Oracle Exadata.