Oracle Essentials Oracle Database 11g (36 page)

|

Chapter 7: Oracle Performance

retries” can be used to understand this situation. It is available through V$SYSSTAT

and is an indication of how often a session waited for space in the redo log buffer. An example of the query you may use to obtain the statistic is: SELECT name, value FROM V$SYSSTAT

WHERE name = 'redo buffer allocation retries';

Youwould monitor these statistics over a period of time to gain insight into the trend. The values at one point in time reflect the cumulative totals since the instance was started and aren’t necessarily meaningful as a single data point. Note that this is true for all statistics used for performance tuning. Ideally, the value of “redo buffer allocation retries” should be close to 0. If you observe the value rising during the monitoring period, you would increase the size of the redo log buffer by resetting the LOG_BUFFER initialization parameter.

Query results caching

One of the most significant performance features in Oracle Database 11
g
can be used to help improve the performance of repeated queries. Oracle caches database and index blocks, eliminating the need to perform resource-intensive disk reads. Oracle caches SQL plans, eliminating the need to reparse and optimize queries. But prior to Oracle Database 11
g
, a cached SQL plan would still have to execute and assemble a result set.

The new feature allows Oracle Database 11
g
to cache the completed result set in the shared pool. This new functionality means that a repeated query requesting the same result set can simply take that result set completely from memory. Since the result sets have to be the same for this feature to work, the query results cache has the biggest impact on situations like web page serving, where the same page is being retrieved repeatedly. This feature also works on the results of PL/SQL functions.

Oracle Database 11
g
also includes the ability to cache query result sets on the client, while automatically keeping the result set consistent with any changes that could affect it. This feature gives the performance benefits of query result set caching on the server while eliminating network roundtrips as an added benefit.

How Oracle Uses the Program Global Area

Each server has a Program Global Area (PGA), which is a private memory area that contains information about the work the server process is performing. There is one PGA for each server process. The total amount of memory used for all the PGAs is a function of the number of server processes active as part of the Oracle instance. The larger the number of users, the higher the number of server processes and the larger the amount of memory used for their associated PGAs. Using the Multi-Threaded Server (known as the shared server from Oracle9
i
on) reduces total memory consumption for PGAs because it reduces the number of server processes.

Oracle and Memory Resources

|

179

The PGA consists of a working memory area for things such as temporary variables used by the server process, memory for information about the SQL the server process is executing, and memory for sorting rows as part of SQL execution. The initial size of the PGA’s working memory area for variables, known as
stack space
, cannot be directly controlled because it’s predetermined based on the operating system you are using for your database server. The other areas within the PGA can be controlled as described in the following sections.

Memory for SQL statements

When a server process executes a SQL statement for a user, the server process tracks the session-specific details about the SQL statement and the progress by executing it in a piece of memory in the PGA called a
private SQL area
, also known as a
cursor
.

This area should not be confused with the shared SQL area within the shared pool.

The shared SQL area contains shareable details for the SQL statement, such as the

optimization plan. Optimizers and optimization plans are discussed in Chapter 4.

The private SQL area contains the session-specific information about the execution of the SQL statement within the session, such as the number of rows retrieved so far.

Once a SQL statement has been processed, its private SQL area can be reused by another SQL statement. If the application reissues the SQL statement whose private SQL area was reused, the private SQL area will have to be reinitialized.

Each time a new SQL statement is received, its shared SQL area must be located (or, if not located, loaded) in the shared pool. Similarly, the SQL statement’s private SQL

area must be located in the PGA or, if it isn’t located, reinitialized by the server process. This reinitialization is relatively expensive in terms of CPU resources.

A server process with a PGA that can contain a higher number of distinct private SQL areas will spend less time reinitializing private SQL areas for incoming SQL

statements. If the server process doesn’t have to reuse an existing private SQL area to accommodate a new statement, the private SQL area for the original statement can be kept intact. Although similar to a larger shared pool, a larger PGA avoids memory churn within the private SQL areas. Reduced private SQL area reuse, in turn, reduces the associated CPU consumption, increasing performance. There is, of course, a trade-off between allocating memory in the PGA for SQL and overall performance.

OLTP systems typically have a “working set” of SQL statements that each user submits. For example, a user who enters car rental reservations uses the same forms in the application repeatedly. Performance will be improved if the user’s server process has enough memory in the PGA to cache the SQL those forms issue. Application developers should also take care to write their SQL statements so that they can be easily reused, by specifying bind variables instead of different hardcoded values in their SQL statements.

180

|

Chapter 7: Oracle Performance

Memory for sorting within the PGA

Each server process uses memory in its PGA for sorting rows before returning them to the user. If the memory allocated for sorting is insufficient to hold all the rows that need to be sorted, the server process sorts the rows in multiple passes called
runs
.

The intermediate runs are written to the temporary tablespace of the user, which reduces sort performance because it involves disk I/O.

Sizing the sort area of the PGA was a critical tuning point in Oracle database releases prior to Oracle Database 10
g
. A sort area that was too small for the typical amount of data requiring sorting would result in temporary tablespace disk I/O and reduced performance. A sort area that was significantly larger than necessary would waste memory.

