21-02-2011, 11:50 AM
SQL Memory Management in Oracle9i
Abstract
Complex database queries require the use ofmemory-intensive operators like sort and hashjoin.Those operators need memory, also referredto as SQL memory, to process their input data.For example, a sort operator uses a work area toperform the in-memory sort of a set of rows. Theamount of memory allocated by these operatorsgreatly affects their performance. However, thereis only a finite amount of memory available in thesystem, shared by all concurrent operators. Thechallenge for database systems is to design a fairand efficient strategy to manage this memory.Commercial database systems rely on databaseadministrators (DBA) to supply an optimal settingfor configuration parameters that are internallyused to decide how much memory toallocate to a given database operator. However,database systems continue to be deployed in newareas, e.g, e-commerce, and the database applicationsare increasingly complex, e.g, to providemore functionality, and support more users. Oneimportant consequence is that the applicationworkload is very hard, if not impossible, to predict.So, expecting a DBA to find an optimalvalue for memory configuration parameters is notrealistic. The values can only be optimal for alimited period of time while the workload iswithin the assumed range.Ideally, the optimal value should adapt inresponse to variations in the application workload.Several research projects addressed thisproblem in the past, but very few commercialsystems proposed a comprehensive solution tomanaging memory used by SQL operators in adatabase application with a variable workload.This paper presents a new model used inOracle9i to manage memory for database operators.This approach is automatic, adaptive androbust. We will present the architecture of thememory manager, the internal algorithms, anda performance study showing its superiority.
1. Introduction
Queries in On-Line Analytical Processing (OLAP)applications and Decision-Support Systems (DSS) tendto be very complex: join many tables, and process largeamounts of data. They make heavy use of SQL operatorssuch as sort and hash join. The sort is used not onlyto produce the input rows in sorted order but also as thebasis in other operators, e.g, grouping, duplicate elimination,rollup, analytic functions, and index creation. Inthe rest of the paper, the term “SQL operators” is used toexclusively refer to memory-intensive operators, e.g.nestedloops join is excluded.Those operators need memory space to process theirinput data. For example, a sort operator uses a work areato perform the in-memory sort of a set of rows. Similarly,a hash-join operator uses a work area to build ahash table on its left input (called build input). Generally,larger work areas can significantly improve the performanceof a particular operator. Ideally, the size of awork area is big enough such that it can accommodatethe input data and auxiliary memory structures allocatedby the operator. This is referred to as the cache size of awork area. When the size of the work area is smallerthan cache, the response time increases since an extra pass is performed over all or part of the input data. This isreferred to as the one-pass size of the work area. When thework area size is less than the one-pass threshold, multiplepasses over the input data are needed, causing dramaticincrease of the operator response time. This is referred toas the multi-pass size of the work area. For example, a sortoperation which needs to sort 10GB of data needs a littlemore than 10GB of memory to run in cache mode and atleast 40MB to run in one-pass mode. It will run in multipassmode with less than 40MB. In On-Line Transaction Processing (OLTP) systems, thesize of input data to SQL operators is generally small,thus, they run in cache mode most of the time. This is notthe case in OLAP or DSS, where the input data is verylarge. Thus, it is important to properly size their work areain order to obtain good response time for the queries, maximizethe usage of the hardware resources, and be fair inallocating them to competing operators.In most commercial systems the burden has been put onthe DBA to provide an optimal setting for configurationparameters that are internally used to decide how muchmemory to allocate to a given database operator. This is achallenging task for the DBA because it’s difficult to estimatememory utilization for an operator work area, for aquery, and the database system. The operator work areasize depends on the size of the operator input data. Thememory utilization of a query depends on the operatorsscheduling and the number of parallel processes assignedto the query, while the memory utilization in a databasesystem depends on the current workload. Most probably,the memory will either end up being under-utilized (if thesettings are based on pessimistic assumptions about theworkload) or over-allocated (if the DBA makes mistakesor under-estimates the workload). Generally, the DBAtries to avoid over-allocation by assuming the worst workloadin order to avoid paging (with dramatic degradation inperformance) or query failure. The challenge for databasesystems is to design a fair and efficient strategy to managethis memory: allocate enough memory to each operation tominimize response time, but not too much memory so thatother operators can receive their share of memory as well.In Oracle9i, we introduced a new memory manager thatdynamically adapts the memory allocation based on theoperation’s need and the system workload. This improvesboth manageability and performance. The manageabilityis improved by relieving the DBA from his “role” of findingoptimal values for memory configuration parameters.The performance is improved by allocating the memory tooperators to maximize throughput and make the operatorsdynamically adapt their memory consumption to respondto changes in the workload.Section 2 presents an overview of related works in commercialsystems. In Section 3, we give an overview of theOracle database system memory model, and in Section 4we present the new memory manager for database operators,including the architecture and algorithms. In Section5, we discuss the memory advisor component. Section 6presents the results of a performance study that validatesand shows the superiority of our approach. Section 7 concludesthe paper.
2. Related Work
In this section we analyze the approaches to SQL memorymanagement and classify commercial database systemsbased on the most important features of a memory man-
download full report
http://citeseerx.ist.psu.edu/viewdoc/dow...1&type=pdf
