Oracle Performance optimization under Linux

Oracle optimization of database memory parameters

Ø Oracle -related system kernel parameters

Ø SGA,PGA parameter setting

Oracle lower disk storage performance Optimization

Ø File System selection (ext2/ext3,xfs,ocfs2)

Ø Oracle ASM storage

1. What to know before optimizing Oracle performance Parameters

1) How large is the physical memory

2) The operating system estimates how much memory to use

3) Whether the database is using a file system or a bare device

4) How many concurrent connections are there?

5) Whether the application is an OLTP type or an OLAP type

2.oracle optimization of database memory parameters

( 1 ) system kernel parameters

To modify the/etc/sysctl.conf file, add the following statement:

Kernel.shmmax = 2147483648

Kernel.shmmni = 4096

Kernel.shmall = 2097152

Kernel.sem = 250 32000 100 128

Fs.file-max = 65536

Net.ipv4.ip_local_port_range = 1024 65000

The parameters are in turn:

Kernel.shmmax: The maximum size, in bytes, of a shared memory segment.

Kernel.shmmni: The maximum number of shared memory segments in the system.

Kernel.shmall: Total amount of shared memory, in pages.

Fs.file-max: The number of file handles that represent the number of files that can be opened on a Linux system.

Net.ipv4.ip_local_port_range: The IPV4 port range that the application can use.

A few problems to be aware of

About Kernel.shmmax

The Oracle SGA is comprised of shared memory, and if the error setting Shmmax may limit the size of the SGA, insufficient shmmax settings can cause the following issues: Ora-27123:unable to attach to shared memory segment, If the parameter is set less than the Oracle SGA setting, the SGA is allocated multiple shared memory segments. This can become a performance burden in a busy system, causing system problems.

Oracle recommends that the Kernel.shmmax be better than the SGA to allow the Oracle Shared memory area SGA to be in a shared memory segment to improve performance.

About Kernel.shmall

Represents the total system shared memory size, in pages.

A 32-bit Linux system, 8G of memory, can be set kernel.shmall = 2097152, that is: 2097152*4k/1024/1024 = 8G that is, there is a total of 8 g of available shared memory, where 4K is the size of a 32-bit operating system page, That is, 4096 bytes.

About Kernel.shmmni

Represents the maximum number of shared memory segments in the system. The system default is 4096, generally no need to modify, under the Sun OS also has the Kernel.shmmin parameter, indicates the shared memory segment minimum size, do not want to mix! (2) settings for the SGA and Pag parameters

A Oracle in the improvements in memory management

Oracle 9i Automatic management of the PGA via parameter pga_aggregate_target parameters Oracle 10g automatically manages the SGA with parameter sga_target parameters,

Oracle 11g enables fully automated management of all memory blocks in the database, making dynamic management of the SGA and PGA a reality.

Two parameters for automatic memory management:

Memory_target: Represents the amount of memory that the entire Oracle instance can use, including the overall size of the PGA and SGA, which is dynamic and dynamically controls the size of the SGA and PGA.

Memory_max_target: This parameter defines the value Memory_target can be reached without restarting the instance, and if no memory_max_target value is set, the default equals the value of Memory_target.

When using dynamic memory management, Sga_target and Pga_aggregate_target represent the minimum settings for their respective memory areas, and for Oracle to have full control over memory management, these two parameters should be set to 0.

B Oracle Five Memory management methods

Ø automatic memory management, i.e. AMM (Automatic memories Management)

Ø Automatic shared memory management, i.e. ASMM (Automatic shared Memories Management)

Ø Manual shared Memory management

Ø Automatic PGA Management

Ø Manual PGA Management

Automatic memory management (AMM)

The default installation oracle11g instance is the Amm method. See below:

Examples are as follows:

Sql> Show Parameters Target  name                                         TYPE                   VALUE---------------------------------     ------------------    ----------------------archive_lag_target                      integer                       0 Db_flashback_ retention_target   integer                    1860 fast_start_io_target                     integer                       0 Fast_start_mttr_target                  integer                        0memory_max_target Big integer 1400M memory_target Big integer 1400MPga_aggregate_target Big integer 0 sga_target Big Integer 0

Note: If the initialization parameter LOCK_SGA = True, then AMM is not available.

Automatic Shared memory management

Automatic shared memory management is oracle10g introduced, if you want to use automatic shared memory management, just set memory_target=0, and then explicitly specify Sga_target.

Examples are as follows:

Sql> alter system set memory_target=0 Scope=both; System altered. Sql> alter system set SGA_TARGET=1024M Scope=both; System altered. Sql>

Manually Shared memory management

Oracle9i and previous versions, only manually set up shared memory management, if you want to use manual shared memory management, you first need to set Sga_target and Memory_target to 0.

The SGA contains the main parameters:

Share_pool_size: Shared pool size, recommended between 300-500m.

Log_buffer: Log buffer size, recommended between 1-3m.

Large_pool_size: Large buffer pool size, non-MTS system, recommended between 20-30m.

Java_pool_size:java pool Size, no Java application is recommended between 10-20m.

Db_cache_size: The size of the data buffer, as large as possible using the memory size.

Automatic PAG Management

The oracle9i version introduces automatic PGA management, and if you are using AMM management, you do not need to worry about the PGA configuration, but if you are not comfortable with AMM management, you can set up automatic PGA management, set

Workarea_size_policy = AUTO

Then specify the pga_aggregate_target size. ，

Manual PAG Management

If you want to achieve precise control of the PGA, you can also set the manual management PGA, set

Workarea_size_policy = Manual

Then specify the PGA-related parameters separately:

PGA Related parameters are:

Sort_area_size

Sort_area_retained_size,

3.Oracle lower disk storage performance Optimization

① Select File system Access data

Selection of File systems

Single file System (Ext2, ext3, XFS, etc.)

Clustered file System (GFS, OCFS2)

File system storage Advantages and disadvantages:

Advantages: Convenient management and maintenance.

Disadvantage: Data read and write to go through the OS-level cache, the efficiency is not very high.

② ASM(Automatic Storage Management)

ASM Benefits:

Data can be read and written directly, without having to access the operating system very high efficiency, read and write efficiency and direct original equipment basically the same.

Oracle provides dedicated management and maintenance tools