Oracle Stored Procedure writing experience and Optimization Measures

Oracle Stored Procedure writing experience and Optimization Measures

1. If developers use tables or views of other databases, they must create a view in the current database to perform cross-database operations. It is best not to directly use "Databse. DBO. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

 

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage specifications:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

III. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

IV. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

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

VII. Try to use "> =" instead of "> ". VIII. Note the replacement between the or clause and the union clause.

IX. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

XI. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page lock will be upgraded to the table lock.

B) Specification for indexing:

I. You should consider creating indexes in combination with applications. We recommend that you create a large OLTP table with no more than six indexes.

Ii. Try to use the index field as the query condition, especially the clustered index. If necessary, you can use index index_name to forcibly specify the index.

Iii. Avoid performing table scan when querying large tables. If necessary, create an index.

IV. when using an index field as a condition, if the index is a joint index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used.

V. Pay attention to index maintenance, rebuild indexes periodically, and recompile the stored procedure.

C) use of tempdb:

I. Try to avoid using distinct, order by, group by, having, join, and *** pute, because these statements will increase the burden on tempdb.

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

III. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.

IV. if the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.

V. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

Vi. Use caution when connecting large temporary tables to other large tables to query and modify them, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in one statement.

D) Reasonable algorithm usage:

Based on the SQL optimization technology mentioned above and the SQL Optimization content in the ASE tuning manual, combined with practical applications, a variety of algorithms are used for comparison to obtain the method with the least resource consumption and the highest efficiency. Specific ASE optimization commands are available: Set statistics Io on, set statistics time on, set showplan on, and so on.

# Optimization of stored procedures and SQL statements in Oracle 1. Full table scan and index Scan
The big data table tries its best to avoid full table scans. All scans Scan each record in sequence, which has a great impact on data tables larger than 1 million.
In Oracle, rowid is the fastest way to access data.
Function Conversion of fields, or prefix fuzzy query, will cause failure to apply indexes for full table scan.
The SQL statements in the Oracle shared pool and buffer must be case-insensitive before they can be matched.

2. Order Problems
Oracle parses data tables from right to left. Therefore, the table at the end of the from statement is the base table. Generally, the table with the least number of records must be selected as the base table.
For the order of where conditions, the conditions for filtering the maximum number of query records must be written at the end of where conditions.
When using a complex function for the where condition, you must note that you must write it to the beginning of the where condition.

3. Indexing
A table with a small number of records can be retained with a primary key index. Do not create other indexes. The full table scan is fast.
It is best to create a tablespace for the index separately and re-build the index if necessary.
Function indexes can be used if necessary, but are not recommended.
Views in Oracle can also be indexed, but it is generally not recommended
* When a large number of functions are used in SQL statements, many indexes cannot be used. analyze the specific problems.

4. Others
Avoid using select * because the system needs to help you convert * to all column names. This requires additional data dictionary queries.
The difference between count (1) and count (*) is not big.
Use the decode function to convert code and name to reduce table Association.
Use truncate instead of Delete to delete records. However, truncate data does not record logs and cannot be rolled back.
For complex stored procedures, data that can be submitted multiple times must be committed multiple times. Otherwise, long transactions will have a great impact on system performance.
Distinct and having clauses are time-consuming operations and should be used as little as possible
Union all is used to replace union when repeat record merging is not required.
Explicit cursors are used instead of implicit cursors, especially when the data volume is large.
Whether to use functions
Use Direct table Association instead of exist. Use exist or not exists to represent in. Sub-queries in are inefficient.

5. SQL Statement Analysis
Use the set trace function in sqlplus to analyze the performance of SQL statements
Analyze the statement performance and index usage through toad or PL/SQL developer
Hint can be forcibly used if you are not satisfied with Oracle's default optimization, but it is not recommended.
It is generally not recommended to create an index for fields such as flag that only store yes or no information. Bitmap indexes are required.
* If there is a recursive query, too many associated tables will have a great impact on the performance. It is often recommended to convert a temporary table to a step-by-step operation to improve the performance.
* Try to use table join queries instead of using functions, but it is applicable to functions that are similar to code tables that need to be repeatedly associated for multiple data retrieval {
Return setreplyauhor ("Doomsday style ")
} 'Href = "http://writeblog.csdn.net/pure/archive/2008/07/29/218254.html#post"> reply to more comments