Oracle Database 11g:
The Top Features for DBAs and Developers
by Arup Nanda,Oracle ACE Director
SQL Performance Analyzer & Real-Time SQL Monitoring
Learn how to accurately predict changes on the actual SQL statements issued against your database - plus, monitor SQL performance in real time (in Release 2 only).
In a different installment of this series you learned about Database Replay, that great tool for capturing the real workload in your database and replay them at will. Database Replay is part of what Oracle calls the Real Application Testing option, with the emphasis on the word "real"—the workload replayed is actually what occurred in your database.
In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? For example, you may be interested in understanding how SQL execution plans and therefore SQL performance might be affected by a change, because they can severely affect application performance and availability. In addition, Database Replay replays only what has been captured; not anything else. You may be interested to know the impact of parameter changes on some SQLs that has not been executed in production yet.
This area is where the other important component of the Real Application Testing family—SQL Performance Analyzer (SPA)—shines. SPA allows you to play out some specific SQL or your entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help you assess their impact. In this installment, you will learn how to use this tool to answer that important question.
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. To answer that question, I turned to the classic paper by the Oracle guru Tim Gorman, "Searching for Intelligent Life in Oracle's CBO." (You will find versions of this paper in many forms all over the Web.)
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, you can search for them later. After the statements are executed, you 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, you 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.
1. Start Enterprise Manager Database Control and click the Performance tab. Then scroll to the bottom of the page where you will see hyperlinks as shown below.
2. Click on Search Sessions, which brings up a screen such as the following:
3. Search for the pattern in the SQLs you 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.
4. Click on SQL Tuning Sets, which brings up the SQL Tuning Sets page. Now select the STS named CONTROL1. In this page, you can examine the STS and add and drop SQLs from it. Here is a screenshot showing the STS page:
5. Now, from the Performance page, click SQL Performance Analyzer. It brings up the main SPA page shown below.
6. As you can see, no SPA tasks have been defined yet. You will define one now. In this example you 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.
7. In the page, you will have to enter the necessary information for the SPA task. Give that task a name: STS1, for this example.
8. Next, you need to enter the SQL Tuning Set name. Click on the flashlight icon next to it and select the STS named CONTROL1.
9. In the section Parameter Change, you 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.
10. 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.
11. Finally, schedule this SPA task for execution. Select the radio button Immediate, meaning you want it to run immediately.
12. 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.
13. 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:
14. 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.
15. Click on the SPA Task name ( STS1) to see the comparison metrics. This brings up a screen as shown below.
16. 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.
17. 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, you 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 you 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; PROMO_ID CNT ---------- ---------- 534 1 999 887837 350 18022 33 2074 351 10910 ---------- sum 918844
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. You 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 you 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, you can take advantage of SQL Plan Management (discussed in this installment ). 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.
SPA is invaluable in several scenarios, including database version upgrades, deploying database patchsets, database parameter changes, and optimizer parameter changes (to name just a few).
For example, when deciding if you want to upgrade the optimizer parameter from say 10.2 to 11.1, you definitely want to see the impact this change will have on your SQL statements. The best tool for the job is SPA. The only difference is, in step 5 above, instead of choosing Database Parameter Changes, choose Optimizer Changes, which brings up a screen as shown below.
In this screen, choose the appropriate source and target optimizer versions and follow the rest of the steps.
When is the best time to use this new tool? The simple answer is: whenever you make any type of change. Unlike with Database Replay—where you can't see the exact SQLs—you can use SPA to get results on specific SQLs or the entire application SQL workload. You can evaluate the pros and cons and arrive at the best possible alternative without compromising your application's performance. No choice is ever black or white; it's the shades of gray that makes arriving at a decision so tough. SPA moves these gray scales toward one of the poles and makes it easier for you to make that decision.
Release 2 Addendum:
There it goes again: that horrid query that wreaks havoc in your database. You identified it and nuked it. All is well again. But consider another twist: What if the query were technically benign yet could potentially grow into a monster by eating away your I/O bandwidth and CPU like locusts attacking a cornfield? If you understand all those internal X$ and V$ views and speak 10046 event as your first language, you could whip up a handful of SQL scripts that will tell you that in a jiffy. For the rest of us mere mortals, we would surely welcome an easier option.
Fortunately, Oracle Enterprise Manager Database Control 11g Release 2 has a Real-Time SQL Monitoring capability that shows you SQL statements being run right now on a GUI screen, along with all the relevant details on resources like CPU, IO and wait events. What’s more, it also shows you the detailed steps of the execution plan and the resource stats at each step. To reduce the clutter on the screen, this feature monitors only those SQLs that consume at least 5 seconds of CPU or I/O.
Let’s see how it works. To demonstrate this feature, we need to run some fairly complex SQL. I will use the SH schema that comes with Oracle Examples. Then run this statement in SQL*Plus:
SELECT /*+ parallel */ 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 2010 - 30 AND 2010 - 20 GROUP BY prod_name , cust_state_province, channel_desc;
Next let’s see the SQL Monitoring. In Database Control, click the Performance Tab, scroll down to the bottom in the tab, and locate the group of hyperlinks called Additional Monitoring Links. Within that group, click SQL Monitoring. It will bring up a screen similar to one shown below.
Note the Status column, it shows the sundial icon that the SQL statement is running, provided the SQL statement you issued earlier is still being executed. If you click on the SQL ID, you will see the details on the SQL. A screen called Monitored SQL Execution Details pops up similar to the one shown below. Here is the top portion of the screen that shows the overview of the execution of the SQL.
Each of the bars shows you the corresponding statistics in real time. You can get the numbers behind the bars by moving the mouse pointer over it.
Right-clicking the screen will show you a context sensitive screen as shown below.
From this popup menu you can choose other relevant information such as the SQL Text, sessions, etc. showing the other regular screens of Enterprise Manager.
Here is the bottom portion that shows the execution plan, also in real time.
Note the screen carefully. It does not just show the execution plan, it also shows the various statistics such as the CPU and IO usage at each stage; not just for the statement overall. This helps identifying the specific area where a statement is slow right now.
Apart from showing the execution plan, it also shows the Parallel Query statistics. Note the tab named Parallel next to the Plan Statistics tab. It should bring up a screen like the following:
It shows the parallel query servers, what type of waits they are experiencing right now, the IO requests they are issued, the number of consistent gets they have asked for, etc. – for each PQ slave. Right-click the IO request bars; the same popup menu you have seen earlier will pop up with a little difference – there is a new item called Toggle IO Bytes. Choosing that will show the IO in bytes, not number of requests.
Here is how the IO Bytes screen looks.
In the detail column if the parallelism is downgraded, you will see the actual parallel degree but there will be little red icon to identify the fact that the actual and requested degrees are different, as shown below. If you hover the mouse pointer over the Parallel value, you can see the requested degree.
What if you want to show this report to a colleague for advice or further analysis? You can generate a html report. Click on Report on the main page, which you can show as regular HTML file. Here is an excerpt from that report:
The report contains all relevant information on the SQL statement including the PQ slaves you saw earlier. Of course, this is just a snapshot in time, not a moving picture as you could see in the EM screens in real time. Real-Time monitoring of SQL statements is especially useful if you want to find out which step in the execution is taking long time – a great way to tune for performance and is sure to have a major place in your toolbox.