oracle writing experience (rpm)

2011-02-10  来源:本站原创  分类:Database  人气:84 

This Transfer:

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

  • oracle writing experience (rpm) 2011-02-10

    This Transfer: 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 clic

  • oracle logon experience "can not resolve connectivity descriptor specified sid" Time Solutions 2010-07-30

    oracle logon experience "can not resolve connectivity descriptor specified sid" Time Solutions 1, see whether the instance of the service SID] [start OracleService 2, view the client configuration file network / admin / tnsnames.ora is configure

  • oracle optimization experience sharing 2011-02-15

    Delete duplicate records in a table (ID is the unique key from the increase, duplicate records: the other fields are the same) (Large amount of data, high performance requirements) Table name: T Id name age 1 louis 20 2 louis 20 3 jimmy 30 4 louis 20

  • Oracle SQL optimization (rpm) 2011-03-03

    Oracle SQL optimization rules: Minimize the use of the IN operator, essentially all of the IN operator can be replaced with EXISTS IN written with the advantages of SQL is easier to write and lucid, but always with the IN SQL performance is relativel

  • Oracle database experience some common operations summary 2011-08-24

    I: Database startup steps: ORACLE database instance and database files into the database in two parts, an instance is a memory area, add some background processes, memory area is usually said data cache, shared pool, the background process is, as che

  • Ibatis sql writing experience, two fuzzy query + Dynamic Query 2010-03-23

    In the past few days learning to use IBATIS suddenly needs to use fuzzy queries, and dynamic multiple criteria query, in accordance with their own ideas for a long time trial, failed to resolve the problem. First, the issue is ambiguous query the beg

  • Oracle development experience a 2010-05-28

    The oracle of the operation; 1. Sometimes we order to achieve a certain function, in the oracle, the write function, and then in the business operation, the splicing sql statement, call the function; However, if the write function directly in the ora

  • Oracle common sql (rpm) 2010-11-13

    1. SELECT clause to avoid using "*" When you want listed in the SELECT clause of the COLUMN of all, the use of dynamic SQL column reference '*' is a convenient way. Unfortunately, this is a very inefficient way. In fact, ORACLE parsing process ,

  • JS writing experience 2010-12-02

    Always encountered when writing JS recent inexplicable errors, summed up a few points, I hope to be able to learn! 1. Function name can not be too popular, easy to conflict; Have encountered such a problem, write a name open () function, only one ale

  • ORACLE 10G uninstall (rpm) 2010-12-13

    Software Environment: 1, Windows XP + Oracle 10g 2, Oracle install path: d: \ Oracle Implementation: 1, the start - "Set -" Control Panel - "Administrative Tools -" Service to stop all Oracle services; 2, the start -> programs ->

  • Oracle date comparison (rpm) 2011-07-19

    oracle sql date comparison: Before today: <!--< Br> <br> Code highlighting produced by Actipro CodeHighlighter (freeware) <br> <br> <br> -> select * from up_date where update <to_date

  • oracle memory management (rpm) 2011-02-24

    Oracle's 9i/10g memory management has done a lot of gradually simplified, 11g step further, introducing a new concept of automatic memory management (Automatic Memory Management, AMM). If you really want to be lazy DBA, then, only set two parameters

  • oracle modify tablespace (rpm) 2011-03-18

    Briefly in the previous version of Oracle 10g, change the name of the table space is almost impossible, unless removed, re-creating, troublesome. Oracle 10g adds a new table space to change the name of the function, so you can instantly change the na

  • mysql trigger real-world experience (rpm) 2011-03-31

    1 Introduction Mysql triggers and stored procedures, are embedded into the section of the mysql program. Triggers are mysql5 new features, the current online Phoenix Nest, Columbus Compass system and database system are mysql5.0.45 version, many prog

  • An oracle optimization experience 2011-05-06

    Background: a user behavior analysis system, using the oracle database to record the user's visit the site each day, each time the user click a record will have a day off the entire site will probably have a ten million click record. System architect

  • oracle tree queries (rpm) 2011-05-13

    Oracle query tree, the most important thing is to select ... start with ... connect by ... prior syntax. Relying on the grammar, we can shape the structure of a table in the order listed in the tree. Oracle in the following sets out the common querie

  • oracle related Summary (rpm) 2011-06-12

    Article from: Do not know who the original author had wanted to mark the original source URL, sorry. One. Oracle database data types commonly used varchar2 (length) variable length strings char (lengt

  • oracle first experience 2011-07-03

    I declare a new oracle, write this log is intended to be a record of some of the knowledge points to facilitate the later, but for more acquaintance friends oracle to provide a reference! 1 install oracle a "service handle not initialized" error

  • ORACLE installation experience 2011-08-31

    First, remove oracle 9i Remove the registry HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE Delete oracle service HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ ORACLE all services under Delete event log HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentCont

  • ORACLE TRIM explain (rpm) 2011-09-26

    In Oracle / PLSQL, the trim function removes all specified characters either from the beginning or the ending of a string. The syntax for the trim function is: trim ([leading | trailing | both [trim_character]] string1) leading - remove trim_string f