****************
SELECT :name FROM EMP WHERE EMP_NO = 135;
SELECT :name FROM EMP WHERE EMP_NO = 137;
can be replaced by the following single statement:
SELECT :name FROM EMP WHERE EMP_NO = :variable;
If several users execute the same program, substantial reuse is almost certain to occur, especially if the library cache is large enough.
****************
Use the utlbstat and utlestat SQL scripts to capture snapshots of database performance statistics. It is best to gather this data after an application is up and running.
The utlestat SQL script reports useful information for performance monitoring. This information is obtained from the V$SYSSTAT, V$LATCH, and V$ROLLSTAT tables.
****************
The Oracle buffer manager ensures that the more frequently accessed data is cached longer. Monitoring the buffer manager and tuning the buffer cache can have a significant influence on Oracle performance. The optimal Oracle buffer size for your system depends on the overall system load and the relative priority of Oracle over other applications.
If your system is swapping and you need to conserve memory:
Start with swap space two to four times your system's Random Access Memory (RAM). Use a higher value if you plan to use CASE, Oracle Applications, or Oracle Office. Monitor the use of swap space and increase it as necessary.
On Sun, IBM AIX, and DG/UX systems, significant paging activity is normal. To detect excessive paging on these systems, run measurements during periods of fast response or idle time to compare against measurements from periods of slow response.
Use vmstat -S on BSD UNIX and sar -p on System V to monitor paging. The following columns from sar -p output are important:
Although this performance gain is minor, you cannot start the database without configuring sufficient shared memory.
You may need to reconfigure the UNIX kernel to increase shared memory. The UNIX kernel parameters for shared memory include SHMMAX, SHMMNI, and SHMSEG. A single shared memory segment should be large enough to hold the SGA.
To check the components of the SGA and their corresponding sizes, use the Server Manager Instance/Database selection or estimate SGA size using the following steps:
$ tstshm Number of segments gotten by shmget() = 50 Number of segments attached by shmat() = 50 Segments attach at higher addresses Maximum size segments are not attached contiguously! Segment separation = 2101248 bytes Default shared memory address = 0x18342000 Lowest shared memory address = 0x18342000 Highest shared memory address = 0x7fe7e000 Total shared memory range = 1741930496 Total shared memory attached = 104857600 Largest single segment size = 2097152 Segment boundaries (SHMLBA) = 4096 (0x1000)
You can also use the UNIX utility ipcs to monitor the status of shared memory.
See Also: Chapter 3, "Setting the Environment", in the Oracle7 Installation Guide for your platform for more information on reconfiguring the UNIX kernel.
The primary function of the SGA is to cache database information. If the SGA begins paging to disk, caching becomes overhead rather than a benefit. Some platform vendors provide techniques to lock the SGA into physical memory.
Although locking the SGA into physical memory can improve Oracle performance, it may reduce the performance of other applications on the same system.
See Also: Chapter 1, "Oracle7 Server", in the Oracle7 Reference Addendum for your platform for information on locking the SGA into memory.
A UNIX system reads entire operating system blocks from the disk. If the database block size is smaller than the UNIX file system buffer size, I/O bandwidth is inefficient.
Your database block size should be equal to, or a multiple of, your operating system block size. The initsid.ora parameter DB_BLOCK_SIZE sets the database block size. You can change the block size by recreating the database.
To find the default value of the DB_BLOCK_SIZE parameter, see the Oracle7 Reference Addendum for your platform or query the V$PARAMETER data dictionary table.
The DB_BLOCK_SIZE parameter also determines the size of the database buffers in the SGA. The DB_BLOCK_BUFFERS parameter is the memory parameter with the most direct effect on system performance.
Use the System Statistics Monitor in Server Manager to check the hit ratio. The hit ratio for the buffer cache is defined as:
Hit Ratio = Logical Reads - Physical Reads Logical Reads
where:
Logical Reads = db block gets + consistent gets
If your hit ratio is less than 60 or 70 percent, increase the number of buffers in the cache by raising DB_BLOCK_BUFFERS.
See Also: Chapter 8, "Tuning Memory Allocation", in Oracle7 Server Tuning for information on how to use the X$KCBRBH table as an alternate method for estimating the number of buffers based on statistics gathered from a running system. The System Statistics Monitor is described in the Oracle Server Manager User's Guide.
The value in the Total column for redo log space requests should be near zero, or at least not increasing. A non-zero value indicates that processes are waiting for space in the buffer. In this case, consider increasing the size of the redo log buffer in increments of 5 percent.
The size of the redo log buffer is determined by the initsid.ora LOG_BUFFER parameter. The value of this parameter is expressed in bytes.
Use the V$SGASTAT table to monitor the shared pool, checking the free space in particular. The following is a sample V$SGASTAT query:
SELECT * FROM v$sgastat ORDER BY bytes desc
NAME BYTES -------------------------- ---------- sql area 1370876 free memory 867036 library cache 785224 db_block_buffers 409600 dictionary cache 275740 ...
The shared pool is often set too large. If the free memory area is as large as the example above, reduce the size of the shared pool. Execute repeated queries to see if any of the values are increasing.
In the Server Manager Statistic display, the Total column shows the number of recursive calls since you started up the database. If the Oracle7 Server does not continue making recursive calls after startup, your data dictionary cache is probably large enough for your dictionary data. If the number of recursive calls accumulates while your application is running, you may need to increase the size of the data dictionary cache.
Note: If your dictionary cache seems too small, query the V$ROWCACHE table to check cache activity.
The library cache contains shared SQL and PL/SQL areas. Even if SQL can be reused, it will not be if the library cache is too small. Determine if library cache misses are affecting performance by querying the V$LIBRARYCACHE table.
Monitor the statistics in the V$LIBRARYCACHE over a period of time with the following query:
SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM V$LIBRARYCACHE;
The query returns output similar to the following:
Executions Cache Misses while Executing
---------- ----------------------------
320871 549The sum of the PINS (first column) indicates that SQL statements, PL/SQL blocks, and object definitions were accessed for execution a total of 320,871 times. The sum of the RELOADS (last column) indicates that 549 of those executions resulted in library cache misses. Total reloads should be near zero, and the ratio should be below 1 percent.
If the ratio of RELOADS to PINS is greater than 1 percent, allocate additional memory to the library cache by increasing the initsid.ora parameter SHARED_POOL_SIZE.
The following procedure:
@dbmspool @prvtpool set serveroutput on size xxx begin sys.dbms_shared_pool.sizes (minsize); end; /
A setting of 20000 is sufficient for xxx, and 20 for minsize. After you identify the frequently used shared objects, you can run the procedure keeping VARCHAR2, flag CHAR DEFAULT `P') as often as necessary to lock objects into the shared pool; dbms_shared_pool.sizes gives a list of objects in the shared pool larger than minsize.
For example, keep (obj_name, "P") pins obj_name in the shared pool.
The unkeep procedure unlocks objects.
Suggestion: Build a SQL*Plus script to do this as part of database startup.
Set the SESSION_CACHED_CURSORS initialization parameter to the maximum number of session cursors you want to cache. Monitor cache performance and adjust the cache size based on the hit ratio. For example, the following SQL statement retrieves data for tuning caching session cursors:
> SELECT value, name
FROM V$sysstat WHERE statistic# IN (122, 123)
VALUE NAME
---------- ----------------------------
12675 session cursor cache hits
12766 session cursor cache count
2 rows selected.
****************
To move the indexes owned by user u from tablespace OLD into tablespace NEW, perform the following steps:
If your Oracle applications involve heavy INSERT and UPDATE activity, you can maximize Oracle performance by locating your redo logs on disks that support no other disk activity. Also, if you have enabled the ARCHIVELOG option, place each redo log on a separate disk to minimize disk contention between the LGWR process (writing to the current redo log) and the ARCH process (reading from the closed redo log).
Place redo logs on raw devices to further enhance performance. Redo logs should be among the first files to be put on raw devices for the following reasons:
![[*]](jump.gif)
for more information on raw devices.
You can use a Logical Volume Manager (LVM) to stripe data across multiple disk drives.
See Also: "Using Logical Volumes" .
See Also: The Oracle7 Reference Addendum for your platform for platform-specific information about logical volumes.
While an LVM is preferable, Oracle7 allows data files to be striped without an LVM. This is done with the DATAFILE keyword of the CREATE TABLE command. Performance is usually better with an LVM, which encourages a smaller stripe size and tends to distribute I/O randomly and automatically.
Oracle offers three solutions to prevent database writer (DBWR) activity from becoming a bottleneck:
select * from v$system_event; EVENT TOTAL_WAIT ----------------- ---------- ... free buffer waits 463 ...
If the TOTAL_WAIT value is significant, consider applying one of the three procedures in the following sections.
Asynchronous I/O is available on many UNIX platforms, but may require the use of raw disk devices or special kernel configurations. Enable asynchronous I/O by setting the ASYNC_WRITE parameter (or the USE_ASYNC_IO parameter on some platforms) to TRUE in the initsid.ora file. If your database files are not on raw devices, use multiple DBWRs, rather than converting to raw devices, to achieve the benefits of asynchronous I/O.
See Also: Chapter 1, "Oracle7 Server", in the Oracle7 Reference Addendum for your platform for information on the availability of asynchronous and list I/O on your platform.
For some write-intensive operations, such as parallel index building, a lack of DBWR bandwidth can be an overwhelming bottleneck.
If there is only one hot file on a disk device and the file is responsible for the large request queue, moving it to another disk will not help.
If the Oracle file or tablespace in question contains data from multiple segments (such as tables and indexes), move the heavily accessed segments to separate tablespaces and to separate files.
A physical device for a database segment can be specified only at the tablespace level. If only one segment is involved, consider table striping to place the segment data into multiple files in a single tablespace.
Tablespace fragmentation prevents Oracle from taking advantage of its multi-block read capability. A fragmented tablespace file also wastes database space if the segment extents are larger than the contiguous free extents.
Tablespace fragmentation can be identified with the following SQL statement:
SELECT * FROM DBA_EXTENTS;
Free space can be queried with the following SQL statement:
SELECT * FROM DBA_FREE_SPACE;
Free space fragmentation takes two forms:
See Also: "Avoiding a Database Reorganization" by Craig A. Shallahamer. The paper is available on the World Wide Web at http://www.europa.com/~orapub.
If your system is I/O bound, increase the number of database buffers to cache more data and reduce I/O. Continue increasing the number of buffers (and the hit ratio) as long as it does not increase paging.
Using raw partitions instead of a file system can improve performance because the database writer bypasses the UNIX buffer cache and eliminates the file system overhead. This results in fewer instructions per I/O.
If you are using raw partitions, minimize the UNIX buffer cache size. The UNIX kernel parameter for this is BUFPCT or NBUF.
Note: This is a global operation and may adversely affect other applications on the same system.
See Also: "Decide on Using Raw Devices" for more information on raw devices.
****************
For example, if the log writer process (LGWR) gets a low priority, it is not executed frequently enough and LGWR becomes a bottleneck. On the other hand, if LGWR has a high priority, user processes may suffer poor response time.
In a multi-processor environment, use processor affinity/binding if it is available on your system. Processor binding prevents a process from migrating from one CPU to another, allowing the information in the CPU cache to be better utilized. You can bind a server shadow process to make use of the cache since it is always active, and let background processes flow between CPUs. Some platforms employ process binding automatically.
See Also: Chapter 1, "Oracle7 Server", in the Oracle7 Reference Addendum for your platform for information on the availability of processor affinity/binding.
Oracle processes usually use semaphores to coordinate access to shared resources. If a shared resource is locked, a process suspends and waits for the resource to become available.
One way to improve shared resource coordination is to use a post-wait driver instead of semaphores, if it is available on your system. A post-wait driver is a faster, less expensive synchronization mechanism than a semaphore.
See Also: Chapter 1, "Oracle7 Server" in the Oracle7 Reference Addendum for your platform for information on the availability of the post-wait driver on your platform.
If you need to transfer large amounts of data between the user and Oracle7 (for example, export/import), it is efficient to use single-task architecture. To make the single-task import (impst) and export (expst) executables, use the oracle.mk program, which can be found in the $ORACLE_HOME/rdbms/lib directory.
Attention: Linking Oracle executables as a single-task allows a user process to directly accesses the entire SGA. In addition, running single-task requires more memory because the oracle executable text is no longer shared between the front-end and background processes.
****************
See Also: The "Monitor Reference" section of Oracle7 Server Utilities for a full description of V$ tables.
The statistics in V$SYSTEM_EVENT indicate how oracle is using its time, and allows you to identify potential problems. Query the table with the following SQL statement:
SELECT * FROM V$SYSTEM_EVENT ORDER BY TIME_WAITED;
A well-tuned database experiences waits, and the presence or absence of an event in this table does not necessarily indicate a problem. It is normal to see events such as client message, pmon timer, smon timer, rdbms ipc message, and rdbms ipc reply. The number of rows in this table changes dynamically. If there is no information to report on an event, the event will not appear in the table.
V$SYSTEM_EVENT is a cumulative table; it is also useful to look at a table measuring events as they occur. Use the V$SESSION_WAIT table by entering:
SELECT sid, event, p1text, p1, p2text, p2 FROM V$SESSION_WAIT;
This query provides a snapshot of the sequence of events. Observing how an event frequency changes with the load on the database provides insight into both the Oracle operation and the nature of the SQL statement being executed.
The sample output from querying V$SESSION_WAIT is:
SID EVENT P1TEXT P1 P2TEXT P2 1 pmon timer 0 0 2 buffer busy waits file# 7 block# 792 10 latch free address 8.05E08 number 8 ...
SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id=7 AND (792 between block_id and block_id+blocks);
The sample output is:
SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS --------------- --------------- -------- -------- C0T1 TABLE 752 50 1 row selected.
The output indicates the contention is occurring in a table segment, rather than an index, cluster, or rollback segment. Because you have the file number and block number, you can obtain additional information from the X$BH table by entering:
SELECT class FROM X$BH WHERE dbafile=7 AND dbablk=792;
This query provides the class of the block, which can be interpreted using the following table:
| Class | Block Type |
| 0 | System rollback segment |
| 1 | Data block |
| 2 | Sort block |
| 3 | Save Undo block |
| 4 | Segment header block |
| 5 | Save Undo segment header block |
| 6 | Free List block |
| 7 + (n*2) | Undo segment header block |
| 7 + ((n*2) + 1) | Undo segment block |
SELECT latch#, name FROM V$LATCH WHERE latch#=8;
Sample output is:
LATCH# NAME
-------- -------------------------
8 cache buffers chains
1 row selected.The cache buffer chains latch often experiences contention, as do the cache buffer lru chain latch and the cache buffer handles latch. These latches typically indicate that raising the number of SGA buffers is necessary.
Use the following SQL statement to determine how often requests for space in a rollback segment cause delays. The hit rate should be more than 95 percent.
SELECT name, gets, waits, ((gets-waits)*100)/gets hits
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn;Rollback segments may be a bottleneck if:
The simple solution to rollback contention is to add more rollback segments.
If the ratio of misses to gets for a particular latch exceeds 10 percent, contention for that latch might affect performance. Each Sleep indicates a delay for the process requesting the latch.
Note: Systems with multiple CPUs may be able to tolerate more contention without performance reduction.
You can reduce contention for the redo allocation latch. Minimize the time that any single process holds the latch by decreasing the value of the initsid.ora LOG_SMALL_ENTRY_MAX_SIZE parameter.
To reduce contention for redo copy latches in multi-processor environments:
SELECT *
FROM V$PQ_SYSSTAT
WHERE statistic = "Servers Busy":STATISTIC
The sample output is:
VALUE --------------------- ----------- Servers Busy 70
If the value of servers busy reaches the value set for PARALLEL_MAX_SERVERS, some parallel queries are probably being processed sequentially.
Run sar -u along with the previous query to observe CPU loading. Observe these measurements over a significant period of time. The following table summarizes tuning actions based on the ratio of servers busy to PARALLEL_MAX_SERVERS compared to CPU utilization.
| Busy/Max Servers | CPU use heavy (95 - 100%) | CPU use OK (60 - 80%) | CPU use light (0 - 30%) |
| 1.0 often | Aggressively decrease parallelism in tables and queries; tune system | Decrease parallelism in tables and queries | Increase MAX servers; tune system |
| 1.0 rarely | Identify queries when maximized; tune system | Increase MAX Servers - watch; decrease parallelism in tables and queries | Increase MAX servers |
| .3 - .7 | If Query Servers using >40% CPU, decrease MAX servers; tune system | Tuned | Increase parallelism in tables and queries; increase MAX servers |
| 0 - .2 | Tune system; consider adding processors | Consider lowering MAX servers | Increase parallelism in tables and queries |
A process continues to request a latch until it obtains one. If the number of requests reaches $SPIN_COUNT, the process fails to acquire the latch, sleeps, then tries to acquire the latch again. Because a latch is a low-level lock, a process does not hold it long. It is less expensive to use CPU time by spinning a process than it is to make a process sleep.
You can check the contention level of the latch by monitoring the miss rate and sleep rate from the utlbstat and utlestat scripts. Try reducing the sleep rate by tuning the spin count. If the contention level is high, increase the spin count to allow processes to spin more before acquiring latches. However, since increasing the spin count increases CPU usage, system throughput may decline at some point.
Traditionally, kernel parameters such as NBUF, NFILE, and NOFILES were used to adjust kernel size. However, most UNIX implementations dynamically adjust those parameters at run time, even though they are present in the UNIX configuration file.
Look for memory mapped video drivers, networking drivers, and disk drivers. They can often be de-installed, yielding more memory for use by other processes.
Warning: Remember to make a backup copy of your UNIX kernel. See your hardware vendor documentation for additional details.
****************
Database concurrence in an Oracle Parallel Server (OPS) system is maintained across the processors using a DLM. Managing resources using the DLM is less efficient than using the shared memory model within a single database instance.
Note: This can also be a problem for Oracle7 running on a Symmetric Multi-Processor (SMP), but is more likely to become a bottleneck on Oracle Parallel Server.
Lock Conversion Ratio = Consistent Gets - Async Lock Converts Consistent Gets
The lock conversion ratio should be 95 percent or higher for the application to scale well. If there is excessive lock contention, the application must be re-evaluated and possibly re-designed for OPS.
Although the application being executed by oracle is the most common source of lock contention, sometimes insufficient locks have been allocated, or were poorly allocated. For example, an OLTP application requires more locks than a decision support application. Allocate locks appropriately with the initsid.ora parameters.