Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






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.

In case you missed them
If you haven't read the first three articles in this series, you can still catch up. The first installment, "Oracle tuning: Start by looking at the big picture," provides an overview of Oracle tuning. The second, "Oracle tuning: Monitoring hardware with UNIX tools," looks at tuning the database server, and the third, "Oracle tuning: Examining the Oracle instance," begins the discussion on tuning the Oracle instance itself.

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):
   customer_name = 'BURLESON';

With host variables in the SQL (reusable):
   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:

  1. 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.
  2. 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.


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational