The Oracle tutorial being looked at is Oracle Database performance optimization Technology Developer Network Oracle.
Introduction: Fine Place to start, skillfully place diligently. The difference between Master and rookie is: Master know everything, rookie know some. Computer tips to collect the most novel tricks, so that you easily set foot on the Master road.
Absrtact: Oracle database is one of the most widely used large-scale databases, and its optimization is directly related to the operation efficiency of the system. Based on the basic principle of database performance optimization, this paper expounds how to avoid competition and how to optimize data access in the database design stage, how to adjust the memory and I/O from the operating system and the database instance level in the database runtime to achieve the optimization of database performance.
Keywords: Oracle database; performance optimization; memory; I/O
1. Introduction
With the continuous development of network applications and E-commerce, the number of visits to each site is increasing, the size of the database has been expanding, the performance of the database system has become more and more prominent, so how to tune the database is essential: how to use limited computer resources for more users? How to ensure the user's response speed and quality of service? These problems are within the scope of server performance optimization.
As the world's largest database manufacturer, Oracle database at home and abroad to obtain a lot of successful applications, according to statistics, the global 93% of the listing. COM companies, 65 "Fortune Global 100" Enterprises invariably use Oracle database to carry out e-commerce. Many enterprises, government units and E-commerce websites in China have also adopted Oracle as the database server. Oracle database server is a highly optimized software product, and regular adjustments can optimize the performance of the application system and prevent system bottlenecks.
The basic principle of database performance optimization is to get the data you need with as little disk access as possible. In order to evaluate the performance of database, it is necessary to compare the evaluation indexes before and after the database adjustment: The tradeoff between response time and throughput, the availability of database, the hit rate of database and the efficiency of memory usage, so as to measure the effect of adjusting measures and the direction of guiding adjustment.
Performance tuning of an Oracle database should be done in a certain order, as the system's adjustments in the previous steps can avoid some unnecessary adjustments or costly adjustments. In general, you can start with two phases:
1, design phase: the logical structure and physical structure of the optimization design, so that in order to meet the demand conditions, the system performance to achieve the best, the system cost to achieve the smallest;
2, the database operation stage: takes the operating system level, the database level some optimization measures to make the system performance best;
2. In the system design development phase to adjust the database
In order to make full use of Oracle database functional characteristics, in the design of information systems, database designers need to be based on the business situation (such as access or number of clients) and existing resource conditions (such as the configuration of the database server) to consider the structure of the system and the logical structure of the database design:
2.1 Adjust the application structure design.
That is, the application is a traditional C/s two-tier architecture, or b/w/d three-tier architecture. Different application architectures require different database resources.
2.2 Proper use of partitioning, indexing, and archiving functions.
If the amount of data in a business grows very fast, consider whether the database table hosting the business uses the partitioning capabilities of the Oracle database, and whether the database tables that are accessed frequently need to be indexed For frequently accessed data that is not changed after the business process is completed, the data can be accessed as little as possible in the application system by putting in a historical file.
2.3 Properly write SQL statements that access data.
A good SQL statement can be reused by the database to reduce the analysis time, and proper use of the index can greatly reduce the access data block and thus reduce the response time. The execution of the application ultimately boils down to the execution of SQL statements in the database, so the efficiency of the execution of the SQL statement determines the performance of the Oracle database. Oracle Company recommends using the Oracle Statement Optimizer (Oracle Optimizer) and the row lock manager (Row-level Manager) to adjust the optimized SQL statements.
2.4 Adjust hard disk I/O
This step was completed prior to the development of the information system. The database administrator can put the data files that make up the same table space on a different hard disk and I/O load balance between the hard disks. The following guidelines should also be followed in cases where the disk is more affluent:
Separate the table from the index;
Create user table space, separate disk from system table space.
Specify a different table space when creating tables and indexes;
Create a table space dedicated to the rollback segment to prevent space competition from affecting the completion of the transaction;
Create temporary table spaces for sorting operations, as much as possible to prevent database fragmentation from being present in multiple tablespace spaces.
2.5 Determine the block size and storage parameters.
Because the block size of the database cannot be modified after the database is created (unless the database is rebuilt), to reduce data link and row migration and increase disk space utilization, determine the appropriate block size and storage parameters when designing the database. Usually we determine the block size based on the sample data and determine the storage parameters according to the business status and future trends.
[NextPage]
3. Adjusting the database during the run phase of the database
Database running phase Tuning database includes two aspects: Operating system level adjustment, database level adjustment.
3.1 Operating system-level adjustments
The primary purpose of implementing operating system-level tuning is to reduce memory switching and to reduce paging so that the SGA (System globle area) can remain in memory.
3.1.1 Reduces memory swapping
Memory swapping (swapping) can cause a lot of memory overhead and should be minimized. An Oracle database running on the Solaris UNIX operating system can use the Vmstat or SAR commands to check the Exchange, see how system-level memory and hard disk I/O are used, adjust the size of the UNIX data buffer pool, the size of memory that each process can use, and so on.
Vmstat command
It reports on the activity of processes, virtual memory, disks, paging, and CPUs on Solaris. The following command displays a summary of what the system does every 5 seconds:
% Vmstat 5
SAR command
Used to monitor Exchange, paging, disk, and CPU activity. The following command is used to display a summary of 10 paging activities every 10 seconds:
% Sar-p 10 10
If the system memory exchange more, and need to save memory, you can use the following measures:
1 Avoid running unnecessary system daemon process or application process;
2 reduce the number of database buffers in order to release some memory without significantly affecting the data block hit rate;
3 Reduce the number of UNIX file buffers (especially when using bare devices).
3.1.2 Control Paging
A small amount of memory paging does not significantly affect system performance because the application does not have to be in memory. However, too much paging will cause system performance to degrade. To detect too many paging, you can run measurements during a quick response or idle period and compare them to measurements that are slow in response. The following approaches can be used to address:
Use Vmstat or sar-p to monitor paging;
Install more memory;
Move some work to another system;
Configure the system core to use less memory;
Keep the SGA in a single shared memory segment.
3.1.3 to keep the SGA (System globle area) in memory
SGA is a fast access to the database data of a system global area, if the SGA itself needs to be frequently released and distributed, it is impossible to achieve rapid access to data, so the SGA is required to reside in memory. At this point, we can reconfigure the UNIX core, adjust some operating system parameters to achieve the purpose of increasing shared memory.
3.2 Database-level tuning
Each Oracle instance is composed of a set of Oracle background processes and one memory area of the SGA. This set of background processes will automatically read and write database data files, so database performance can be affected by these factors: the distribution of the SGA parts is reasonable, the use of the normal efficiency, I/O and lock competition is more.
Allocation and utilization efficiency of 3.2.1 SGA
The memory allocated to each instance, that is, how efficiently the SGA is used, can greatly affect the performance of the database system. The SGA consists of a shared pool, a data block buffer, a redo log buffer, and a large pool of components.
L Shared Pool
Shared pool repository cache (store shared SQL and Pl/sql area) and data dictionary cache (database object information) and session information (for MTS). Because this information is frequently accessed by applications, this information needs to be kept to a high percentage. You can confirm the hit ratio of the shared pool data by using the following statement:
Library cache:
Select Gethitratio from V$librarycache should be greater than 90%
Select sum (reloads)/sum (pins) from V$librarycache should be less than 1
[1] [2] Next page
The Oracle tutorial being looked at is Oracle Database performance optimization Technology Developer Network Oracle. %
Data Dictionary caching:
Select SUM (getmisses)/sum (gets) from V$rowcache should be less than 15%
Because of the uneven level of program designers, there may be large anonymous blocks, which can cause SQL to be reusable, so you need to find large anonymous blocks to convert to stored procedures for reuse:
SELECT * from V$sqlarea where command_type=47 and Length (sql_text) >500
SQL objects such as stored procedures, functions, packages, and so on that are used very frequently by application systems can be pinned in memory to prevent the shared pool from being removed because it is too small:
EXEC dbms_shared_pool.keep (object name)
Chunk Buffer (Db block buffer):
The data block buffer holds the data block contents of the data file that the user often accesses and the data content that the user modifies. The database reads the contents of the data file into memory and reads it directly from memory the next time it is needed, thereby reducing the disk's I/O and response time. Of course, only small data tables, such as common code tables, are typically cached in memory.
Because it is not possible to store all of the data in the fast buffer, the LRU algorithm can be used to determine which blocks of data are removed, but to ensure a high data hit rate.
The SQL statement that views the data block hit ratio is:
Select N (phy.value/(Cur.value+con.value)) from V$sysstat Cur,v$sysstat Con,v$sysstat PHY
Where Cur.name= ' db block gets ' and con.name= ' consistent gets '
and phy.name= ' physical gets '
If the hit rate is less than 0.85, consider allocating more memory to the block buffer.
Redo Log buffers (log buffer):
The redo log buffer holds the redo entries for each DML or DDL statement copied from the user's memory area. If this buffer allocation is too small, there is not enough space for the redo entries to wait.
3.2.2 I/O and resource competition
Because there are numerous processes to write data files, I/O tuning is required to address I/O bottlenecks. If the reasonable allocation of table space is considered effectively in the design stage, I/O competition can be effectively reduced to some extent. As the database runs, Oracle automatically allocates space to these database objects because of the dynamic growth of the data and the space that was originally allocated to the table or index has been exhausted. This dynamic allocation will have an impact on system performance, so require:
Avoid dynamic space management
Table space localization management to reduce disk competition with the data dictionary table space.
In the system design and trial run phase of the relatively small amount of data, inefficient SQL may not affect system response time, but when the amount of system data grows to a certain extent, you need to monitor and find out which SQL is not valid to use indexes or missing indexes when the system is running, and make adjustments to them: indexing, and modifying SQL notation.
In addition, in Oracle, there are mechanisms to ensure the stability of database objects during use and the consistency of data, such as using latches (latch), locks (lock), and so on. Therefore, contention and the resources associated with these mechanisms can affect the performance of the database. In order to reduce this resource competition, it is possible to reduce resource contention and optimize database performance by adjusting the relevant initialization parameters of the database (such as db_block_lru_latches, Dml_locks).
4. Some common methods and tools for performance optimization
Oracle database systems provide tools and scripts to obtain database performance metrics and optimized methods. such as using Utlbstat.sql and Utlestat.sql scripts to capture the memory, disk I/O, etc. of the database over a period of time, using dynamic performance views and data dictionary views to obtain information such as hit rates and system wait events. Of course, you can also use the Oracle Enterprise Manager graphical tool for monitoring.
5. Concluding remarks
Performance Tuning for Oracle databases is important, but also difficult. Database administrators need to comprehensively use the rules described above, in the establishment of the database, can be based on the needs of the application of reasonable design allocation table space and storage parameters, memory use initialization parameters, for future database performance has great benefits. The Oracle database can be efficiently and reliably run only if a careful analysis of the various performance problems occurred during the Oracle operation. Also need to point out is: The performance of the database is a system engineering, involved in many aspects, can not only according to a point of time to determine the performance of the database running good and bad. How to effectively adjust, the database administrator needs to go through the iterative process. All these need to accumulate experience in a lot of practical work, so as to better improve the database tuning.
Previous page
prev [1] [2]
