oracle stored procedure tuning Basics

2011-06-07  来源:本站原创  分类:Database  人气:114 

1, 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-library 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 instead of 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 scans and index scans

Large scale data 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 index in

Number of records to retain a small table 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

Avoid the use of 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 statement analysis

The SET TRACE through SQLPLUS Sql statement on the performance of functional analysis

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

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

相关文章
  • oracle stored procedure tuning Basics 2011-06-07

    1, 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-library table o

  • oracle stored procedure tuning Summary 2011-08-09

    1 for the partition table data, deleted partition table directly, alter table table_name drop partition partition_name; partition because the partition table is equivalent to a separate table, delete the partition is equivalent to directly remove a t

  • Oracle stored procedure 2010-03-30

    1. Overview Oracle stored procedure development key points: • Use Notepad text editor, using Oracle PL / SQL programming language to write a stored procedure; • In the Oracle database to create a stored procedure; • In the Oracle database using SQL *

  • Oracle stored procedure in the preparation of lessons learned 2010-06-23

    1, developers use other libraries if the Table or View, be sure to create a View in the current database to cross-database operations, it is best not to directly use the "databsevv.dbo.table_name", because it can not show that the SP sp_depends

  • Instance of Oracle stored procedure development 2010-10-25

    Instance of Oracle stored procedure development 2009-5-14 11:21:00 <script type="text/javascript"> </ script> <script src="http://images.sohu.com/cs/jsfile/js/ct.js" type="text/javascript"> </ script >

  • Calling oracle stored procedure with java Summary 2009-07-18

    1: no return value stored procedure Stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called inside the java

  • java call the Oracle stored procedure 2010-03-30

    Time to start learning to write stored procedures, the main reason is because the work requires it, would have thought that simple, but after several setbacks, the heroic spirit vanished, but finally pass out, and in order to avoid later, few detours

  • Call oracle stored procedure with java Summary (transfer) 2010-03-07

    A: No return value stored procedure Stored procedure is: CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called in the java in the followi

  • basic syntax oracle stored procedure 2010-03-29

    Keywords: oracle 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select

  • oracle stored procedure syntax 2010-03-30

    1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select the query results

  • The basic syntax of oracle stored procedure and precautions 2010-03-31

    The basic syntax of oracle stored procedure 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELEC

  • ORACLE stored procedure in the asynchronous call 2010-04-09

    ORACLE stored procedure in the asynchronous call Analysis: Wang Hui Source: SAN Published: 2003.05.26 1. About the Author Wang Hui work since 1994, has served as teacher, database administrators, the main programmer, project manager for a company as

  • oracle stored procedure, the array as the entry parameters, the operation of the database accordingly. 2010-04-15

    oracle stored procedure, the array as import parameters, the operation of the database accordingly. 1. To create a similar type of table as rec_tab; create or replace type rec_tab as object (Field 1 VARCHAR2 (16), Field 2 VARCHAR2 (15), .... ); 2. To

  • Oracle stored procedure execution 2010-04-21

    Oracle stored procedure to execute in two ways, as follows: 1, exec (or execute) procedure_name (parameter list); 2, declare var1 type: = initValue; var2 type: = initValue; ... begin procedure_name (var1, var2 ,...); end;

  • C # How to call oracle stored procedure in the most simple example 2010-04-26

    C # calling oracle stored procedure example of the simplest Oracle side 1. Create Oracle stored procedure create or replace procedure proce_test (paramin in varchar2, paramout out varchar2, paraminout in out varchar2) as varparam varchar2 (28); begin

  • _ Oracle stored procedure help documentation 2010-04-27

    oracle stored procedure help documentation

  • Oracle stored procedure to learn 2010-05-14

    The basic syntax of oracle stored procedure Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the v

  • learning oracle stored procedure 2010-05-15

    Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the variable 1 from table A where column name = p

  • Oracle stored procedure and cursor 2010-06-02

    Oracle stored procedure and cursor Oracle stored procedure and cursor: select myFunc (parameter 1, parameter 2 ..) to dual; - one can perform some business logic: Oracle functions and stored procedures in the difference between: A: function must retu

  • Using Spring's JdbcTemplate call Oracle stored procedure 2010-06-05

    Spring's SimpleJdbcTemplate stored procedure calls for a good package, but unfortunately only for jdk1.5 environment, can no longer use jdk1.4 environment, while the JdbcTemplate is fully applicable under jdk1.4 environment, the following column a ca