NO1: Select the appropriate optimizer
See the relevant information about the contents of the optimizer.
NO2: Share SQL
In order not to repeat the same SQL statement, after the first resolution, Oracle stores the SQL statement in memory. This block in the shared buffer pool of the system global area can be All database users share. So, when you perform a SQL statement (sometimes called a cursor), if it
And the previously performed statements are exactly the same, Oracle can quickly get the statements that have been parsed and the best
Perform a path. Oracle's functionality greatly increases SQL execution performance and saves memory usage.
Unfortunately, Oracle only provides cache buffering for simple tables, which does not apply to multi-table connection queries.
The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the more statements can be retained, and the possibility of being shared is, the larger.
When you submit an SQL statement to Oracle, Oracle will first find the same statement in this memory.
What needs to be indicated here is that oracle takes a strict match to reach a sharing, the SQL statement must
Example (including space, wrap, etc.).
The shared statement must meet three conditions:
A. Comparison of characters:
B. The object referred to in the two statements must be exactly the same:
C. Bind variation of the same name must be used in two SQL statements (Bind Variables)
NO3. Select the most efficient table order (only valid in rule-based optimers)
Oracle's parser handles the table name in the FROM clause in order from right to left, so the FROM clause writes in the final table (base table driving table) will be processed. In the FROM clause contains multiple In the case of the table, you must select the table for the number of records as a base table. When Oracle handles multiple tables, you will connect to them, first, first, scan the first table (last in the FROM clause That table), sort the record, then scan the second table (the last second table in the FROM clause), finally consolidate all records detected from the second table with the appropriate record in the first table. .
If there are more than 3 table connection queries, you need to select a crosstum (INTERSECTION TABLE) as a base table, and the crosstab refers to the table referenced by the other tables.
NO4: connection order in where clause
Oracle parses the WHERE clause from the bottom, according to this principle, the connection between the tables must be written before other WHERE conditions, and the conditions that can be filtered out must be written at the end of the WHERE clause.
NO5: Avoid using SELECT *
When you want to list all Column in the SELECT clause, use dynamic SQL columns to reference '*' is a convenient way. Unfortunately, this is a very inefficient method. In fact, Oracle is in parsing In the middle, the '*' will be converted into all column names, which is done by querying the data dictionary, which means more time.
NO6: Reduce the number of access to the database
When performing each SQL statement, Oracle has performed a lot of work inside: parsing the SQL statement, estimating the utilization, binding variables, reading data blocks, etc. Reduce the workload of Oracle.
NO7: replacing Delete with truncate
When the recording in the table is deleted, in general, the rollback segments is used to store information that can be recovered. If you don't have a commit transaction, Oracle restores the status before the data is deleted (accurately said Restore the situation before performing the deletion command)
And when using Truncate, the rollback segment no longer stores any information that can be recovered. When the command is run, the data cannot be recovered. Therefore, few resources are called, the execution time will be short.
NO8: Appropriate use of commits
The performance of this program is improved, and the demand will also decrease due to the resources released by CommMit:
Resources released by Commit:
a. Information on the recovery of data is returned.
b. Locks obtained by the program statement
c. Space in Redo Log Buffer
d. Oracle is used to manage the internal costs of the above three resources.
Commit
You must pay attention to the integrity of the transaction
,
Reality and the integrity of efficiency and business integrity are often fish and bear's own, and the use is too frequent.
Commit
Efficiency will also be reduced.
NO9: Reduce the query of the table
In the SQL statement containing subquery, pay special attention to reduce the query of the table.
Update multiple column examples:
Inefficient:
Update EMP
SET EMP_CAT = (SELECT MAX (CATEGORY) from EMP_CATEGORIES,
SAL_RANGE = (SELECT MAX (SAL_RANGE) from EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
Efficient:
Update EMP
Set (EMP_CAT, SAL_RANGE)
= (SELECT MAX (Category), Max (SAL_RANGE)
From EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
NO10: IN with EXIST
Let me talk about
He is equivalent to executing the Inner Table, and then the obtained query result set is connected to the Outer Table, of course, the use of the connection and the index is still with ordinary two tables.
Select * from T1 Wheree x in (select y from t2);
Can be converted into the following
SELECT * FROM
T1, (SELECT DISTINCT Y from T2) T2
WHERE T1.X = T2.Y;
Saying exists
In fact, Exists is equivalent to the full-scale scan of Outer Table, and the corresponding eligible result of each row and INNER TABLE search, which is the main overhead is the full table scan for Outer Table, And the connection method is the NESTED LOOP method.
Can write
Select * from T1 WHERE EXISTS (SELECT NULL from T2 Where T2.Y = T1.x);
Convert into
For Cursor1 in (SELECT * from T1)
loop
IF (SELECT NULL from T2 Where T2.Y = CURSOR1.X))
THEN
Returns the recorded record;
END IF;
End loop;
Through the above explanation, it is easy to understand that when the amount of data is large and the index is not good (a large number of repetition values, etc.), it is not advisable to generate a huge IN operation that generates the DISTINCT search for T2, and vice versa Huge (unaffected) and less data of T2 tables and should not use exists operations that cause T1 full table scanning
NO11: NOT IN, NOT EXIST
In the child query, the Not in clause will execute an internal sort and merge. No matter which case, Not in is minimal (because it performs a full table traversal in the subquery). In order to Avoid using NOT IN, we can rewrite the Outer Join, or NOT EXISTS.
NO12: Use table connection to replace EXIST
Typically, the way to use table connections is more efficient than EXISTS.
SELECT ENAME
From EMP E
WHERE EXISTS (SELECT 'X'
From dept
Where dept_no = E.DEPT_NO
And dept_cat = 'a');
(More efficient)
SELECT ENAME
From DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO
And dept_cat = 'a';
NO13: Improve efficiency by indexing 1. Index foundation
Index is a concept part of the table, used to improve the efficiency of retrieval data. In fact, Oracle uses a complex self-balance B-Tree structure. Usually, the query data is scanned than full menu. When Oracle is found The Oracle Optimizer will use index when querying the best path for the Update statement. The same use index is also increasing efficiency when connecting multiple answers. Another benefit of using an index is that it provides a uniqueness of primary keys. verification.
In addition to those LONG or long RAW data types, you can index almost all columns. Typically, use indexes in large tables. Of course, you will also find that when scanning small tables, use indexes can also improve efficiency.
Although the use of indexes can be improved in query efficiency, we must also pay attention to it. Index requires space
Storage, you also need regular maintenance, and whenever there is a record in the table, the index itself will be modified. This means that every record of INSERT, DELETE, UPDATE will pay more 4, 5 Supreme disk I / O. Because index requires additional storage space and processing, those unnecessary indexes will slow down the query response time.
Oracle has two access modes for the index.
Index unique Scan (Index Unique Scan)
In most cases, the optimizer accesses Index through the WHERE clause.
E.g:
Table Lodging has two indexes: the uniqueness index lodging_pk, which is built on the LODGING column, and the non-unique index that is built on the Manager column Lodging $ Manager.
SELECT *
From lodging
WHERE LODGING = 'Rose Hill';
Internally, the above SQL will be divided into two steps. First, the LODGING_PK index will be accessed by the index unique scan, obtain the corresponding RowID, perform the next search method through the RowID access table.
If the column returned by the retrieved returned is included in the index column, Oracle will not perform the second step (via the RowID access table). Because the retrieval data is saved in the index, single access index can fully meet the query results.
The following SQL only needs Index Unique Scan operations.
SELECT LODGING
From lodging
WHERE LODGING = 'Rose Hill';
Index range query (Index Range Scan)
Suitable for two cases:
1. Based on a range of retrieval
2. Retrieval based on unique index
example 1:
SELECT LODGING
From lodging
WHERE LODGING LIKE 'M%';
WHERE clause conditions include a series of values, Oracle will query lodging_pk by inquiry through the index range. Since the index range query will return a set of values, its efficiency is the only scan
Low.
Example 2:
SELECT LODGING
From lodging
WHERE manager = 'bill Gates';
This SQL execution is divided into two steps, and the index range of Lodging $ Manager is queried (get all the requirements of the ROWID) and the next step of the RowID access table to get the value of the Lodging column. Because Lodging $ Manager is an unique index, The database cannot perform an index unique scan.
Since SQL returns the LodGing column, it does not exist in the Lodging $ Manager index, so an operation through the RowID access table is performed after the index range query.
2. There is no index in the SQL below
There is a data type invisible conversion, such as:
Select * from staff_member where staff_id = '123';
There are mathematical operations on the column, such as:
Select * from staff_member where salary * 2 <10000;
Use it does not equal (<>), such as:
Select * from staff_member where dept_no <> 2001; remember, index can only tell you what exists in the table, but can't tell you what does not exist in the table.
Using Substr string functions, such as:
Select * from staff_member where substr (Last_name, 1, 4) = 'fred';
'%' Wildcard in the first character, such as:
Select * from staff_member where first_name like '% don';
String connection (||), such as:
Select * from staff_member where first_name || '' = 'donald'
Avoid using any columns that can be empty in the index, Oracle will not be able to use the index. For a single column index, this record will not exist if the column contains null values. For a composite index, if each column is empty, this record is not present. If there is at least one column is not empty, then record existence In the index.
Typically, we must avoid using NOT on the index column, and Not will generate the same function as the function on the index column.
Impact. When Oracle "encounters" NOT, he stops using indexing to perform full table scans.
If you must enable indexing using a column using a function, Oracle's new features: Function-based Index is perhaps a better solution.
CREATE INDEX EMP_I ON EMP (Upper (ename)); / * Establish a function-based index * /
Select * from Emp where Upper (ename) = 'blacksnail'; / * will use index * /
3. Selection in multiple index
When the execution path of the SQL statement can use multiple indexes distributed on multiple tables, Oracle will simultaneously use multiple indexes and merge their records at runtime, retrieved only records that are valid only to all indexes.
When Oracle selects the execution path, the level of unique index is higher than that of the unique index. However, this rule is only
When WHERE clauses are more valid. If the index class of the index column is compared to the index class of other tables. This clause is very low in the optimizer.
If the index in the different tables in different tables will be referenced, the order in the FROM clause will determine which will be used first. The final table in the FROM clause will have the highest priority.
If two indexes of the same level in the same table will be referenced, the most referenced index in the WHERE clause will have the highest priority.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
Select ename,
From EMP
WHERE DEPT_NO = 20
And Emp_cat = 'a';
Here, the DEPTNO index will be first retrieved, and then the records are merged with the EMP_CAT index. The execution path is as follows:
Table Access by RowID on EMP
And-Equal
Index Range Scan on DePT_IDX
Index Range Scan on Cat_IDX
When Oracle is unable to determine the level of high and low differences, the optimizer will use only one index, which is listed in the WHERE clause.
4. Automatically select index
If there are more than two (including two) indexes in the table, there is a unique index, while others are unique.
In this case, Oracle will use a unique index to completely ignore the unique index.
Example:
SELECT ENAME
From EMP
WHERE Empno = 2326
And deptno = 20;
Here, only the index on the EMPNO is unique, so the EMPNO index will be used to retrieve records.
Table Access by Rowid On Empindex Unique Scan on EMP_NO_IDX
5. Equality comparison and scope comparison
When there is an index column in the WHERE clause, Oracle cannot merge them, Oracle will use the range comparison.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
SELECT ENAME
From EMP
WHERE Deptno> 20
And Emp_cat = 'a';
Only the EMP_CAT index is used here, and then all records compare it to the DEPTNO condition. The execution path is as follows:
Table Access by RowID on EMP
Index Range Scan on Cat_IDX
3. Combined index always uses the first column of the index
If the index is built in multiple columns, the optimizer selects the index only when its first column is referenced by the WHERE clause.
6. Use more selective indexing under CBO
Cost-based optimizer (CBO, COST-based Optimizer) determines whether the use of indexes can improve efficiency.
If the index has a high selectivity, it is to say that there is a small number of records for each non-repetitive index key.
For example, there are 100 records in the table, and 80 non-repetitive index key values. The selectivity of this index is 80/100 = 0.8. The higher the selectivity, the less recorded by the index key value.
If the selectivity of the index is very low, retrieval data requires a large number of index range query operations and RowID access tables.
Operation. Perhaps lower efficiency than full measuring.
Please refer to the following experience
:
a.
If the amount of retrieved data exceeds
30%
Number of records in the table
.
Use index will have no significant efficiency improvement
.
b.
In certain cases
,
Use index may be slower than full table
,
But this is the same order
the difference
.
And usually
,
Use the index to scan several times even thousands of full tables
!
7. Replace OR with Union (ALL) (for index columns)
Typically, the OR in the WHERE clause will be replaced with Union will play a better effect. Use OR to the index column will result in full mete scans. Note that the above rules are only valid for multiple indexes. If there is a column is not Index, the query efficiency may be reduced because you didn't choose OR.
In the following example, there is an index on the LOC_ID and Region.
Efficient:
SELECT LOC_ID, LOC_DESC, REGON
From location
WHERE LOC_ID = 10
Union
SELECT LOC_ID, LOC_DESC, REGON
From location
Where region = "melbourne"
Inefficient:
SELECT LOC_ID, LOC_DESC, REGON
From location
Where loc_id = 10 or region = "melbourne"
If you insist on using OR, you need to return to the least index column written in the front.
note:
Where key1 = 10 (return to the minimum record)
Or Key2 = 20 (return to the most record)
Oracle is converted to
Where key1 = 10 and
((Not Key1 = 10) and key2 = 20)
8. Use> = alternative>
DEPT> 3 and DEPT> = 4 The difference is that the former DBMS will jump directly to the first DEPT equal to 4 records and the latter will first locate records of DEPTNO = 3 and scan forward to the first DEPT greater than 3 record of.
NO14: Replace Union with UNION ALL (if possible)
When the SQL statement needs UNION two query results collection, the two result collections are merged in the way Union-ALL and then sorted before the final result is output.
If you use union all to replace Union, this sort is not necessary. Efficiency will be improved. NO15: Use Tips (HINTS)
For table access, you can use two hints.
Full and Rowid
Full Hint told Oracle to access the specified table using full mete-scan.
E.g:
SELECT / * FULL (EMP) * / *
From EMP
WHERE EMPNO = 7893;
Rowid Hint tells Oracle to access the table using the Table Access By RowID.
Typically, you need to use Table Access By Rowid, especially when accessing a big table, you need to know the value of ROIWD or use an index.
If a big table is not set to a cached table and you want its data to end in the end of the query, still stay
In SGA, you can use Cache Hint to tell the optimizer to keep the data in SGA. Usually Cache Hint and Full Hint work together.
E.g:
SELECT / * FULL (Worker) Cache (Worker) * / *
From Work;
Index Hint tells Oracle to use an index-based scan mode. You don't have to explain the specific index name
E.g:
SELECT / * INDEX (LODGING) * / LODGING
From lodging
WHERE manager = 'bill Gates';
In the case of hint without using Hint, the above query should also use the index, however, if the index is too much, your optimizer is CBO, the optimizer may ignore the index. In this case, you can Mandatory Oracle with Index Hint Use the index.
Oracle Hints also includes all_rows, first_rows, rule, us_nl, us_merge, us_hash, and so on.
Using Hint, we are unsatisfactory to the Oracle Optimizer's default execution path. This is a very skillful job. I suggest that only for specific, few SQL is optimized. Optimization of Oracle There are still confidence (especially CBO)
NO16: Avoid operating resources
SQL statement with Distinct, Union, Minus, INTERSECT, ORDER BY will start SQL engine
Execute the sort function of the resource. DistINCT requires a sorting operation, while others need to perform two sorting.
For example, a Union query, each query comes with a Group By clause, and Group By will trigger embedd sort; this, each query needs to be sorted, and then a unique order when executing Union, Sort unique) Operation is performed and it can only begin execution after the end of embedding sorting. The depth of embedded sorting will greatly affect the efficiency of the query.
Typically, both the SQL statement with UNION, Minus, and INTERSECT can be rewritten in other ways.
If your database's sort_AREA_SIZE is well fixed, use union, minus, and intersect can also be considered, after all, their readability is very
NO17: separation table and index
Always build your tables and indexes in different tablespaces. Never store objects that do not belong to the Oracle internal system into the System table space. At the same time, ensure that data tablespace and index table space are different from Hard disk control card controlled hard drives.
NO18: Sorting
SQL contains group BY clause
SQL contains the order by clause
DISTINC clause
SQL includes minus or union operations
Create an index
These are slow.
NO19: EXECUTE IMMEDIATE, DBMS_SQL
Dynamic SQL, try to use Execute Immediate, and use less DBMS_SQL, the former comprehensive efficiency is superior
NO20: Replace with Like and Substr
For 'Like' and 'Substr', the efficiency is not much different. However, when the search value does not exist, the speed of 'Like' is significantly greater than 'Substr'. So: Select * from a where substr (A1, 1, 4) = '5378' can be replaced with LIKE
SELECT * from a where a1 like '5378%'
NO20: DML statement optimization
1. If it is possible, Truncate is replaced by delete.
2. The deletion of the big table is converted to the remainder of the table, the Truncate original table then rename the new table as original table.
3. When Update is multi-column, try not to use multiple sets; such as:
Update EMP
Set (EMP_CAT, SAL_RANGE)
= (SELECT MAX (Category), Max (SAL_RANGE)
From EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
4. If there is an index, remove the index after performing the operation, reconstruct the index after the operation is complete.
5. The environment is allowed to use parallel
NO21: use parallel
Hint / * Parallel (TableName, Parallel-Degree) * /
Adjusting the parallel execution is to maximize hardware capabilities. If you have a high-performance system, high-priority SQL statements are running, then all valid resources can be used in parallel statements. Oracle can perform the following parallel:
l in parallel;
l Parallel DML (including INSERT, UPDATE, DELETE; APPEND prompt, parallel index scanning);
l Parallel DDL;
If your system lacks the following characteristics, it may not improve how much it is in parallel.
l Symmetric multiprocessor (SMP), cluster or powerful parallel system;
l Effective I / O bandwidth;
l Low-utilized or idle CPUs (such as CPU use less than 30%);
l Invisible for additional memory, such as classification, Haxi index, I / O buffer, etc.
If the specified parallelism is greater than the actual resource (hardware resources> Parallel_Max_server> you specify), the parallelism of the available resources will be used to handle the parallelism of available resources.
If many people use parallel, SUM (Parallel_degree)> Parallel_max_server; may generate a wait for efficiency to decline.
