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

Oracle commonly used tuning methods, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The optimization of Oracle database application system mainly includes ten aspects:

(1)Optimize database memory;

(2)Fix application code in Oracle shared pools;

(3)Optimize data storage;

(4)technology for optimizing data sorting;

(5)optimize SQL statements;

(6)optimizing the back-off section;

(7)optimizing the index;

(8)Optimize disk I/O;

(9)periodically generating state statistics of database objects;

(10)Optimize the operating system environment.

The essence is to reduce CPU load and improve I/O performance.

1. Disk I/O

The role of database is to realize the management and query of data, so there must be a large number of read and write operations on data, and its I/O problems are often an important reason for Oracle database performance problems.

1.1 I/O generation in Oracle

1.2 Optimize OS storage

1. In UNIT environment, the storage device using Raw Device as Oracle data file has higher reading and writing efficiency than File System.

Second, asynchronous IO (Asynchronous IO) mode is adopted. In asynchronous IO mode, the process does not have to wait for the IO to complete after issuing the IO request, and can handle other things; the IO request is placed in a queue, and once the IO is completed, the system will signal the process. Asynchronous IO enables write-intensive Oracle processes, such as DBWn processes, to queue IO requests to take full advantage of hardware IO bandwidth, allowing them to maximize parallel processing. After confirming that the operating system has been set to support AIO, you also need to set the Oracle initialization parameter DISK_ASYNCH_IO to true to support asynchronous IO.

III. Disk Load Balancer and Striping. Striping is the technique of dividing data into small parts and storing them in different areas on different disks. This enables multiple processes to access multiple different parts of the data simultaneously without causing disk conflicts. Many operating systems, disk equipment vendors, and various third-party software can do striping. With striping, DBAs can easily do IO Load Balancer without manual configuration.

1.3 Optimize IO configuration

1. Use LVM (Logical Volume Manager) software to reasonably configure the width and depth of the stripe.

Second, the use of separate file strategy to avoid disk hot spots. Even though we have disk Load Balancer at the hardware and OS levels through disk striping, we still cannot prevent certain data files from becoming "hot files."

Separate table, index and temporary table space storage, that is, establish separate data table space, index table space and temporary table space for application system.

Detach the Redo Log file. If the IO throughput rate of Redo Log file is high, Redo Log should be stored on a separate disk. If there are enough disks, Redo Log file should be striped and distributed to multiple disks.

Separate Archive Log files. When an ORACLE database is running in archive mode, the archive process (ARCn ) inevitably generates a large number of disk reads and writes. Archive log files should be striped across multiple disks for separate storage.

1.4 Optimize parameter settings related to ORACLE I/O

db_file_multiblock_read_count: Controls the maximum number of blocks read by one I/O operation in a fully continuous scan. default value of 8

db_writer_processes: The initial number of database "write processes."

disk_asynch_io: Used to control whether I/O for data files, control files, and log files is asynchronous. This parameter can only be changed if the platform supports disk asynchronous I/O.

log_archive_max_processes: Specifies the number of ARCH processes in archive mode.

2. optimize data storage

2.1 Tablespace optimization

SYSTEM table space is used to store information related to ORACLE system. Objects created by ordinary users should not be placed in SYSTEM table space. In addition, attention should be paid to increasing the appropriate size of SYSTEM table space to ensure that there is about 100M free space.

For ORACLE database application systems, independent ORACLE user (scheme), data table space, index table space, temporary table space should be established for the application system. Tablespaces and data files should be set to appropriate sizes at one time to avoid automatic growth of data files, resulting in discontinuity of data segment blocks/segments and affecting system performance.

Periodically check database table space usage to prevent excessive table space fragmentation that affects system performance. Query the view dba_extents for detailed table space usage.

Defragment table space and reclaim free space for data table segments.

Merging table space debris

alter tablespace tablespacename coalesce;

Reclaim free space in data table segments

alter table tablename deallocate unused;

2.2 Reasonable configuration of rollback segment size

After Oracle 9i, the system adopted transparent localization management mode. By default, the rollback segment table space is relatively small, which often cannot meet the needs of handling large transactions in practical applications, so it is necessary to establish large rollback segments for special large applications or transactions.

2.3 Temporary Tablespace Design Planning

Temporary tablespaces are mainly used for distinct, union, order by and create index operations in query operations and for storing temporary table data. Oracle default table space is Temp, its size is 1MB, for a real medium and large application system is far from enough, so you need to do the following work:

Increase the Temp table space to a suitable size, typically around 300M-500M.

When creating users, you should select a dedicated temporary table space for them.

A large temporary table space should be established for large application systems to store temporary table data for monthly, quarterly and annual reports of the system.

