The objective of tuning a system is either to reduce the response time for end users of the system, or to reduce the resources used to process the same work.

The NOREWRITE hint disables the use of function-based indexes.

看到这句话之后不太理解为什么提示NOREWRITE 的使用会丧失基于函数index的使用能力！

doc上的原话，记录一下，感觉写的很精彩！

定位系统性能瓶颈的时候要多利用数据以及统计信息等资源而不要过多的依赖自己以前积累的经验想当然。因为今天的系统已经变的非常复杂，性能分析的一些浮浅的规则不容易产生。oracle性能改进方法论定义的是一种工作的方法而不是一组权威性的规则。在定位性能瓶颈的时候，最好的规则就是没有规则（With bottleneck detection, the only rule is that there are no rules!The best performance engineers use the data provided and think laterally to determine performance problems.）

On an Oracle data server system, where there is generally only one application running, the server runs database activity in user space. Activities required to service database requests (such as scheduling, synchronization, I/O, memory management, and process/thread creation and tear down) run in kernel mode. In a system where all CPU is fully utilized, a healthy Oracle system runs between 65% and 95% in user space.

）oracle对系统cpu的利用如果在user cpu（应用程序使用cpu的百分比）的65% ～95%之间，则oralcle system被认为是健康的。

e文不好，不知理解的是否准确！

什么是Scalability，看看他的形容词的意思：scalable adj.可攀登的
可升级的，这里Scalability我通常理解为系统的可攀升能力。doc的原意是：

Scalability is a system's ability to process more workload, with a proportional increase in system resource usage. In other words, in a scalable system, if you double the workload, then the system would use twice as many system resources. This sounds obvious, but due to conflicts within the system, the resource usage might exceed twice the original workload.

不知道该起个什么样的标题来记录这句话，看到这句话时感觉写的非常好，想记录一下：

Performance problems are usually the result of contention for, or exhaustion of, some system resource

性能问题通常是一些系统资源被争夺或者是被耗尽的结果！

What Is Disk Contention?

Disk contention occurs when multiple processes try to access the same disk simultaneously. Most disks have limits on both the number of accesses (I/O operations each second) and the amount of data they can transfer each second (I/O data rate, or throughput). When these limits are reached, processes must wait to access the disk.

Some pitfalls are discussed in the following sections:

• Hit ratios
• Wait events with timed statistics
• Comparing Oracle statistics with other factors
• Wait events without timed statistics
• Idle wait events
• Computed statistics

需要注意的一句话是：

Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan.

If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.

Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.

当我第一次看到hint相关内容中讲到AND_EQUAL时（

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

）感觉没有读懂，不知道这里提到的merge到底指的什么意思，但是当看到RBO Path 9: Single-Column Indexes章节中的解释时对AND_EQUAL已经非常清楚了，原来是对单列index scan找到满足条件的rowid的merge（If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.）

RBO Path 1: Single Row by Rowid

This access path is available only if the statement's WHERE clause identifies the selected rows by rowid or with the CURRENT OF CURSOR embedded SQL syntax supported by the Oracle precompilers. To execute the statement, Oracle accesses the table by rowid.

doc上的原话，随手记录下！

Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.

从上面这段话我们知道full table scan 是最低等级的访问路径，那意味着下面列出来的15种访问路径的数字越小等级越高，也就是说使用访问路径的等级越高rbo认为效率越高，当然实际情况可能并非如此。

都是doc上的原话，记录一下！

FIRST_ROWS(n)提示告诉优化器要执行的sql语句的优化目标是获得最快的响应时间，但是下面这些情况该提示不起作用：

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:

• Set operators (UNION, INTERSECT, MINUS, UNION ALL)
• GROUP BY clause
• FOR UPDATE clause
• Aggregate functions
• DISTINCT operator
• ORDER BY clauses, when there is no index on the ordering columns

These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.

这是9i性能优化向导上的内容，在11g下测试count操作（Aggregate functions）发现提示也起作用！

doc上介绍hint不能被传播到view内部，除非在创建view时把hint写在view的内部或者使用global hint!

doc上的这段话对Optimizer Hints介绍的非常清楚，记录一下!

当然更重要的是能够熟练的使用各种hint来改变sql的执行计划从而优化sql

这里对下面一段做一点解释：

You can use hints to specify the following:

• The optimization approach for a SQL statement
• --The optimization approach 指的是cbo和rbo
• The goal of the cost-based optimizer for a SQL statement
• --The goal of the cost-based optimizer 指的是语句追求的是最大吞吐量( all_rows)还是最快响应时间(first_rows(n))
• The access path for a table accessed by the statement
• --The access path for a table accessed是指full table scan or index scan
• The join order for a join statement
• --是指表的连接顺序
• A join operation in a join statement
• --A join operation 指的应该是表与表之间的连接方法：nl，merge or hash

 查看全文

在性能优化向导中经常会出现Cardinality，Cardinality到底是指什么，看看doc的准确解释：

Cardinality is the number of unique values in a column in relation to the number of rows in the table

primary key和unique约束是要依赖index的，下面通过试验来看看他们之间的依赖关系!

之前没有想过这个问题，第一次看到这样的说法是在piner的面试题中。当时觉得有点意思，这几天细读doc，上面也提到了这样的说法：

Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table.

 查看全文

看到经常有人问此问题！doc的原话，看到了随手记录下！

那么究竟什么是DETERMINISTIC Functions，看看doc的解释！

DETERMINISTIC Functions

Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.

可以通过dbms_stats提供的set_table_stats来修改表的统计信息从而使cbo产生我们希望的执行计划！

利用dbms_stats的procedure gather_schema_stats 和gather_database_stats通过使用参数options 和objlist可以列出那些没有统计信息或者统计信息stale的对象。

在创建函数index之后系统会自动产生虚拟列，而这一列也是隐藏的，在创建index时系统自动使用了compute statistics选项为创建的index搜集了statistics，然而并不会为生成的虚拟列搜集statistics，这时如果需要为虚拟列搜集statistics就需要重新分析表或者单独为虚拟列搜集statistics。

doc上的原话，随手记录一下。

 查看全文

Use the DBMS_STATS package to generate statistics.

Statistics generated include the following:

index和index之间是如何进行hash join的，因为index中包括了rowid，所以通过rowid可以连接。

Index Joins

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation. The index join is available only with the CBO.

Index Join Hints

You can specify an index join with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_JOIN hint. For more information on the INDEX_JOIN hint, see "INDEX_JOIN".

 查看全文

不使用绑定变量，而open_cursors又开的很大，很容易导致ORA-04031错误!

有图片，所以这里没有放内容，查看的话，大家可以直接看看下面的连接！