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

Today, I will talk to you about how to allocate and tune the memory of oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

I've always wanted to summarize my knowledge of oracle memory tuning. Recently, I happened to optimize a database memory parameter, look up some information and google a lot. Now write it down and make a backup.

I. Overview:

The memory of oracle can be divided into system global area and process global area, namely SGA and PGA (process global area or private global area), from a shared and private point of view. For memory in the SGA region, it is shared globally. On UNIX, shared memory segments (which can be one or more) must be set for oracle, because oracle is multi-process on UNIX, while oracle is single-process (multiple threads) on WINDOWS, so there is no need to set shared memory segments. A PGA is an area that is private to a process (thread). When oracle uses shared server mode (MTS), a part of PGA, that is, UGA, is put into the shared memory large_pool_size.

Send a picture of the composition of the oracle memory architecture, according to the figure above, you can see the key parameters and parameter names at a glance:

For the SGA section, we can see through the query in sqlplus:

SQL > select * from v$sga

NAME VALUE

--

Fixed Size 454032

Variable Size 109051904

Database Buffers 385875968

Redo Buffers 667648

Fixed Size:

Oracle may vary from platform to platform and from version to version, but it is a fixed value for determining the environment, which stores information about the components of the SGA and can be seen as the area that guides the establishment of the SGA.

Variable Size:

Contains memory settings such as shared_pool_size, java_pool_size, large_pool_size, etc.

Database Buffers:

Index according to buffer area:

Db_block_buffer*db_block_size, buffer_pool_keep and buffer_pool_recycle are included in 8i.

Include db_cache_size, db_keep_cache_size, db_recycle_cache_size and db_nk_cache_size in 9i.

Redo Buffers:

Refers to the log buffer, log_buffer. One additional point to note here is that the query values for v$parameter, v$sgastat, and v$sga may be different. The value in v$parameter refers to the user at the beginning

The value set in the initialization parameter file, v$sgastat is the actual log buffer size allocated by oracle (because the allocation value of the buffer is actually discrete and is not allocated in the smallest unit of block)

The query value in v$sga is that after oracle allocates the log buffer, some protection pages are set in order to protect the log buffer. Usually, we will find that the protection page size is about 11k (different environments may be different).

2. Parameters and settings in SGA:

2.1 Log_buffer

Generally speaking, I don't think there is too much advice on setting the size of the log buffer, because after referring to the trigger conditions written by LGWR, we will find that it is usually not very meaningful to exceed 3m. As a formal system

It may be considered to set the log_buffer=3-5m size first, and then adjust it according to the specific situation.

Log_buffer is the buffer of Redo log.

Therefore, it is necessary to understand the trigger event (LGWR) of Redo Log here.

1. When the capacity of redo log buffer reaches 1Compact 3

2. The set time interval for writing redo log arrives, usually 3 seconds.

3. The redo log capacity in redo log buffer reaches 1m.

4. Before DBWn writes the data in the buffer to the data file

5. Each time commit-- commits a transaction.

The above conclusion can be put in other words

1. The cache will be refreshed once the content in the log_buffer reaches 1max 3.

2. The maximum interval is 3 seconds, and the cache is refreshed once.

3. The data in log_buffer reaches 1m and the cache is refreshed once.

4. Commit one "transaction" each time, and refresh the cache once.

2.2 Large_pool_size

For large buffer pool settings, if you do not use MTS, it is recommended that 20-30m is sufficient. This section is mainly used to save some information about parallel queries, and RMAN may be used when backing up.

If MTS is set, because the UGA part is to be moved here, the setting of this part size needs to be taken into account according to the number of server process and the setting of relevant session memory parameters.

2.3 Java_pool_size

If the database does not use JAVA, we usually think that keeping the 10-20m size is sufficient. In fact, it can be less, or even at least 32k, but it depends on the components when you install the database (such as http server).

2.4 Shared_pool_size

The cost of Shared_pool_size should usually be kept within 300m. Unless the system uses a large number of stored procedures, functions, packages

Applications such as oracle erp may reach 500m or higher. So let's assume that a system with 1G memory might consider

If you set this parameter to 100m ~ 2G, consider the system set to 150m ~ 8G, and you can set it to 200-300m.

2.5SGA_MAX_SIZE

The SGA zone includes a variety of buffers and memory pools, most of which can be specified by specific parameters. However, as an expensive resource, the physical memory size of a system is limited.

Although there is no need to relate to the actual physical memory size for CPU memory addressing (more on this later), excessive use of virtual memory leads to page in/out

It will greatly affect the performance of the system, and may even lead to the system crash. So you need to have a parameter to control the maximum size of virtual memory used by SGA, and this parameter is SGA_MAX_SIZE. When the instance is started

Each memory area allocates only the minimum size required by the instance, and then expands their size as needed in the subsequent operation, and their total size is limited by SGA_MAX_SIZE.

For OLTP systems, refer to:

System memory

SGA_MAX_ size value

1G

400-500m

2G

1G

4G

2500M

8G

5G

2.6 PRE_PAGE_SGA

When the oracle instance starts, only the smallest size of each memory area is loaded. Other SGA memory is allocated only as virtual memory.

Only when the process touch to the corresponding page will it be replaced into physical memory. But we may want all the SGA once the instance is started

Are allocated to physical memory. At this point, you can do this by setting the PRE_PAGE_SGA parameter. The default value of this parameter

Is FALSE, that is, all SGA is not put into physical memory. When set to TRUE, instance startup will put all SGA into the physical

