0563-0566

Previous Table of Contents Next

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.

Tuning Database SGA

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:

  • The redo log buffer
  • The database buffer cache
  • The shared SQL area (the shared pool)

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.

Examining the Current SGA

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.

Changing the SGA Size

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 
Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net