Five, Oracle performance optimization under Linux
Optimization of Oracle Database memory parameters Oracle-related system kernel parametersSGA, PGA parameter settings Oracle down-disk storage performance Optimization file System Selection (EXT2/EXT3, XFS, OCFS2)Oracle ASM Storage1. 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
Optimization of 2.oracle 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 = 32000 Fs.file-max = 65536 Net.ipv4.ip_local_port_range = 1024x768 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 made up 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 Segm ENT, if the parameter is set less than the Oracle SGA setting, then the SGA will be 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 improvements in memory management for Oracle 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)manually shared memory managementAutomated PGA ManagementManual PGA ManagementAutomatic memory Management (AMM) The default installation oracle11g instance is the Amm method. See below: examples are as follows: sql> Show Parameters targetNAME TYPE VALUE-------------------------------------------------------------------------archive_lag_target Integer 0db_flashback_retention_target Integer 1860fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target Big integer 1400Mmemory_target Big integer 1400Mpga_aggregate_target Big integer 0sga_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 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
Linux Server performance and optimization (ii)
