记不清从那找得了，感觉有些地方不一定正确，请大家评论一下？
1. Use the Appropriate Oracle Optimizer
The ORACLE optimizer has three primary modes of operation:
Ø RULE
Ø COST &
Ø CHOOSE
To set the optimizer goal, you can specify RULE, COST, CHOOSE, ALL_ROWS or FIRST_ROWS for the OPTIMIZER_MODE parameter in the init.ora file at session level. You can override the optimizer’s default operations at both the query level (using Hints) and the session level (using ALTER SESSION command).
The Rule-Based Optimizer (RBO) evaluates possible execution paths and rates the alternative execution paths based on a series of syntactical rules.
To make use of the Cost-Based Optimizer (CBO), you need to make sure that you run the analyze command frequently enough to generate statistics about the objects in your database to accurately reflect the data.
Setting OPTIMIZER_MODE to CHOOSE invokes the CBO if the tables have been analyzed, and the RBO if the tables have not been analyzed.
By default, ORACLE uses CHOOSE optimizer mode. To reduce the potential for unplanned full table scans, you should avoid using the CHOOSE option; either use the RBO or the CBO throughout your database.
2. Operations That Access Tables
ORACLE performs two operations for accessing the rows of a table:
Ø TABLE ACCESS FULL
A full table scan sequentially reads each row of a table. To optimize the performance of a full table scan, ORACLE reads multiple blocks during each database read.
A full table scan is used whenever there is no where clause on a query.
Ø TABLE ACCESS BY ROWID
To improve the performance of table accesses, you can use this operation, which allows you to access rows by their RowID pseduo-column values. The RowID records the physical location where the row is stored. ORACLE uses indexes to correlate data values with RowID values - and thus with physical locations of the data. And because indexes provide quick access to RowID values, they help to improve the performance of queries that make use of indexed columns.
3. Share SQL Statements
ORACLE holds SQL statements in memory after it has parsed them, so the parsing and analysis won’t have to be repeated if the same statement is issued again. The single shared context area in the shared buffer pool of the System Global Area (SGA) is shared by all the users. Thus, if you issue a SQL statement, sometimes known as a cursor, that is identical to a statement another user has issued, you can take advantage of the fact that ORACLE has already parsed the statement and figured out the best execution plan for it. This represents major performance improvements and memory savings. Unfortunately, the cache buffering is applied only to simple tables; multiple table queries and joins are never cached.
The DBA must set the appropriate INIT.ORA parameters for the context areas. The larger the area, the more statements that can be retained and the more likely statements actually get shared.
Whenever you issue a SQL statement, ORACLE first looks in the context area to see if there is an identical statement there. Unfortunately, ORACLE does an extra string comparison on the new statement and the contents of the context area. To be shared, the SQL statements must truly be the same: carriage returns, spaces, and case (upper vs lower) all affect the comparison.
In order to qualify for this matching condition, all three of the following rules must be true to make use of the parsed statement in the shared area.
A. There must be a character-by-character match between the statement
being examined and the one already in the shared pool.
Note: Before this comparison is performed, Oracle applies an internal algorithm using the new statement. It then checks the results against values of statements already in the pool. If the new value matches one already there, then only the string comparison outlined in Rule 1 is performed.
For example:
SELECT * FROM EMP;
is not the same as any of these:
SELECT * from EMP;
Select * From Emp;
SELECT *FROM EMP;
The following statements do not qualify because the first SQL statement is split over two lines whereas the second is on a single line.
Select pin from person where last_name =
‘LAU’;
Select pin from person where last_name = ‘LAU’;
B. The objects being referenced in the new statement are exactly the same
as those objects in a statement that has passed the comparison in Rule 1.
For example:
Assume that for this example, the users have access to the objects as shown below:
USER
OBJECT NAME
ACCESSED VIA
Jack
sal_limit
private synonym
work_city
public synonym
plant_detail
public synonym
Jill
sal_limit
private synonym
work_city
public synonym
plant_detail
table owner
Consider the following SQL statements and why they can or cannot be shared between the two users listed above.
SQL Statement Object Matching
WHY
select max(sal_cap)NO
Each user has a private synonym
from sal_limit;
sal_limit - these are different objects.
selectcount(*) YES
Both users reference work_city
from work_city
by the same public synonym - the
where sdesc
same object.
like 'NEW%';
select a.sdesc, NO
User jack references plant_detail
b.location
by a public synonym whereas user
fromwork_city a,
Jill is the table owner - these are
plant_detail b
different objects.
wherea.city_id = b.city_id;
select *
NO
Each user has a private synonym
from sal_limit
sal_limit- these are different
whereover_time
objects.
is not null;
C. If bind variables are referenced, they must have the same name in both
the new and existing statements.
For example:
The first two statements in the following listing are identical, whereas the next two statements are not (even if the different bind variables have the same value at run time).
select pin, name from people where pin = :blk1.pin;
select pin, name from people where pin = :blk1.pin;
select pos_id, sal_cap from sal_limit where over_time = :blk1.ot_ind;
select pos_id, sal_cap from sal_limit where over_time = :blk1.ov_ind;
4. Select the Most Efficient Table Name Sequence (Only for RBO)
ORACLE parser always processes table names from right to left, so the table name you specify last (driving table) is actually the first table processed. If you specify more than one table in a FROM clause of a SELECT statement, you must choose the table containing the lowest number of rows as the driving table. When ORACLE processes multiple tables, it uses an internal sort/merge procedure to join those tables. First, it scans and sorts the first table (the one specified last in the FROM clause). Next, it scans the second table (the one prior to the last in the FROM clause) and merges all of the rows retrieved from the second table with those retrieved from the first table.
For example:
Table TAB1 has 16,384 rows.
Table TAB2 has 1 row.
Select TAB2 as the driving table.
(Best Approach)
SELECT COUNT(*) FROM TAB1, TAB20.96 seconds elapsed
Now, select TAB1 as the driving table. (Poor Approach)
SELECT COUNT(*) FROM TAB2, TAB126.09 seconds elapsed
If three tables are being joined, select the intersection table as the driving table. The intersection table is the table that has many tables dependent on it.
For example:
The EMP table represents the intersection between the LOCATION table and the CATEGORY table.
SELECT . . .
FROM LOCATION L,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
is more efficient than this next example:
SELECT . . .
FROM EMP E,
LOCATION L,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
5. Position of Joins in the WHERE Clause
Table joins should be written first before any condition of WHERE clause. And the conditions which filter out the maximum records should be placed at the end after the joins as the parsing is done from BOTTOM to TOP.
For example:
Least Efficient :(Total CPU = 156.3 Sec)
SELECT. . . .
FROM
EMP E
WHERE
SAL > 50000
AND
JOB = ‘MANAGER’
AND
2550000
AND
JOB = ‘MANAGER’;
6. Avoid Using * in SELECT Clauses
The dynamic SQL column reference (*) gives you a way to refer to all of the columns of a table. Do not use the * feature because it is very inefficient -- the * has to be converted to each column in turn. The SQL parser handles all the field references by obtaining the names of valid columns from the data dictionary and substitutes them on the command line, which is time consuming.
7. Reduce the Number of Trips to the Database
Every time a SQL statement is executed, ORACLE needs to perform many internal processing steps; the statement needs to be parsed, indexes evaluated, variables bound, and data blocks read. The more you can reduce the number of database accesses, the more overhead you can save.
For example:
There are 3 distinct ways of retrieving data about employees who have employee numbers 0342 or 0291.
Method 1 (Least Efficient) :
SELECTEMP_NAME, SALARY, GRADE
FROMEMP
WHERE EMP_NO = 0342;
SELECTEMP_NAME, SALARY, GRADE
FROMEMP
WHERE EMP_NO = 0291;
Method 2 (Next Most Efficient) :
DECLARE
CURSOR C1(E_NO NUMBER) IS
SELECT EMP_NAME, SALARY, GRADE
FROM
EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH
C1 INTO …, …, …;
.
.
OPEN C1(291);
FETCH
C1 INTO …, …, …;
CLOSE C1;
END;
Method 3 (Most Efficient) :
SELECTA.EMP_NAME, A.SALARY, A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE,
FROM EMP A,
EMP B
WHERE A.EMP_NO = 0342
AND B.EMP_NO = 0291;
Note: One simple way to increase the number of rows of data you can fetch with one database access and thus reduce the number of physical calls needed is to reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms and Pro*C. Suggested value is 200.
8. Use DECODE to Reduce Processing
The DECODE statement provides a way to avoid having to scan the same rows repetitively or to join the same table repetitively.
For example:
SELECT COUNT(*), SUM(SAL)
FROM
EMP
WHERE DEPT_NO = 0020
AND
ENAME LIKE ‘SMITH%’;
SELECT COUNT(*), SUM(SAL)
FROM
EMP
WHERE DEPT_NO = 0030
AND
ENAME LIKE ‘SMITH%’;
You can achieve the same result much more efficiently with DECODE:
SELECT COUNT(DECODE(DEPT_NO, 0020, ‘X’, NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, 0030, ‘X’, NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROMEMP
WHEREENAME LIKE ‘SMITH%’;
Similarly, DECODE can be used in GROUP BY or ORDER BY clause effectively.
9. Combine Simple, Unrelated Database Accesses
If you are running a number of simple database queries, you can improve performance by combining them into a single query, even if they are not related.
For example:
SELECT NAME
FROM
EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM
DPT
WHERE DPT_NO = 10;
SELECT NAME
FROM
CAT
WHERE CAT_TYPE = 'RD';
The above three queries can be combined as shown below:
SELECT E.NAME, D.NAME, C.NAME
FROM
CATC, DPTD, EMPE, DUAL X
WHERE NVL('X', X.DUMMY) = NVL('X', E.ROWID (+))
AND
NVL('X', X.DUMMY) = NVL('X', D.ROWID (+))
AND
NVL('X', X.DUMMY) = NVL('X', C.ROWID (+))
AND
E.EMP_NO (+) = 1234
AND
D.DEPT_NO (+) = 10
AND
C.CAT_TYPE (+) = 'RD'
10. Deleting Duplicate Records
The efficient way to delete duplicate records from a table is shown below. It takes advantage of the fact that a row’s ROWID must be unique.
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROMEMP X
WHERE X.EMP_NO = E.EMP_NO);
11. Use TRUNCATE instead of DELETE
When rows are removed from a table, under normal circumstances, the rollback segments are used to hold undo information; if you do not commit your transaction, Oracle restores the data to the state it was in before your transaction started.
With TRUNCATE, no undo information is generated. Once the table is truncated, the data cannot be recovered back. It is faster and needs fewer resources.
Use TRUNCATE rather than DELETE for wiping the contents of small or large tables when you need no undo information generated.
12. Issue Frequent COMMIT Statements
Whenever possible, issue frequent COMMIT statements in all your programs. By issuing frequent COMMIT statements, the performance of the program is enhanced and its resource requirements are minimized as COMMIT frees up the following resources:
Ø Information held in the rollback segments to undo the transaction, if necessary
Ø All locks acquired during statement processing
Ø Space in the redo log buffer cache
Ø Overhead associated with any internal Oracle mechanisms to manage the resources in the previous three items
13. Counting Rows from Tables
Contrary to popular belief, COUNT(*) is faster than COUNT(1). If the rows are being returned via an index, counting the indexed column - for example, COUNT(EMPNO) is faster still.
14. Use WHERE in Place of HAVING
Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. This could include sorting, summing, etc. Restricting rows via the WHERE clause, rather than the HAVING clause, helps reduce these overheads.
For example:
Least Efficient :
SELECT REGION, AVG(LOC_SIZE)
FROM
LOCATION
GROUP BYREGION
HAVING
REGION != ‘SYDNEY’
AND
REGION != ‘PERTH’
Most Efficient :
SELECT REGION, AVG(LOC_SIZE)
FROM
LOCATION
GROUP BYREGION
WHERE REGION != ‘SYDNEY’
AND
REGION != ‘PERTH’
15. Minimize Table Lookups in a Query
To improve performance, minimize the number of table lookups in queries, particularly if your statements include sub-query SELECTs or multi-column UPDATEs.
For example:
Least Efficient :
SELECT TAB_NAME
FROM
TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND
DB_VER = (SELECTDB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
Most Efficient :
SELECT TAB_NAME
FROM
TABLES
WHERE (TAB_NAME, DB_VER) = (SELECTTAB_NAME, DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
Multi-column UPDATE example:
Least Efficient :
UPDATE EMP
SET
EMP_CAT = (SELECT MAX(CATEGORY)
FROMEMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE)
FROMEMP_CATEGORIES )
WHERE EMP_DEPT= 0020;
Most Efficient :
UPDATE EMP
SET
(EMP_CAT, SAL_RANGE) =
(SELECT MAX(CATEGORY), MAX(SAL_RANGE)
FROMEMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
16. Reduce SQL Overheads via “Inline” Stored Functions
SELECT H.EMPNO, E.ENAME,
H.HIST_TYPE, T.TYPE_DESC,
COUNT(*)
FROM
HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND
H.HIST_TYPE = T.HIST_TYPE
GROUP BYH.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;
The above statement's performance may be improved via an inline function call as shown below:
FUNCTION Lookup_Hist_Type (typ IN number) return varchar2
AS
tdesc varchar2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM
HISTORY_TYPE
WHERE
HIST_TYPE = typ;
BEGIN
OPEN C1;
FETCH C1 INTO tdesc;
CLOSE C1;
return (NVL(tdesc, ’?’));
END;
FUNCTION Lookup_Emp (emp IN number) return varchar2
AS
ename varchar2(30);
CURSOR C1 IS
SELECT ENAME
FROM
EMP
WHERE EMPNO = emp;
BEGIN
OPEN C1;
FETCH C1 INTO ename;
CLOSE C1;
return (NVL(ename, ’?’));
END;
SELECT H.EMPNO, Lookup_Emp(H.EMPNO),
H.HIST_TYPE, Lookup_Hist_Type(H.HIST_TYPE),
COUNT(*)
FROM
EMP_HISTORY H
GROUP BYH.EMPNO, H.HIST_TYPE;
17. Use Table Aliases
Always use table aliases & prefix all column names by their aliases where there is more than one table involved in a query. This will reduce parse time & prevent syntax errors from occurring when ambiguously named columns are added later on.
18. Use EXISTS in Place of IN for Base Tables
Many base table queries have to actually join with another table to satisfy a selection criteria. In such cases, the EXISTS (or NOT EXISTS) clause is often a better choice for performance.
For example:
Least Efficient :
SELECT *
FROM
EMP
(Base Table)
WHERE EMPNO > 0
AND
DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
Most Efficient :
SELECT *
FROM
EMP
WHERE EMPNO > 0
AND
EXISTS (SELECT‘X’
FROMDEPT
WHEREDEPTNO = EMP.DEPTNO
AND
LOC = ‘MELB’)
19. Use NOT EXISTS in Place of NOT IN
In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge. The NOT IN clause is the all-time slowest test possible as it forces a full read of the table in the sub-query SELECT. Avoid using NOT IN clause either by replacing it with Outer Joins or with a NOT EXISTS clause as shown below:
SELECT . . .
FROM
EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROMDEPT
WHEREDEPT_CAT = ‘A’);
To improve the performance, replace this code with:
Method 1 (Efficient) :
SELECT . . .
FROM
EMP A, DEPT B
WHERE A.DEPT_NO = B.DEPT_NO (+)
AND
B.DEPT_NO IS NULL
AND
B.DEPT_CAT(+) = 'A'
Method 2 (Most Efficient) :
SELECT . . .
FROM
EMP E
WHERE NOT EXISTS (SELECT
‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND
DEPT_CAT = ‘A’);
20. Use Joins in Place of EXISTS
In general, join tables rather than specifying sub-queries for them such as the following:
SELECT ENAME
FROM
EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
To improve the performance, specify:
SELECT ENAME
FROM
DEPT D, EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND
D.DEPT_CAT = ‘A’;
|
