New Features of Oracle 11G: SQL Performance Analyzer (1)

Database replay is a great tool that can capture the actual load in the database and replay it at will. It is part of the real Oracle Application Test option. It focuses on the word "actual", that is, the replay load is the actual load in the database.

In database replay, all the captured loads are replayed against the database. But what if you don't want to do that? For example, you may be curious about the SQL Execution Plan and how the SQL Performance is affected by changes because they seriously affect the application performance and availability. In addition, database replay only replays the captured load. Before running the command in the production version, you may be curious about the impact of parameter changes on some SQL statements.

This field is where SQL Performance Analyzer SPA, another important component of the real application testing series, is used. With SPA, you can play a specific SQL or the entire SQL load based on various types of changes, such as initialization parameter changes, optimization program statistics refresh, and database upgrades, and then generate a comparison report, helps you evaluate their impact. In this article, you will learn how to use the SQL Performance Analyzer tool to solve this important problem.

Example

Let's perform a test. First, we define the problem to be solved.

This is a typical problem: Oracle does not use indexes, but you want to know the reason. To answer this question, I read the Classical Essay "Searching for Intelligent Life in Oracle's CBO" by Tim Gorman, the Oracle elite ". Various versions of this paper can be found everywhere on the Web .)

One suggestion of Tim is to change the value of optimizer_index_cost_adj from the default 100 to a smaller value. This paper also gives a formula for calculating this value. According to the formula, in my case, I calculated this value as 10. however, this brings about a tricky problem: Will such changes benefit each SQL statement?

In Oracle Database versions earlier than 11 GB, I need to capture all SQL statements, trace and run these statements, and then get the execution plan-this is an extremely time-consuming and error-prone task. With the new version, I don't need to do that anymore. I use a very simple and effective SQL Performance Analyzer.

First, for example, we run the following query in the database:

select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;

Assume that these queries are sent by your application. By adjusting the annotations in SQL, you can search for them later. After the statement is executed, you can use SPA for it.

Usage

Generally, it is best to use the SPA function through Oracle Enterprise Manager. Of course, you can also use the command line option through the system-provided package dbms_sqlpa, but cannot exceed the manageability value added by the Enterprise Manager .)

Perform the following steps:

1. Open the Enterprise Manager Database Control and click the Performance tab. Scroll to the bottom of the page and you will see the following hyperlink.

 

2. Click Search Sessions and the following screen is displayed:

 

3. Search the executed SQL mode from the cursor cache. Note that all these SQL statements have an annotation CONTROL_QUERYn, where n is 11, 12, and so on. Enter this string as the search function. All previously executed SQL statements will be displayed. On the screen displayed above, you will see the single-choice button titled Save to a new SQL Tuning Set. Select this button and enter an SQL adjustment toolset named CONTROL1. Note: For example, You need to select some statements .) You do not need to add any comments to SQL. You only need to create an "SQL adjustment toolset" and fill in all the relevant statements.

4. Click SQL Tuning Sets. The SQL Tuning Sets page is displayed. Now select the STS named CONTROL1. on this page, you can check the STS and add and delete SQL statements for it. The following is the screen of the STS page: