Parallel index creation,Parallel recovery,Parallel table creation,oracle virtual server

The system memory is basically a set of memory chips, either protected or not protected, that stores data and instructions used by the system. System memory can be protected by parity or by a more sophisticated advanced ECC correction method. Data parity will detect an incorrect value in memory and flag it to the system. An advanced ECC correction method will not only detect an incorrect value in memory, but in many cases can correct it. The system memory can range in size from 4MB on a small PC to 4GB on a large SMP server.

Typically, the more memory available to Oracle, the better your performance. Allocation of a large SGA allows Oracle to cache more data, thus speeding access to that data.

New Term:System memory is accessed by the CPUs through a high-speed bus that allows large amounts of data and instructions to be quickly moved from the CPU to L2 cache. Data and instructions are typically read from memory in large chunks and put into the cache. Because the CPU expects that memory will be read sequentially, in most cases it will read ahead the data or instruction that it thinks will be needed next. Sometimes this works, so the data that is needed next is already in cache; sometimes the CPU has guessed incorrectly and other data needs to be retrieved. This process of prereading the data is known as prefetching.

Depending on the specific implementation of an SMP system, the memory bus might be shared by all system processors; alternatively, each processor might have a private bus to memory.

Virtual Memory System

New Term:In a virtual memory system, the OS and hardware allow programs and users to use more memory than is actually available in the system hardware. This memory, known as virtual memory, can be mapped to physical memory. Code or data that is being run by the CPU must reside in physical memory. If a program or data that is larger than physical memory is being accessed, the parts of code and data that are not immediately needed by the program can reside in virtual memory, not physical memory. As that bit of code or data is needed, it can be copied into physical memory, and parts no longer needed can be copied to disk. The process of mapping virtual memory onto physical memory by copying the memory to and from disk is called paging or swapping (depending on the OS architecture).

Both paging and swapping serve the same purpose, but each operates slightly differently from the other. In a swapping system, an entire process is swapped out (moved from memory to disk) or swapped in (moved from disk to memory). In a paging system, the movement of data to and from the secondary storage occurs on a memory page basis; when more memory is needed, one or more pages is paged out (moved from memory to disk) to make room. A memory page is the smallest unit of memory that is used in the operating system. A typical memory page size is 4KB. If data is requested from virtual memory and is not in physical memory, that data is paged in (moved from disk to memory) as needed. The rest of this section uses the term paging to describe both paging and swapping.

Suppose you have a computer system with 16MB of physical memory. If you have a program that needs to access 20MB of data, it obviously won't fit in physical memory. In a virtual memory system, the data is read until little memory remains (the OS reserves some for itself), then the OS copies some of the data pages to disk with the paging mechanism. This is usually done using a least recently used algorithm in which the oldest data is moved out. When some memory has been freed, the program can read more data into memory. As far as the program is concerned, all the data is still in memory; in fact, it is--in virtual memory. As the program begins to reread some of the data and manipulate it, different pieces might be paged in (from disk to physical memory) and paged out (from physical memory to disk).

As you can imagine, paging in or out can be time consuming and uses a lot of system resources. This is why I warn you several times in this book to avoid using so much memory that you cause paging or swapping. Access to disk is approximately 50 times slower than access to memory.

Bus Design

New Term: Simply put, bus is a connection path used by the system to move data from one place to another. Buses get complicated when you look at them from a performance perspective: Capacity, or bandwidth, becomes an issue. Over the years, the term bandwidth, which was originally used to describe the electronic characteristics of a circuit, has been adopted by computer designers. In this case, bandwidth refers to the amount of data that can be transmitted across a bus in a certain time.

Several bus designs have been introduced in the last few years, all with the same goal: increased capacity. As processors, network hardware, disk controllers, and disks become increasingly fast, buses must develop to support the load generated by these devices. Thankfully, as computers have increased in performance, computer designers have improved bus designs to accommodate these changes. The system bus should not be a bottleneck in your system.

Oracle Resources

The Oracle DBMS allocates different resources for various different functions, including the allocation of system memory. The memory might be allocated for database caching or for the data dictionary or library cache. The careful balance of this precious resource is very important in tuning the Oracle RDBMS.

As much data as possible must be cached to avoid the additional cost of going to disk. If you allocate a large Oracle data cache, a higher cache-hit rate can be achieved. A high cache-hit rate indicates that a large percentage of requested data is found in the Oracle cache rather than retrieved from disk.

Application Design

Application design can affect performance more than any other factor. In most cases, performance can be severely degraded by an application that does not have well-tuned SQL statements or does not use indexes. A good application design can also significantly improve performance. The application is typically the first place to look when you experience system performance problems.

If a database is built with indexes on a certain set of columns but those columns are not specified in the WHERE clause of the SQL statement, the index probably won't be used. It's not enough to create the correct index on tables; you must ensure that the indexes are used.

TIP:It's wise to create a specification identifying the tables and indexes in your database. That way, the application developers and the team that creates the database have a crystal-clear document that identifies which columns are indexed. This can help avoid confusion and allow the application code to fully exploit the indexes.

Oracle Features