2.4 Store data files and log files on separate disks

Data file writing is achieved through DBWR background process, log file writing is achieved through LGWR background process, because log file is written continuously, so there is no concurrent processing phenomenon. Data files are written relatively randomly, and to avoid DBWR and LGWR collisions at the same time, log files and data files should be placed on different hard disks.

In addition, conflicts between log file writing disks and log archiving may occur in ARCHIVELOG mode, which can only be avoided by distributing log files to multiple disks.

3. Optimize the operating system environment

3.1 Configure the appropriate semaphore for the operating system

Oracle requires appropriate operating system semaphores to run under certain Unix operating system environments. This should be set according to the requirements of Oracle release, for example, in SOLARIS environment, you need to log in as root and modify the system file in/etc directory according to the parameters of Oracle installation manual.

3.2 Configure an appropriately sized exchange area

In UNIX operating system environments, swap areas are a basic requirement of Oracle. This can be determined by Oracle's release requirements. It is recommended that the swap area be more than twice the size of the server's memory.

3.3 Configure the operating system Enable asynchronous I/O

Most operating systems today support asynchronous I/O, but Oracle must be configured to run in asynchronous I/O mode. For example, under HP-UNIX, you need to authorize "/dev /async" to the oracle user of the operating system and modify the Oracle parameter disk_asynch_io=true.

3.4 Lock Oracle's SGA in physical memory.

Almost all operating systems support virtual memory, so even if we use less memory than physical memory, we can't prevent the operating system from swapping SGA into virtual memory (SWAP). By locking Oracle SGA in physical memory to avoid being swapped into virtual memory, we can reduce page swapping in and out, thus improving performance.

HP-UNIX configuration method:

#/etc/setprivgrp dba MLOCK

Adjust ORACLE parameters: lock_sga=TRUE

restart the database

Configuration under AIX 5L (AIX 4.3.3):

$/usr/sbin/vmo -r -o v_pinshm=1 (or vmtune -s 1)

Adjust ORACLE parameters: lock_sga=TRUE

restart the database

3.5 Control memory swapping operations (Paging)

A large number of memory swap operations can greatly affect system performance, especially when database files are created on the file system. In this case frequently accessed data exists both in the SGA and in the file cache. This situation where the same data is cached twice in memory will reduce the efficiency of memory usage, resulting in frequent memory swap operations, causing I/O bottlenecks in the system and reducing the performance of the entire system. Memory swap operations can be effectively reduced by placing ORACLE data files on bare devices and adjusting system file caches.

On AIX, the system file cache can be adjusted to control memory swap operations through the MINPERM (default 20) and MAXPERM (default 80) parameters of the vmtune command. MINPERM and MAXPERM are typically set to 5% and 20% or less, respectively, to allow more memory to be used for Oracle's SGA than for the system's file cache.

#vmtune -p 5 -P 20

In versions prior to HP-UINX 10.X, allocating too large a file system cache also caused Oracle's SGA to be swapped into virtual memory. File system cache allocation is dynamic in versions after 10.X. Unreasonable settings dbc_min_pct and dbc_max_pct can also lead to occasional or intermittent pauses that cannot be explained.

4. Optimize database memory

4.1 Oracle Memory Structure

4.2 SGA(System Global Area )

For Oracle memory adjustment settings, to be considered according to the actual situation, the basic principles are:

Data buffer: used to store data blocks read from data files, which can be as large as possible;

Shared pool (shared_pool_size): used to store data dictionary and currently executed SQL statements and stored procedures, to be moderate;

Log buffer (log_buffer): Used to buffer user updated data, not too large.

shared_pool_size: to moderate size, usually set to about 500M, should not exceed 700M.

log_buffer: Usually set to 512K to 1M.

large_pool_size: If MTS (Multi-Threaded Server) is not set, this part of memory will only be used in RMAN (Recovery Management) and OPQ (Parallel Query), usually set to 16M-64M.

java_pool_size : If java is not used in the database, it is usually set to 16M.

data buffer: After the above settings are made, any memory that can be provided to Oracle should be given to data buffer = (db_block_size * db_block_buffers).

SGA=data buffer+shared_pool_size+log_buffer+java_pool_size+large_pool_size

4.3 PGA (Program Global Area )

sort_area_size: default 64K, usually set to 128K to 512K

hash_area_size: not configured, calculated according to 2*sort_area_size

These two parameters belong to PGA(Program Global Area) under non-MTS, not SGA. It is allocated separately for each session, so the memory overhead on the server usually requires: OS memory +SGA+session* (sort_area_size+hash_area_size+2M)

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.