Oracle Database 11g: by Arup Nanda
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).
See Series TOC
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.
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;
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.
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
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.
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.
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:
Back to Series TOC
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.