Three effective ways to improve Oracle's handling of large data efficiency __oracle

Oracle performance topics are very broad and there are many books on the market that specifically describe Oracle tuning. The pursuit of performance is endless, need long-term unremitting efforts, but to avoid the problem of performance is not difficult, can be said to be simple. From a simple and practical point of view, this paper gives several effective ways to improve the efficiency of Oracle processing data.

first, metabase initialization parameters

All initialization parameters for the database can be seen in the OEM. See figure below:

If you do not configure these initialization parameters when you create a new database, Oracle gives these parameters a default value. When the database is small in size, the default value of Oracle is usually not experiencing performance problems. The following describes a "significant" impact on the efficiency of handling large amounts of data, and the default values bring several parameters to performance problems:

1) db_block_size

 

This parameter sets the basic unit of Oracle's primary I/O-the size (in bytes) of the database block. It is no exaggeration to say that this parameter is the most important parameter for large amount of data processing. The larger the value of the parameter is, the more advantageous it is for large data processing. Limited to the operating system, NT4 can only be set to 8k,win2k maximum can be set to 16K. The maximum allowable value for Oracle itself is 64K. Forgot to say that the db_block_size should be set to a power of 2.

For analytical databases, setting to 32K is a good choice. For OLTP systems, the author does not have much experience, the online version of the idea is to set 8K is a better balance point. Oracle is a lot of "myths", 8K is not consistent with the current hardware and software situation, but not necessarily in line with the actual situation of our business. If there is time to have the opportunity to compare the 8K or 16K good is always not wrong.

Db_block_size is one of the most basic parameters, and one that is most easily overlooked. This parameter can only be set when the database is created, and cannot be changed thereafter, and can only be remedied by rebuilding the database if there is a mistake. Therefore, you should consider this parameter carefully when you build the library.

2 )

Db_file_multiblock_read_count

Oracle's official Description: The maximum number of blocks read during an I/O operation involving a full continuous scan. For large queries, a full table scan is often more efficient than using an index. The full table scan operation is a typical "full continuous scan". If the db_block_size is set to 32k,db_file_multiblock_read_count set to 8, then a single I/O operation can read up to 8 database blocks, or 256K, in a row.

Db_file_multiblock_read_count not bigger is better. For data analysis systems, the product of Db_file_multiblock_read_count and Db_block_size is 256K enough. For OLTP systems built on UNIX, the product of the 64K is a good choice, according to the online version.

According to the author's experience, it is more efficient to distribute the data continuously on the physical disk than to consider the parameter.

3) sort_area_size

 

The importance of sort_area_size can be said to be tied to db_block_size. This parameter indicates how much memory can be used to sort the database when it executes a query. Limited by system resources, we cannot set this parameter too large. Especially when we build a library in standalone mode, each session may request one or more sort spaces. If we set the Sort_area_size to 8M, and boarded up to 100 concurrent queries, it might take up 800M of memory or even more. When the main memory is not enough, it is necessary to use virtual memory. If Oracle is forced to use virtual memory, the performance of the database will drop dramatically.

For the setting of this parameter, it is recommended that at least more than the square root used to sort the number of records. That is, 1 million records are sorted and each record occupies 1K of space, and the sort_area_size should be set to at least 1M. 10 million records are sorted, each record occupies 1K of space, Sort_area_size set to 4M should be enough.

Based on the above figures, the sort_area_size of an OLTP system may be set to 1M or 2M, and the sort_area_size of the data analysis system may be set to 4M or 8M.

Db_file_multiblock_read_count and Sort_area_size can be modified after the database is established. The modification method is simple. Search Oracle's installation directory, find the Pfile folder (you may find multiple, the name of its parent directory will give us a hint), there is an init text file, according to the contents of the changes can be changed (can't find the relevant parameters to add their own). After the modification, restart the database before it can take effect. Oracle9i above version can do without restarting the database, this article is not introduced.

second, write efficient SQL

Generally speaking, SQL that looks simple does not typically experience performance problems. The execution efficiency of SQL is usually higher than the execution efficiency of the program. Therefore, it should be the guiding principle that we develop to solve problems with SQL as much as possible and to solve them with simple SQL.

Writing efficient SQL requires a certain level of basic skills. This article does not discuss the theoretical basis of SQL. This article only describes a useful technique: an artificial intervention in SQL's execution plan. When SQL is more complex, there are many possibilities for executing the plan. Using the shortest possible time to choose an optimal execution plan is the goal of Oracle's struggle. Oracle database has relevant parameters to adjust the selection of execution plan algorithm, these parameters are not discussed in this article, interested readers can go online to search.

The so-called "Human intervention SQL Execution Plan" actually prompts Oracle how to pick the best execution plan. The syntax for SQL hints is simple: use "/*+" and "* *" to include the prompts, and write the key words in the middle. SQL: Prompt keyword has a lot of, the following introduces a few typical keywords, more use can own Internet search.

1 Specifies a full table scan:

SELECT/*+FULL (table_name) */FIELD1,FIELD2

from table_name;

A large query if you use a significant portion of the data in a large table, the execution plan with a full table scan is much more efficient than an indexed execution plan.

2 data directly into the end of the table, you can increase the speed:

INSERT/*+append*/

INTO table_name

SELECT * from Table_name1;

Many data blocks in Oracle have free space because of the delete operation, and if the APPEND keyword is used, Oracle will not look for these free space blocks to improve the execution speed of the INSERT statement. Note that the APPEND keyword is only suitable for large amount of data inserts.

Third, zoning

Partitioning technology is a bit more complicated than the technology described earlier. Partitioning is actually based on a (some) field in the physics of a large table of data storage, so that can improve the efficiency of our query, but also enhance our data management. Typical examples are partitions based on date fields, so that when we query data for a certain period, we only need to scan a partition's data without having to scan the entire table for data.

<