Shulou(Shulou.com)06/01 Report--

How to set the parameters of oracle SGA, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Parameter setting of oracle SGA

SGA (system global area system global area): used to store operational data (Buffer Cache Size),

Memory area of control information such as library cache (library cache), data dictionary (data dictionary cache), etc.

Most of the memory in SGA is shared.

There are at least three components in SGA:

■ The database buffer cache

■ The log buffer

■ The shared pool

The following are optional components:

■ A large pool

■ A Java pool

■ A Streams pool

The Shared Pool (shared pool):

Shared pools are the most complex components in SGA and can be divided into dozens of substructures.

Some of the following components are more important

■ The library cache

■ The data dictionary cache

■ The PL/SQL area

■ The SQL query and PL/SQL function result caches

Oracle provides several initialization parameters to set the memory size of each part of the SGA

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 244M

Controls the size of the shared pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter java_pool_size

NAME TYPE VALUE

-

Java_pool_size big integer 52M

Controls the size of the JAVA pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter large_pool_size

NAME TYPE VALUE

-

Large_pool_size big integer 60M

Control the size of the large pool, which can be adjusted dynamically during the operation of the instance.

SQL > show parameter streams_pool_size

NAME TYPE VALUE

-

Streams_pool_size big integer 0

Control the size of the flow pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter db_cache_size

NAME TYPE VALUE

-

Db_cache_size big integer 160M

Controls the size of the data buffer, which can be adjusted dynamically while the instance is running

SQL > show parameter log_buffer

NAME TYPE VALUE

-

Log_buffer integer 5603328

Controls the size of the log buffer, which cannot be modified during operation.

SQL > show parameter sga_target

NAME TYPE VALUE

-

Sga_target big integer 0

Related to the automatic management of SGA. If the value is zero, DBA is required to manage SGA manually.

SQL > show parameter sga_max_size

NAME TYPE VALUE

-

Sga_max_size big integer 524M

The maximum memory that can be used by SGA. The value of sga_target cannot exceed sga_max_size, which is composed of parts of SGA.

When allocating memory, the total memory of SGA cannot exceed the value set by sga_max_size, otherwise an error will be reported. Sga_max_size

It cannot be adjusted dynamically while the instance is running. You must first modify the initialization parameter file, and then restart the instance.

The sga_max_size parameter is valid only when the instance is running. If the parameter value of each part of the SGA is found when the instance is started,

If the sum is greater than sga_max_size, the instance ignores the currently set sga_max_ size value and modifies the sga_max_size to

The maximum of the current SGA.

SQL > show parameter statistics_level

NAME TYPE VALUE

-

Statistics_level string TYPICAL

This value is related to the automatic management of SGA memory. If the value is typical or all, oracle will collect more memory according to the

The relevant statistics are used as a reference to determine how to allocate the memory of each part of the SGA.

All parts of memory in oracle SGA are allocated and reclaimed at a granularity. The size of granularity cannot be set by DBA.

With the size of SGA, the type of system platform.

On most platforms, the SGA1G granularity is 16MB.

Under the win32 platform, SGA > 1G, the granularity is 8MB.

When setting the value of each part of oracleSGA, the integer multiple of oracle granularity is needed, otherwise oracle will adjust automatically.

SQL > select component,granule_size

2 from v$sga_dynamic_components

COMPONENT GRANULE_SIZE

-

Shared pool 4194304

Large pool 4194304

Java pool 4194304

Streams pool 4194304

DEFAULT buffer cache 4194304

KEEP buffer cache 4194304

RECYCLE buffer cache 4194304

DEFAULT 2K buffer cache 4194304

DEFAULT 4K buffer cache 4194304

DEFAULT 8K buffer cache 4194304

DEFAULT 16K buffer cache 4194304

DEFAULT 32K buffer cache 4194304

Shared IO Pool 4194304

ASM Buffer Cache 4194304

14 rows selected

The view of the particle size value of each part of oracle SGA.

SQL > select name,bytes from v$sgainfo

NAME BYTES

Fixed SGA Size 1375820 stores status information for databases and instances

Redo Buffers 5795840

Buffer Cache Size 167772160

Shared Pool Size 255852544

Data dictionary cache 62914560

Java Pool Size 54525952

Streams Pool Size 0

Shared IO Pool Size 0

Granule Size 4194304

Maximum SGA Size 548237312

Startup overhead in Shared Pool 58720256

Free SGA Memory Available 0

View the memory allocation of each part of the SGA.

SQL > select * from v$sga

NAME VALUE

--

Fixed Size 1375820

Variable Size 373293492 includes shared pool, large pool, java pool and stream pool.

Database Buffers 167772160 data buffer (Buffer Cache Size)

Redo Buffers 5795840

View the memory allocation of each part of the SGA.

The memory used by each part of the SGA can only be used for its own use and cannot be shared with each other. If Java Pool is assigned

1 gigabyte of memory, but the current database does not use the flow feature, and the space allocated to the stream pool cannot be used by other SGA

Components are used. So when using alter system set to set up the memory of each part of SGA, you need to be careful.

Set so as not to cause waste of system memory and loss of performance.

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 244M

SQL > alter system set shared_pool_size=230m

System altered

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 232M

Oracle automatically adjusts to granularity (in this case, 4MB) according to granularity.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.