This article will make you learn how oracle SQL Performance Analyzer Accurately assess the impact of rewriting of SQL statements and get suggested improvements.
A Sample Problem
Let's go on a test drive. First, let's define the problem you are trying to solve.
The problem is a typical one: Oracle is not using an index, and you want to know why not.
One of Tim's suggestions is to change the value of the parameter optimizer_index_cost_adj from the default 100 to something smaller. The paper also gives a formula to calculate what the value should be. Based on that formula, I calculated the value to be 10, in my case. But here comes a difficult question: Is that change going to be beneficial for every SQL statement?
In releases prior to Oracle Database 11g, I have to capture all SQL statements, run them through tracing, and get execution plans—a task not only enormously time consuming but error prone as well. With the new release, I don't need to do that; instead, I have the very simple and effective SQL Performance Analyzer.
First, for the purpose of illustration, run the following queries 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 these queries are issued by your application. By pacing the comments in the SQL, Oracle DBA can search for them later. After the statements are executed, Oracle DBA can use SPA on them.
How to Use It
As usual, the best way to harness the power of this tool is via Oracle Enterprise Manager. (Of course, Oracle DBA can still use command-line options through the provided package dbms_sqlpa but the manageability value added by Enterprise Manager can't be beat.)
Follow the steps shown below.
- Start Enterprise Manager Database Control and click the Performance tab. Then scroll to the bottom of the page where Oracle DBA will see hyperlinks as shown below.
- Click on Search Sessions, which brings up a screen such as the following:
- Search for the pattern in the SQLs Oracle DBA executed from the cursor cache. Note that the SQLs had a comment in them—CONTROL_QUERYn—where n was 11, 12, etc. Enter the string as a search function. It will bring up all the SQLs executed earlier. On the screen shown above, you will see a radio button titled Save to a new SQL Tuning Set. Select that and enter a SQL Tuning Set named CONTROL1. (Note: for the purposes of examples you are selecting a set number of statements.) No need to put any comments in SQL; all you need to do is create a "SQL Tuning Set" and put all relevant statements in there.
- Click on SQL Tuning Sets, which brings up the SQL Tuning Sets page. Now select the STS named CONTROL1. In this page, Oracle DBA can examine the STS and add and drop SQLs from it. Here is a screenshot showing the STS page:
- Now, from the Performance page, click SQL Performance Analyzer. It brings up the main SPA page shown below.
- As Oracle DBA can see, no SPA tasks have been defined yet. Oracle DBA will define one now. In this example Oracle DBA are evaluating the impact of change to the parameter optimizer_index_cost_adj; so click Parameter Change. It brings up the SPA Task Definition page shown below.
- In the page, you will have to enter the necessary information for the SPA task. Give that task a name: STS1, for this example.
- Next, you need to enter the SQL Tuning Set name. Click on the flashlight icon next to it and select the STS named CONTROL1.
- In the section Parameter Change, Oracle DBA need to enter the parameter you need to change. Click the flashlight icon and select the parameter optimizer_index_cost_adj. The current value is already populated; enter the target value in the box Changed Value.
- Next, decide how you will compare these changes: on elapsed time, on CPU time, and so on. For this purpose of this example, choose Buffer Gets.
- Finally, schedule this SPA task for execution. Select the radio button Immediate, meaning Oracle DBA want it to run immediately.
- With all the details in place, click Submit. It creates a job that you can monitor independently but you can also be in this page and monitor the status of the SPA task as shown below.
- Click Refresh to see the current status of the task. Note the icon under the Last Run Status column. Here is a description of those icons:
- The icon in the picture means the task is running now. Keep on clicking Refresh. When the icon changes to Completed, shown as a tick mark, the SPA task has completed.
- Click on the SPA Task name (STS1) to see the comparison metrics. This brings up a screen as shown below.
- Note the eyeglass icon under the column Comparison Report. This is where you can compare the results of SQL runs before and after the parameter change. Click on that icon.
- This is the most exciting part. A screen comes up as shown below:
This is exactly what you were waiting for. On the top left corner, you will see the comparison of the two runs of the SQL set before and after the change. Overall, there was a 60% improvement in performance! It's right there, in black and white. In the right hand side of the screen, Oracle DBA will see how many SQL statements changed the execution plans. It seems a majority changed the plans while a small number didn't.
The bottom portion of the screen shows you the SQL ID of the SQL statements analyzed in this task. The little arrows preceding these SQL IDs show whether these SQL statements improved or degraded, and the numbers following the SQL IDs show the percentage of that impact. These numbers tell you exactly the impact of the change on each SQL statement. If you wish, you can examine the SQL by clicking SQL ID. Note the very first SQL, which has the highest impact; if you click on that, you will see a screen similar to the following:
The screen shows a lot of statistics on the execution of the SQL. The bottom part of the screen shows the comparison of the execution plans:
Now Oracle DBA can see how the use of the index forces fewer buffers. But is the picture that rosy? Consider another SQL:
In this case, the gains were modest, just 0.48%, compared to 31.95% in the previous case. For what reason? To find the answer, click on the SQL ID, which brings up a screen as shown below:
Here you can see exactly what changed. The elapsed time actually went up from 0.504 to 1.022 seconds, and all because of CPU time. Why? If you examine the data distribution pattern, you will see the promo_id is distributed as shown below:
SQL> select promo_id, count(1) cnt from sales group by promo_id;
The promo_id 999 appears 887,837 times in the table or almost 97%. When the plan was changed to include an index scan, this query suffered. Oracle DBA would have been better off with a full table scan. So even though the overall impact was positive, some individual components lagged. When you make a decision on whether you want to change the parameter, you should consider how important these SQL statements are—the ones that improved as well as regressed.
As Oracle DBA can see, you wanted to estimate the impact of making a very important change in the database parameter. Using SPA, you did not have to make an estimate—not even a "guess-timate"—on the potential performance impact. Rather, you were able to objectively measure it using the very SQL statements that your application makes against your own database.
Now consider another case: After the change to the parameter, the performance degrades, not improves. Here is a screenshot:
Here the SQL statements are all running worse than before. To fix the problem, Oracle DBA can take advantage of SQL Plan Management (See Learn Oracle SQl Plan managment for more information) . SPM provides execution plan stability by allowing you to select good execution plans as your baseline, which are then used by the optimizer for all executions of that SQL. This baselined plan is used until it is disabled or a new baseline plan is created by you. Another way to fix the problem of regressed SQL is by using the SQL Tuning Advisor, which can provide SQL tuning recommendations or suggest external modifications such as creating indexes to improve performance.