ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle使用提示(Hints)

Oracle使用提示(Hints)

原创 Linux操作系统 作者:tolywang 时间:2005-01-19 00:00:00 0 删除 编辑

http://download-west.oracle.com/docs/cd/B10501_01/em.920/a86647/vmqtune.htm#1006173  

Oracle使用提示(Hints) 

-------------------------------------------


对于表的访问,可以使用两种Hints.

FULL 和 ROWID


FULL hint 告诉ORACLE使用全表扫描的方式访问指定表.


例如:

SELECT /*+ FULL(EMP) */ *

FROM EMP

WHERE EMPNO = 7893;


ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表.


通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式, 你需要知道ROIWD的值或者使用索引.

如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留

在SGA中,你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.

例如:

SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *

FROM WORK;


索引hint 告诉ORACLE使用基于索引的扫描方式. 你不必说明具体的索引名称

例如:

SELECT /*+ INDEX(LODGING) */ LODGING

FROM LODGING

WHERE MANAGER = ‘BILL GATES';


在不使用hint的情况下, 以上的查询应该也会使用索引,然而,如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引. 在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引.


ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.


译者按:

使用hint , 表示我们对ORACLE优化器缺省的执行路径不满意,需要手工修改.

这是一个很有技巧性的工作. 我建议只针对特定的,少数的SQL进行hint的优化.

对ORACLE的优化器还是要有信心(特别是CBO)

 -----------------------------------------------------------------

Understanding Hints

As an application designer, you may know information about your data that the optimizer cannot. For example, you may know that a certain index is more selective for certain queries than the optimizer can determine. Based on this information, you may be able to choose a more efficient execution plan than the optimizer. In such a case, you can use hints to force the optimizer to use your chosen execution plan.

You can use hints to specify:

  • the optimization approach for a SQL statement.
  • the goal of the cost-based approach for a SQL statement.
  • the access path for a table accessed by the statement.
  • the join order for a join statement.
  • a join operation in a join statement.

Specifying Hints

Hints apply only to optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

  • a simple SELECT, UPDATE, or DELETE statement
  • a parent statement or subquery of a complex statement
  • a part of a compound query

You can send hints for a SQL statement to the optimizer by enclosing them in a Comment within the statement.

A statement can have only one Comment containing hints. This Comment can only follow the SELECT, UPDATE, or DELETE keyword.

If you specify hints incorrectly, Oracle ignores them but does not return an error:

  • Oracle ignores hints if the Comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
  • Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same Comment.
  • Oracle ignores combinations of conflicting hints, but considers other hints within the same Comment.

Oracle also ignores hints in all SQL statements in environments which use PL/SQL Version 1.

The optimizer only recognizes hints when using the cost-based approach. If you include any hint (except the RULE hint) in a statement block, the optimizer automatically uses the cost-based approach.

For more information about comments and hints, see the Oracle SQL Analyze online help, and the Oracle9i Performance Guide and Reference.

The following hints, organized according to the optimization area they impact, can be added to your SQL statement manually or using the Hint Wizard.

Note that the availability of some hints is limited by the database version.


Note:

For more information on any of these hints, see the Oracle9i Database Performance Guide and Reference.




Optimization Approaches

Access Methods

ALL_ROWS

AND_EQUAL

CHOOSE

CLUSTER

FIRST RULES

FULL

RULE

HASH

Parallel Execution

HASH_AJ

APPEND*ORDERED

HASH_SJ ***

STAR**

INDEX

STAR_TRANSFORMATION*

INDEX_ASC

Join Operations

INDEX_COMBINE*

DRIVING_SITE*

INDEX_DESC

USE_HASH**

INDEX_FFS*

USE_MERGE

MERGE_AJ**

USE_NL

MERGE_SJ***

Additional Hints

ROW_ID

CACHE

USE_CONCAT

NOCACHE

NO_EXPAND***

PUSH_SUBQ

REWRITE***

MERGE***

NOREWRITE***

NO_MERGE*

Join Orders

PUSH_JOIN_PRED***

NO_PUSH_JOIN_PRED***

NOAPPEND*

ORDERED PREDICATES***

NOPARALLEL

PARALLEL

PARALLEL_INDEX*

NO_PARALLEL_INDEX***

* Available only for Oracle8 databases

**Available only for Oracle7.3 and Oracle8 databases

***Available only for Oracle8i databases 



Understanding Rules-of-Thumb

The syntax of SQL statements has a significant affect on performance. The use of certain command clauses can disable indexes or cause inefficient data sorting and filtering. In some cases, the order in which command clauses are used or the order in which data and tables are referenced can add an extra burden on resources.

Oracle SQL Analyze contains a set of rules, developed by database experts, that evaluates SQL statements and suggests alternative statements, when possible. These rules focus on principles of optimizing performance, such as:

  • enabling indexes to eliminate the need for full table scans
  • reducing the number of sorts, merges, and filtering operations required
  • reducing the number of rows that need to be sorted, filtered, or merged

