Oracle Database performance optimization)

Technorati label: Oracle

Font: Small Medium large | printed on: Author: tangshan_ph Source: cnoug blog homepage

Oracle performance can be enhanced by adjusting database applications, databases, and operating systems. Appropriate Oracle adjustment can achieve better performance for specific application and hardware configurations. Performance adjustment should consider the response time, throughput, and limits.
Depending on different features of the application, the requirements for these three factors are also different. For OLTP, high response time is required, while for OLAP, high throughput is required. The system response time can be divided into service time and wait time. For response time performance adjustment, it is to shorten the service time and wait time. For throughput performance adjustment, it is to use the same resources to do more things (shorten service time) and quickly complete the work (shorten wait time ).
Resources such as CPU, memory, I/O capability, and network bandwidth have a great impact on reducing service time and waiting time. Increasing resources may result in high throughput and fast response time. System performance since:
Number of available resources
Number of customers requesting resources
Resource wait time
Resource usage time
Generally, application development and design have the greatest impact on performance. Once an application is put into practice, the database administrator can only make basic adjustments under the limits of system design. Performance adjustment involves 10 steps. This article mainly discusses the impact of resources such as CPU, memory, I/O capability, and network bandwidth on performance, and how to configure resources to improve system performance.
First, you must understand the running status of the system. There are several tools that can be used to obtain data that represents the system performance. The following data sources can indicate system performance:
Data Volume
Online Data Dictionary
Operating System Tools
Dynamic Performance Table
SQL tracking tool
Alarm log
Application output
User
Initialize the parameter file
Program text
Design (analysis) Dictionary
Comparable data
The following tools are available for obtaining the data:
Dynamic Performance view: Oracle uses a set of dynamic performance views to monitor databases. The view names start with "V $. Sys users have these views.
Oracle and SNMP (Simple Network Management Protocol) support: SNMP allows users to write their own tools and applications.
Explain plan: an SQL statement that lists the access paths selected by the query optimizer.
SQL tracking tool and tkprof: the SQL tracking tool records in text the resource consumption, analysis, execution, result acquisition, submission, or rollback of statements in each stage.
Tkprof summarizes the files generated by the SQL tracking tool, which are included in the explain Plan output.
Script: Oracle supports many PL/SQL packages, which can be used to adjust database instances. For example: utlbstat. SQL utlestat, sqlutlchain. SQL, utldtree. SQL, and utllockt. SQL.
Application Registration: you can record the name of the application and the operations it performs in the database, so that the administrator can track the performance based on the module. The Application name and activity are recorded in the V $ session and V $ sqlarea views.
Oracle Enterprise Manager: it is a tool set used to manage the Oracle environment. Including:
Oracle Performance Manager: it obtains, computes, and displays database performance data. It can monitor databases to effectively use memory, minimize disk I/O, and avoid resource competition.
Oracle topsessions: used to monitor the activity of each session.
Oracle trace: Oracle Performance Manager and Oracle topsessions use sampling technology to regularly collect data from the database dynamic performance view, while Oracle trace Collects performance data based on predefined events.
Oracle tablespace MANAGER: If you suspect that the database performance problems come from tablespaces, you can use this tool to detect and correct these problems.
Oracle expert: it provides automatic performance adjustment. Problems Detected by Oracle Performance Manager, Oracle topsessions, and Oracle trace can be analyzed in Oracle expert.
Oracle Parallel Server Management (opsm): opsm is a comprehensive management solution for Oracle Parallel servers. It manages multiple database instances in heterogeneous environments through an open client-server system.
(1) (1) optimizing CPU resources
Determine CPU-related issues: first, determine whether the CPU is large enough. In this regard, we mainly consider the following three aspects:
CPU usage during idle period
CPU usage during peak hours
Average CPU usage
If you suspect that the CPU is the bottleneck of the system performance, you can determine it using the following method:
Check the CPU usage of the system: Oracle only counts the CPU usage of Oracle sessions, and any process in the system uses the CPU. Therefore, adjusting other applications may also improve the CPU performance of oracle.
In Unix systems, you can use the SAR-u command to obtain information about CPU usage. Generally, if the CPU idle time or I/O wait time is close to 0, CPU is the bottleneck. In Windows NT, you can use the performance monitor to obtain information about CPU usage.
Memory Management: Paging and swapping. Analyze the causes of paging and switching by using unix sar or vmstat commands and performance monitors in Windows NT.
I/O management: thrashing: ensures that the workload is suitable for the memory size and avoids excessive page exchanges. If the CPU spends a large part of the time slice to ensure that the program can run, only 50% of the time may be used for actual work. Client/Server round trips: Some implicit messages will increase the CPU burden, and applications often generate some messages to be transmitted back and forth in the network.
Process Management: Scheduling and switching: the operating system may spend a lot of time switching processes and checking whether a large number of processes have been started. Context switching: Context switching of a process also consumes a lot of CPU time.
Check Oracle CPU usage: There are two dynamic performance views that can be used to check orcale process information. V $ sysstat: displays the usage of the Oracle CPU for all sessions. V $ sesstat: displays the Oracle CPU usage for each session.
The following describes how to use the Oracle CPU:
Re-analysis of SQL statements: inefficient SQL sharing causes re-analysis of statements.
Select * from V $ sysstat
Where name in
('Parse time CPUs ', 'parse time elapsed', 'parse count (hard )');
Select SQL _text, parse_cils, executions from V $ sqlarea
Order by parse_cils;
Low-efficiency SQL statements: inefficient SQL statements consume a lot of CPU time.
Select buffer_gets, executions, SQL _text from V $ sqlarea;
Read consistency: it may take a lot of CPU time to implement read consistency through rollback segments, which can be solved by increasing the number of rollback segments.
Solution:
When the maximum CPU limit is reached and the performance cannot be optimized by adjusting the CPU, you must re-design the application. You can consider migrating to different application architectures for appropriate CPU usage.
Migrate from a single-layer structure to a two-layer structure: Separate the customers and servers on one machine.
Multi-layer, with smaller clients: multiple clients on one machine can be separated to smaller clients.
Two to three layers: using a transaction processor
Using Oracle Parallel servers
(2) (2) optimizing Memory Allocation
Access to data in the memory is faster than access to the disk, so the purpose of memory optimization is to increase the memory hit rate. Generally, the memory allocation is optimized only after the application and SQL statements are optimized. Memory Allocation determines the disk I/O quantity.
How to solve the memory allocation problem:
Optimize the memory requirements of the operating system: this solution reduces Page Swap of memory, ensures that SGA is in memory, and allocates enough memory for each user (which may only be supported by some operating systems ).
Optimize the redo log Buffer: The log_buffer parameter is used to control the redo log buffer. For computers with slow disk speed and fast CPU, the use of the redo log buffer may conflict. You can increase the size of the redo log buffer. Generally, the redo log buffer only occupies a small part of the SGA. You can increase the throughput accordingly.
Optimize private SQL and PL/SQL: This optimization mainly involves two parts: one is to distinguish unnecessary re-analysis calls, and the other is to reduce unnecessary re-analysis calls. You can use SQL tracking tools and check the V $ sqlarea view to differentiate unnecessary re-analysis calls; you can use the three attributes hold_cursor = Yes, release_cursor = No, And maxopencursors = desired value to reduce unnecessary re-analysis calls.
Optimized sharing pool: The Shared Pool of SGA is composed of database cache, Dictionary cache, and session information of some users and servers.
The database cache contains the analysis and execution of SQL statements. The SGA shared part of each SQL statement is the amount of memory in the corresponding shared pool, which contains the statement analysis tree and execution plan. An important issue for implementing SGA optimization is to ensure that the database cache is large enough to enable Oracle to maintain analysis and execution plans in the cache. You can view the high-speed cache performance of a database through the system performance monitor or the V $ librarycache view.
The dictionary cache contains data dictionary information about the database, file space availability, and object permissions. The hit rate shows whether the dictionary cache is invalid. You can view related data through the system performance monitor or the V $ rowcache view. You can use the shared_pool_size parameter to increase the buffer pool size.
Optimized sorting Buffer: if there are a large number of sorting actions, you need to increase the size of the sorting buffer. You can use the sort_area_size parameter.
(3) (3) optimizing I/O operations
I/O optimization is scheduled to reduce I/O conflicts after memory optimization. In this case, i/O performance can be further improved through some adjustments.
For new systems, I/O requirements should be analyzed from top to bottom to determine the required resources. The bottom-up method should be used for existing systems:
1. Learn about the number of disks in the system.
2. Learn about the number of disks used by Oracle.
3. Understand the I/O type of the application system.
4. understand whether I/O operations are intended for file systems or raw devices.
5. Learn about the distribution of objects on the disk.
You can check the I/O problems by using the following methods:
Check System I/O usage: You can use the tools provided by the operating system to monitor access to disk files by the entire system, and store a large number of disk access applications and Oracle-related files separately. In Unix systems, Sar-D can be used to obtain relevant data. In Windows NT, you can view it through performance monitor.
Check oracle I/O usage: for Oracle, you can obtain relevant information through the following views:
File type where to find statistics
Database Files v $ filestat
Log Files v $ sysstat, V $ system_event, V $ session_event
Archive files v $ system_event, V $ session_event
Control Files v $ system_event, V $ session_event
You can solve the I/O problem by using the following methods:
Reduce disk competition:
Disk competition: when multiple processes access the same disk at the same time, disk competition will occur. To reduce access to a high-load disk, you can move files with high access volume to a low-load disk.
Separation of redo log files and data files: Oracle always accesses redo log files and data files frequently. Putting the two together may increase disk conflicts.
Striped table data: the data in a large table is distributed to different data files on different disks, which can also reduce disk conflicts.
Separating tables and indexes: this is not necessary. Because indexes and table reads are serialized, you can also put the tables and indexes together without disk conflicts.
Disk Striping: data in a large table is distributed to different data files on different disks. The striping allows different processes to access different parts of a table at the same time. This is especially helpful for randomly accessing multiple rows in a table. Strip can be the I/O load balancing of disks. There are two striping methods.
Manual method: Use tablespaces and partition tables.
Operating System: Raid
Avoid dynamic space management: After an object is created, Oracle will allocate an object to the object. When additional space is required for subsequent database operations, Oracle will expand the segment. Dynamic scaling damages performance.
Detecting dynamic expansion: dynamic expansion can cause recursive calls to SQL statements (Oracle itself needs to issue some SQL statements). You can obtain information about recursive calls through the V $ sysstat view. If you find that there are a large number of recursive calls when the application is running, you can use a large extension.
Allocate extended segments (extent): determine the maximum object size and select an appropriate storage parameter value. The segments allocated by Oracle can meet the storage requirements.
(4) (4) network optimization: It mainly ensures the speed of the network and the traffic on the network.
You can check network problems by using the following methods: You can check network latency information through the V $ session_event, V $ session_wait, and V $ sesstat views.
The average_wait list in the V $ session_event view shows the Oracle wait time.
The event column in the V $ session_wait view lists the pending active sessions.
The V $ session_wait view describes what the Active session is waiting for. You can also see the number of bytes sent and received.
You can solve network problems by using the following methods:
Use the array interface: Replace the method of taking only one row at a time with the array interface. It is more effective to retrieve multiple rows back and forth in a network than to retrieve one row.
Use a pre-started process: If a pre-started process is not used by a multi-threaded server, the connection time can be greatly shortened.
Adjust the size of the session data cell Buffer: when a large amount of data is to be transferred, increasing the size of the Data cell buffer will improve network performance. You can use the Oracle Network Manager to define the size of the Data cell buffer.
Increase the size of the listener queue: You can process more requests by increasing the size of the listener queue.
Use TCP. nodelay:
Use Connection Manager: The Connection Manager allows multiple sessions to share one transport layer connection, which increases the number of sessions processed by the process.
(5) (5) Optimize the Operating System
Operating system performance problems mainly involve process management and memory management. If you want to achieve better performance after optimizing Oracle, You need to optimize the operating system. However, the performance cannot be greatly improved.
Operating System Performance Check: The data indicators to be obtained from the Operating System Performance Monitor include the following:
CPU load: Check the time when the system runs in user and system modes.
Device queue
Network Activity (queuing)
Memory Management (page switching)
Solve operating system problems:
UNIX-based systems: Understand the CPU time consumption ratio of the system. In user mode, the CPU time is 60%-75%, and in system mode, the CPU time is 25%-40%. This ratio represents the underlying problems of the system, such as excessive page exchange, too many system calls, and too many processes.
NT-based systems: similar to UNIX-based systems. It's just a more convenient monitoring tool.
(6) (6) optimizing resource competition
When multiple processes access resources at the same time, competition will occur. You can use the V $ system_event view to check resource competition.
Detection of competition problems:
The V $ resource_limit view provides the current and maximum resource usage information.
In the V $ system_event view, check the maximum average wait time.
V $ latch view to check latch usage.
The V $ waitstat view and the V $ session_wait view wait for the buffer period.
Solve the competition problem:
Reduce the competition of rollback segments: you can determine the competition that includes the buffer zone of rollback segments. It can be determined through the V $ waitstat view. Use the following statement to obtain data. If the wait time of the rollback segment buffer exceeds 1% of the total time, create more rollback segments.
Select class, count
From v $ waitstat
Where Class in ('System undo header', 'System undo Block ',
'Undo header', 'undo Block ');
Reduce competition for multi-threaded server processes, including reducing competition for distribution and sharing server processes.
Obtain information about the distribution process through the V $ dispatcher view.
Select network "rotocol ",
Sum (busy)/(sum (busy) + sum (idle) "Total busy rate"
From v $ dispatcher
Group by network;
V $ queue view information about the response queue activity of the distribution process.
Select network "rotocol ",
Decode (sum (totalq), 0, 'no response ',
Sum (wait)/sum (totalq) ''' hundredths of seconds ')
"Average wait time per response"
From v $ queue Q, V $ dispatcher d
Where Q. type = 'dispatcher'
And Q. paddr = D. paddr
Group by network;
You can use the mts_dispatchers parameter of the alter system command to change the number of distribution processes. Use the initialization parameter mts_max_dispatchers to change the maximum number of distribution processes.
The number of requests in the Request queue can be used to determine the competition of the shared server process. The V $ queue view contains the activity information in the Request queue.
Select decode (totalq, 0, 'no requests ',
Wait/totalq '''hundredths of seconds ')
"Average wait time per requests"
From v $ queue
Where type = 'common ';
Oracle automatically increases the number of shared processes when the load is high, so it is impossible to manually change its value. However, you can change the maximum number of shared processes by initializing the mts_max_servers parameter.
Reduce competition for Parallel Server Processes: You can use the V $ pq_sysstat view to determine the number of parallel server processes. Generally, the maximum number of parallel servers depends on the system's processing capacity (CPU, I/O) and cannot be changed. If the server is frequently started or shut down, you can change the value of the parallel_min_servers parameter. Periodically check the value of V $ pq_sysstat to check whether the number of parallel servers is appropriate:
Select * from V $ pq_sysstat
Where statistic = "servers busy ";
Reduce the competition for redo log buffer latches: The redo log buffer rarely affects database performance. You can use the redo Buffer Allocation retries check in the V $ sysstat view to check the waiting buffer time.
Select name, Value
From v $ sysstat
Where name = 'redo Buffer Allocation retries ';
You can change the buffer size by initializing the log_buffer parameter.