High-Performance Oracle Database Applications:Tuning The Oracle Architecture

Table of Contents

With Oracle version 7.2, several new parameters were added for the init.ora file to allocate a new in-memory sort area. The sort_write_buffer_size parameter defines the size of this new buffer, and the sort_write_buffers defines the number of buffer blocks. You must also set the parameter sort_direct_writes=true to use this feature. Writing sorts to this buffer bypasses the need for the sort to contend for free blocks in the buffer cache, thereby improving sorting performance by up to 50 percent. Of course, this is done at the expense of additional memory with the SGA. This move towards segmenting the buffer into individual components can dramatically improve response time in Oracle.

The Oracle PGA

Please note that the SGA is not the only memory area available to programs. The PGA is a private memory area that is allocated to external tasks. The PGA is used for keeping application-specific information, such as the values of cursors, and also allocates memory for internal sorting of result sets from SQL queries. Two init.ora parameters influence the size of the PGA:

• open_links—This parameter defines the maximum number for concurrent remote sessions that a process may initiate. Oracle’s default is 4, meaning that a single SQL statement may reference up to four remote databases within the query.

• sort_area_size—This defines the maximum amount of PGA memory that can be used for disk sorts. For very large sorts, Oracle will sort the data in its temporary tablespace, and the sort_area_size memory is used to manage the sorting process.

Tuning The shared_pool_size

The shared pool component of the Oracle SGA is primarily used to store shared SQL cursors, stored procedures, and the cache for data from the data dictionary cache. The library and dictionary cache are the two components of the shared pool. The shared SQL areas and the PL/SQL areas are called the library cache, which is a subcomponent of the shared pool. The other main component of the shared pool is the dictionary cache.

Tuning The Library Cache

The library cache miss ratio tells the DBA whether or not to add space to the shared pool, and represents the ratio of the sum of library cache reloads to the sum of pins. In general, if this ratio is over 1, you may want to consider adding to the shared_pool_size. Library cache misses occur during the compilation of SQL statements. The compilation of an SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse an SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement.

Within the library cache, the hit ratios can be determined for all dictionary objects that are loaded. These include table/procedures, triggers, indexes, package bodies, and clusters. If any of the hit ratios fall below 75 percent, you might be well advised to add to the shared_pool_size.

The table V$librarycache is the V$ table that keeps information about library cache activity. The table has three relevant columns. The first is the namespace, which says whether the measurement is for the SQL area, a table or procedure, a package body, or a trigger. The second value in this table is pins, which counts the number of times that an item in the library cache was executed. The reloads column counts the number of times that the parsed representation did not exist in the library cache, forcing Oracle to allocate the private SQL areas in order to parse and execute the statement.

Listing 3.2 shows an example of a SQL*Plus query to interrogate the V$librarycache table to retrieve the necessary performance information.

Listing 3.2 An example of an SQL*Plus query.

library.sql - lists the library cache
PROMPT
PROMPT         =========================
PROMPT         LIBRARY CACHE MISS RATIO
PROMPT         =========================
PROMPT (If > 1 then increase the shared_pool_size in init.ora)
PROMPT
COLUMN "LIBRARY CACHE MISS RATIO"        FORMAT 99.9999
COLUMN "executions"                      FORMAT 999,999,999
COLUMN "Cache misses while executing"    FORMAT 999,999,999
SELECT sum(pins) "executions", sum(reloads)
       "Cache misses while executing",
       (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
FROM v$librarycache;

PROMPT
PROMPT         =========================
PROMPT          LIBRARY CACHE SECTION
PROMPT         =========================
PROMPT hit ratio should be > 70, and pin ratio > 70 ...
PROMPT

COLUMN "reloads" FORMAT 999,999,999
SELECT namespace, trunc(gethitratio * 100) "Hit ratio",
TRUNC(pinhitratio * 100) "pin hit ratio", RELOADS "reloads"
FROM v$librarycache;

Listing 3.3 shows the output.

Listing 3.3 The output of the query.

SQL> @temp

=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)

  executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ---------------------------- ------------------------
     251,272                        2,409                    .0096

=========================
LIBRARY CACHE SECTION
=========================
hit ratio should be > 70, and pin ratio > 70 ...

NAMESPACE              Hit ratio   pin hit ratio     reloads
--------------         ----------  ------------   ------------
SQL AREA                   90            94            1,083
TABLE/PROCEDURE            93            94            1,316
BODY                       96            95                9
TRIGGER                    89            86                1
INDEX                       0            31                0
CLUSTER                    44            33                0
OBJECT                    100           100                0
PIPE                      100           100                0

8 rows selected.

One of the most important things that a developer can do to reduce usage of the library cache is to ensure that all SQL is written within stored procedures. For example, Oracle library cache will examine the following SQL statements and conclude that they are not identical:

SELECT * FROM customer;

SELECT * FROM Customer;

Table of Contents