Another way to improve Oracle performance is to enable Oracle performance features. Among the most important of these features (and my personal favorite) is the Oracle Parallel Query option. Other Oracle performance features include partitioned tables and the Oracle index-only table, both new in Oracle8.

The Oracle Parallel Query Option

The Oracle Parallel Query option allows parallelism of many different operations, which greatly enhances performance. The Oracle Parallel Query option consists of several different components, including

Parallel Query

The Oracle parallel query allows a single query to be divided into components and run in parallel. Because a query spends much of its time waiting for I/O operations to complete, parallelizing queries can greatly improve performance. In a well-tuned system where I/O is not a problem, parallel queries can run many times faster than normal queries. Statements that can be parallelized include

NOTE: You might be wondering why parallelizing operations would help performance; after all, the work must still be done. In a typical Oracle operation (for example, a SELECT statement), the following steps occur:

As shown here, that the time-consuming job of retrieving data from disk is duplicated, thus improving performance. This parallelism allows the CPU(s) to be utilized while other threads are waiting for I/Os.

Retrieving data from disk is a slow process compared to the activity of the CPU, and your goal is to keep the CPUs busy. Because a significant part of any Oracle operation involves CPU processing and I/Os, it is possible and desirable to keep the CPUs busy while many I/Os are being processed simultaneously. This is the main goal of the Parallel Query option.

Parallel Index Creation

Index creation involves reading from data tables and then writing to the index tables. Because the parallel query allows reading of tables to be accelerated, the index-creation process is sped up. Index creations can be quite time consuming, so this can be a real advantage.

Parallel Recovery

Recovery from a system failure can be quite time consuming. During recovery, users must usually wait for the system to come back online, so any improvement in performance is an advantage. Parallel recovery can speed the recovery process by parallelizing the read from the redo log files, and the roll forward and rollback process.

Parallel Table Creation

Although the Oracle Parallel Query option does not generally allow table creations to occur, it is often the case when a table is created as a subset of other tables. Data is often reduced from several large tables into a smaller subset, and this parallelism can be beneficial. In such instances, the following statement allows for parallelism:

Oracle Index Tables

OS Resources

New Term: In most systems, few resources can be allocated in the operating system. Most OS parameters are changed only to allocate sufficient resources to Oracle; additional resources usually do not improve performance. A lack of resources, however, can decrease performance. OS resources often refers to system memory or, in the case of UNIX, shared memory. Other OS resources and tunables include network buffers and disk I/O tunables.

* Remove unnecessary network protocols--Depending on how the system is configured, several network protocols that you do not use might be configured into your system. These extra protocols use CPU and memory resources.

* Configure the protocols you use in order from most-often used to least-often used--This reduces some of the overhead associated with traversing the infrequently used protocols.

* Keep a close eye on paging--Windows NT treats all memory as virtual. The best way to determine whether your system is paging is to watch Pages/Sec in NT's perfmon. If paging occurs, lower the amount of memory allocated to Oracle.

Hardware

Memory Capacity

Earlier today you saw an overview of how the system hardware operates. Clearly, any operation that must access slower components, such as a disk or network, will slow down processing. Therefore, it is important that you have sufficient memory in your system.

New Term:Most hardware architectures are limited to 4GB of physical memory, but some architectures on the market support much more. These architectures are said to support a VLM, or Very Large Memory, architecture. Soon it will be possible to support hundreds of gigabytes of physical memory in a system, allowing for very fast RDBMS operations.

System memory is allocated to Oracle and used for database caching, user memory, and the shared pool, which is used for both the data dictionary and the library cache. You must have enough memory for the shared pool because an insufficient shared pool can hurt performance. When the shared pool is satisfied, the more database buffers you can allocate to the DBMS the better. Be careful, though, to avoid starving the PGA memory needed by your processes, and avoid paging at all costs. You can never have too much memory in your system. Anything that can be cached will reduce system I/O, improving performance.

Number of CPUs

Oracle typically scales well with additional CPUs. By adding CPUs you can see significant performance improvement with little additional cost. Some factors that determine how much improvement you will see by adding more processors are the CPU cache and memory- bus bandwidth.

CPU Cache

A large CPU cache allows more data and executable code to be stored on the local processor than in memory. This reduces the number of times the CPU must access main memory. Whenever the CPU accesses memory, a slowdown occurs while the CPU waits for that data or code to be retrieved. It is especially bad when the memory bus is busy; the CPU waits even longer until the bus becomes free.

Memory-Bus Bandwidth

The memory-bus bandwidth determines how quickly data can be transferred between CPU to memory. If the memory bus is busy when data or code is needed, a CPU stalls waiting for the bus to free. This can severely degrade performance in a multiprocessor computer. A fast memory bus can reduce this problem. A large CPU cache can also reduce this problem by allowing more data and code to be cached.

I/O Capacity

I/O is typically one of the biggest factors limiting system performance. Because most DBMS operations involve retrieving data from disk, I/O can be a limiting factor if you do not have adequate capacity for your system load. Fortunately, you can usually solve this problem by carefully configuring your system for proper I/O distribution and by having sufficient I/O capacity. Simply having adequate disk space is insufficient; you must also have enough disk drives to support the number of disk I/Os that the system requires.

Better to know some

... than all

Oracle Architecture