This Transfer: http://bbs.chinabyte.com/thread-381770-1-1.html
The following article is written for Oracle stored procedure experience and optimization of specific operations, if you are related to the actual operation of their interest, you can click the following article watched. Hope you browse the contents will be finished the following harvest.
1, developers use other libraries if the Table or View, be sure to create a View in the current library to implement cross-database, it is best not to use "databsevv.dbo.table_name", because it can not show that the SP sp_depends used Cross-database table or view, convenient check.
2 developers submit SP, you must have been analyzed using the set showplan on query plan, query optimization has done its own inspections.
3, high-efficiency program, optimizing the application, the process of writing the SP should note the following:
a) SQL of Use:
i. to avoid large transaction operations, caution holdlock clause, improve system concurrency.
ii. to avoid repeated access to the same one or a few tables, especially large amount of data tables, it can consider the conditions to extract the data according to a temporary table, then do the connection.
iii. try to avoid using a cursor, because the poor efficiency of the cursor if the cursor operation more than 10,000 lines of data, then it should be rewritten; If you use a cursor, it is necessary to avoid the cursor and then loop table join operation.
iv. Note that where the words written, the statement must be considered in order, the order should be based on the index, to determine the size range of conditions before and after the order of clauses, as much as possible so that field order is consistent with the order index, ranging from big to small.
v. not in the where clause "=" left for the function, arithmetic operations, or other expressions, or the system may not be the proper use of the index.
vi. to make use exists instead of select count (1) to determine whether there is record, count function only in the statistics used in all rows, and count (1) over count (*) more efficient.
vii. to make use of ">=", Do not use ">." viii. Note that some of the union clause or clauses and substitution between
ix. Note that the connection between the table data type, to avoid the connection between different types of data.
x. Note that Oracle stored procedure parameters and data types of relationships.
xi. Note that insert, update operational data, to prevent conflicts with other applications. If the data of more than 200 data pages (400k), then the system will lock escalation, page-level lock will be upgraded into a table-level lock.
b) index of Use:
i. index creation and application integration to consider the proposed large OLTP table not more than six indexes.
ii. as much as possible using the index field as a query, especially in the clustered index, if necessary, to force through the specified index index index_name
iii. to avoid a large table when the query table scan, when necessary, to consider the new index.
iv. as a condition of using the index field, if the index is a combined index, you must use the index in the first field as a condition to ensure the system uses the index, otherwise the index will not be used.
v. to pay attention to maintenance of the index, periodic re-index, recompile the Oracle stored procedure.
c) tempdb of Use:
i. to avoid using distinct, order by, group by, having, join, *** pute, because these statements will increase the burden of tempdb.
ii. to avoid the frequent creation and deletion of temporary tables, system tables to reduce resource consumption.
iii. in the new temporary table, if one inserts a large amount of data, you can use select into Daiti create table, to avoid the log, increase speed; if the amount of data, in order to ease the system table of resources, the proposed first create table, then insert.
iv. If a large amount of data to temporary table, need to create the index, you should create a temporary table and indexing process of a child stored in a separate process, so as to ensure the system can use a good index to the temporary table .
v. If you used a temporary table in stored procedure will last all the temporary table must be explicitly removed, first truncate table, then drop table, to avoid locking the system tables a long time.
vi. caution large temporary tables and other large tables join query and modify, to reduce the burden on system tables, because this operation will be used multiple times in a single statement tempdb system tables.
d) a reasonable algorithm to use:
According to the above mentioned SQL optimization technology and ASE Tuning manual SQL optimize content, combined with practical application, using a variety of algorithms compared in order to obtain a minimum consumption of resources, the most efficient way. ASE can be used to tune the specific command: set statistics io on, set statistics time on, set showplan on and so on.
Oracle stored procedure in Oracle and Sql statements to optimize focus 2008-07-29 09:14 | Doom style
1 full table scan and index scan large data scale as far as possible to avoid full table scans, all scans will scan each record in order for> 100 million data form a significant influence.
Oracle by RowID is the fastest way to access data
A function of field conversion, or before the application of fuzzy query can lead to full table scan and index
Of the Oracle shared pool and buffer in case of Sql must spend to be able to fully match the
2. Order problem
Oracle in accordance with the order from right to left on the table for resolution. From the final surface of the table so the basis of the table, generally choose the least number of table records as a basis for the table.
Where the conditions for the order, the query filter to the maximum number of conditions must be recorded in writing at the end of Where the conditions.
Where conditions related to the use of complex functions must be taken to determine the conditions at the top Where to write
3 The small number of index records in the table retains the primary key index on it, do not go to build the other indexes, full table scan quickly
Index is best to establish a separate table space, when necessary, rebuild the index
When necessary, can use the function index, but not recommended
Oracle can also be increased in view of the index, but generally not recommended
* Sql statements in the extensive use of function will lead to a lot of time on the index can not be used to analyze specific problems
4 Other avoided Select *, because the system needs to go and you will * convert all the column names, this extra to query the data dictionary.
Count (1) and Count (*) is not very different.
Decode function to make more use of simple conversion between the code and name, to reduce the associated table
Truncate alternative use DELETE to delete records, but data is not Truncate log, can not be rolled back
Oracle stored procedures for complex data submitted can be repeated many times to be multisection Commit, or long transaction a great impact on system performance
Distinct and Having clause are time-consuming operation, it should use as little as possible
Merge duplicate records without considering the time to replace the Union with the Union All
Instead of using explicit cursors implicit cursors, especially in the case of a large amount of data hidden cursor a great impact on performance
Question whether the function
Associated with the table directly instead of Exist. With Exist or Not Exists to agents In. In the sub-query performance is poor.
5.SQL SQLPLUS statement analysis through the SET TRACE function to analyze the performance of Sql statements
By Toad or PL / SQL Developer of the statement and the performance index to analyze the use of
Not satisfied with the default optimization for Oracle can force the use of Hint, it is generally not recommended
To the Flag and other information are stored only yes or no fields, indexing is generally not recommended. Necessary to use a bitmap index
* Recursive queries if the situation there is too much relevance Table great impact on performance will often recommend the use of temporary tables to improve performance step by step operation
* To use the table associated with the query rather than function, but involve similar to the code table to be repeated several times to take the data associated with the problem when they use the function for