Oracle SQL Analyze applies these "rules-of-thumb" when you tune a statement with the Tuning Wizard, and supplies alternative SQL statements when possible. Oracle SQL Analyze checks your statement against the following rules, which are explained in this section:

  • Use NOT EXISTS instead of NOT IN
  • Use NOT EXISTS or NOT IN with hints instead of MINUS
  • Use TRUNC differently to enable indexes
  • Use operators differently to enable indexes
  • Do not use columns on both sides of operator
  • Use WHERE in place of HAVING
  • Use UNION ALL instead of UNION

Use NOT EXISTS instead of NOT IN

Using NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary.

The following example uses a NOT IN clause to find names and department IDs in the DEPARTMENT table where the department ID does not also exist in the EMPLOYEE table:

  SELECT name, department_id
FROM department
WHERE department_id NOT IN
(SELECT department_id FROM employee)

Because NOT IN does not use a limiting condition, Oracle will perform a full table scan of DEPARTMENT. For each record in DEPARTMENT, the subquery will be executed. Since the subquery has no limiting WHERE clause, it will perform a full table scan for every record in the full table scan of DEPARTMENT.

Instead, NOT EXISTS can be used so that nested index scans will be used in the subquery for each row in the DEPARTMENT table. The logic of the NOT EXISTS clause tells Oracle not to return the row if it finds a match in both tables. The only records that will be returned from DEPARTMENT are those that return no rows from the subquery, and no full table scans are performed by the subquery. The following statement, therefore, is more efficient than the previous example.

SELECT name, department_id
FROM department,
WHERE NOT EXISTS
(SELECT department_id
FROM employee
WHERE department.department_id=employee.department_id)

Use NOT EXISTS or NOT IN with hints instead of MINUS

MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. Rewriting queries using NOT EXISTS or NOT IN can enable them to take advantage of indexes, reducing the number of full table scans a clause may require.

In some cases, Oracle SQL Analyze might determine that because a hash anti-join (HASH_AJ) usually does not require a sort, it will produce better results than MINUS.

The following query, for example, matches names and birthdates in the EMPLOYEE table with those in the STOCKHOLDER table, then returns the names and birthdates of employees who are not stockholders. Because MINUS does not use indexes, Oracle will use two full table scans and perform a sort on each table before the MINUS operation can be performed.

SELECT birth_date, last_name, first_name
FROM employee 
MINUS
SELECT birth_date, last_name, first_name
FROM stock_holder

If the statement is re-written using NOT EXISTS, Oracle can use nested index scans in the subquery for rows in the primary statement.

SELECT birth_date, last_name, first_name
FROM employee 
WHERE NOT EXISTS
(SELECT 1
  FROM stock_holder
WHERE stock_holder.birth_date = employee.birth_date
  AND stock_holder.first_name = employee.first_name)

If Oracle SQL Analyze determines that a hash anti-join will produce better results, the example query could be rewritten to use two full table scans and an anti-join algorithm to join the rows, instead of performing sort and minus operations.

SELECT birth_date, last_name, first_name
FROM employee 
WHERE (birth_date, last_name, first_name)NOT IN
(SELECT /*+ hash_aj (stock_holder) */ birth_date, last_name, first_name
FROM stock_holder)

Use TRUNC differently to enable indexes

Using the truncate command (TRUNC) on an indexed column disables the index. Rewriting your query so that fewer columns are truncated allows it to take advantage of indexes to increase performance.

In the following example, trans_date is an indexed column, but the index is disabled by the TRUNC command.


SELECT account_name, trans_date
FROM transaction
WHERE TRUNC(trans_date) = TRUNC(sysdate)

The query can be rewritten as shown below to use the trans_date index and increase performance.

SELECT account_name, trans_date
FROM transaction
WHERE trans_date BETWEEN TRUNC(sysdate) AND TRUNC(sysdate) + .99999

Use operators differently to enable indexes

The optimizer does not use an index if the indexed column is part of a function (in the WHERE clause). If Oracle SQL Analyze determines that an equation can be rewritten to avoid the use of operators, it can rewrite the statement as shown below.

In this example, the equation in the query can be rewritten as a simple inequality clause. statement. Therefore the query

SELECT account_name, trans_date, amount
FROM transaction
WHERE amount + 3000 < 5000

can be rewritten as


SELECT account_name, trans_date, amount
FROM transaction
WHERE amount < 2000

Do not use columns on both sides of operator

When an indexed column appears on both sides of an operator, the index for that column is disabled. Oracle SQL Analyze detects this condition and, when possible, rewrites the statement to allow the index to be used.

In the following example, the column account_name is indexed, but the index is disabled.

SELECT account_name, trans_date, amount
FROM transaction
WHERE account_name = NVL(:acc_name, account_name)