In memory. It can enable the instance to start up to its maximum performance state, but it will also take longer to start (because in order for all SGA

It's all in physical memory, and the oracle process needs all the SGA pages of touch).

2.7 LOCK_SGA

To ensure that the SGA is locked in physical memory without having to page in / out, it can be controlled by the parameter LOCK_SGA.

The default value of this parameter is FALSE, and when TRUE is specified, all SGA can be locked in physical memory. That's for sure,

Some systems do not support memory locking, so this parameter is invalid.

2.8 SGA_TARGET

What I'm going to introduce here is a very important parameter introduced in Oracle10g. Before 10g, each memory area of SGA

Need to be specified by their respective parameters, and cannot exceed the value of the specified size of the parameter, although their sum may not be equal to

The maximum limit for SGA was not reached. In addition, once allocated, the memory of each area can only be used by this area and cannot be shared with each other.

Take the two most important memory areas in SGA, Buffer Cache and Shared Pool, which have the greatest impact on the performance of the instance.

But there is such a contradiction: in the case of limited memory resources, sometimes the demand for data by cache is very large.

In order to improve buffer hit, it is necessary to add Buffer Cache, but because SGA is limited, it can only be "grabbed" from other areas-- such as shrinking Shared Pool.

Adding Buffer Cache; and sometimes large chunks of PLSQL code are parsed into memory, resulting in insufficient Shared Pool

There is even a 4031 error and you need to expand the Shared Pool, which may require human intervention to get the memory back from the Buffer Cache.

With this new feature, this memory contradiction in SGA is easily resolved. This feature is called automatic shared memory management.

(Automatic Shared Memory Management ASMM). It is only this parameter SGA_TARGE that controls this feature.

After setting this parameter, you do not need to specify the size for each memory area. SGA_TARGET specifies the maximum amount of memory that SGA can use

On the other hand, the size of each memory in SGA is controlled by Oracle and does not need to be artificially specified. Oracle can adjust the size of each area at any time to reach the system.

The most reasonable size of the best performance state, and controls the sum of them within the value specified by SGA_TARGET. Once a value is assigned to the SGA_TARGET

(the default is 0, that is, ASMM is not started), and the ASMM feature is started automatically.

Third, oracle memory tuning method

When there is a performance problem in the production environment of the project, how can we determine which parameters need to be adjusted?

3.1Check the Library Cache hit rate of ORACLE instances:

Standard: generally greater than 99%

Check method: select 1-(sum (reloads) / sum (pins)) "Library cache Hit Ratio" from v$librarycache

Treatment measures:

If the value of Library cache Hit Ratio is less than 99%, you should increase the size of shared_pool_size. Resize the shared_pool_size by connecting to the database through sqlplus and executing the following command:

SQL > alter system flush shared_pool

SQL > alter system set shared_pool_size= set value scope=spfile

3.2 check the Data Buffer (data buffer) hit rate of the ORACLE instance:

Standard: generally greater than 90%

Check method:

Select 1-(phy.value / (cur.value + con.value)) "HIT RATIO"

From v$sysstat cur, v$sysstat con, v$sysstat phy

Where cur.name ='db block gets'

And con.name = 'consistent gets'

And phy.name = 'physical reads'

Treatment measures:

If the value of HIT RATIO is less than 90%, you should increase the size of db_cache_size. Connect to the database through sqlplus and execute the following command

Resize db_cache_size

SQL > alter system set db_cache_size= set value scope=spfile

Check the Dictionary Cache hit rate of the ORACLE instance:

Standard: generally greater than 95%

Check method:

Select 1-(sum (getmisses) / sum (gets)) "Data Dictionary Hit Ratio"

From v$rowcache

Treatment measures:

If the value of Data Dictionary Hit Ratio is less than 95%, you should increase the size of shared_pool_size. Resize the shared_pool_size by connecting to the database through sqlplus and executing the following command:

SQL > alter system flush shared_pool

SQL > alter system set shared_pool_size= set value scope=spfile

3.4 check the Log Buffer hit rate of the ORACLE instance:

Standard: generally less than 1%

Check method:

Select (req.value * 5000) / entries.value "Ratio"

From v$sysstat req, v$sysstat entries

Where req.name = 'redo log space requests'

And entries.name = 'redo entries'

Treatment measures:

If the Ratio is higher than 1%, increase the size of the log_buffer. Resize the log_buffer by connecting to the database through sqlplus and executing the following command:

SQL > alter system set log_buffer= set value scope=spfile

3.5 check undo_retention:

Standard: the value of undo_retention must be greater than that of max (maxquerylen)

Check method:

Col undo_retention format a30

Select value "undo_retention" from v$parameter where name='undo_retention'

Select max (maxquerylen) From v$undostat Where begin_time > sysdate- (1go 4)

Treatment measures:

If the requirements are not met, you need to increase the value of undo_retention. Connect to the database through sqlplus and execute as ordered below

To resize the undo_retention:

SQL > alter system set undo_retention= set value scope=spfile

Note:

The problem with 32bit and 64bit

For oracle, there are problems of 32bit and 64bit. This problem mainly affects the size of the SGA. Under the database of 32bit, usually oracle can only use no more than 1.7 gigabytes of memory, even though we have 12 gigabytes of memory, we can only use 1.7 gigabytes of memory, which is a great pity. If we install 64bit's database, we can use a lot of memory and it's almost impossible for us to reach the upper limit. However, 64bit's database must be installed on 64bit's operating system. Unfortunately, only 32bit's database can be installed on windows. We can check whether the database is 32bit or 64bit in the following ways

However, under a specific operating system, it may provide a means for us to use more than 1.7 gigabytes of memory to reach more than 2 gigabytes or more.

After reading the above, do you have any further understanding of how to allocate and tune the memory of oracle? If you want to know more knowledge or related content, 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.