Shulou(Shulou.com)06/01 Report--

1. If developers use Table or View of other libraries, be sure to establish View in the current library to achieve cross-library operation. It is best not to use "databsevv.dbo.table_name" directly, because sp_depends can not show the cross-library table or view used by the SP, so it is not convenient to verify.

2. Before submitting SP, developers must have used set showplan on to analyze the query plan and do their own query optimization check.

3. In order to improve the running efficiency of the program and optimize the application, we should pay attention to the following points in the process of writing SP:

A) specifications for the use of SQL:

i. Try to avoid large transaction operations and use holdlock clauses cautiously to improve the concurrency ability of the system.

ii. Try to avoid repeatedly visiting the same table or several tables, especially those with a large amount of data, you can consider first extracting data into temporary tables according to conditions, and then making connections.

iii. Avoid using cursors as much as possible, because cursors are inefficient, and should be rewritten if they operate on more than 10,000 rows of data; if cursors are used, try to avoid table joins in the cursor loop.

iv. Pay attention to where sentence writing, must consider the sentence order, should be based on the index order, scope size to determine the order of conditional clauses, as far as possible to make the field order consistent with the index order, the range from large to small.

v. Do not perform functions, arithmetic operations, or other expression operations to the left of the "=" in the where clause, or the system may not be able to use the index correctly.

vi. Try to use exists instead of select count (1) to determine whether there is a record. The count function is only used when there are all rows in the statistical table, and count (1) is more efficient than count (*).

vii. Try to use "> =", do not use ">".

viii. Note the substitution between some or clauses and union clauses

ix. Pay attention to the data types of connections between tables to avoid joins between different types of data.

x. Note the relationship between parameters and data types in Oracle stored procedures.

xi. Pay attention to the amount of data in insert and update operations to prevent conflicts with other applications. If the amount of data exceeds 200 data pages (400k), the system will upgrade the lock and the page-level lock will be upgraded to a table-level lock.

B) specifications for the use of the index:

i. The creation of the index should be considered in combination with the application. It is recommended that the large OLTP table should have no more than 6 indexes.

ii. Use index fields as query conditions as much as possible, especially clustered indexes. If necessary, you can use index index_name to force the specified index.

iii. Avoid table scan when querying large tables and consider creating new indexes if necessary.

iv. When using an index field as a condition, if the index is a federated index, the first field in the index must be used as a condition to ensure that the system uses the index, otherwise the index will not be used.

v. We should pay attention to the maintenance of the index, rebuild the index periodically, and recompile the Oracle stored procedure.

C) specifications for the use of tempdb:

i. Avoid using distinct, orderby, groupby, having, join, and * * pute as much as possible, as these statements add to the burden of tempdb.

ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

iii. When creating a new temporary table, if you insert a large amount of data at one time, you can use select into instead of create table to avoid log and improve speed; if the amount of data is small, in order to ease the resources of the system table, it is recommended to first create table, and then insert.

iv. If the temporary table has a large amount of data and needs to be indexed, then the process of creating the temporary table and indexing should be placed in a separate sub-stored procedure to ensure that the system can make good use of the index of the temporary table.

v. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate table, and then drop table, to avoid prolonged locking of system tables.

vi. Be careful to use large temporary tables to query and modify connections with other large tables to reduce the burden on system tables, because this operation will use tempdb's system tables multiple times in one statement.

D) reasonable use of algorithms:

According to the SQL optimization technology mentioned above and the SQL optimization content in the ASE Tuning manual, combined with practical application, a variety of algorithms are compared to obtain the method with the least consumption of resources and the highest efficiency. ASE tuning commands are available: setstatistics io on, setstatisticstimeon, set showplan on, etc.

Key points of optimization of Oracle stored procedures and Sql statements in Oracle 2008-07-2909 Sql 14 | Doomsday style

1. Full table scan and index scan

Big data scale should try to avoid full table scanning, all scans will scan each record sequentially, which has a great impact on the > 1 million data table.

Accessing data through RowID is the fastest way in Oracle.

Functional conversion of fields or pre-fuzzy queries will result in full table scans that cannot be applied to the index.

The Sql in the Oracle shared pool and buffer must be fully capitalized in order to match

two。 Order problem

Oracle parses the data table from right to left. Therefore, the last table in From is the basic table, and the table with the least number of records is generally selected as the basic table.

For the order of the Where condition, the condition filtered to the maximum number of query records must be written at the end of the Where condition.

When it comes to the use of complex functions in the Where condition, attention must be paid to writing to the front of the Where condition.

3. Index aspect

The table with a small number of records can keep the primary key index, do not build other indexes, and the full table scan is also very fast.

It is best to create a separate tablespace for the index and rebuild the index if necessary

Functional indexes can be used if necessary, but not recommended

Views in Oracle can also be indexed, but generally not recommended

* when a large number of functions are used in Sql statements, many indexes cannot be used. It is necessary to analyze specific problems.

4. Other

Avoid using Select * because the system needs to help you convert * to all column names, which requires additional queries for data dictionaries.

There is little difference between Count (1) and Count (*).

Use more Decode functions to do simple code-to-name conversions to reduce table association

Use Truncate instead of delete to delete records, but Truncate data is not logged and cannot be rolled back

It is necessary to divide multiple Commit for data that can be submitted many times by complex Oracle stored procedures, otherwise long transactions have a great impact on system performance.

Both Distinct and Having clauses are time-consuming operations and should be used as little as possible

Use Union All instead of Union when there is no need to consider duplicate record merging

Using explicit cursors instead of implicit cursors, especially in the case of large amounts of data, has a great impact on performance.

The question of whether to use a function

Replace Exist with direct table association. Use Exist or Not Exists to proxy In. The In subquery is not very efficient.

5.SQL sentence analysis

The performance of Sql statement is analyzed by SET TRACE function in SQLPLUS.

Analyze the performance of statements and the usage of indexes through Toad or PL/SQL Developer

If you are not satisfied with the default optimization of Oracle, you can force the use of Hint, but it is generally not recommended.

Indexing is generally not recommended for fields such as Flag that only store yes or no information. Bitmap indexing can be used if necessary

* Recursive query exists. If there are too many associated Table, it will have a great impact on performance. It is often recommended to use temporary tables to switch to step-by-step operations to improve performance.

* try to use the table association query instead of the function, but it is suitable to use the function when it is similar to the problem of repeatedly associating multiple data fetches in the code table.

The above content is a description of the Oracle stored procedure writing experience and optimization measures, I hope it will give you some help in this regard.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.