resize sga oracle 11g

SGA_TARGET vs SGA_MAX_SIZE

SGA_MAX_SIZE

sga_max_size sets the maximum value for sga_target
If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET

This parameter is new with Oracle 10g. It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.
This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.

SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
Parameter description:
SGA_TARGET
Property Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes

SGA_TARGET provides the following:

• Single parameter for total SGA size
• Automatically sizes SGA components
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• STATISTICS_LEVEL must be set to TYPICAL

By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)
• SHARED_POOL_SIZE (Shared Pool)
• LARGE_POOL_SIZE (Large Pool)
• JAVA_POOL_SIZE (Java Pool)

Enable SGA_TARGET

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 600M

As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=500m;
System altered.

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ -----------

sga_target big integer 500

Resize SGA_TARGET

• SGA_TARGET is dynamic
• Can be increased till SGA_MAX_SIZE
• Can be reduced till some component reaches minimum size
• Change in value of SGA_TARGET affects only automatically sized components

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_max_size big integer 1G

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_target big integer 1G

WE can resize it to only 1G if we will try to increase it from 2G we will get error.

SQL> alter system set sga_target=2G;
alter system set sga_target=2G *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
For that we must first increase our SGA_MAX_SIZE parameter value.But we must restart out instance because its STATIC parameter.

SQL> alter system set sga_max_size=2G scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes

Fixed Size 1337492 bytes
Variable Size 624953196 bytes
Database Buffers 369098752 bytes
Redo Buffers 4800512 bytes
Database mounted.
Database opened.

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_max_size big integer 2G

SQL> alter system set sga_target=1900m;
System altered.

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.
SQL> alter system set sga_target=0;
System altered.

