 |
|
|
|
|
|
|
|
In the first article of my contention series we discussed how contention can be regarded as a literal “bottleneck”, limiting the amount of work that the database can sustain - Figure 1 illustrates the concept. By reducing contention, we essentially widen the funnel so that more of the application demand can be met.
Figure 1 Contention can be thought of as a literal bottleneck
Tuning contention is appealing from a DBA perspective, since you don’t have to change the application code or schema. But it’s often more effective to reduce the demand on the database by making the application work smarter. In general, this involves tuning SQL and PL/SQL.
SQL tuning is a big topic; many books have been written on the topic including mine. I can’t reproduce a complete guide to SQL tuning here, but I can get you started on finding SQL to tune, and how to use Toad DBA suite tools to tune them.
Finding problem SQL
There’s a number of ways to identify SQL that might need tuning, but since time immemorial (OK, since Oracle 7!) the easiest way has been to examine the cached SQL information held in the V$SQL view. This view contains information about the SQL statements that are stored in the shared pool. Providing SQL statements are sharable – usually meaning that they use bind variables appropriately – then most of the high resource SQL will be represented in this view.
Figure 2 V$SQL and related tables
Originally V$SQL contained only limited information about SQL statement execution – logical IO and disk IO counts. Today, V$SQL includes CPU time, elapsed time and time spent in other high level categories such as Application time (which includes lock wait time).
While SQL statements which consume the most logical IO or have the highest elapsed times are often good targets for tuning, it’s often only examination of individual steps that will pinpoint the best tuning opportunity. In Oracle Database 10g, we can use cached query plan statistics to pinpoint individual steps within an SQL execution that might warrant attention. The view V$SQL_PLAN shows the execution plan for all cached SQL statements, while V$SQL_PLAN_STATISTICS shows execution counts, IO and rows processed by each step in the plan (You may have to up your STATISTICS_LEVEL from TYPICAL to ALL to get some of this new information). Figure 2 shows the essential columns in these new views.
Mining V$SQL, we can identify SQL statements that have high elapsed times, CPU or IO requirements. Using the newer plan tables, we can find SQLs that are performing actions that might be undesirable, such as table scans of large tables. We can also see the “real” execution plan and even get information about which steps in the plan are most troublesome.
The easiest way to mine this information is through Spotlight on Oracle.
Figure 3 Finding tuneable SQLs in Spotlight
The Spotlight top SQL facility allows us to filter SQLs for analysis in a number of ways. We can set a minimum filter condition, such as only those SQLs that haven high elapsed times, can sort by a wide range of metrics.
Figure 4 Viewing the SQL details
Figure 4 shows the detail information that is available for a specific SQL. Not only can we see the execution timings overall, but we can see them on a per execution basis and we can see a breakdown of timing in high level categories. In this case, the SQL spends about 70% of its elapsed time performing IO. An examination of the cached execution plan (Figure 5) shows us why: the SQL is performing a full table scan on the relatively massive LINE_ITEMS table.
 Figure 5 Viewing the cached SQL plan
It’s worth noting elapsed times in cached execution plans are cumulative. So for instance, if I perform a full table scan and then sort the results, the sort step will include the elapsed time from the scan step. That’s why every elapsed time above the full table scan in Figure 5 is roughly the same; the time spent performing the scan dominates every other step.
Tuning the SQL
As I said earlier, whole books can and have been written about SQL tuning and it takes a long time to get up to speed. But if you’re a Toad DBA suite or Toad Xpert owner then most of the hard work is removed, since you can use SQL Optimizer to automatically tune your SQL.
Clicking the SQL optimizer button (highlighted in Figure 4) launches SQL optimizer to tune the current SQL. I usually go straight for “Optimize and Index” to get indexing suggestions as well as options for optimizing the SQL with hints or rewrites.
Figure 6 SQL Optimizer in action
SQL Optimizer identifies several rewrites, one of which (#4) is a significant improvement over the original SQL, reducing elapsed time by 20%. It’s interesting to note however, that the improved SQL actually has a higher estimated plan cost – Oracle’s cost based optimizer is smart, but not infallible.
However, as is often the case, it’s the indexing changes that have the most dramatic improvement – reducing execution time and logical reads by 98%. Nothing beats correctly indexing if you want to get the best performance of an SQL. Our best bet would be to deploy the index; SQL Optimizer will provide the necessary CREATE INDEX statement if you hit the “Resolution” icon on the left toolbar.
Sometimes it’s not possible to immediately create a desirable index, or to change the text for a SQL statement. If we want to get the 20% improvement we’ve discovered without adding an index or having to edit application code, we can use SQL Optimizer’s outline facility. There’s a detail tutorial on deploying outlines using SQL Optimizer here . To get started, however, all you have to do is press the green pushpin ( ) next to the plan that you want to outline. This creates a new outline based on the rewrite that you chose (Figure 7).
Figure 7 Creating an Outline
The created outline is not yet active. If you want to activate it, you need to move it to an outline category that is active or active the category that it’s been assigned to. By default, the outline category is SQL_OPTIMIZER, but you can choose an existing category if you choose. An outline becomes active if it is in the category that is specified in the USE_STORED_OUTLINES setting. You can choose which category to active, move outlines between categories and view the hints associated with specific outlines in the SQL Optimizer Outlines manager (Figure 8).
Figure 8 SQL Optimizer outline manager
Conclusion
Getting started with SQL tuning involves mastering two fundamentals: finding SQL that warrants tuning, and working out how to make those SQLs work more efficiently. Spotlight and SQL Optimizer – within the Toad DBA Suite – make these two steps extremely easy even if you’re new to SQL tuning.
Tuning SQL reduces the demands that the application makes on the database. Every downstream activity – contention, memory efficiency and IO load – will be improved as a result. For this reason, tuning SQL is an essential pre-requisite for an efficient and scalable database server.
|
|
|
|
|
|
|
|
|
|
|
|
|
|