SQL和PL/SQL的性能优化之一--优化器
1、ALL_ROWS 优化器最小化SQL语句处理所有记录的成本。这是一种系统默认行为，最适合批量处理和报表查询。
FIRST_ROWS_N 检索前N行记录所有成本最小化的执行计划，（1，10，100或1000），适用OLTP
2、直方图 列直方图的创建，优化器能为有选择性的值选择用索引，为没选择性的列选择全表扫描。
3、绑定变量窥探--ORACLE 10G引入，通过ORACLE使用得到的第一个绑定变量值来决定执行计划。如果第一个值引发了索引扫描，这将导致接下来每一个查询都使用索引扫描。
4、自适应游标共享--ORACLE 11G引入，试图解决绑定变量窥探带来的问题。
如果优化器发现SQL可能会随着绑定变量的值的不同而产生不同的执行计划，它会把这个SQL标记为绑定敏感。当使用不同的变量值执行此SQL时，多个副本被缓存，每个副本对应不同的执行计划。一个SQL_ID，对应多个CHILD_NUMBER。 V$SQL
5、收集统计信息
自动统计信息收集--从ORACLE 10G开始，默认通过一个系统产生的任务收集对像统计信息。下面是11G数据库里关闭自动统计信息收集的方法：
BEGIN
dbms_auto_task_admin.disable
(client_name => 'auto optimizer stats collection',
operation => null,
window_name => null);
END;
对dbms_auto_task_admin.enable程序执行类似调用可使自动收集重新生效。
如果存在从最后一次统计收集后有超过10%的记录被修改的表，那么下面会重新收集所有这些统计信息过时的表的统计信息
BEGIN
dbms_stats.gather_schema_stats(owner => user,
options => 'GATHER STALE');
END;
动态抽样（数值在0~10之间）
如果参数optimizer_dynamic_sampling设为0，不会产生抽样，设为10时，当SQL所涉及表信息丢失或不完整，抑或SQL两个或多列有过滤条件，系统将读取这些表的所有块。此参数默认值是2，表示优化器仅未分析的表进行少量数据块的抽样。
6、DMBS_STATS
使用默认设置，收集一张表的统计信息
dbms_staus.gather_table_staus(owner => user,
tablename => 'EMPLOYEES');
收集整个模式的统计信息：dbms_stats.gather_schema_stats(ownername=>'HR');
收集一个模式中任何“过时”的表的统计信息:
dbms_stats.gather_schema_staus(ownername =>'HR',
options => 'GATHER STALE');
创建所有索引列的直方图
dbms_stats.gather_schema_stats(ownname => 'HR',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
将默认收集方式设置为只为数据倾斜分布的索引列创建直方图
dbms_stats.set_database_prefs(pname =>'METHOD_OPT',
pvalue=>''FOR ALL INDEXED COLUMNS SIZE SKEWONLY');
创建并导出统计信息到一张统计表中
dbms_staus.create_stat_table(ownname => user,
stattab => 'GuysStatTab');
dbms_stats.export_table_stats(ownname =>user,
tablename =>'EMPLOYEES',
stattab=>'GuysStatTab',
statid =>'Demol');
从一张统计信息表中导入统计信息到当前模式：
dbms_stats.import_table_stats(ownname => user,
tabname => 'EMPLOYEES',
stattab => 'GuysStatTab',
statid => 'Demol');
把HR模式的衡量统计信息是否过时的阀值设置为20%，只有在DML操作已经修改了20%的记录的情况下，这些信息才会被认为是过时的。
dbms_stats.set_schema_prefs(ownname => 'HR',
pname => 'STALE_PERCENT',
pvaule=> 20);
抽样：准确的统计信息通常可以在不必读取表或索引中每一个块的情况下就能被收集起来。默认情况下，dbms_stats只会读取每张表记录的一个抽样。
分区统计信息
GRANULARITY子句将决定对一张分区表的统计信息是把整张表作为一个整体还是在分区或子分区级别上收集。另外，你可以使用dbms_stats.set_table_prefs程序为一张分区表建立一个增量的（INCREMENTAL）收集策略。如果INCREMENTAL被设置为TRUE，统计信息就只会从已被修改过的分区中收集。
锁定统计信息
LOCK_SCHEMA_STATS或LOCK_TABLE_STATS(不希望统计信息改变)
解锁 UNLOCK_SCHEMA_STATS或UNLOCK_TABLE_STATS
操纵统计信息
当我们需要确定当表增长后执行计划将如何改变时，尤其需要使用这种方法。例如，我们可用dbms_stats调用将雇员表的记录数设置为10000，并保证到MANAGER_ID显示为我们期望的200个经理。
BEGIN
dbms_stats.set_table_stats(ownname => user,
tabname =>'EMPLOYEES',
numrows=>10000,
numblks=>500);
dbms_stats.set_column_stats(ownname => user,
tabname =>'EMPLOYEES',
colname=>'MANAGER_ID',
distcnt=>200,
density=>0.005);
END;
