1.调整哪些查询？查询V$SQLAREA
1.1在V$SQLAREA视图中选出最占资源的查询
select b.username,a.DISK_READS,a.EXECUTIONS exec,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,
a.SQL_TEXT statement
from v$sqlarea a,dba_users b
where a.PARSING_USER_ID=b.user_id
and a.DISK_READS>100000
order by a.DISK_READS desc;
可以在该语句中使用BUFFER_GETS列来替换DISK_READS列，从而可以提供占用最多内存的SQL语句的相关信息。
【技巧】查询V$SQLAREA视图可以找到需要调整的有问题的查询。

1.2在V$SQL视图中选出最占用资源的查询
查询V$SQL可允许您单独查看共享SQL区域中的每条语句，而不用查看这些语句组（在V$SQLAREA视图中就是如此）。以下是显示从V$SQL中获得最占用资源语句的更快
查询（只要改动视图名就可以使这个查询访问V$SQLAREA)。
select * from
( select sql_text,address,
rank() over(order by buffer_gets desc) as rank_bufgets,
to_char(100*ratio_to_report(buffer_gets) over() ,'999.99') pct_bufgets
from v$sql
)
where rank_bufgets<11;
【附V$SQL】
Column Datatype Description
SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor
SQL_FULLTEXT CLOB Full text for the SQL statement exposed as a CLOB column. The full text of a SQL statement can be retrieved using this column instead of joining with the V$SQL_TEXT dynamic performance view.
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
SHARABLE_MEM NUMBER Amount of shared memory used by the child cursor (in bytes)
PERSISTENT_MEM NUMBER Fixed amount of memory used for the lifetime of the child cursor (in bytes)
RUNTIME_MEM NUMBER Fixed amount of memory required during the execution of the child cursor
SORTS NUMBER Number of sorts that were done for the child cursor
LOADED_VERSIONS NUMBER Indicates whether the context heap is loaded (1) or not (0)
OPEN_VERSIONS NUMBER Indicates whether the child cursor is locked (1) or not (0)
USERS_OPENING NUMBER Number of users executing the statement
FETCHES NUMBER Number of fetches associated with the SQL statement
EXECUTIONS NUMBER Number of executions that took place on this object since it was brought into the library cache
END_OF_FETCH_COUNT NUMBER Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column.
USERS_EXECUTING NUMBER Number of users executing the statement
LOADS NUMBER Number of times the object was either loaded or reloaded
FIRST_LOAD_TIME VARCHAR2(19) Timestamp of the parent creation time
INVALIDATIONS NUMBER Number of times this child cursor has been invalidated
PARSE_CALLS NUMBER Number of parse calls for this child cursor
DISK_READS NUMBER Number of disk reads for this child cursor
DIRECT_WRITES NUMBER Number of direct writes for this child cursor
BUFFER_GETS NUMBER Number of buffer gets for this child cursor
APPLICATION_WAIT_TIME NUMBER Application wait time
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time
CLUSTER_WAIT_TIME NUMBER Cluster wait time
USER_IO_WAIT_TIME NUMBER User I/O Wait Time
PLSQL_EXEC_TIME NUMBER PL/SQL execution time
JAVA_EXEC_TIME NUMBER Java execution time
ROWS_PROCESSED NUMBER Total number of rows the parsed SQL statement returns
COMMAND_TYPE NUMBER Oracle command type definition
OPTIMIZER_MODE VARCHAR2(10) Mode under which the SQL statement is executed
OPTIMIZER_COST NUMBER Cost of this query given by the optimizer
OPTIMIZER_ENV RAW(648) Optimizer environment
OPTIMIZER_ENV_HASH_VALUE NUMBER Hash value for the optimizer environment
PARSING_USER_ID NUMBER User ID of the user who originally built this child cursor
PARSING_SCHEMA_ID NUMBER Schema ID that was used to originally build this child cursor
KEPT_VERSIONS NUMBER Indicates whether this child cursor has been marked to be kept pinned in the cache using the DBMS_SHARED_POOL package
ADDRESS RAW(4 | 8) Address of the handle to the parent for this cursor
TYPE_CHK_HEAP RAW(4) Descriptor of the type check heap for this child cursor
HASH_VALUE NUMBER Hash value of the parent statement in the library cache
OLD_HASH_VALUE NUMBER Old SQL hash value
PLAN_HASH_VALUE NUMBER Numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
CHILD_NUMBER NUMBER Number of this child cursor
MODULE VARCHAR2(64) Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_MODULE
MODULE_HASH NUMBER Hash value of the module listed in the MODULE column
ACTION VARCHAR2(64) Contains the name of the action that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_ACTION
ACTION_HASH NUMBER Hash value of the action listed in the ACTION column
SERIALIZABLE_ABORTS NUMBER Number of times the transaction fails to serialize, producing ORA-08177 errors, per cursor
OUTLINE_CATEGORY VARCHAR2(64) If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
CPU_TIME NUMBER CPU time (in microseconds) used by this cursor for parsing/executing/fetching
ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
OUTLINE_SID NUMBER Outline session identifier
CHILD_ADDRESS RAW(4 | 8) Address of the child cursor
SQLTYPE NUMBER Denotes the version of the SQL language used for this statement
REMOTE VARCHAR2(1) Indicates whether the cursor is remote mapped (Y) or not (N)
OBJECT_STATUS VARCHAR2(19) Status of the cursor (VALID or INVALID)
LITERAL_HASH_VALUE NUMBER Hash value of the literals which are replaced with system-generated bind variables and are to be matched, when CURSOR_SHARING is used. This is not the hash value for the SQL statement. If CURSOR_SHARING is not used, then the value is 0.
LAST_LOAD_TIME VARCHAR2(19) Time at which the query plan (heap 6) was loaded into the library cache
IS_OBSOLETE VARCHAR2(1) Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
CHILD_LATCH NUMBER Child latch number that is protecting the cursor
SQL_PROFILE VARCHAR2(64) SQL profile
PROGRAM_ID NUMBER Program identifier
PROGRAM_LINE# NUMBER Program line number

