Oracle Stored Procedure Tuning Basics

1, if you use the table or view of other libraries, be sure to create a view in the current library to achieve cross-library operation, it is best not to use "Databsevv.dbo.table_name", because sp_ Depends cannot display the cross-Library table or view used by the SP, and is not easy to verify.

2, the developer before submitting the SP, must already use set SHOWPLAN on to analyze the query plan, has done its own query optimization check.

3, high program operation efficiency, optimize the application, in the SP writing process should pay attention to the following points:

a) Usage specification for SQL:

I. Try to avoid large business operations, and use HOLDLOCK clauses with caution to improve the concurrency capability of the system.

II. Try to avoid repeated access to the same or several tables, especially those with a large amount of data, you might consider extracting the data into a temporary table based on conditions before making a connection.

III. Avoid using cursors as much as possible, because cursors are inefficient and should be overwritten if the cursor is operating with more than 10,000 rows of data, and if a cursor is used, try to avoid the operation of table joins in the cursor loop.

Iv. Note the where sentence is written, you must consider the order of the statements, should be based on the index order, the range size to determine the order of the condition clauses, as far as possible to make the field order and index order consistent, ranging from large to small.

v. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

VI. Try to use exists instead of select COUNT (1) to determine if a record exists, the Count function is used only for all rows in the statistics table, and COUNT (1) is more efficient than count (*).

VII. Use ">=" as much as possible and do not use ">". Viii. note the substitution between some or clauses and the Union clause

IX. Note The data types that are connected between tables to avoid connections between different types of data.

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

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

b) Index usage specification:

I. The creation of an index to be considered in conjunction with an application, it is recommended that a large OLTP table not exceed 6 indexes.

II. Use indexed fields as much as possible as query criteria, especially clustered indexes, and, if necessary, by using the index index_name to force the index to be specified

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

Iv. When using an indexed field as a condition, if the index is a federated index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used.

v. To be aware of the maintenance of the index, periodically rebuild the index and recompile the Oracle stored procedure.

c) Usage specification for tempdb:

I. Try to avoid using distinct, order BY, group BY, have, join, ***pute, because these statements aggravate the burden of tempdb.

II. Avoid frequent creation and deletion of temporary tables, reducing the consumption of system table resources.

III. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table, avoid log, improve speed, if the amount of data is small, in order to alleviate the resources of the system table, we recommend that you create table first, Then insert.

Iv. If the data volume of the staging table needs to be indexed, then the process of creating the staging table and indexing should be placed in a single sub-stored procedure, in order to ensure that the system can use the index of the temporary table well.

v. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

VI. Careful use of large temporary tables and other large table connection query and modification, reduce the system table burden, because this operation will be in one statement multiple times the system table of tempdb.

d) Rational use of the algorithm:

based on the SQL optimization techniques mentioned above and the SQL optimization content in the ASE Tuning manual, a variety of algorithms are used to compare with each other in order to obtain the least cost and most efficient method. Specific ASE tuning commands are available: SET STATISTICS IO on, SET statistics time on, set Showplan on, and so on.

optimization of Oracle stored Procedures and SQL statements in Oracle 2008-07-29 09:14 | Doomsday style

1. Full table scan and Index scan

Big Data Scale to avoid full table scan, all scans are scanned sequentially for each record, which has a great impact on the >100 data sheet.

The fastest way to access data through ROWID in Oracle

A function conversion to a field, or a previous fuzzy query, would result in a full table scan without applying an index

SQL in the Oracle shared pool and buffer must be case-fully used to match

2. Sequencing issues

Oracle parses a data table in a right-to-left order. Therefore, the table at the back of the table is the underlying table, which is typically selected as the base table with the least number of records.

for the order of where conditions, the criteria for filtering to the maximum number of query records must be written at the end of the Where condition.

The where condition involves the use of complex function judgments must be noted to write to the front of the Where condition

3. Index aspects

A table with a small number of records retains a primary key index, so you don't have to build another index, and the full table scan is fast.

The index is best to create a separate table space and rebuild the index when necessary

function indexes can be used when necessary, but not recommended

views in Oracle can also be indexed, but it is generally not recommended

The use of functions in *SQL statements can cause many indexes to be unusable, and to analyze specific problems

4. Other

Avoid using SELECT * because the system needs to help you convert the * to all column names, which requires additional querying of the data dictionary.

count (1) and COUNT (*) differ little.

use the Decode function to make simple conversions between code and names to reduce table Association

Delete a record using truncate instead of DELETE, but truncate data does not log and cannot be rolled back

multiple commits for complex Oracle stored procedures that can submit data multiple times, otherwise long transactions have a significant impact on system performance

DISTINCT and HAVING clauses are time-consuming operations and should be used as little as possible

Use UNION ALL instead of union when you don't need to consider repeating records merging

explicit cursors are used without implicit cursors, especially when large data volumes have a significant impact on performance

questions about whether to use functions

Use a direct Table association instead of exist. Use exist or not exists to proxy in. In making subqueries is inefficient.

5.SQL Statement Analysis

analyze the performance of SQL statements by using the Set TRACE function in Sqlplus

analysis of performance and index usage of statements by toad or PL/SQL developer

not satisfied with Oracle default optimizations you can force the use of hint, but it is generally not recommended

It is generally not recommended to index a field that stores only or no information for flag. You can use bitmap indexing if necessary

* There is a recursive query situation if the association table too many impact on performance, it is often recommended to take the temporary table to step-up operation to raise performance

* Try to use a table association query instead of a function, but involve a function similar to a code table that repeats multiple data fetching problems

Oracle Stored Procedure Tuning Basics