 
Oracle
tuning:
Boost performance by tweaking global instance parameters
Mar 21, 2001
Donald Burleson
© 2001 TechRepublic, Inc.
|
|
This week, we continue our study of Oracle
tuning by looking at more global parameters in tuning the Oracle instance: the
Oracle shared pool, the library cache, and Oracle sorting.
Shared pool tuning
An important area of Oracle instance tuning is the Oracle shared pool. The
Oracle shared pool contains Oracle's library cache, which is responsible for
collecting, parsing, interpreting, and executing all of the SQL statements that
go against the Oracle database. Hence, the shared pool is a key component, so
it's necessary for the Oracle database administrator to check for shared pool
contention.
When the Oracle shared pool is stressed, Oracle reports can quickly tell you if
shared pool thrashing is occurring. One of the most common causes of Oracle
shared pool problems occurs when an application does not utilize reusable SQL.
Oracle's parsing algorithm ensures that identical SQL statements do not have to
be parsed each time they're executed. However, many Oracle admins fail to insert
host variables into SQL statements and instead ship the SQL statements with the
liberal host values. The following example illustrates this point.
With literals in the SQL (not reusable):
Select
customer_details
from
customer
where
customer_name = 'BURLESON';
With host variables in the SQL (reusable):
Select
customer_details
from
customer
where
customer_name = :var1;
As we can see, the addition of the host variable makes the SQL statement
reusable and reduces the time spent in the library cache. This improves the
overall throughput and performance of the SQL statement. In severe cases of
nonreusable SQL, many Oracle DBAs will issue the Alter Database Flush Shared
Pool command periodically in order to remove all of the nonreusable SQL and
improve the performance of SQL statements within the library cache.
Library cache usage measurement
The library cache consists of the shared SQL areas and the PL/SQL areas, which
are, in turn, a subcomponent of the shared pool. The library cache miss ratio
tells the DBA whether or not to add space to the shared pool, and it represents
the ratio of the sum of library cache reloads to the sum of pins. In general, if
the library cache ratio is over 1, you should consider adding to the
shared_pool_size. Library cache misses occur during the compilation of SQL
statements.
The compilation of a SQL statement consists of two phases:
- The parse phase—When the time comes to parse a 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.
- The execution phase—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. During the
execution phase, the query plan is run and the data is retrieved from
Oracle.
Within the library cache, 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 should add to the
shared_pool_size.
The table V$LIBRARYCACHE is the internal Oracle psuedo-table that keeps
information about library cache activity. The table has three relevant columns:
namespace, pins, and reloads. The namespace column indicates whether the
measurement is for the SQL area, a table or procedure, a package body, or a
trigger. The pins column counts the number of times an item in the library cache
is executed. The reloads column counts the number of times 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.
Figure A shows an example of a SQL*Plus query to interrogate the
V$LIBRARYCACHE table and retrieve the necessary performance information.
| Figure A |
 |
| A script to display library cache information |
When we run this script, we see all of the salient areas within the library
cache, as shown in Figure B.
| Figure B |
 |
| Output from the library cache query script |
Again, we must always check to see if any component of the shared pool needs to
be increased. Next, let's take a look at how instance-wide sort operations
affect the performance of the Oracle database.
Tuning Oracle sorting
A small but very important component of Oracle tuning, sorting is often
overlooked. An Oracle database will automatically perform sorting operations on
row data under the following circumstances:
- When an index is created
- When using the ORDER BY clause in SQL
- When using the GROUP BY clause in SQL
At the time a session is established with Oracle, a private sort area is
allocated in memory for use by the session for sorting, based on the value of
the sort_area_size initialization parameter. Unfortunately, the amount of memory
must be the same for all sessions, and it's not possible to add additional sort
areas for tasks that are sort intensive. Therefore, the DBA must strike a
balance between allocating enough sort area to avoid disk sorts for the large
sorting tasks, keeping in mind that the extra sort area will be allocated and
not used by tasks that do not require intensive sorting.
Whenever a sort can't be completed within the assigned space, a disk sort is
invoked using the temporary tablespace for the Oracle instance. A sort in the
temporary tablespace is very I/O intensive and can slow down the entire
database.
As a general rule, the sort_area_size should be large enough that only index
creation and ORDER BY clauses using functions should be allowed to use a disk
sort. However, operations on large tables will always perform disk sorts. For
example, the following query will sort the salaries for all 100,000 employees at
Oracle Corporation:
select salary
from employee
order by salary;
Oracle always tries to sort in the RAM space within sort_area_size and only goes
to a disk sort when the RAM memory is exhausted.
Disk sorts are expensive for several reasons. First, they consume resources in
the temporary tablespaces. Oracle must also allocate buffer pool blocks to hold
the blocks in the temporary tablespace. In-memory sorts are always preferable to
disk sorts, and disk sorts will surely slow down an individual task, as well as
affect concurrent tasks on the Oracle instance.
Also, excessive disk sorting will cause a high value for free buffer waits,
paging other tasks' data blocks out of the buffer. You can see the amount of
disk and in-memory sorts by issuing the query shown in Figure C against
the V$SYSSTAT table.
| Figure C |
 |
| A script to show Oracle sorting activity |
Here, you can see that there were 49 sorts to disk. Out of a total of 7,019
sorts, this is well below 1 percent and is probably acceptable for the
system.
The sort information can be captured in STATSPACK tables and plotted to
determine the times when disk sorts are experienced by the instance, as shown in
Figure D.
| Figure D |
 |
| Graph of total sorts |
Figure E shows the STATSPACK script that will produce the numbers for the
above graph.
| Figure E |
 |
| A STATSPACK script to display sorting information by
day of the week |
These plots can give the DBA insight into when their database may be
experiencing slowdowns related to disk sorts in their TEMP tablespace. At the
risk of being redundant, we need to reemphasize that the single most important
factor in the performance of an Oracle database is the minimization of disk I/O.
What's next?
Once we've tuned all of the Oracle global instance parameters, we can drill down
and begin to look at the parameters for individual tables and indexes within the
database. We'll turn our attention to those issues in our final installment.
|