As of Oracle Database 10
g,
the database provides automatic sizing for the PGA. By default, this memory management is enabled, and sizing for PGA work areas is based on 20 percent of the SGA memory size. By using automatic sizing for the PGA, you eliminate the need to size individual portions of the PGA, such as SORT_AREA_

SIZE.

Oracle Database 11
g
introduced automatic memory management that spans both the SGA and the PGA. By setting a single MEMORY_TARGET initialization parameter (given that the PGA size can be based on a percentage of the SGA memory size), the PGA and SGA will be automatically set to appropriate initial values. Oracle then tunes memory for optimal SGA and PGA performance on an ongoing basis.

TimesTen

In 2005, Oracle acquired the leading in-memory database TimesTen.
In-memory
databases
provide optimal performance by reducing data retrieval latency. The optimizer used with the TimesTen database is aware of the memory location of the data, and creates execution plans to take advantage of this residency. In addition, the actual number of machine instructions is significantly reduced, since there is no need for code to handle situations where data resides only on disk. TimesTen is most appropriate for a high-load OLTP environment that requires extremely high throughput and real-time responsiveness.

A TimesTen instance can be used as a cache for an Oracle database. You load a subset of Oracle tables into the TimesTen instance, and the Cache Connect to Oracle feature keeps the data synchronized.

Youcan also enable replication between TimesTen instances on different machines for load sharing and higher availability. For more information on TimesTen, please refer to the relevant pages on the Oracle Technology Network.

Oracle and Memory Resources

|

181

Oracle and CPU Resources

The Oracle database shares the CPU(s) with all other software running on the server.

If there is a shortage of CPU power, reducing Oracle or non-Oracle CPU consumption will improve the performance of all processes running on the server.

If all the CPUs in a machine are busy, the processes line up and wait for a turn to use the CPU. This is called a
run queue
because processes are waiting to run on a CPU.

The busier the CPUs get, the longer processes can spend in this queue. A process in the queue isn’t doing any work, so as the run queue gets longer, response times degrade.

You can use the standard monitoring tools for your particular operating system to check the CPU utilization for that machine.

Tuning CPU usage is essentially an exercise in tuning individual tasks: it reduces the number of commands required to accomplish the tasks and/or reduces the overall number of tasks to be performed. You can do this tuning through workload balancing, SQL tuning, or improved application design. This type of tuning requires insight into what these tasks are and how they’re being executed.

As mentioned earlier, an in-depth discussion of all the various tuning points for an Oracle database is beyond the scope of this book. However, there is a set of common tasks that typically result in excess CPU consumption. Some of the usual suspects to examine if you encounter a CPU resource shortage on your database server include the following:

Bad SQL

Poorly written SQL is the number one cause of performance problems. An Oracle database attempts to optimally execute the SQL it receives from clients. If the SQL contained in the client applications and sent to the database is written so that the best optimization plan Oracle can identify is still inefficient, Oracle will consume more resources than necessary to execute the SQL. Tuning SQL can be a complex and time-consuming process because it requires an in-depth understanding of how Oracle works and what the application is trying to do. Initial examinations can reveal flaws in the underlying database design, leading to changes in table structures, additional indexes, and so on. Changing the SQL

requires retesting and a subsequent redeployment of the application—until Oracle Database 10
g
.

Oracle Database 10
g
introduced the SQL Tuning Advisor, a tool that can not only recognize poorly written SQL, but also create an optimizer plan to circumvent the problem and replace the standard optimization plan with the improved plan. With this capability, youcan improve the performance of poorly written
182

|

Chapter 7: Oracle Performance

SQL without changing any code in the application. The SQL Advisor in Oracle Database 11
g
combines the functionality of the SQL Tuning Advisor, the SQL

Access Advisor, and the Partition Advisor.

In Oracle Database 11
g
, the database can automatically spot the SQL queries with the largest loads and automatically create SQL profiles to improve their performance, if appropriate. This process can also result in advice on new indexes that could improve the performance of these statements.

Oracle Database 11
g
also tracks changes in execution plans for SQL statements, described in
Chapter 4.
The optimizer can maintain the history of execution plans, and when a new plan is detected, the optimizer uses the old plan and evaluates the performance of the new plan. Once the optimizer verifies that the new plan can deliver the same performance, the old plan is replaced. This feature does not directly relate to bad SQL, but rather to the occasional effects of plan changes, which can result in unplanned performance degradation.

Excessive parsing

As we discussed in the section
“Memory for SQL statements,”
Oracle must parse every SQL statement before it’s processed. Parsing is very CPU-intensive, involving a lot of data dictionary lookups to check that all the tables and columns referenced are valid. Complex algorithms and calculations estimate the costs of the various optimizer plans possible for the statement to select the optimal plan.

If your application isn’t using bind variables (discussed in
Chapter 9),
the database will have to parse every statement it receives. This excessive and unnecessary parsing is one of the leading causes of performance degradation. Another common cause is a shared pool that’s too small, as discussed previously in the section
“The shared pool.”
Keep in mind that youcan avoid the creation of execution plans by using stored outlines, as described in
Chapter 4.
And, as of Oracle9
i
, you also have the ability to edit the hints that make up a stored outline. As described earlier, Oracle Database 11
g
includes the ability to cache complete result sets, which can minimize the impact of repeated execution of identical queries.