Oracle Database 11g: The Top Features for DBAs and Developers

Oracle Exadata Simulator

Predict how your statements will behave in Oracle Exadata using any Oracle Database 11g Release 2 EE database (using SQL Performance Analyzer).

See Series TOC 

By now you have probably heard about the Oracle Exadata Database Machine, a a fully integrated package of servers, storage, networking, and software for hosting database applications.

Without going into details, it’s the inter-node communication, or more specifically the reduced traffic, that is the “secret sauce” of Oracle Exadata - customers have reported performance increases of up to 50x. While it may be tempting to jump onto the bandwagon for that extra boost with no further questions asked, you should consider what it will do for your specific database application first.

The ideal approach would be to capture all your activities in production, replay them on an Exadata box, and observe the impact first hand - great solution, if you already have an Exadata machine. For those who don't, in Oracle Database 11g Release 2 there is another option: You can use the SQL Performance Analyzer’s special Exadata Simulation tool to simulate the behavior of SQL statements under Exadata on any Oracle Database 11g Release 2 EE database.

To demonstrate, let’s see the impact of Exadata on this query’s performance. Issue this query first so that it goes into the shared pool. This SQL statement uses the SH schema included in the Example Schemas in Oracle Database 11g Release 2 software (available separately from the main database software). Please note that the precise query is not important; any query will do for the demo as long as it causes some considerable I/O.

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

To simulate Exadata, first you need to create a SQL Tuning Set (STS). There are many ways to do this but using Oracle Enterprise Manager Database Control is simplest and most intuitive. Let me show you how: From the main Database page, choose the Performance tab, scroll down to the bottom of the page, and locate the group of hyperlinks called Additional Monitoring Links. From there, click on SQL Tuning Sets. It will bring up a screen similar to the one shown below (screen cut to show only the relevant section to reduce the space usage):

Next you need to create an STS. Click Create; it will bring up a screen similar to one below. Here you will be able to pick how you want to populate the SQL statements into this STS.

Choose the appropriate loading method. In this example we will use the one-time loading option and the Data Source we choose is Cursor Cache, which is the cache of SQL statements cached in shared pool. Click Next, which will bring up a screen similar to the one shown below. You can add different filtering conditions to filter out the SQL statements you don't want.

Once all filtering conditions are put, click Finish, which will create a Scheduler Job, after the execution of which a SQL Tuning Set in the name ExaSimul1 will be created.

After this, you can call upon the power of Real Application Testing’s SQL Performance Analyzer (SPA) to perform the simulation. From the main Database page, choose the Software and Support tab and then click SQL Performance Analyzer under the group heading Real Application Testing. Here is the screenshot of the main SPA page:

Note the new link: Exadata Simulation. Click on that hyperlink. You will see a screen similar to the one shown below. Provide a task name – Exadata Simulation Task 1. In the SQL Tuning Set field, click the flashlight icon and choose the STS you created earlier – EXASIMUL1.

In this screen you also have the ability to set a time limit for the execution of the SQL statement in the Per SQL Time Limit field. Setting this parameter will ensure that any SQL taking longer than this limit is terminated, all necessary performance metrics collected then the SPA is moved on to the execution analysis of the next SQL statement in that SQL Tuning Set. In this example, we will leave it at Unlimited.

Click Submit. This will create a Scheduler Job and you will be presented with the SQL Performance Analyzer screen shown below. The page will continue to be refreshed with the Status column reflecting the progress of our submitted SQL Performance Analyzer Task.

When the Status column changes to “Completed”, clicking the hyperlink that is the name of the task will take you to the details on the execution of the task, shown below:

Click the eyeglass icon under the heading “Comparison Report”. This is the report you are interested in.

This reports tells the difference in performance you will most likely see when you run the SQL statements in the STS (just a few in this example but in reality you will likely select a lot of SQLs from your applications). The comparison method is the interconnect I/O bytes. In the above example we can see that one of the SQLs caused about 25MB of interconnect traffic but will likely cause only 2MB in Exadata – a 12x decrease. (Your results will vary; this is merely an illustration.)

Because of the filtering made by the Exadata software directly at the storage cell level, the amount of information handled by the RAC nodes is significantly reduced, reducing interconnect traffic as a result. In addition, since the RAC nodes have fewer buffers to process, they will consume less CPU as well, further reducing the elapsed time.

All this of course depends on how much filtering is done at the Exadata storage cells. This is what you will expect to find out in the simulation; you will see the SQLs that improved in execution time (and those that suffered, if any). The effect on each SQL statement individually and cumulatively will be shown clearly in the report, which makes it very useful to see the potential impact should you choose to implement Exadata.

If you have run SPA before, you might have noticed that the comparison report showed the plan changes – how many SQLs changed plans for the better or worse, and how many remained unchanged. In Exadata simulation, the plan does not change. (Remember, it is after all an Oracle 11g Release 2 database.) Therefore a comparison in plan change terms will not be relevant.

Now that you can appreciate the power of the Exadata simulation, you might be wondering about a related question: If you are one of those lucky ones already running Exadata hardware, would you use this simulation path in SPA? No, not at all. If you already have an Exadata, then you should use the regular paths in SPA – parameter change or guided workflow to test the settings or changes in SQL to see their impact.

Back to Series TOC