Page 563
CAUTION |
Oracle Corporation has stated that it is not committed to supporting the V$ tables in future product releases. Although that would be unlikely , given the number of market products that use these views, be aware of the possibility. |
V$ views are useful in many applications, such as backup and recovery, administrative monitoring, and performance tuning. Here are some of the more commonly used views as they apply to performance tuning:
V$DB_OBJECT_CACHE | Contains information about all the database objects that currently exist in the library cache of the SGA |
V$FILESTAT | Contains the amount of physical reads and writes taking place on a specific data file associated with the database |
V$LATCH | Contains a current statistical picture of all the latches within the database |
V$LATCHHOLDER | Contains the name of the current latchholder of each latch specified in V$LATCH |
V$LATCHNAME | Contains the name of each latch in the V$LATCH view |
V$LIBRARYCACHE | Contains statistics that represent the overall performance of the library cache area of the SGA |
V$ROLLSTAT | Contains statistics on all the online rollback segments in the database |
V$ROLLCACHE | Contains statistical information about the performance of the data dictionary cache of the SGA |
V$SESSION_WAIT | Provides information on what sessions are waiting for other sessions, if one session is waiting for another to complete a task or event |
V$SESSTAT | Contains current statistical information for each active database session |
V$SESS_IO | Contains current logical and physical I/O information for each active database session |
V$SGASTAT | Summarizes statistical information on the overall SGA |
V$SQLAREA | Contains statistical information on the cursor cache of the SGA |
V$STATNAME | Contains the names of all the statistics from V$SESSTAT |
V$WAITSTAT | Contains information on block contention ”active only when TIMED_STATISTICS is set to TRUE |
Page 564
CAUTION |
Because statistics are gathered over a period of time, a database that has just started up does not have sufficient statistics to provide any sort of tuning information. Statistics gathered from a newly started database are misleading and cannot pinpoint actual performance problems. As a rule of thumb, a database should run for several hours before you gather any performance statistics. |
Here is a query that uses the V$ views. It displays the name and current value of each database statistic. It is useful for quickly seeing how the database is performing.
select n.statistic# , n.name , s.value from v$statname n , v$sysstat s where n.statistic# = s.statistic# and value > 0 /
There are many more V$ views that are not mentioned here. Many gather I/O, cache, and buffering statistics that are invaluable for performance tuning. Consult the Oracle7 Server Administrator's Guide, the Oracle7 Applications Developers Guide, and the Oracle8 Server Documentation for more information on these views.
If you have a version of Oracle installed older than Oracle7.3, you can examine the files used by SQL*DBA in the ORACLE_HOME/rdbms/sqldba directory and use them as guidelines for querying the V$ views. These scripts also show how many of the V$ views have relationships with other V$ views.
Problems with database applications often involve memory and disk drives. When the CPU runs faster than the throughput of the input/output devices (such as the disk drives ), the system is called I/O bound. When the throughput of the input/output devices is faster than the CPU, the system is called CPU bound.
Most systems are I/O bound, so it is easy to dismiss poor performance as a by-product of poor throughput transfer. Many DBAs, therefore, perform load balancing and contention analysis to optimize performance. What is forgotten, however, is that poor memory management can often aggravate many performance problems. Poor use of the available memory can contribute to throughput that is less than superior . For example, sorts that could be optimized to run in memory run to disk, or the operating system pages and swaps processes to disk.
It is important, therefore, to understand the single most memory- intensive part of the Oracle RDBMS ”the system global area (SGA). By definition, the SGA is simply a combination of buffers and caches stored in virtual memory that enables the database to function. To be efficient, Oracle performs many of its operations in memory, writing to disk only in bulk so that
Page 565
it optimizes performance hits. This is good because, from the standpoint of software development, accessing the disk drive is "expensive" in terms of performance cost, whereas running a process in memory is "inexpensive."
As Figure 23.1 shows, the SGA is composed of three primary units:
Figure 23.1.
Architecture of the
Oracle SGA.
It is important to ensure that the SGA is large enough to fit comfortably into the system's existing memory. It should also be small enough to coexist with other applications and not allocate more memory than it requires. It is equally important to make certain that there is enough shared memory ”semaphores ”available to support the database instance. Like all other aspects of performance tuning, memory management means balancing available resources against needed resources and reaching an effective compromise.
To tune the SGA of a database instance, you must determine the current size of the SGA. There are several ways to do this, including extracting the information from the DBA views or V$
Page 566
tables or calculating it based on values in the INIT.ORA parameter file. The simplest method, however, is to issue the show sga command from Oracle Server*Manager. For example:
% svrmgrl SVRMGR> connect internal Connected. SVRMGR> show sga Total System Global Area 95243632 bytes Fixed Size 46384 bytes Variable Size 70588480 bytes Database Buffers 24576000 bytes Redo Buffers 32768 bytes
The size of the SGA remains constant as long as the database is running, although the DBA can change it when the database is restarted.
The sum of the parts equals the whole. Sizing the SGA is no exception. Changing the size of the SGA requires that values of some INIT.ORA parameters be modified, which in turn alters the overall size of the SGA. The following parameters control the size of the SGA:
DB_BLOCK_BUFFERS | The number of database blocks (of size DB_BLOCK_SIZE) allocated to the database buffer cache |
LOG_BUFFER | The size (in bytes) of the redo log buffer |
SHARED_POOL_SIZE | The size (in bytes) of the shared SQL area |
CAUTION |
Some of the parameters require their arguments in bytes, whereas others require their arguments in blocks. |
After the size of a SGA buffer is set, the size of the SGA remains constant as long as the database continues to run. If the values of these three parameters are changed and the database is restarted, they immediately take effect. You should make a backup of the INIT.ORA parameter file before you make considerable changes. For example:
# # SGA Size Parameters # # each database block is set to 8192 (8K) bytes db_block_size = 8192 # buffer is 25MB (8192 bytes x 3200 blocks) db_block_buffers = 3200 # buffer is 32K log_buffer = 32768