The query can be rewritten using LIKE so that the indexed column is only on one side of the operator.

SELECT account_name, trans_date, amount
FROM transaction
WHERE account_name LIKE NVL(:acc_name, `%')

Use WHERE in place of HAVING

The HAVING clause limits rows collected by a GROUP BY clause only after they have been aggregated. Whenever possible, it is better to limit the number of rows retrieved before they are merged and sorted into an aggregation. Using WHERE in place of HAVING eliminates rows before they are added to the aggregation.

The statement below sorts an entire list of items by quantity, then removes from the aggregation all items with a quantity less than 40.

SELECT quantity, AVG(actual_price)
FROM item
GROUP BY quantity
HAVING quantity > 40

The statement can be rewritten so that all rows where QUANTITY is less than 40 are removed before the aggregation is sorted.

SELECT quantity, AVG(actual_price)
FROM item
WHERE quantity >40
GROUP BY quantity

Note that if the HAVING clause is applied to aggregate functions, it cannot be replaced by WHERE. In the query below, for example, HAVING is applied to a SUM function.

SELECT program_name
      ,count
      ,min(end_date-start_date) "Min Runtime"
      ,avg(end_date-start_date)"Avg Runtime"
      ,max((end_date-start_date)"Max Runtime"
      ,sum(end_date-start_date)"tot Runtime"
FROM jobs
WHERE start_date>sys_date - 7
GROUP BY program_name
HAVING sum((end_date-start_date)>0.25 or max(end_date-start_date) > 0.04

Use UNION ALL instead of UNION

The difference between the UNION and UNION ALL is that UNION requires a sort operation to eliminate any rows that are duplicated across the two row sets, while UNION ALL returns all rows, even if they are duplicated. If duplicated rows are not important, using UNION ALL can avoid potentially expensive sorts, merges, and filtering operations.

For example, the statement

SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = `31-DEC-99'
UNION
SELECT acct_num, balance_amt
FROM credit_transactions
WHERE tran_date = `31-DEC-99'

Can be rewritten as

SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = `31-DEC-99'
UNION ALL
SELECT acct_num, balance_amt
FROM credit_transactions  
WHERE tran_date = `31-DEC-99'

Using the SQL Tuning Wizard

The SQL Tuning Wizard guides you through the entire SQL statement tuning process. It evaluates your SQL Statement using Rules-of-Thumb to generate alternate, optimized versions of your SQL statement.

To use the SQL Tuning Wizard:

Select Tools=>SQL Tuning Wizard. This launches the SQL Tuning Wizard.

The SQL Tuning Wizard Process

The SQL Tuning Wizard is an automated guide that leads you through tuning a SQL statement. Throughout the process, you will be able to make choices that will help the wizard optimize your specific SQL statement. If you need more information to make your choices, select the Help button from any of the wizard pages.

The SQL Tuning Wizard will guide you through the following processes:

  • Evaluation

    The evaluation process identifies inefficiencies in the way that your SQL statement is written. The SQL Tuning Wizard provides a graph with a projected improvement percentage that is based on a modified version of the SQL.

    The SQL Tuning Wizard projected improvement graph is derived from the information collected by the system optimizer. In some cases the SQL Tuning Wizard may detect inefficiencies in the way that the statement was written, but may not be able to predict the degree of performance improvement. It may still be worthwhile to look at the modified SQL statement, however, to see if the changes have improved the overall performance of the SQL statement.

  • Recommendations

    The recommendation review process allows you to see which rules have been violated by the SQL statement. For each rule that is checked, the SQL Tuning Wizard provides a recommendation that improves the SQL statement. You can also view the Rule Details for each of the rules listed. You may chose to accept (checked) or decline (unchecked) recommendations for any of the rules.

    By default, a rule is checked only if the recommendation is guaranteed to return the same result set as the original SQL statement.

  • Explain Plan Comparison

    The comparison process allows you to compare the original SQL statement to the modified statement to verify the actual performance improvements for all of the recommendations you accepted. You can compare the changes to the actual SQL statements before choosing to accept the modified statements. Once you have verified the performance improvements, you can Execute the modified SQL statements.

Using the Hint Wizard

The Hint Wizard identifies hints in a statement and allows the user to present other hints that can be added to the statement. It provides a description for a selected hint and will automatically generate a new SQL statement if a hint is added or deleted.

To use the Hint Wizard:

Select Tools=>Hint Wizard. The Hint Wizard will guide you through the rest of this process.

  1. Select a subquery to analyze from the Hint Wizard page.
  2. View/delete the current hints.
  3. Select a new hint to add, and supply:
    • table parameters, if necessary.
    • index parameters, if necessary.
  4. Review the current hints.
  5. Apply hints to the SQL statement.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84245/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 给有福气的人看
下一篇: ORACLE锁的管理(zt)
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13383846