Назад в раздел
A shadow thread is created on behalf of each user accessing the Oracle database. Remember, The shadow processes communicate with the user and interact with Oracle to carry out the user's requests. For example, if the user process requests a piece of data not already in the SGA, the shadow process is responsible for reading the data blocks from the datafiles into the SGA. Under Windows NT, these processes are invoked as kernel threads. Each of these threads has its own thread number. UNIXUNIX, born in 1969, predates NetWare, Windows NT, and OS/2 by many years. UNIX is different from NetWare, Windows NT, and OS/2 in that it was not designed as a client/server operating system. That is not to say that UNIX is not now used as a network server, but that network service was not the original intent of the operating system. Even though UNIX is fairly standard, a divergence in UNIX has occurred over the years. Because almost every major computer company develops and sells a version of UNIX, there are differences in the UNIX offerings from the different vendors. The PC server market boasts three main UNIX versions: SCO UNIX, SCO UnixWare, and Solaris from SunSoft. IBM calls its UNIX offering AIX. Hewlett-Packard has a UNIX operating system called HP-UX; AT&T Global Information Solutions simply calls its product UNIX System V. SunSoft produces versions of UNIX for both Intel and Sun SPARC processors; its products are called Solaris and Solaris X86. Currently, most vendors (with the exception of SCO) base their versions of UNIX on one common core: UNIX System V Release 4, which is sometimes referred to as UNIX SVR4. In many cases, applications are binarily compatible between operating systems on the same platform, but it is not uncommon for the vendor to add special enhancements in the operating system for performance. Oracle always takes advantage of OS-specific features, even though it would be simpler to ship just one binary. Architectural Overview of UNIXUNIX consists of a core piece called the kernel surrounded by applications and tools. The kernel contains all hardware support, device drivers, scheduling routines, and the network stack. Unlike the microkernel architecture used in Windows NT, the UNIX kernel contains all the core operating system functionality. UNIX is a virtual-memory operating system but is very configurable. In the UNIX operating system, not all memory is allocated and used in the same manner. Shared memory used for the Oracle SGA is treated differently from normal memory. Shared memory is allocated at boot time, is not available to general user processes, and must be allocated through shared memory system calls in the operating system. The fact that shared memory is treated differently allows certain functions to occur. Some varieties of UNIX allow you to allocate shared memory using a 4MB memory page size. This arrangement cuts down on page entries that must be maintained by the operating system and guarantees larger chunks of contiguous memory. Shared memory is also unique in that it is nonpageable. This means that if you have enough shared memory to create the SGA at instance startup, you don't ever have to worry about the SGA being paged out. Other features that have been added over the years to the UNIX operating system to improve database performance include realtime processing, high-speed semaphores, and asynchronous I/O. Not all these features are in every implementation of UNIX. Check with your OS vendor to see what is available for your environment. Finding a Performance BottleneckFinding a performance bottleneck can be difficult. There is no proven formula or list of things to look for that immediately tells you what the problem is. Finding a performance bottleneck is an investigative task, and the more experience you get, the better at it you will be. Knowing Your System's LimitationsTo determine whether a system component is prompting a performance bottleneck, ascertain the limitations of that component when possible. Doing so allows you to determine whether you are exceeding that limitation. Unfortunately, these limitations are difficult to gauge in most cases, but it is possible to characterize the component in some cases. The limitations of the I/O subsystem and in the networking components are ascertainable. The I/O SubsystemSome pieces of the I/O subsystem, such as the disk drive, have been well tested and documented over the last few years; limitations of the controller and bus are not as well known. The disk drive is a physical piece of hardware that usually comes with a specification. This specification tells you certain things about the disk drive, such as
A typical disk drive might have an average seek time of 9 milliseconds; for random I/Os, it takes 9 milliseconds to move to the next data. Add 1 millisecond for data transfer to make 10 milliseconds per random I/O. Divide 1 second by 10 milliseconds to see how many times per second you can perform a random seek: Seeks = 1 Sec / 0.01 Sec/Seek = 100 Seeks On Day 4, "Properly Sizing Your Database and Planning for Growth," you saw the "knee of the curve" theory. According to that theory, you don't want to exceed 60-70% of your available capacity; otherwise, times increase. This is why you should not exceed 60-70 seeks per second for random I/O. The Network SubsystemThe network subsystem is another area where you can compare your throughput against a known maximum. Ethernet networks are available in a number of different throughput levels. This is usually indicated in the name of the network hardware. A few of the network hardware specifications include
Monitor your system to you determine whether you are close to meeting or exceeding the available throughput. Remember that smaller packets are not as efficient, so if you send many small requests (common in database applications), you might not realize the full potential of the network. A good way to determine how much network bandwidth you are using is by employing a network monitor. Network monitors can be hardware or software devices. Most operating systems do not provide any way to monitor the network from your system because your network card normally passes only packets addressed to that machine. Other packets are ignored. MemoryMemory problems sometimes manifest themselves as other problems. For example, a lack of memory might cause excessive I/O, which will appear as an I/O problem. Of prime importance is the fact that the system is not paging or swapping. Paging and swapping occur when no physical memory is available for code processing. The CPU pages, or moves data out of memory, onto a paging area on disk. This operation can be quite expensive. If you are seeing significant paging, add more memory to the system or reduce the Oracle DB block buffer cache to free more memory.
Monitoring the SystemAfter you know your limitations, you can determine whether you are exceeding them by monitoring the system. There are several ways to monitor the system; one of the best ways under Windows NT is with the Performance Monitor, or perfmon. On a UNIX system, you can monitor many objects with the sar utility. Most operating systems also offer a graphical monitoring tool. These tools are good, but are specific to the brand of hardware you purchase. Look in your OS documentation for instructions on how to use these tools. Oracle provides a set of useful scripts for monitoring the performance of the RDBMS. These scripts are called UTLBSTAT (begin) and UTLESTAT (end). UTLBSTAT should be run first, followed shortly thereafter by UTLESTAT. UTLESTAT displays a large number of statistic and counter values based on the time since UTLBSTAT was run. Using perfmonSelect Programs | Administrative Tools (Common) | Performance Monitor to invoke perfmon. The Performance Monitor appears in chart mode, which is fine for small numbers of items (such as a trend over an extended period of time). If you are looking at a large amount of data, use report mode; in this mode, values are displayed onscreen as numerical values. Invoke report mode by selecting it via the View drop-down menu or by clicking the Report button.
Looking for I/O Bottlenecks with perfmon To use perfmon to view events in the system, you must select counters. Each of these counters represents a different event that is monitored. To view counter information, do the following:
Choose the PhysicalDisk object.
Of particular note should be the number of I/Os per second per disk drive and the disk latency. A typical disk drive or disk array should complete I/Os in 20-30 milliseconds (0.020-0.030 seconds). If any volume is much higher than that (more than 0.060 seconds), you are probably seeing some sort of disk bottleneck. Consider adding more disk drives. If you are running on UNIX, you can get this information using sar or other monitoring tools. Looking for Memory Bottlenecks with perfmon In Windows NT, the Pages/Sec counter under the Memory objects can be used to indicate that the system is paging. If you see significant activity with this counter, you are using too much memory. If you cannot add more memory, you should reduce the memory used by Oracle and other processes or reduce the number of users on the system. Using UTLBSTAT and UTLESTATWithin the directory ORANTRDBMS80ADMIN are a number of scripts used internally within Oracle. You ran CATPROC.ORA and CATALOG.ORA from this directory when you created a database. UTLBSTAT and UTLESTAT are also run from here. UTLBSTAT sets up some internal tables and takes a snapshot of some of the internal Oracle counters. After you run under load for a time, UTLESTAT takes another snapshot and compares the results. UTLESTAT displays a large number of statistic and counter values based on the time since UTLBSTAT was run.
The UTLBSTAT and UTLESTAT scripts can be run from the SVRMGR30 utility. After you connect in as INTERNAL or SYS, you can run the UTLBSTAT script with the following syntax: @D:ORANTRDBMS80ADMINUTLBSTAT; After you run for a while under load, you can finish monitoring Oracle and view the results by running the UTLESTAT script with the following syntax: @D:ORANTRDBMS80ADMINUTLESTAT;
The result of UTLESTAT is, in its entirety, quite long. Following is the resulting data from UTLESTAT only; the echo of the SQL statements has been removed: OUTPUT: LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI
------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY 0 1 0 1 0 0
CLUSTER 97 1 50 1 0 0
INDEX 0 1 0 1 0 0
OBJECT 0 1 0 1 0 0
PIPE 0 1 0 1 0 0
SQL AREA 120 .808 364 .874 0 0
TABLE/PROCED 235 .923 265 .879 0 0
TRIGGER 0 1 0 1 0 0
Statistic Total Per Transact Per Logon Per Second
---------------------------- ----------- ------------ ----------- -----------
DBWR buffers scanned 601 601 150.25 2.86
DBWR free buffers found 581 581 145.25 2.77
DBWR lru scans 35 35 8.75 .17
DBWR make free requests 35 35 8.75 .17
DBWR summed scan depth 601 601 150.25 2.86
DBWR timeouts 68 68 17 .32
SQL*Net roundtrips to/from 129 129 32.25 .61
background timeouts 210 210 52.5 1
buffer is not pinned count 2175 2175 543.75 10.36
buffer is pinned count 6343 6343 1585.75 30.2
bytes received via SQL*Net 11212 11212 2803 53.39
bytes sent via SQL*Net to c 8270 8270 2067.5 39.38
calls to get snapshot scn: 145 145 36.25 .69
calls to kcmgas 1 1 .25 0
calls to kcmgcs 10 10 2.5 .05
calls to kcmgrs 181 181 45.25 .86
cleanouts only - consistent 5 5 1.25 .02
cluster key scan block gets 801 801 200.25 3.81
cluster key scans 506 506 126.5 2.41
commit cleanouts 16 16 4 .08
commit cleanouts successful 16 16 4 .08
consistent gets 4557 4557 1139.25 21.7
cursor authentications 6 6 1.5 .03
db block changes 104 104 26 .5
db block gets 141 141 35.25 .67
enqueue releases 26 26 6.5 .12
enqueue requests 20 20 5 .1
execute count 144 144 36 .69
free buffer inspected 7 7 1.75 .03
free buffer requested 1383 1383 345.75 6.59
immediate (CR) block cleano 5 5 1.25 .02
logons cumulative 4 4 1 .02
messages received 38 38 9.5 .18
messages sent 39 39 9.75 .19
no work - consistent read g 4063 4063 1015.75 19.35
opened cursors cumulative 109 109 27.25 .52
parse count (hard) 23 23 5.75 .11
parse count (total) 120 120 30 .57
physical reads 1369 1369 342.25 6.52
physical writes 45 45 11.25 .21
recursive calls 2030 2030 507.5 9.67
redo blocks written 49 49 12.25 .23
redo entries 67 67 16.75 .32
redo size 25360 25360 6340 120.76
redo small copies 67 67 16.75 .32
redo synch writes 1 1 .25 0
redo wastage 644 644 161 3.07
redo writes 3 3 .75 .01
session logical reads 4698 4698 1174.5 22.37
session pga memory 645168 645168 161292 3072.23
session pga memory max 645168 645168 161292 3072.23
session uga memory 13772 13772 3443 65.58
session uga memory max 144296 144296 36074 687.12
sorts (memory) 85 85 21.25 .4
sorts (rows) 783 783 195.75 3.73
table fetch by rowid 2148 2148 537 10.23
table fetch continued row 2 2 .5 .01
table scan blocks gotten 1085 1085 271.25 5.17
table scan rows gotten 456 456 114 2.17
table scans (long tables) 2 2 .5 .01
table scans (short tables) 17 17 4.25 .08
total file opens 5 5 1.25 .02
user calls 139 139 34.75 .66
user commits 1 1 .25 0
write requests 6 6 1.5 .03
Average Write Queue Length
--------------------------
0
Event Name Count Total Time Avg Time
--------------------------------- ------------ ------------- -------------
SQL*Net message from client 150 0 0
SQL*Net message to client 150 0 0
control file sequential read 68 0 0
db file scattered read 137 0 0
db file sequential read 413 0 0
file identify 3 0 0
file open 9 0 0
log file sync 1 0 0
Event Name Count Total Time Avg Time
--------------------------------- ------------ ------------- -------------
control file parallel write 2 0 0
control file sequential read 2 0 0
db file parallel write 6 0 0
db file scattered read 1 0 0
db file sequential read 5 0 0
db file single write 2 0 0
log file parallel write 3 0 0
log file sync 1 0 0
pmon timer 70 0 0
rdbms ipc message 250 0 0
smon timer 1 0 0
LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS
------------------- ---------- ----------- ----------- ----------- -----------
Active checkpoint 69 0 1 0 0
Checkpoint queue l 1488 0 1 0 0
Token Manager 22 0 1 0 0
cache buffer handl 43 0 1 0 0
cache buffers chai 10389 0 1 0 0
cache buffers lru 1451 0 1 0 0
dml lock allocatio 11 0 1 0 0
enqueue hash chain 24 0 1 0 0
enqueues 62 0 1 0 0
ktm global data 1 0 1 0 0
library cache 3099 0 1 0 0
library cache load 64 0 1 0 0
list of block allo 1 0 1 0 0
messages 536 0 1 0 0
modify parameter v 4 0 1 0 0
multiblock read ob 288 0 1 0 0
ncodef allocation 3 0 1 0 0
process allocation 1 0 1 0 0
redo allocation 115 0 1 0 0
row cache objects 1318 0 1 0 0
session allocation 56 0 1 0 0
session idle bit 288 0 1 0 0
session switching 3 0 1 0 0
shared pool 1063 0 1 0 0
sort extent pool 1 0 1 0 0
system commit numb 193 0 1 0 0
transaction alloca 7 0 1 0 0
undo global data 101 0 1 0 0
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------- --------------- ---------------- ----------------
Token Manager 3 0 1
cache buffers chai 8643 0 1
cache buffers lru 1465 0 1
library cache 3 0 1
multiblock read ob 1 0 1
process allocation 1 0 1
row cache objects 3 0 1
vecio buf des 3 0 1
CLASS COUNT TIME
------------------ ---------------- ----------------
0 rows selected.
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS
ANLYSIS: As you can see, a wealth of data collected within Oracle can be retrieved via the UTLBSTAT and UTLESTAT SQL scripts. This data is retrieved mainly from the Oracle V$ views. These views are the window into the Oracle internal performance tables.
System TuningI like to divide tuning into the following components:
Oracle TuningThis section focuses on things that can be tuned in the Oracle RDBMS. A complete list of Oracle tuning parameters is given in Appendix B, "Oracle Tuning Parameters." The SGAThe System Global Area (SGA) contains the shared pool, the redo log buffer, and the database block buffers. The Shared Pool The shared pool contains the library cache, the data-dictionary cache, and the shared session area (with the multithreaded server). The Library Cache The library cache contains the shared SQL and PL/SQL areas. You can improve performance by increasing the cache-hit rate in the library cache and by speeding access to the library cache by holding infrequently used SQL statements in cache longer. The V$LIBRARYCACHE table contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:
A small number of reloads relative to the number of executions indicates a high cache-hit rate. The Data-Dictionary Cache The data-dictionary cache contains a set of tables and views that Oracle uses as a reference to the database. Here Oracle stores information about the logical and physical structure of the database. To check the efficiency of the data-dictionary cache, check the cache-hit rate. Statistics for the data-dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data-dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:
To determine your cache-hit ratio, use the following SQL statement or look at PINHITRATIO in the output of UTLESTAT: SELECT SUM(reloads) "Cache Misses", SUM(pints) "Executions", 100 * (SUM(reloads) / SUM(pins) ) "Cache Miss Percent" FROMB V$librarycache; This will show you the cache-miss percent: Cache Hit % = 100% - Cache Miss % A low number of cache misses is expected, especially during startup when the cache has not been populated. Shared Session Information In a multithreaded server configuration, the session information is also stored in the shared pool. This information includes the private SQL areas as well as sort areas. Ensure that you do not run out of memory for this shared session information. To determine whether you need to increase space for these shared sessions, you can extract the sum of memory allocated for all sessions and the maximum amount of memory allocated for sessions from the dynamic performance table V$SESSTAT. If the maximum amount of memory used is high, it might be necessary to increase the size of the shared pool. Because the shared pool is used for other functions as well, such as the library cache and the data-dictionary cache, it is a good idea to increase the size of the shared pool to accommodate this additional memory usage. If you have enough memory in your system, increase the shared pool by the maximum amount of memory used by the shared server processes; if you have a limited amount of memory, use the sum of memory allocated to sessions that was obtained when an average number of users was connected and running as a basis for the amount of memory by which to increase the shared pool. Database Block Buffer Cache The most important Oracle cache in the system is probably the buffer cache. The buffer cache makes up the majority of the Oracle SGA and is used for every query and update in the system. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT and are included in the output from UTLESTAT. The important columns to view in this table are listed here:
The sum of the values in DB BLOCK GETS and CONSISTENT GETS represents the total number of requests for data. The cache-hit ratio is determined using this formula: Cache-hit ratio = 1 - (PHYSICAL READS / (DB BLOCK GETS + CONSISTENT GETS)) The block buffers are the most important area of the SGA and must be tuned because of the large effect they have on the system and the number of resources they consume. Performance EnhancementsI prefer to separate the performance-enhancement options from the general tuning of Oracle. Performance enhancements tend to be things that might or might not help your configuration and application; in fact, they might hurt. On the other hand, tuning parameters always help, based on the correct interpretation of Oracle statistics. The following sections review a few of the enhancements you have seen throughout the book. Block Size Depending on your configuration and data-access patterns, you might be able to benefit from using a larger block size. With a larger block size, you get the benefit of less wasted space and more efficient I/O in certain conditions. Here are a few guidelines that might help you decide whether changing the size of DB_BLOCK_SIZE can help you:
Because unnecessarily changing the block size increases I/O overhead, this change does carry some risk. Change the block size with caution. Clusters New Term: A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. Within a cluster, multiple related tables are stored together to improve access time to the related items. Clusters are useful in cases where related data is often accessed together. The existence of a cluster is transparent to users and applications; the cluster affects only how data is stored. A cluster can be useful for tables in which data is primarily accessed together in a join. In such situations, the reduced I/O needed to bring the additional data into the SGA and the fact that the data is already cached can be a big advantage. However, for situations in which the tables have a large number of INSERT statements or the data is not frequently accessed together, a cluster is not useful and should not be used. Do not cluster tables if full-table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O reduces performance. Reduction of Fragmentation New Term: Fragmentation occurs when pieces of the database are no longer contiguous. Fragmentation can consist of disk fragmentation or tablespace fragmentation. Both of these types of fragmentation usually affect performance. Disk fragmentation usually causes multiple I/Os to occur when one I/O would have been sufficient (for example, with chained or migrated rows). Disk fragmentation can occur when the extents that comprise the database segments are noncontiguous; this is sometimes caused by excessive dynamic growth. Tablespace fragmentation is caused by the dropping and creating of segments. This can produce large free areas between segments, which results in the inefficient use of space and excessive disk seeks over the empty areas. Tablespace fragmentation can also prevent Oracle from taking advantage of multiblock reads. One way to eliminate fragmentation is to export the table or tablespace data, remove and re-create the table or tablespace, and import the data. By eliminating fragmentation, you can reduce excessive I/Os and CPU usage, streamlining data access. Any overhead and unnecessary I/Os you can reduce will improve system performance. Hash Clusters New Term: A hash cluster is similar to a cluster except that it uses a hash function rather than an index to reference the cluster key. A hash cluster stores the data based on the result of a hash function. The hash function is a numeric function that determines the data block in the cluster based on the value of the cluster key. To achieve good performance from a hash cluster, you must meet the following criteria:
If you can take advantage of hashing by meeting this strict criteria, you will see good performance. Hashing is extremely efficient under the right conditions; however, having a hash cluster under the wrong conditions can degrade performance. Indexes New Term: An index, like the index in this book, is an optional structure designed to help you achieve faster access to your data. When optimally configured and used, indexes can significantly reduce I/O to the datafiles and greatly improve performance. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Having decided to use an index, you must decide which columns to index. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times. Careful planning and periodic testing with SQL Trace can lead to the effective use of indexes, with optimal performance being the outcome. Even though indexes have been built on tables in your database, they might not necessarily be used. If the SQL statement is not correctly formed, the index might be bypassed and a full-table scan might be the result. This is why the application developer and the system designer must communicate constantly throughout the development and deployment cycle. The application should be tested and the SQL statements analyzed to ensure that indexes are being used where intended. Indexes are covered in detail on Day 13, "Using Indexes and Sequences." Multiblock Reads When performing table scans, Oracle can read more than one block at a time, thus speeding I/Os. Consequently, a larger chunk of data can be read from the disk, thus eliminating some disk seeks. The reduction of disk seeks and the reading of larger blocks reduces both I/O and CPU overhead. The amount of data read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. The value for this parameter should always be set high because there is rarely any disadvantage in doing so. The size of the individual I/O requests depends on DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE. A good value for multiblock reads is 64KB. Multiblock Writes Multiblock writes are similar to multiblock reads and have many of the same requirements. Multiblock writes are available through the direct path loader as well as through sorts and index creations. As with multiblock reads, the multiblock writes reduce I/O and CPU overhead by writing multiple database blocks in one larger I/O operation. The amount of data written in a multiblock write is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_WRITE_COUNT. The size of the individual I/O requests depends on both DB_FILE_MULTIBLOCK_WRITE_COUNT and DB_BLOCK_SIZE. As with multiblock reads, a good value is 64KB. The Oracle Parallel Query Option The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. The functions affected are queries, index creation, data loading, and recovery. For each of these functions, the general principle is the same: Keep processing while Oracle waits for I/O. For most queries, the time spent waiting for the data to be retrieved from disk usually overshadows the amount of time actually spent processing the results. With the Parallel Query option, you can compensate for this wasted time by using several server processes to execute the query. While one process is waiting for I/Os to complete, other processes can execute. If you are running on a Symmetric Multiprocessor (SMP) computer, a cluster, or a Massively Parallel Processing (MPP) machine, you can take maximum advantage of the Parallel Query option. The amount of parallelism can be tuned with several of the Oracle initialization parameters:
I am a big fan of the Parallel Query option. I have seen great improvements from the use of parallel queries as well as dramatic reductions in recovery time when the parallel recovery feature is used. You saw this in yesterday's lesson, "Advanced Oracle Options." The Oracle Parallel Server Option The Oracle Parallel Server option is one of the most innovative and impressive options available from Oracle. With the Parallel Server option, you can cluster several computers using a shared-disk subsystem and have multiple Oracle instances access the same database. If your application is suitable, you can see good scalability from adding computers. The Oracle Parallel Server option uses a sophisticated locking mechanism in conjunction with a shared-disk subsystem to allow multiple instances to access the same data. If you have an application that can take advantage of the Oracle parallel server architecture, you should see some good performance improvements. The two areas that can most influence the performance of your parallel server system are data partitioning and PCM lock management. Both of these can make a huge difference in the performance of your system.
Spin Counts Multiprocessor environments might benefit from tuning of the parameter SPIN_COUNT. Under normal circumstances, if a latch is not available, the process sleeps and then wakes up to try the latch again. If you are on a multiprocessor system, it is likely that the process holding the latch is currently processing on another CPU and will be finished in a short time. If you set SPIN_COUNT to a value greater than 0, the process spins while counting down from SPIN_COUNT to 0. If the latch is still not available, the process goes to sleep. Setting SPIN_COUNT can hurt performance if you're not careful. This parameter should be set only for multiprocessor computers and should be monitored for effectiveness. A good value to try is 2,000. The value of SPIN_COUNT specifies how many times the process will spin before putting itself to sleep. Because the speed of processors varies, the time it takes to spin also varies; however, the speed of the process holding the desired resource will also vary with the speed of the processor. OS TuningOS tuning is specific and depends on the OS you are running. I will present some of the basics on tuning Windows NT and UNIX here. NT Tuning ConsiderationsWith Windows NT, probably the most important tuning consideration is ensuring that sufficient physical memory is available to run Oracle and the required number of server processes. Windows NT uses virtual memory, which means that Oracle and user processes can allocate an almost unlimited amount of memory through paging. If you are not careful, you might overconfigure the amount of virtual memory that you are using and exceed the amount of physical memory in the system. If this occurs, the system will begin paging and performance will be severely degraded. In addition to the concern about memory, other areas of importance in tuning the Windows NT system include the network and I/O subsystems and the reduction in OS overhead. Memory Windows NT uses only virtual memory. Because of this, all memory in the system is treated equally, limited only by the size of the paging file; you must be wary of user processes consuming large amounts of memory and causing paging out the SGA. In Windows NT, there are no limitations on memory used for user processes, disk caching, print caching, and so on; thus, it is best to dedicate the Windows NT server to either file and print services or to application services.
If you use asynchronous I/O (AIO) and Oracle can lock down memory for AIO, the database block buffers are not swappable. Monitor the system on a regular basis to ensure that no paging is occurring in the system. Reduce Unnecessary Memory Usage You can take some measures to reduce the amount of memory used by Windows NT. Invoke the Control Panel's Network Settings screen and choose the Maximize Throughput for Network Applications option. This optimizes the server memory for network applications, reducing some of the file-system caching and overhead in memory management. Also remove any network protocols not needed by the system to cut down on system overhead and memory usage via the Control Panel. Also use the Control Panel to turn off any services you are not using. Doing so reduces memory usage and CPU overhead. By reducing all the unnecessary services, you can increase the performance of the system. SGA Tuning To maximize performance, allocate as much memory as possible to the SGA. Use the techniques discussed on Days 9, "Managing Data," and 10, "Administering User Accounts," to determine whether the memory can best be used for the shared pool or for database block buffers. Because the SGA resides in virtual memory, it is important that you not allocate so much memory for the SGA that the system pages. The overhead of paging and swapping overshadows any benefit you might gain from a larger SGA. Remember to save memory for the user processes. You should frequently monitor your system to ensure that no paging occurs at any time. User Capacity You can easily determine the amount of memory necessary for your application on a per-user basis. Start Oracle and note the amount of available memory by using the Windows NT Performance Monitor. Monitor the Available Bytes option under Memory in the Performance Monitor. After users begin accessing the application in a typical manner, record the amount of memory again. Take the difference and divide this result by the number of users accessing the application. Multiply this per-user memory usage value by the maximum number of users who might be connected to the application to determine the amount of memory you must reserve for user connections. Be sure to add a little extra memory just in case. Be careful that a user's PGA does not consume too much system memory. To increase the number of Oracle connections, you might also have to adjust the Oracle initialization parameter PROCESSES. This parameter should reflect the maximum number of user connections you expect to have plus the Oracle background processes. You should also include some extra processes for administrative tasks. Network Minimal network tuning is necessary with Windows NT. However, you can enhance performance by prioritizing the network bindings. Do so the with the Control Panel's Network Configuration utility. If you put the network protocol you use most frequently first on the list, that protocol gets highest priority. Place other network protocols in the list in the order in which you use them. Removing any protocols you do not use can also boost performance by reducing memory consumption and CPU overhead. Also be sure that the system is configured as a server in the Network Configuration screen. I/O Subsystem It is important that performance not be bound by physical I/O rates. If you ensure that the system disks are not driven past their limits, you can avoid this. This is covered in detail on Days 14, "Using Oracle Clusters, Stored Procedures, and Database Links," and 15, "Managing Job Queues and Using Oracle Auditing." With Windows NT, asynchronous I/O (AIO) is always enabled. There is no need to adjust any tuning parameters to ensure that AIO is enabled. The default block size for Oracle on Windows NT is 2,048. This might be sufficient. If you have a large database or if the data access is primarily sequential, you might want to increase DB_BLOCK_SIZE to 4,096. Although the data blocks take up more space in the SGA, the number of I/Os performed is significantly reduced. It doesn't take much more overhead and time to retrieve 4KB of data from the disks than it does to get 2KB of data. If data access is primarily sequential, you might benefit by setting the DB_BLOCK_SIZE parameter to 8,192. Because sequential access to the database will read the next block anyway, larger block sizes will read that data into the SGA before you need it. If your data access is random, you will waste space in the SGA. The value you choose for the block size affects performance either for better or worse. If you are unsure, leave the parameter at the default of 2,048. When creating Windows NT file systems, you have several choices:
Although each of these performs well in certain situations, I recommend using NTFS. NTFS provides you with the best level of overall performance and is the file system that Microsoft endorses. UNIX Tuning ConsiderationsAs is true for NT and UNIX, the most important tuning consideration is ensuring that sufficient memory is available to run Oracle and the required number of server processes. Because UNIX is a virtual memory operating system, you can always start more server processes; but if you are not careful, you might begin paging. Many varieties of UNIX have made available extended features such as the Post-Wait Semaphore and asynchronous I/O to enhance Oracle performance. Other areas of importance in tuning the UNIX system include the network and I/O subsystem. Memory Memory is important in the UNIX operating system. As you have seen in earlier lessons, the way to increase performance is to maximize the use of the SGA to avoid costly disk I/Os. In UNIX as in all operating systems, it is important to avoid paging. Because the SGA is in shared memory in UNIX, the SGA is guaranteed not to page--but the server processes can page if too much memory is being used. It is important to allocate enough shared memory to accommodate the SGA. If you do not have enough memory to create the SGA, the instance does not start. If this happens, you must either configure the operating system to allow more shared memory or reduce the size of the SGA. If you are using enhanced 4MB shared memory pages, you can allocate the amount of shared memory only in 4MB units. Be careful not to allocate so much shared memory for the SGA that user and server processes page. Be sure to periodically monitor the system to ensure that no paging is occurring. To maximize memory used by the SGA, reduce all unnecessary memory used by the operating system and limit the amount of memory that can be allocated by users. Reduce Unnecessary Memory Usage One of the best ways to free memory for Oracle is to siphon from memory used by the file-system buffers. These buffers are used by the OS to cache data. A significant performance increase can incur when files that have been cached are accessed. But Oracle uses a feature called synchronous I/O to ensure that writes to the disk are not returned until the data has actually been written. Because Oracle must guarantee that I/Os have been written to the disk to ensure data integrity, OS disk write caching cannot be performed. By default, the number of file-system buffers is determined by the amount of memory in the system. Because Oracle bypasses the disk cache on writes and uses the SGA for reads, you don't really need a large number of file-system buffers. By reducing this number, you might see a slight performance decrease with OS operations, but any additional memory allocated to the SGA increases Oracle performance. The parameters used to tune the file-system buffers is fairly vendor dependent; this should be documented in your OS manuals. If you reduce the unnecessary memory used for disk caching, more memory can be allocated to the SGA. Don't reduce the disk cache so far that it is difficult to run OS commands and access Oracle parameter files. Do not set the disk cache buffers to less than 600. These values represent the number of 512 byte blocks. SGA Tuning To maximize performance, allocate as much memory as possible to the SGA. Use the techniques discussed earlier today to determine whether memory is best used for the shared pool or for databases block buffers. In UNIX, the shared memory area used by Oracle for the SGA is usually contiguous. However, if you have multiple instances of Oracle that have started and stopped several times, the shared memory area might no longer be contiguous. If you use 4MB pages, you are guaranteed to have at least 4MB of contiguous memory. The amount of memory allocated for shared memory is the product of two OS-tunable parameters: SHMMAX and SHMSEG. SHMMAX specifies the maximum size of a shared memory segment; SHMSEG specifies the maximum number of shared memory segments available in the system. Applications are responsible for allocating only the amount of shared memory they need and so do not waste space. In SCO UNIX and UnixWare, the parameters SHMMAX and SHMSEG are located in the UNIX parameter file /ETC/CONF/CF.D/STUNE. In Solaris, the shared memory parameters are set in the file /ETC/SYSTEM. Your OS administrator's manual should have more information about setting system parameters.
Most varieties of UNIX that run on the Intel platform have a variety of 4MB pages. By referencing your OS vendor-specific documentation, you can determine how to tune this. The use of 4MB pages can improve performance dramatically, especially if you are running on an Intel Pentium processor.
The amount of memory allocated to a single user must also be tuned in the UNIX operating system. Because Oracle is treated the same as any other user, you must allocate enough memory for Oracle to use for the SGA and the server processes. By referencing your OS-specific documentation, you can see how to configure this. Ensure that sufficient memory is available so that Oracle can allocate for the SGA. Remember to save memory for the user processes as well. You should frequently monitor your system to ensure that no paging is occurring at any time. User Capacity You can easily determine the amount of memory necessary for your application on a per-user basis. Start Oracle and note the amount of available memory available with the UNIX utility sar -r. The output from sar -r consists of freemem (free memory pages) and freeswp (free swap pages). The value given in the freemem column is the number of 4KB pages available. When users begin accessing the application in a typical manner, record the amount of memory again. Take the difference and divide this result by the number of users accessing the application. This value is the per-user memory usage. Multiply this value by the maximum number of users who might connect to the application to determine the amount of memory you must reserve for user connections. Be sure to leave a little extra memory just in case. The size of a user's PGA is not bound by any initialization parameters. Because of this, be careful that a user's PGA does not consume too much system memory. The UNIX operating system parameters MAXUP and NPROC must also be set to allow a sufficient number of users to connect. Remember that when users connect to Oracle, an Oracle shadow process is created under the Oracle user ID. Therefore, you must increase not only the number of processes systemwide, but also the per-user process limits. The per-user process limit is set with the OS parameter MAXUP. The maximum number of processes systemwide is set by the OS parameter NPROC. Both values are in the stune file. NPROC should be at least 50 greater than MAXUP to account for OS processes. To increase the number of Oracle connections, you might also have to adjust the Oracle initialization parameter PROCESSES. The PROCESSES parameter should reflect the maximum number of user connections you expect to have plus the Oracle background processes. You should also include some extra processes for administrative tasks. Network With UNIX, the amount of network tuning is usually minimal. Typically, no network tuning is required, and the network tuning that is required is vendor specific. Refer to your OS documentation for any networking tuning that is required. I/O Subsystem As with all other operating systems described today, it is important to ensure that performance is not bound by physical I/O rates. Be sure that random I/Os do not exceed the physical limitations of the disk drives. Refer to Days 14 and 15 for details. With UNIX, you have the choice of using the UNIX file system for your data storage or the RAW device interface. This choice is not always an easy one. The RAW device interface is more difficult to manage but provides a higher level of performance. File-system files are much easier to use but have more overhead associated with them. File System Using the UNIX file system is easier than using RAW devices. Using file-system files, Oracle simply creates the file. However, when using the file system, Oracle must contend with the UNIX disk-caching system and use synchronous writes to ensure that the write request does not return to the DBWR or LGWR before it has actually written the data to disk. With the UNIX file system, there is also the overhead of the data being read into the UNIX disk cache and then being copied to the SGA. This arrangement causes additional overhead on reads. Finally, when you use the file system, you are not guaranteed to have contiguous blocks on the disk--in fact, you are almost guaranteed not to have contiguous blocks. RAW Device Interface The RAW device interface allows for the least amount of overhead you can achieve with UNIX I/Os. When using the RAW device interface, UNIX simply assigns a section of the disk to each RAW device. This portion of the disk is contiguous; accesses to it bypass all disk caching and file-system overhead. RAW devices are not as easy to manage because each device is considered one big chunk of data for the operating system. Backup and recovery must be handled slightly differently because file copies do not work, and the size of the raw device cannot be changed after it is created. Backup operations must be done via the UNIX DD command or by a third-party backup utility that supports raw devices. RAW devices give greater performance with less overhead and are fully supported by Oracle. Whether you use RAW devices is a decision you must make based on ease of use and increased performance. Asynchronous I/O With UNIX, AIO is not always enabled. It is necessary to enable AIO in both the OS and in Oracle. By using AIO, the DBWR can manage many I/Os at once, eliminating the need for multiple DBWR processes. List I/O allows the DBWR to pass to the OS a list of AIO commands, reducing the number of calls it must make.
For some UNIX implementations, the following Oracle initialization parameters for asynchronous I/O should also be set:
You should always use asynchronous I/O (if possible). When you use asynchronous I/O, you can keep the number of DBWR processes to one and therefore reduce process overhead. Hardware TuningThe I/O system should be designed and implemented with the following goals in mind:
If you follow these guidelines and plan your system so that your disk drives can support the amount of disk I/O demanded of them, I/O should not be a problem. Application TuningOne of the most common areas that performance can be enhanced is in the application itself. SQL statements can often be redesigned to dramatically improve performance. I recently worked on a project where one of the engineers redesigned the SQL statements of a set of queries that took more than four hours to run to more effectively use indexes; after the engineer made the changes, the queries took less than 10 minutes to run. Here are a few things that can be done to improve the application itself. My other book, Oracle Performance Tuning and Optimization (Sams Publishing) covers these topics in great detail.
If you tune the SQL statements and application to take advantage of indexes, clusters, and other features, you should see significant improvement in performance. SummaryToday you looked at various ways of determining whether some sort of performance bottleneck exists, and if one does, how to isolate that bottleneck and provide solutions. The lesson began by defining the term bottleneck. You were also presented with my performance tuning methodology. If you follow these steps (or steps of your own), you should be able to find the bottleneck or at least find a way to work around it. Today's lesson also provided a brief overview into the operation and function of your OS. Next, you saw a way of looking at the system in terms of performance. How you monitor the system was also shown today. Much can be learned from the NT Performance Monitor and sar, and much can be learned from Oracle itself using UTLBSTAT and UTLESTAT. With these tools, you can narrow the focus of your performance investigations and determine potential changes to your system. What's Next?Tomorrow's lesson is the last of the 21 days of instruction on the ins and outs of Oracle8. This book finishes up by teaching you the specifics of the Oracle8 networking system. You will learn how to configure SQL*Net and use the Oracle Name Server. Also, you will learn how to configure and use the Oracle Web server. Other miscellaneous topics will also be covered in the final lesson of the book. Q&A
WorkshopThe workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. The answers to the quiz questions can be found in Appendix A, "Answers." Quiz
Exercises
   
© Copyright, Macmillan Computer Publishing. All rights reserved. |
 |
|
UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS
SHRINKS WRAPS
------------------ ------------------ ------------------