2.选出最占用资源的会话和查询的新10g视图
Oracle 10g提供了很多可以从操作系统和自动工作量库（AWR）中访问信息的视图。
AWR提供了基于度量的信息，这些信息对于监控和诊断性能问题很有帮助。
度量（metrice）是指“由Oracle定义的某些系统属性的统计集合”。
2.1从v$sessmetric视图中选出当前最占用资源的会话
以下查询会显示在定义的时间间隔内（默认是15秒）最占用物理读取、CPU使用或逻辑读取的会话。您可能会为自己的环境调整适当的阈值。
select
to_char(m.END_TIME,'yyyy-mm-dd hh24:mi:ss') e_dttm,--Interval End Time
m.INTSIZE_CSEC/100 ints, --Interval size in sec
s.USERNAME usr,
m.SESSION_ID sid,
m.SESSION_SERIAL_NUM ssn,
round(m.CPU) cpu100, --CPU usage 100th sec
m.PHYSICAL_READS prds,
m.LOGICAL_READS lrds,
m.PGA_MEMORY pga,
m.HARD_PARSES hp,
m.SOFT_PARSES sp,
m.PHYSICAL_READ_PCT prp,
m.LOGICAL_READ_PCT lrp,
s.SQL_ID
from v$sessmetric m,v$session s
where (m.PHYSICAL_READS>10 or m.CPU>10 or m.LOGICAL_READs>100)
and m.SESSION_ID=s.SID and m.SESSION_SERIAL_NUM=s.SERIAL#
order by m.PHYSICAL_READS desc,m.CPU desc, m.LOGICAL_READS desc;

