Previous | Table of Contents | Next

Page 776

Let's sum up how to use utlbstat/utlestat properly and tune your database system:

  1. Set TIMED_STATISTICS=TRUE either at the instance or session level.
  2. Log in to svrmgrl (server manager line mode) and CONNECT / AS SYSDBA (or CONNECT INTERNAL).
  3. Run $ORACLE_HOME/rdbms/admin/utlbstat.sql at the beginning of your monitoring period, which should be some duration of normal, peak activity.
  4. Run $ORACLE_HOME/rdbms/admin/utlestat.sql at the end of your monitoring period.
  5. Interpret your results in report.txt by using reasonable guidelines (which we'll cover soon).
  6. Make recommended changes or fixes (if any); this could be anything from changing an init.ora parameter to reorganizing your physical design.
  7. Repeat the process beginning with step 2.

Reviewing the Report File

The file report.txt contains a large variety of information to help you tune the application, memory, and I/O. At the high level, it has statistics on the shared pool (the data dictionary and library caches), the database buffer cache, per transaction/login data, per tablespace/file I/O, and wait events. You can use all these statistics or some of them.

TIP
Make sure the individual statistics you use are relevant to your application. In the final analysis, your users are the true test of whether your application's performance is "good."

Some statistics offer different views on the performance of the same item and should agree. When they don't agree, interpretation becomes more subjective and application-specific. For example, suppose you have three statistics that report the performance of the library cache in different ways. Furthermore, what if two out of three show "good" performance according to accepted guidelines, but the remaining one shows "poor" performance? Does this mean your application's performance is good? It truly depends on what type of application you have and what each statistic means in relation to it. For example, if the two statistics showing good performance mean more to a batch system, and you have an OLTP system, those particular measures are misleading.

Tuning the Shared Pool

As you learned in Chapter 6, "The Oracle Database Architecture," the shared pool consists largely of two main structures:

The areas that store parsed SQL statements for later reuse are the shared SQL areas. The private SQL areas are those areas associated with cursor durations within applications.

Page 777

Tuning the shared pool is where tuning memory and tuning the application overlap considerably. This chapter augments Chapter 30, especially from the viewpoint of the DBA, or server, rather than the developer, or client. The shared pool is a cache structure. Like all cache structures, it is a memory-resident data structure.

A cache is a special type of buffer. Whereas a buffer is a "dumb" mechanism, simply providing temporary storage for data on its way between fast memory and slow disk, a cache is a "smart" mechanism, retaining memory as to whether it has that information, or part of it, so that it may avoid as many unnecessary trips to the disk as possible. When an I/O request is made, the cache checks to see whether it already has it in memory. If it does, it answers the request itself, returning the requested data. This is known as a hit. If it does not, a trip to the disk is warranted. This is known as a miss.

For almost all cache mechanisms, the guideline for effective performance is to have a 90 percent+ hit ratio, which may be defined as 1 _ (sum(misses) / sum(requests)), where sum(requests) = sum(misses) + sum(hits). For example, if your cache has 4 misses and 46 hits, your hit ratio is 100 _ (4/50) = 100 _ (.08), or 92 percent, which is very good. Caches are generally managed by a Least Recently Used (LRU) algorithm, which ensures that, at any given time, the Most Recently Used (MRU) data is held in cache, and the LRU data is aged out.

When Oracle parses a SQL statement, it allocates a SQL area in the library cache for it by applying a mathematical formula to the alphanumeric text of the SQL statement and using the result to store (and later find) it in the cache. In other words, it uses a hash function. As you might expect, in order for a statement to be reused by another, they must be identical. For example, the following are not identical in the eyes of Oracle when storing them in the library cache:

SELECT * FROM EMPLOYEES;

SELECT       * FROM EMPLOYEES;

SELECT * FROM employees;

Although to you and me, they are functionally identical, they are not hash-identical. In order for them to be hash-identical, there can be no whitespace (spaces, tabs, indents, nonprintable control characters) or differences in case. Furthermore, the following two statements cannot reuse the same hashed SQL area parse plan:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=927354;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=746293;

This is true because of the use of the literals 927354 and 746293 in the WHERE clause. Because the input of the alphanumeric statement in the hash function is different, they cannot possibly hash to the same library cache location. How to win? Well, except for DSS as discussed earlier, you should use bind variables. Bind variables enable SQL statements to be general enough to be reused and yet have parametric values rather than constants:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:EMPID;

This type of statement can be reused and is typically found in embedded SQL application code, where :EMPID is the bind variable—in this case, a host 3GL variable, such as a C integer.

Page 778

The value of the C variable can now take on 927354, 746293, and so forth, and yet be reused in the library cache.

Guidelines for Improving the Performance of the Library Cache

Minimize unnecessary parse calls from within applications. Parsing is CPU-intensive. Because caching and buffering are involved, it is also memory-intensive as a by-product. Cursor opening and closing should be carefully placed in the application to facilitate reuse of the private SQL areas for multiple SQL statements. The DBA may have to increase the init.ora parameter OPEN_CURSORS as necessary to allow for the sufficient allocation of cursor space (private SQL areas). To determine whether your application may be inefficient in this regard, run SQL TRACE/TKRPOF (see Chapters 29 and 30) and examine whether the count column for Parse is near the value for Execute (or Fetch). If so, the application is then reparsing for almost every execute (or fetch).

Maximize reuse of those statements that must be parsed. As mentioned, SQL statements must be identical to be reused. One way to help do this is to adopt a standard way of coding that all application developers must follow, such as "always code SQL statements in uppercase." You could go one step further and have a program pass through all your development code and enforce such a thing, in case developer compliance was poor. Further, except for DSS applications, use bind variables when appropriate. These almost always make sense because they generalize your application, as opposed to having literal values hard-coded. This pays for itself in maintainability, if not library cache reuse.

Pin frequently used program objects in memory. In Oracle, a cursor, trigger, procedure, or package may be held in memory using a special shared pool package, DBMS_SHARED_POOL. To create this package, run the $ORACLE_HOME/rdbms/admin/dbmspool.sql script. You may also need to run $ORACLE_HOME/rdbms/admin/prvtpool.sql. Check the version on your platform to see whether this is the case. To pin a program object in memory, use the following:

SQL> EXECUTE DBMS_SHARED_POOL.KEEP(`<object_name>');

To unpin it:

SQL> EXECUTE DBMS_SHARED_POOL.UNKEEP(`<object_name>');

To determine whether the object was successfully pinned:

SQL> SELECT SUBSTR(NAME,1,25), KEPT FROM V$DB_OBJECT_CACHE;

If the object was pinned, the KEPT column will have a YES value; otherwise, it will say NO.

Minimize fragmentation in the library cache. Your application will suffer ORA-04031 errors (not enough contiguous free space) unless you guard against fragmentation. One way is to pin frequently used large objects in memory. For less frequently used objects, which may be large, reserve some space. You can do this by setting the init.ora parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_MIN_ALLOC. You set aside a shared pool "reserved area" for your large objects. Essentially, you are guaranteeing that your necessary large objects will find space. Set SHARED_POOL_RESERVED_SIZE to what would be the

Previous | Table of Contents | Next