Page 749
Page 750
Tuning the application really amounts to tuning your SQL statements within your application. If your application has a graphical user interface (GUI) front end, tuning the application still amounts to tuning analogs of SQL statements. In other words, all the GUI-level actions can be mapped to one or more SQL statements (SELECT, INSERT, UPDATE, DELETE) of varying complexity. In any case, our Return-on-Investment (ROI) strategy tells us that we must tune the application first, if at all possible, then resort to tuning all the aspects of the database last.
Subjective estimates have shown applications being responsible, on average, for 80 percent of the total system (application+database) performance, leaving only 20 percent for the database. I agree with the idea behind this claim, as we have just covered. However, I tend not to agree with the numbers that make up this claim. In my experience, I have seen systems that fulfill these estimates and systems that exhibit the opposite situationthe opposite situation being the database accounting for most if not all the performance.
What this really means to DBAs is that the performance of systems may be labeled situational, relative, or application specific. This should sound familiar, especially in regard to discussions of application types. Yet one absolute rule does hold true, and I wholeheartedly advocate that DBAs pay close attention to the first rule of tuning: The application should always be tuned first.
This holds true for two reasons: (1) The majority of database systems existing today owe most of their performance to their applications, and (2) Even if this does not hold true for your individual system, as a DBA you must categorize the system's application type. This means that you must review the application, specifically the types of transactions: the access code and the SQL code. Even if your application code is programmed as efficiently as possible from a database access point of view, you must still understand how the programs interact with your database.
How do you accomplish the task of reviewing the application? If the application is reasonably smallin terms of lines of code or number of separate program units (modules)then manually reviewing everything is possible. On the other hand, if the application is large, full-manual review would most likely be out of the question. Principle 3, Triage, of our Performance Tuning Fundamentals (Chapter 29) gives us direction. You can focus on those modules or sections of code that tend to account for most of the resource and time consumption.
First you will attempt to fix the 20 percent of the application code that accounts for 80 percent of the system performance. (Then you can work on the remaining code to squeeze out the rest of the performance potential, if you have time and personnel to do so.) How do you actually do this? The sections following the next one discuss the Oracle tools that you will need. But first, the next section gives you the necessary background to use those toolsan understanding of the Oracle optimizer and how it works.
Page 751
An optimizer is a piece of software and part of an RDBMS that is responsible for optimizing, or formulating, the most efficient way for a given SQL statement to access the data. To do this, the optimizer chooses a sequence of access paths that provide the (hopefully) fastest way for Oracle to get to the data and then builds the execution plan based on those access paths. An access path is a physical way to the data. An execution plan is the sequence of Oracle executable steps that follows the chosen access paths.
The technology behind the Oracle optimizer, as with many other RDBMS vendors, has advanced considerably over the past few years. Ironically, the optimizer still may not always provide the best way (the optimal way) to access data. In older versions, the programmer had to be careful about how she coded her SQL statements. With the knowledge that the ordering within a SQL statement could drastically affect how it would perform, programmers had to do a lot of manual optimization. Likewise, the DBA had to be aware of this situation. The better the optimizer, the less optimization responsibility is on the users, programmers, and DBAs. Even with the current field of RDBMS optimizers, programmers still need to know optimization techniques and apply them when possible. However, programmers don't need to be quite as expert about the optimizer as before. DBAs should always remain aware of the optimizer technology, optimization rules, and proper optimizer usage.
Optimizers come in two flavors:
Oracle's 7.x and higher optimizers offer both rule-based and cost-based optimization capabilities within the same optimizer. Oracle's 6.x optimizer offers rule-based, but does not have a fully automatic cost-based optimization. All these versions offer a programmer an override known as a hint. It is not a true override, in the sense that the optimizer does not always follow the hint. Consider a hint a suggestion to the optimizer. If no syntax errors exist, the optimizer should generally follow it. A hint is placed inline, meaning directly in the SQL statement code. Listing 30.1 shows a SELECT statement that suggests to the optimizer to use the INDEX on the table from which it is selecting:
Listing 30.1An Example of a Query with a Hint
SQL> SELECT /*+ INDEX */ EMPLOYEE_ID 2> FROM EMPLOYEES 3> WHERE EMPLOYEE_ID = 503748;