2.2查看可用的AWR快照
select
t.snap_id,
to_char(t.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') b_dttm,
to_char(t.end_interval_time,'yyyy-mm-dd hh24:mi:ss') e_dttm
from dba_hist_snapshot t
where t.begin_interval_time>trunc(sysdate)
order by t.snap_id;

2.3从DBA_HIST_SQLSTAT视图中选出最占用资源的查询
超过预定义阈值的SQL语句将在AWR中保存一段时间（默认是7天）。可以查询DBA_HIST_SQLSTAT视图找出最占用资源的查询。与本章前面V$SQLAREA查询
等价的语句为：
select
t.snap_id,t.disk_reads_delta reads_delta,
t.executions_delta exec_delta,t.disk_reads_delta/decode(t.executions_delta,0,1,t.executions_delta) rds_exec_tatio,
sql_id
from dba_hist_sqlstat t
where t.disk_reads_delta>1000
order by t.disk_reads_delta desc;
注意，同一个sql_id可能出现在不同的AWR快照中。

2.4从dba_hist_sqltext视图中选择查询文件
由上个选择语句检索到的存在问题的查询的文本可以使用以下查询从dba_hist_sqltext视图中获得：
select * from dba_hist_sqltext t
where t.sql_id='6hmqk9hsfg8d6';

2.5从dba_hist_sql_plan视图中选出查询执行计划
存在问题的SQL的执行计划也能被捕获。可以通过dba_hist_sql_plan表来查看有关执行计划的信息。如果想要显示执行计划，最简单的方法就是使用DBMS_XPLAN包和
以下语句：
select * from table(dbms_xplan.display_awr('6hmqk9hsfg8d6'));

3.确定何时使用索引
在Oracle 5版本中，很多DBA将索引规则称为80/20规则；如果查询只返回少于20%的行，就需要使用索引。在Oracle 7版本中，这个数字减少到7%（均值），而在Oracle 8i
和Oracle9i版本中则接近4%。在版本10中，Oracle能更好地检索整张表，所以该值仍然在5%范围内，虽然它不仅取决于行数，还取决于块的分存情况。
对于较小的表，Oracle中基于成本的优化器通常会在只查询到1%记录的情况下使用索引。
4.遗忘索引
5.创建索引

6.查看表上的索引
sellect * from dba_ind_columns;
【技巧】如果没有在受限的列（返回很少比例的表）上建立索引，那么将会遇见大量较慢的查询。在受限制列上建立索引是改善性能的第一步。

7.修正差的索引
【技巧】不好的索引（为错误的列建立索引）与忘记在正确的列上使用索引一样，也会产生很多的麻烦。尽管Oracle中基于成本的优化器通常会限制一些无效的索引，
但如果同时使用差的索引和好的索引，同样也会产生问题。

8.在删除索引时保持谨慎
对于某些人来说，当他们找到使用无效索引的查询时的第一反应是删除索引。限制索引应该是第一反应，而第二步应该找出索引对其他查询的影响。除非您的查询是
在给定表上执行的唯一查询，否则修改/删除索引可能会导致有害的结果。

9.对SELECT和WHERE中的列使用索引
【技巧】对于系统中很关键的查询，可以考虑在SELECT和WHERE子句的列上创建连接索引（复合索引），这样就只会访问索引。

10.使用快速全局扫描
【技巧】INDEX_FFS提示（从Oracle 8开始可以使用）只会处理索引，而不会访问具体的表。该查询使用和检索的所有列都必须包含在索引中。

11.使查询魔术般加速
select /*+richs_secret_hint*/ --深藏在X$表中
--上面的提示不是隐藏的调整秘密。查询加速的原因是：
【技巧】当连续多次运行同一个查询，该查询将会更加快速，因为您已经在内存中缓存了数据（虽然全表扫描中的数据会比索引扫描更快地超出内存范围）。有
时，访问存储在内存中的数据会让您错误地相信已经有了一个快速的查询。

12.在内存中缓存表
【技巧】将一个相对很小的常用表缓存到内存中，这样可以保证其中的数据不会被其他数据清除出内存。但这样操作时要多加注意，因为缓存的表通常可以修改由优化
器所选择的执行计划，从而导致产生不可预料的查询执行序列（它会影响到嵌套循环连接上的驱动表）。

13.使用多个索引（利用最佳选择性）
【技巧】当在查询中使用表上的多个索引时，请使用最有限制性的索引，特别当您需要改写优化器的选择时。尽管Oracle中基于成本的优化器通常会强制使用最有限制
性的索引，但是根据Oracle版本的不同、查询结构以及所用的初始化参数的不同，Oracle还可能使用不同的索引。

14.使用索引合并
当两个索引与表相比都较小时，索引合并（可以使用提示INDEX_JOIN）就可以改善性能。

15.处理受限制的索引
... where substring(product_id,1,1)=1;
可以改为
... where product_id like '1%';

16.使用基于函数的索引
1）必须设置如下的初始参数才可以使用基于函数的索引（必须是基于成本的优化模式）。
query_rewrite_enabled=true
query_rewrite_integrity=trusted(or enforced)
2）【技巧】当使用基于函数的索引创建通常以在WHERE子句的选择性列上使用的函数为基础的索引时，可能会有效地改善性能。
3）查看函数索引的细节：
select * from dba_ind_expressions t
where t.index_owner='SCOTT' and t.table_name='T1'

17.了解“古怪”的OR
可以将OR看作已合并的多个查询。
select *
from table_test
where pk_col1='A'
and pk_col2 in('B','C')
and pk_col3 ='D';

--9i之前的执行计划
2 Table Access By Rowid TABLE_TEST
1 Index Range Scan TAB_PK
--从9i开始，执行计划为
5 Concatenation
2 Table Access By Rowid TAB
1 Index Unique Scan Tab_pk
4 Table Access By Rowid TAB
3 Index Unique Scan Tab_pk
从9i开始，Oracle改进了优化器处理这个查询的方式（在内部进行了OR扩展），现在的结果使用了全部的主键，并且整个结果都连接在一起（如下面示例所示）。这种
方法比只使用主键的一部分（如上面的示例所示）要快得多，先前的查询只是因为执行计划中的行非常少而显得非常规则。不要被这一点所蒙蔽：执行计划中有较少的行
并不一定代表有更高的查询效率。

在9i之前，如果要得到这样的执行计划，必须分解这个查询：
select *
from table_test
where (pk_col1='A'
and pk_col2 ='B'
and pk_col3 ='D')
or (pk_col1='A'
and pk_col2 ='C'
and pk_col3 ='D')
;

18.使用EXISTS函数
--使用in
select product_id,qty
from product
where product_id=167
and item_no in
(select item_no
from items);--这个查询检索了整个items表

--使用exists
select product_id,qty
from product a
where product_id=167
and exists
(select 'x'
from items b
where b.item_no=a.item_no );
在这个查询中，只将在外部查询（来自PRODUCT表）检索到的记录与在ITEMS表进行比较。如果ITEMS表上的ITEM_NO列建有索引或者ITEMS表非常大，则这个查询要比
第一个查询更加快速，而且该查询可以通过外部查询中的条件PRODUCT_ID=167来限制数据项。
【技巧】根据查询每部分所检索的数据，将EXISTS子句和嵌套子查询放在一起使用可以使查询更快。

19.表就是视图
视图虽然可以隐藏SQL的复杂性，但是会增加优化的复杂性。

20.SQL和“大一统”理论
最好是为需要完成的每个任务单独编写高效的语句

21.Oralce Database 10g中的调整修改
通用的SQL调整原则在10g中仍然不变，但是需要注意一些优化器的修改：
1）10g反对使用RULE optimizer_mode。（在10g中获得基于规则行为的唯一方法是在查询中使用RULE提示）。通常，不建议使用该提示，但在个别查询中可以使用。
2）在10g中有两种基于成本的优化器模式----NORMAL和TUNING：
a)在基于成本的优化器的NORMAL模式中，CBO只考虑了很小的执行计划集合用于确定选择哪个计划。为了在严格限制下生成执行计划，考虑的计划数远比9i中少得多。
SQL配置文件可用来判断使用哪些计划
b)基于成本的优化器的TUNING模式可用来完成更为详细的SQL语句的分析，并且处于NORMAL模式中运行时采取的动作和SQL配置文件中接收的辅助统计数据提供建议。
NORMAL模式可以为大多数SQL语句提供可接受的执行路径。在NORMAL模式中不能很好执行的SQL语句可能需要在TUNING模式中进行调整，然后在NORMAL模式中使用。这能
为已定义过SQL配置文件的查询的性能以及在TUNING模式中执行的复杂查询的优化器工作提供较好的平衡，而不用每次重复地分析SQL语句。

