Oracle Performance Optimization ____oracle

Performance optimization for Oracle databases
Mainly from internal storage structure, logical partitioning, table structure paradigm, SQL statement optimization.

Hibernate at higher levels, mainly from caching, and the use of SQL statements

For Oracle database data access, there are four different levels of adjustment, the first level of adjustment is the operating system level includes hardware platform, the second level of adjustment is Oracle RDBMS level adjustment, the third level is the database design level adjustment, the last adjustment level is the SQL level. The database is usually adjusted and optimized according to the level of level four adjustment, and the overall performance of the database can be improved greatly. The following is an introduction to Oracle database optimization design from nine different aspects.

One, database optimization free structure OFA (Optimal flexible architecture)
The logical configuration of the database has a great influence on the performance of the database, optimizing the free structure OFA, simply speaking, it is possible to distribute the logical data objects efficiently and freely in the database, so we should first classify the logical objects in the database according to their usage and physical structure to the database impact. This classification includes separating system and user data, separating general and indexed data, separating low and high activity tables, and so on.
The results of the logical design of the database should conform to the following guidelines: (1) storing the types of segments used in the same way, (2) designing the system according to standard use, (3) Separating regions for exceptions, (4) Minimizing table space collisions, and (5) separating data dictionaries.
Second, make full use of the system global region SGA (Systems global Area)
The SGA is the heart of the Oracle database. The user's process sends transactions to this memory area and reads the hit data here as a cache for accelerated purposes. The correct SGA size is critical to the performance of the database. The SGA consists of the following sections:
1, the data block buffer is the SGA in a cache, accounting for the entire database size of 1%-2%, used to store data from the database to read the block (table, index, cluster, etc.), so using least recently Used (LRU, Least recently used) method for space management.
2, Dictionary buffer. The information in this buffer includes user account data, data file name, segment name, panel location, table description and permissions, and it is also managed in LRU mode.
3, redo log buffer. This buffer is saved as a roll forward operation during the database recovery process.
4, SQL sharing pool. The parsing tree that holds the SQL statement that executes the plan and runs the database. Also used LRU algorithm management. If set too small, the statement will be continuously loaded into the library cache, affecting system performance.
Third, the standard and counter-norm design Database
1. Standardized
The so-called normalization is essentially the single of the concept. The advantage of data normalization in database is that it reduces data redundancy, saves storage space, reduces the number of I/O in logic and physics, and speeds up, deletes and changes.
2. Anti-Normalization
In the process of designing the database, it is necessary to keep the nonstandard constraint intentionally, or to standardize the later, which is usually to improve the query performance of the database and speed up the response of the database system.
3. Optimization strategy in database design
Data should be organized in two categories: frequently accessed data and frequently modified data. A more complex approach is to use a normalized table as the basis for logical database design, and then physically normalize the data according to the needs of the entire application system. Both the specification and the anti-norm are the constraints based on the actual operational basis, which are not meaningful either. Only by combining the two rationally can we complement each other and play our respective advantages.
Iv. reasonable design and management of tables
1, the use of table partitioning
Partitions separate data physically, and data from different partitions can be stored in data files that are located on different disks. So, when you query this table, by simply scanning in the table partition without having to perform a full table scan, the query time is significantly shortened, and the partition on the different disks will scatter the data transfer to the table in different disk I/O, and a carefully set partition can i/the data to the disk o competition is evenly dispersed.
2, to avoid the occurrence of row connections and row migration
When you create a table, the data in the data block will have row and row links because of the incorrect settings for the parameters Pctfree and pctused, that is, the same row of data is not saved in the same data block. Therefore, when you create a table, you should fully estimate the possible future changes in data, set both parameters correctly, and minimize the occurrence of row and row links in the database.
3, the use of aliases
Alias is a large database of application techniques, is the table name, column name in the query with a letter alias, query speed than the build connection table 1.5 times times faster.
Optimal design of Index indexes
1, Management Organization index
The index can greatly speed up the query speed of the database, the index maps the logical value in the table to the safe rowid, so the index can locate the physical address of the data quickly. When querying for an indexed large table, the index data may run out of all chunk cache space, and Oracle has to frequently read and write to the data, so after partitioning a large table, you can establish a partitioned index based on the appropriate partition.
2, the use of the cluster
Oracle provides another way to improve query speed, which is clustering (Cluster). Clustering stores data from multiple tables in the same Oracle block based on common code values, retrieving a set of Oracle blocks and obtaining data from two of tables, which reduces the number of Oracle blocks that need to be stored, thereby improving application performance.
3, optimize the index of setting, must make full use of to speed up the database access speed.
Oracle will use an index with some of the most basic conditions: 1 The field in the where child name must be the first field of the composite index, and 2 the field in the where child name should not be involved in any form of calculation.
Six, multi-CPU and parallel query Pqo (Parallel query Option) utilization
1. Use multiple CPU processors to perform transaction processing and query
The rapid development of the CPU makes Oracle pay more and more attention to the application of the parallel technology of multiple CPUs, the access work of a database can be done with multiple CPUs, and distributed computing is quite common, as long as it is possible to separate the database server from the CPU request of the application. Or move the CPU request from one server to another server. For multiple-CPU systems, use parallel query option (PQO, parallel query options) as much as possible for database operations.
2. Using Parallel Query
Option (pqo, parallel query selection) to query the data. The Pqo method can be used to allocate the request processing of SQL statements between multiple CPUs, and when the data being queried is in different disks, a separate process can simultaneously read the data.
3. Bulk data loading using the Sql*loader Direct path option
When data is loaded using this method, the program creates a formatted block of data that is written directly into the data file and does not require additional I/O to the database kernel.
VII. implementation of System resource management allocation plan
Oracle provides database Resource Manager (DRM, Database resource Manager) to control the user's resource allocation, which the DBA can use to assign a percentage of the system resources of the user class and the job class. In a OLDP system, you can assign 75% of the CPU resources to online users and the remaining 25% to the batch user. In addition, the CPU can also be multi-level allocation. In addition to CPU resource allocation, DRM can also perform parallel operations on the resource user group.
Viii. using optimal database connectivity and SQL optimization scenarios
1. Use a direct OLE DB database connection method.
ADO allows you to connect to a database in two ways, a traditional ODBC approach and an OLE DB approach. ADO is based on OLE DB technology, in order to support ODBC, you must establish the corresponding OLE DB to ODBC call conversion, while using direct OLE DB method without conversion, thereby increasing processing speed.
2. Use of the connection pool mechanism
In database processing, the most expensive resource is to establish a database connection, and the user will have a longer connection wait time. The solution is to reuse the existing connection, that is, to use the connection pool object mechanism. Connection Pool principle is: The iis+asp system maintains a connection buffer pool, so that the next user access, directly in the connection buffer pool to obtain a database connection, without the need to reconnect the database, so can greatly improve the system response speed.
3. Efficient SQL statement Design
In general, you can optimize the performance of SQL for data operations using the following methods:
(1) Reduce the number of queries on the database, that is, to reduce requests for system resources, use of snapshots and maps and other distributed database objects can reduce the number of queries on the database.
(2) Try to use the same or very similar SQL statements to query, so that not only the full use of the SQL shared pool of the parsed syntax tree, the data to be queried in the SGA hit the possibility of a significant increase.
(3) To avoid the execution of SQL statements without any conditions. An SQL statement that has no condition is executed, typically FTS, the database locates a block of data, and then sequentially looks for other data, which is a lengthy process for large tables.
(4) If you have constraints on the data in some tables, it is best to use descriptive integrity for the SQL statements in the table, instead of using the SQL program to implement them.
Nine, the full use of data background processing program to reduce network traffic
1. The full use of database packaging technology
Using the database description language to write the process or function of the database, and then the process or function into a package in the background of the database unified operation package can be.
2. Data replication, snapshots, views, remote process call technology application
Data replication, the data is replicated to the local, so that future queries will use local data, but only for those data that is not changed. Snapshots can also be used to dynamically replicate data between distributed databases, to define automatic refresh times for snapshots, or to manually refresh them to ensure referential referential integrity of the data. Invoking a remote procedure can also significantly reduce network congestion caused by frequent SQL statement calls. In summary, there is no single solution for all performance problems, but Oracle offers a rich selection environment that can be integrated into the architecture, software architecture, schema objects, and specific business and technology implementations of Oracle databases.
Oracle offers a rich selection environment that can be integrated into the architecture, software architecture, schema objects, and specific business and technology implementations of Oracle databases. Improving system performance requires a holistic approach to optimization of the application, I/O subsystem, and the operating system (OS) when optimizing the database. Optimization is the process of making a destination change to one or more components of a system to meet one or more goals. For Oracle, optimization is a purposeful tuning of the component level to improve performance by increasing throughput and reducing response time. If the DBA can synthesize the optimization scenario from the above nine aspects, it is believed that most Oracle applications can access the data in the best way possible.

=================================sql==================================