Syetem Global Area (SGA)
Which SGA structures are required, and which are optional? The database buffer cache, log buffer, and shared pool are required; the large pool, Java pool, and Streams pool are optional.
Determining the optimal size of the database buffer cache is application specific and a matter of performance tuning. It is impossible to give anything but the vaguest guidelines without making observations, but it is probably true to say that the majority of databases will operate well with a cache sized in hundreds of megabytes up to a few gigabytes. Very few applications will perform well with a cache smaller than this, and not many will need a cache of hundreds of gigabytes.
The size of the database buffer cache can be adjusted dynamically, and can be automatically managed.
Raising the log buffer size above the default may be necessary for some applications, but as a rule start tuning with the log buffer on default.
If redo generation is the limiting factor in a database’s performance, the only option is to go to RAC. In a RAC database, each instance has its own log buffer, and its own LGWR. This is the only way to parallelize writing redo data to disk.
The size of the log buffer is static, fixed at instance startup. It cannot be automatically managed.
The Shared Pool:
■ The library cache
■ The data dictionary cache
■ The PL/SQL area
■ The SQL query and PL/SQL function result caches
The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. The slightest difference (even something as trivial as SELECT instead of select) means that the statement will not match but will be parsed again.
The data dictionary cache is sometimes referred to as the row cache. Whichever term you prefer, it stores recently used object definitions: descriptions of tables, indexes, users, and other metadata definitions.
Shared pool tuning is usually oriented toward making sure that the library cache is the right size. This is because the algorithms Oracle uses to allocate memory in the SGA are designed to favor the dictionary cache, so if the library cache is correct, then the dictionary cache will already be correct.
PL/SQL can be issued from user processes, rather than being stored in the data dictionary. This is called anonymous PL/SQL. Anonymous PL/SQL cannot be cached and reused but must compiled dynamically. It will therefore always perform worse than stored PL/SQL. Developers should be encouraged to convert all anonymous PL/SQL into stored PL/SQL.
By default, use of the SQL query and PL/SQL function result cache is disabled, but if enabled programmatically, it can often dramatically improve performance. The cache is within the shared pool: unlike the other memory areas described previously, it does afford the DBA some control: he can specify a maximum size.
Determining the optimal size is a matter for performance tuning, but it is probably safe to say that most databases will need a shared pool of several hundred megabytes. Some applications will need one of more than a gigabyte, and very few will perform adequately with less than a hundred megabytes.
The shared pool size is dynamic and can be automatically managed.
Sizing the large pool is not a matter for performance. If a process needs large pool of memory, it will fail with an error if that memory is not available. Allocating more memory than is needed will not make statements run faster.
The large pool size is dynamic and can be automatically managed.
The Java pool is only required if your application is going to run Java-stored procedures within the database: it is used for the heap space needed to instantiate the Java objects. Java code is not cached in the Java pool: it is cached in the shared pool, in the same way that PL/SQL code is cached.
The Java pool size is dynamic and can be automatically managed.
The Streams pool size is dynamic and can be automatically managed.
Show the current, maximum, and minimum sizes of the SGA components that can be dynamically resized:
select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE
from v$sga_dynamic_components;
Determine how much memory has been, and is currently, allocated to program global areas:
select name,value from v$pgastat
where name in ('maximum PGA allocated','total PGA allocated');
SQL> select * from v$sga;
Fixed Size 2225576
Variable Size 247466584
Database Buffers 58720256
Redo Buffers 4747264
SQL> show sga;
Total System Global Area 313159680 bytes
Fixed Size 2225576 bytes
Variable Size 247466584 bytes
Database Buffers 58720256 bytes
Redo Buffers 4747264 bytes
SQL>
No comments:
Post a Comment