22.10g自动SQL调整
22.1保证调整用户能访问API
只有授权用户才能访问这些特权，特权由SYS授予。“ADMINISTER SQL TUNING SET”只允许访问其自己的调整集。
grant administer sql tuning set to scott; --or
grant administer any sql tuning set to scott;
grant advisor to scott;
grant create any sql profile to scott;
grant alter any sql profile to scott;
grant drop any sql profile to scott;

22.2创建调整任务
declare
tuning_task_name varchar2(30);
tuning_sqltext CLOB;
begin
tuning_sqltext :='select count(*) '||
'from t1 '||
'where lower(t1.b)=:owner';
tuning_task_name := dbms_sqltune.create_tuning_task(sql_text => tuning_sqltext,
bind_list => sql_binds(anydata.ConvertVarchar2(100)),user_name => 'SCOTT',
scope => 'COMPREHENSIVE',time_limit => 60,
task_name => 'first_tuning_task13',
description =>'Tune T1 Count');
end;

22.3查看顾问日志中的任务
select task_name from user_advisor_log;

22.4执行SQL调整任务
begin
dbms_sqltune.execute_tuning_task(task_name => 'first_tuning_task13');
end;

22.5查看调整任务的状态
select status from user_advisor_tasks where task_name='first_tuning_task13';

22.6显示SQL调整顾问报告
select dbms_sqltune.report_tuning_task('first_tuning_task13') from dual;

22.7检查报告输出
findings：n.发现物（ finding的名词复数 ）；调查（或研究）的结果；<律>（陪审团的）裁决
section：n.部分；节；部件；部门
recommendationn：推荐；推荐信；建议；可取之处

[@more@]