Some common optimization scenarios for Oracle Database

Take advantage of the system global area SGA:

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 acceleration purposes. The correct SGA size is critical to the performance of the database.

The SGA includes the following sections:

1. The data block buffer cache, which is a chunk of the SGA, accounts for 1%-2% of the entire database size, which is used to store data blocks (tables, indexes, clusters, etc.) that are read from the database, and therefore uses least recently used (LRU, The 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, which are also managed by LRU mode.

3. Redo the log buffer. The buffer is saved for roll forward operations during database recovery

4. SQL shared pool. The parsing tree that holds the SQL statements for the execution plan and the running database. The LRU algorithm is also used to manage. If the setting is too small, the statement will be continuously loaded into the library cache, affecting system performance.

5. In addition, the SGA also includes a large pool, a Java pool, and a multi-buffer pool. But it is mainly composed of the above 4 buffers. The reasonable setting of these memory buffers can greatly speed up the data query speed, a large enough memory area can store most of the data in memory, only those less frequently used data, read from disk, so that can greatly improve the memory area hit rate.

An optimized design for indexed index:

1. Management Organization Index
Indexes can greatly speed up the query speed of a database, which maps the logical values in a table to a secure rowid, so that the index can quickly locate the physical address of the data. But some DBAs have found that indexing a large table does not improve the speed of data queries, but it can affect the performance of the entire database. This is primarily related to the data management approach of the SGA. Oracle has higher residency permissions than normal data for block cache management, and Oracle moves out of normal data when space is competing. When querying a large table with indexes, the index data may run out of all the block cache space, and Oracle has to read and write the disk frequently to get the data, so after partitioning a large table, you can create a partitioned index based on the corresponding partition. If the data query for such a large table is more frequent, or simply do not build the index. In addition, when a DBA creates an index, it should try to ensure that the index is most likely to be used in the WHERE clause, and if the query simply formulates an index, it does not necessarily speed up because the index must specify a suitable access path.

2, the use of cluster

Oracle provides another way to increase query speed, which is clustering (Cluster). The so-called clustering, simply put a few tables together, according to a certain public properties mixed storage. Clustering stores data from multiple tables in the same Oracle block based on common code values, retrieving a set of Oracle blocks to obtain data from two tables at the same time, thus reducing the number of Oracle blocks that need to be stored, thereby improving application performance.

3, optimize the index of settings, you must make full use to speed up database access. Oracle is going to use an index with some of the most basic conditions:

1), where the field in the child name must be the first field of the composite index;

2), the where sub-name in this field, should not participate in any form of computation. sal* (2*90/100)

Efficient SQL statement Design:

Typically, you can use the following methods to optimize the performance of SQL for data operations:

1, reduce the number of queries to the database, that is, reduce the request for system resources, use of distributed database objects such as snapshots and graphs can reduce the number of queries to the database.

2, try to use the same or very similar SQL statements to query, so not only take full advantage of the parsed syntax tree in the SQL shared pool, the likelihood of the data being queried to hit in the SGA will be greatly increased.

3, restricting the use of dynamic SQL, although dynamic SQL is very useful, but even in the SQL shared pool has an identical query value, dynamic SQL will be re-parsing.

4. Avoid the execution of SQL statements without any conditions. SQL statements that do not have any conditions are usually executed with FTS, the database locates a block of data first, and then sequentially finds other data in order, which is a lengthy process for large tables.

5. If you have constraints on the data in some tables, it is best to implement the SQL statement in the table with the description integrity rather than the SQL program.

6. By canceling autocommit mode, the SQL statement is aggregated into a set of executions, and the program can also commit and roll back the transaction by explicitly committing and rollbacl.

7, the retrieval of large amounts of data time is very long, set the number of pre-fetching can improve the performance of the system, set a maximum value, when the SQL statement return row exceeds the value, the value library temporarily stopped execution, unless the user issued a new instruction, start to organize and display the data, rather than let the user wait.

Some common optimization scenarios for Oracle Database