SQL-Memory-Management-Oracle9i

Server Process 2 PGA Metadata and SQL execution plans
...
Server Process N PGA
Variable: Others Database Buffer Cachee Oracle instance allocates all the shared data & control structures
The amount of memory allocated by these operators affects their performance Challenge for DB systems is to design a fair and efficient strategy to manage this memory Database systems rely on DBA to supply an optimal solution for Memory allocation
Improve the performance
Maximize throughput and make the operators dynamically adapt their memory usage 4
Execution Modes
Optimal execution: everything is in memory The entire SQL operation run in memory Work area size = Cash size One-pass execution: not enough memory Extra pass has to be performed over the input data Response time increases Work area size < Cash size Multi-pass execution: minimum memory Multiple passes over the input data are required Response time dramatically increases Work area size < One-pass threshold
7
Oracle9i Memory Model
Aggregated PGA Memory
Dynamic SGA
Most recently accessed DB blocks Maximum SGA Unused Fixed – General DB Info Shared Pool For changing the Max, DB must be started up
Contents
Problem Definition Motivation and Goal Memory Management Approaches Oracle9i Memory Model Automatic SQL Memory Management Experimental Results Conclusion and Discussion
10
Expected Work Area Size: F ( x )
X ≥ Minimum Memory Requirement X ≤ Cash Requirement If (Minimum Memory Requirement < Bound < Cash Requirement) Then X = Bound [ ! Sort ] Operator is running serial: X ≤ 5% Memory Target Operator is running parallel: X ≤ 30% Memory Target Work area is parallel (Parallel Sort): X = X * Degree of Parallelism
Prevent one-pass or multi-pass over the input data to some extend
Modify the manageability
Relieve the DBA from his role of finding optimal solution for memory allocation
SQL Memory Management in Oracle9i
Benoit Dageville and Mohamed Zait Oracle Corporation
Presenter: Mehrdad Nojoumian
May 15, 2011
David R. Cheriton School of Computer Science
Redo Log Buffers Process Global Areas System Global Areas
8
Some Definitions
The memory may end up being: Under-utilized: The settings are based on pessimistic assumption about the work area Over-allocated: The DBA makes mistakes or under-estimates the work area
SGA
New
SQL1
Global Memory Bound
SQL2
...
SQLn
Local Memory Manager
PGA Memory Usage Statistics
Workarea Profile Active Workarea Table
WA1 HJ WA2 Sort WA3 BM
Current GMB
6
Commercial DB Systems
Three major criteria can be used to classify DB systems regarding memory management: 1. Initial work area size of an operator Computed based on dynamic factors:
Server Process 1 A private memory region allocated by Oracle when a DB server process is created PGA “Advice Statistics” base on the workload
Buffer Cache Library Cache Java Pool Large Pool Components can exchange memory within themselves
9
Automatic SQL Memory Management
PGA
1st Step: pga_aggregate_target set by the DBA 2nd Step: Target SQL Memory Auto Target +10% , -10% new comers and existing operators
3
Motivation & Goal
Oracle9i uses an automatic and adaptive approach to manage memory for DB operators Memory allocation works based on the operation’s need and system workload Memory management applies a fair policy to all operators, regardless of the time of entry Minimize response time
2
Problem Definition
Complex queries require the use of memory-intensive operators: Sort and Hash-join These operators need memory in order to process their input data A finite amount of memory is available, shared by all concurrent operators
Work area profile: piece of metadata which describes all the characteristics of a work area Type: sort, hash-join, group-by Current memory requirement Number of instances of that work area Amount of the PGA memory currently used by this work area
Application workload & input data properties
Computed based on static factors:
Configuration parameters set by the DBA
2. Work area size during execution Is operator able to adapt its memory consumption? 3. Response to memory pressure How does the system respond when demands for memory cannot be satisfied? Does it reduce the memory usage, queue new operators, or make the new query fail?