[Reference Link: http://it-toolkit.blogspot.com/2013/08/resize-sga-oracle-11g.html]

PRACTICAL EXERCISE:

SQL> show parameter db_cache_size log_buffer shared_pool_size large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 4G
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 5242880
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 4G
SQL> show parameter large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 128M
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system db_cache_size=2500M scope=both;
alter system db_cache_size=2500M scope=both
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set db_cache_size=2500M scope=both;
alter system set db_cache_size=2500M scope=both
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system db_cache_size=2500M scope=both sid='stg4cus1x02';
alter system db_cache_size=2500M scope=both sid='stg4cus1x02'
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> alter system set db_cache_size=2500M scope=both sid='stg4cus1x02';

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system set shared_pool_size=2500M scope=both sid='stg4cus1x02';

System altered.

SQL> alter system set sga_target=6000M sid='stg4cus1x02';

System altered.

SQL> alter system set sga_max_size=6000M sid='stg4cus1x02';
alter system set sga_max_size=6000M sid='stg4cus1x02'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=6000M sid='stg4cus1x02' scope=spfile;

System altered.

SQL> startup force;
ORA-00823: Specified value of sga_target greater than sga_max_size

SQL>

SOLUTION:

SQL> show parameter db_cache_size log_buffer shared_pool_size large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 4G
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 5242880
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 4G
SQL> show parameter large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 128M
SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system set db_cache_size=2500M scope=both sid='stg4cus1x02';

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system set shared_pool_size=2500M scope=both sid='stg4cus1x02';

System altered.

SQL> alter system set sga_target=6000M sid='stg4cus1x02';

System altered.

SQL> alter system set sga_max_size=6200M sid='stg4cus1x02' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 6480490496 bytes
Fixed Size 2265112 bytes
Variable Size 3254783976 bytes
Database Buffers 3204448256 bytes
Redo Buffers 18993152 bytes
Database mounted.
Database opened.

Total System Global Area 6480490496 bytes
Fixed Size 2265112 bytes
Variable Size 3254783976 bytes
Database Buffers 3204448256 bytes
Redo Buffers 18993152 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> SHOW PARAMETER SGA

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6208M
sga_target big integer 6016M
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 2528M
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 2528M
SQL> show parameter large_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 128M
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 5242880
SQL>

http://www.akadia.com/services/ora_asm_multiple_block_sizes.html

How to combine Automatic Memory Management
(AMM) with manually sized SGA

Martin Zahn, Akadia AG
Information Technology, CH-3672 Oberdiessbach

15.09.2007

Overview

If you have one or more very wide tables with more than 255 columns you may encounter massive row chaining on this table. All other tables are small, so the standard blocksize is 4K or 8K, but for this extra table it can be advantageous to have a blocksize of 16K or even 32K to avoid the row chaining.

Oracle Database 10 automates the management of shared memory (AMM) used by an instance and liberates administrators from having to manually configure the sizes of shared memory components.

So the question is - how can we combine AMM with a manually sized SGA.

Oracle SGA

The Oracle system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior.

Simply stated, the system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior. The following figure gives a brief synopsis of the particular components of the SGA. Note, that the blue components are managed by AMM, whereas the red component is manually managed.

What can be seen from this figure, is that there are many options available to setup the SGA and without a complete understanding of what the applications are doing in the background, the ability to guess the appropriate amount of memory to give each of these individual components is not always optimal. AMM can solve this complex configuration part - but gives the possibility to size an extra, manually sized buffer cache for objects within the extra tablespace. In our example, we used 16K, but Oracle offers additional blocksizes of 2K, 4k, 8k, 16k and 32K.

Components of the SGA

SGAComponent

Size controlled by

Areas of Influence

Description

Shared
Pool

SHARED_POOL_SIZE

Library Cache

- Shared SQL areas- Private SQL areas- PL/SQL procedures
   and packages- Various control
   structures

Oracle needs to allocate & deallocate memory as SQL or procedural code is executed based on the individual needs of users' sessions and in accordance to the LRU algorithm.

Dictionary Cache

- Row cache- Library cache

Highly accessed memory structures that provide information on object structures to SQL statements being parsed.

Java
Pool

JAVA_POOL_SIZE

- Run state- Methods- Classes- Session code- Data in JVM

Memory available for the Java memory manager to use for all things Java.

Streams
Pool

STREAMS_POOL_SIZE

- Stream activity

New to Oracle 10g, memory available for stream processing.

Redo Log Buffer

LOG_BUFFER

- Redo entries

Holds changes made to data and allows for the reconstruction of data in the case of failure.

Database
Buffer
Cache

DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE

- Write list- LRU list

Holds copies of data requested by SQL and reduces requests to disk by having data in memory. You may have many different buffer caches that help segregate on usage patterns.

Large
Pool

LARGE_POOL_SIZE

- Shared server- Oracle XA- I/O server
   processes- Backup & restore

For large memory allocations.

You can look at the size of your SGA by looking at the initialization parameters that control its size. Here is a simple query and its output.

Automatically managed SGA

There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter in the INIT.ORA.

# Using Automatic SGA Management

sga_target        = 300M
sga_max_size      = 400M
shared_pool_size = 0
java_pool_size    = 0
large_pool_size   = 0

Multiple Block Sizes

Oracle supports multiple block sizes as follows.

DB_BLOCK_SIZE It has a standard block size, as set by the DB_BLOCK_SIZEinitialization parameter. The standard block size is used for the SYSTEM tablespace and most other tablespaces.

The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If not specified, the default data block size is operating system specific, and is generally adequate.

The block size cannot be changed after database creation, except by re-creating the database. If a database's block size is different from the operating system block size, make the database block size a multiple of the operating system's block size.

For example, if your operating system's block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:

DB_BLOCK_SIZE = 4096

Non Standard
Block Sizes
Additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.

Tablespaces of non-standard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These non-standard block sizes can have any power-of-two value between 2K and 32K: specifically, 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.

To use non-standard block sizes, you must configure sub-caches within the buffer cache area of the SGA memory for all of the non-standard block sizes that you intend to use

Configure sub-caches for non-standard block sizes

If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZEand at least one DB_nK_CACHE_SIZE parameter set. Oracle assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.

The DB_CACHE_SIZE initialization parameter replaces the DB_BLOCK_BUFFERS initialization parameter that was used in previous releases. The DB_CACHE_SIZE parameter specifies the size of the cache of standard block size buffers, where the standard block size is specified by DB_BLOCK_SIZE. However, this have only be done if you don't use AMM.

The sizes and numbers of non-standard block size buffers are specified by the following initialization parameters:

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Each parameter specifies the size of the buffer cache for the corresponding block size.

INIT.ORA example

DB_BLOCK_SIZE = 4096
DB_CACHE_SIZE = 12M
DB_2K_CACHE_SIZE = 8M
DB_8K_CACHE_SIZE = 4M

In the above example, the parameters specify that the standard block size of the database will be 4K. The size of the cache of standard block size buffers will be 12M. Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively.

Note:

These parameters cannot be used to size the cache for the standard block size. For example, if the value of DB_BLOCK_SIZE is 2K, it is illegal to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE.

Example

Database 10.2.0.3, Standard Blocksize with 4K, automatically managed SGA (AMM) but with extra tablespace with 16K Blocksize for very wide tables.

Setup INIT.ORA

### Basic Configuration Parameters
### ------------------------------
compatible                        = 10.2.0
db_block_size                     = 4096

### Database Buffer Cache, I/O
### --------------------------
# The Parameter SGA_TARGET enables Automatic Shared Memory Management
# Using Automatic SGA Management

sga_target                        = 300M
sga_max_size                      = 400M
shared_pool_size                  = 0
java_pool_size                    = 0
large_pool_size                   = 0
db_16k_cache_size                 = 50M

Creating the tablespace with nonstandard block sizes

CREATE TABLESPACE wide_tab
DATAFILE '/u01/oracle/db/AKI1/tab/wide_tab.dbf' SIZE 50M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K
PERMANENT
ONLINE;

Check Sizes in Oracle Enterprise Manager

You can see, the manually configured 16K buffer cache and all other automatically sized components.

Oracle PLSQL Programming and Oracle DBA

Tuesday, 9 August 2016