Common Oracle Optimization Methods

Oracle common tuning methods Oracle database application system optimization mainly includes ten aspects: (1) optimizing database memory; (2) fixed application code in the Oracle shared pool; (3) optimizing data storage; (4) optimizing data sorting; (5) optimizing SQL statements; (6) optimizing rollback segments; (7) optimize indexes; (8) Optimize disk I/O; (9) regularly generate status statistics for database objects; (10) Optimize the operating system environment. The essence is to reduce CPU load and improve I/O performance. 1. The role of the disk I/O database is to manage and query data, so there must be a large number of read/write operations on data, its I/O problems are also an important cause of Oracle database performance problems. 1.1. I/O generation in Oracle 1.2. Optimized OS storage 1. Raw Device is used in the UNIT environment) as a storage device for Oracle data files, the reading and writing efficiency is higher than that of the File System for storing Oracle data files. Ii. Asynchronous I/O (Asynchronous IO) is used. In asynchronous IO mode, after a process sends an IO request, it can process other tasks without waiting for the IO to complete. The IO request is put into a queue. Once the IO is completed, the system sends a signal to the process. Asynchronous IO can enable Oracle processes that require a large number of writes (such as DBWn processes) to queue IO requests to make full use of the hardware I/O bandwidth so that they can implement parallel processing to the maximum extent possible. After confirming that the operating system supports AIO, you also need to set the Oracle initialization parameter "DISK_ASYNCH_IO" to "true" to support asynchronous IO. Iii. Disk Load Balancing and Striping ). The strip technology divides data into many small parts and stores them in different regions on different disks. This allows multiple processes to access multiple different parts of the data at the same time without causing disk conflicts. Many operating systems, disk equipment suppliers, and various third-party software can be striped. Through striping, DBA can easily achieve IO Load Balancing without manual configuration. 1.3. Optimized IO configuration 1. Use the LVM (Logical Volume Manager) software to reasonably configure the width and depth of the Strip. 2. Adopt the file separation policy to avoid disk hotspots. Although we achieve Disk Load Balancing through Disk Striping at the hardware and operating system level, we still cannot avoid some data files becoming "Hotspot Files ". Separate the storage of tables, indexes, and temporary tablespaces, that is, create separate data table spaces, index tablespaces, and temporary tablespaces for the application system. Detach the Redo Log file. If the IO throughput of the Redo Log file is high, store the Redo Log on a separate disk, redo Log files should be strip out to multiple disks and Archive logs should be separated ). When the ORACLE database runs in archive mode, the archive process (ARCn) will inevitably generate a large number of disk reads and writes. Therefore, archive log files should be distributed to multiple disks for separate storage. 1.4 optimize oracle I/O-related parameter settings db_file_multiblock_read_count: used to control the maximum value of data blocks read by an I/O operation in a completely continuous scan. The default value is 8db_writer_processes: the initial number of Database "Write processes. Disk_asynch_io: used to control the asynchronous I/O of data files, control files, and log files. This parameter can be changed only when the platform supports asynchronous disk I/O. Log_archive_max_processes: specifies the number of ARCH processes in archive mode. 2. optimize data storage 2.1. Optimize the tablespace. The SYSTEM tablespace is used to store information about the oracle system. Generally, objects created by users should not be placed in the SYSTEM tablespace, in addition, you also need to pay attention to the appropriate size of the SYSTEM tablespace to ensure that there is about MB of free space. For ORACLE database application systems, an independent ORACLE user (scheme), data table space, index tablespace, and temporary tablespace should be created for the application system. Table space and data files should be set to an appropriate size at a time to avoid automatic growth of data files, resulting in Data Segment block/segment discontinuing and affecting system performance. Regularly check the usage of the database tablespace to prevent the system performance from being affected by too many tablespace fragments. You can query the view dba_extents to obtain detailed table space usage. Sort the tablespace fragments to clear the available space of the data table segment. Merge tablespace fragments alter tablespace tablespacename coalesce; reclaim the free space of the data table segment alter table tablename deallocate unused; 2.2. Reasonably configure the rollback segment size after Oracle 9i, the system adopts a transparent local management mode. By default, the tablespace In the rollback segment is relatively small and cannot meet the needs of large transactions in actual applications, you need to create a large rollback segment for specialized large-scale applications or transactions. 2.3. Temporary tablespace design planning temporary tablespaces are mainly used to query distinct, union, order by, create index, and store temporary table data. Oracle's default tablespace is Temp, and its size is 1 MB. It is far from enough for a real medium or large application system. Therefore, we need to do the following: Increase the Temp tablespace to an appropriate size, generally around-M. When creating a user, select a dedicated temporary tablespace for the user. A large temporary tablespace should be set up for large application systems to store temporary table data for monthly, quarterly, and annual reports. 2.4 data files and log files are stored on different disks. data files are written through the DBWR background process, and log files are written through the LGWR background process, because log files are continuously written, there is no concurrent processing. Writing data files is relatively random. To avoid conflicts between DBWR and LGWR within the same time period, you should place the log files and data files on different hard disks. In ARCHIVELOG mode, conflicts between log file writing disks and log archiving may also occur. This conflict can only be solved by allocating log files to multiple disks. 3. Optimize the operating system environment 3.1. Configure the appropriate OS semaphores for Oracle to run in Some Unix operating system environments. It should be set according to the requirements of the Oracle version release. For example, in the SOLARIS environment, You need to log on as root and modify the system file of the/etc directory according to the parameter requirements of the Oracle Installation manual. 3.2 configuring an appropriate swap zone in a UNIX operating system environment is a basic requirement of Oracle. It can be determined according to the Oracle release requirements. It is recommended that the swap area be larger than twice the server memory. 3.3 configure the operating system to enable asynchronous I/O. Currently, most operating systems support asynchronous I/O, but if Oracle runs in asynchronous I/O mode, configuration is required. For example, in the HP-UNIX, You need to authorize "/dev/async" to the oracle user of the operating system, and to modify the Oracle parameter disk_asynch_io = true. 3.4. Lock the Oracle SGA in the physical memory. Almost all operating systems support virtual memory, so even if the memory we use is smaller than the physical memory, we cannot avoid the OS switching SGA to the virtual memory (SWAP ), by locking Oracle SGA in the physical memory through the corresponding configuration to avoid being switched to the virtual memory, the page switching and switching can be reduced to improve performance. Configuration method under the HP-UNIX: #/etc/setprivgrp dba MLOCK to adjust the ORACLE parameter: lock_sga = TRUE restart database AIX 5L (AIX 4.3.3) configuration method: $/usr/sbin/vmo-r-o v_pinshm = 1 (or vmtune-s 1) Adjust the ORACLE parameter: lock_sga = TRUE restart database 3.5, control the memory Switching Operation (Paging) A large number of memory swap operations will greatly affect the system performance, especially when database files are created on the file system. In this case, frequently accessed data exists in the SGA and also in the file cache. If the same data is cached twice in the memory, the memory usage efficiency is reduced, so that the memory is frequently exchanged, resulting in system I/O bottlenecks, reduces the performance of the entire system. By creating ORACLE data files on bare devices and adjusting the System File Cache, You can effectively reduce memory swap operations. On AIX, you can adjust the System File Cache by using the MINPERM (default value 20) and MAXPERM (default value 80) parameters in the vmtune command to Control Memory swap operations. Generally, MINPERM and MAXPERM are set to 5% and 20% or even smaller, so that the memory is more used in the SGA of Oracle than the System File Cache. # Vmtune-p 5-P 20 in versions earlier than HP-UINX 10. X, allocating too much file system cache also causes Oracle's SGA to be switched to virtual memory. In Versions later than 10. X, the file system cache is dynamically allocated. Improper configuration of dbc_min_pct and dbc_max_pct may cause undefinable occasional or intermittent pauses. 4. optimize the database memory 4.1, Oracle memory structure 4.2, and SGA (System Global Area) for Oracle memory adjustment settings. The basic principles are as follows: data buffer: used to store data blocks read from data files, which can be as large as possible; Shared Pool (shared_pool_size ): it is used to save the data dictionary and the SQL statements and stored procedures currently executed. It must be moderate. The log buffer (log_buffer) is used to cache user updated data, which does not need to be too large. Shared_pool_size: The size must be moderate. It is usually set to around 700 mb and should not exceed MB. Log_buffer: generally set to kb to 1 MB. Large_pool_size: If MTS (Multi-Threaded Server) is not set, this part of memory is only used in RMAN (recovery management) and OPQ (parallel query, usually set to 16 M-64 M. Java_pool_size: If java is not used in the database, it is usually set to 16 Mb. Data buffer: After the above settings, all the 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, optimized PGA (Program Global Area) sort_area_size: The default value is 64 K, which is usually 512 K to K hash_area_size, it is calculated based on 2 * sort_area_size. The two parameters belong to PGA (Program Global Area) and not SGA in non-MTS. 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 + 2 M) <total physical RAM.