This chapter is from the book
This chapter is from the book
This chapter is from the book
Stage 3: Reducing Physical IO
Now that the application demand has been minimized, and contention that might otherwise mask that demand eliminated, we turn our attention to reducing the time spent waiting for IO. In other words, before trying to reduce the time taken for each IO (IO latency), we try to reduce the amount of IO. As it turns out, reducing the amount of IO almost always reduces the IO latency, so attacking the volume of IO first is doubly effective. Having reduced application demand through SQL tuning and other means, we now try to further reduce IO by configuring memory to cache and buffer IO requests.
Most physical IO in an Oracle database occurs either because an application session requests data to satisfy a query or data modification request, because the session must sort or hash data, or must create a temporary segment to support a large join, ORDER BY, or similar operation.
Oracle's shared memory (the SGA) stores copies of data blocks in memory and eliminates the need to perform a disk IO if the requested data block is in that memory. Correctly allocating this memory goes a long way toward minimizing disk IO.
In the past, allocating SGA memory was a hit-and-miss affair. Luckily, in modern Oracle the server can automatically adjust memory allocations for you, or you can measure the effect of adjusting the size of the various memory pools by examining advisories, which accurately predict the effect of changing the sizes of those pools.
Oracle enables you to set up separate memory areas to cache blocks of different size and also enables you to nominate specific areas to cache data that might need to be kept in memory. Not all these memory areas will be automatically and dynamically resized by Oracle, and Oracle will not automatically allocate the areas in the first place, or assign specific data segments to these areas; those tasks are left to the DBA.
In addition to disk reads when accessing data not in the shared memory, Oracle might perform substantial IO when sorting or hashing data during ordering, grouping, or join operations. Where possible, Oracle performs a sort or hash operation in memory within the area of memory allocated for program use—the Program Global Area (PGA). However, if sufficient memory is not available, Oracle writes to—and reads from—temporary segments on disk to complete the sort or hash operation.
Oracle has improved its capability to automatically manage these memory areas in most recent releases. As of 10g, Oracle automatically resizes allocations within the PGA and the SGA, but will not shift memory between these areas. In 11g Oracle can move memory between the PGA and SGA as required—or at least, as Oracle calculates is required.
Despite the progress in automatic memory management, there's still a lot for the Oracle administrator to do to ensure optimal performance. These tasks include
- Determining whether the correct amount of OS memory is available to Oracle
- Determining the correct division of memory between the PGA and SGA, or—in 11g—allowing Oracle's Automatic Memory Management to make that determination
- Fine-tuning the allocation of segments to specific memory areas
- Fine-tuning the parameters controlling sorting and joining
- Monitoring Oracle's memory allocations and over-riding if necessary
Chapters 18–20 cover these memory optimization techniques.