在数据库优化中，除了针对业务架构、逻辑及sql调整之外，数据库本身也有很多影响优化器的一些技术，主要有数据库参数、sql计划管理器、hint等。如下图所示：

图 19-1 说明如下

下面我们具体看下这几个技术

1.初始化参数

控制优化器行为的主要参数为：

初始化参数	描述
`CURSOR_INVALIDATION`	为 DDL 语句提供默认的游标失效级别。 `IMMEDIATE` 为 DDL 设置与 Oracle Database 12c 第 2 版 (12.2) 之前的版本相同的游标失效行为。这是默认设置。 `DEFERRED` 允许应用程序利用减少的 DDL 游标失效，而无需对应用程序进行任何更改。延迟失效减少了游标失效的次数，并随着时间的推移分散了重新编译的工作量。出于这个原因，游标在重新编译之前可能会以次优计划运行，并且可能会产生很小的执行时间开销。您可以在 `SYSTEM` 或 `SESSION` 级别设置此参数。请参阅 “ 关于共享游标的生命周期 ” 。
`CURSOR_SHARING`	将 SQL 语句中的文字值转换为绑定变量。转换这些值可以改善游标共享，并且会影响 SQL 语句的执行计划。优化器根据绑定变量的存在而不是实际的文字值生成执行计划。设置为 `FORCE` 在共享现有游标或游标计划不是最佳时启用新游标的创建。设置为 `EXACT` 仅允许具有相同文本的语句共享相同的光标。
`DB_FILE_MULTIBLOCK_READ_COUNT`	指定在全表扫描或索引快速全扫描期间在单个 I/O 中读取的块数。优化器使用这个参数的值来计算全表扫描和索引快速全扫描的开销。较大的值会降低全表扫描的成本，这可能会导致优化器选择全表扫描而不是索引扫描。此参数的默认值对应于数据库可以有效执行的最大 I/O 大小。此值取决于平台，对于大多数平台为 1 MB。因为该参数以块表示，所以它被设置为等于可以有效执行的最大 I/O 大小除以标准块大小的值。如果会话数非常大，则多块读取计数值会降低，以避免缓冲区缓存被过多的表扫描缓冲区淹没。
`OPTIMIZER_ADAPTIVE_PLANS`	控制适应性计划。适应性计划有替代选择。优化器根据在查询执行时收集的统计信息在运行时决定计划。默认情况下，此参数为 `true` ，表示启用自适应计划。设置此参数可 `false` 禁用以下功能：嵌套循环和散列连接选择星形变换位图修剪自适应并行分布方法请参阅 “ 关于自适应查询计划 ” 。
`OPTIMIZER_ADAPTIVE_REPORTING_ONLY`	控制自动重新优化和自适应计划的报告模式（请参阅 “ 自适应查询计划 ” ）。默认情况下，报告模式为关闭 ( `false` )，这意味着启用了自适应优化。如果设置为 `true` ，则自适应优化以仅报告模式运行。在这种情况下，数据库收集自适应优化所需的信息，但不采取任何行动来更改计划。例如，自适应计划始终选择默认计划，但数据库会收集有关在参数设置为时数据库将使用哪个计划的信息 `false` 。您可以使用来查看报告 `DBMS_XPLAN.DISPLAY_CURSOR` 。
`OPTIMIZER_ADAPTIVE_STATISTICS`	控制自适应统计。当查询谓词过于复杂而无法单独依赖基表统计信息时，优化器可以使用自适应统计信息。默认情况下， `OPTIMIZER_ADAPTIVE_STATISTICS` is `false` ，这意味着禁用以下功能： SQL 计划指令统计反馈自适应动态采样请参阅 “ 自适应统计 ” 。
`OPTIMIZER_MODE`	在数据库实例启动时设置优化器模式。可能的值是 `ALL_ROWS` ， `FIRST_ROWS_` `n` 和 `FIRST_ROWS` 。
`OPTIMIZER_INDEX_CACHING`	使用嵌套循环控制索引探测的成本分析。值的范围 `0` 以 `100` 表示在缓冲区缓存索引块，的百分比，其修饰优化有关索引缓存嵌套循环和IN-列表迭代假设。值 `100` 推断 100% 的索引块可能会在缓冲区缓存中找到，因此优化器会相应地调整索引探测或嵌套循环的成本。设置此参数时要小心，因为执行计划可能会更改以支持索引缓存。
`OPTIMIZER_INDEX_COST_ADJ`	调整索引探测的成本。该值的范围是 `1` 到 `10000` 。默认值为 `100` ，这意味着优化器将索引评估为基于正常成本模型的访问路径。值 `10` 表示索引访问路径的成本是索引访问路径正常成本的十分之一。
`OPTIMIZER_INMEMORY_AWARE`	此参数启用 ( `TRUE` ) 或禁用 ( `FALSE` ) 所有 Oracle Database In-Memory (Database In-Memory) 优化器特性，包括 IM 列存储的成本模型、表扩展、布隆过滤器等。将参数设置为 `FALSE` 会使优化器 `INMEMORY` 在优化 SQL 语句时忽略表的属性。
`OPTIMIZER_REAL_TIME_STATISTICS`	当 `OPTIMIZER_REAL_TIME_STATISTICS` 初始化参数设置为时 `true` ，Oracle 数据库会在常规 DML 操作期间自动收集实时统计信息。默认设置为 `false` ，即禁用实时统计。
`OPTIMIZER_SESSION_TYPE`	确定数据库在自动索引验证期间是否验证语句。默认是 `NORMAL` ，这意味着语句被验证。 `CRITICAL` 优先于 `NORMAL` . 通过在会话中设置 `OPTIMIZER_SESSION_TYPE` 初始化参数 `ADHOC` ，您可以暂停此会话中查询的自动索引编制。自动索引过程不识别索引候选，也不创建和验证索引。此控件对于即席查询或测试新功能可能很有用。
`OPTIMIZER_CAPTURE_SQL_QUARANTINE`	启用或禁用 SQL 隔离配置的自动创建。要启用 SQL 隔离在资源管理器终止查询后自动创建配置，请将 `OPTIMIZER_CAPTURE_SQL_QUARANTINE` 初始化参数设置为 `TRUE` （默认为 `FALSE` ）。
`OPTIMIZER_USE_INVISIBLE_INDEXES`	启用或禁用不可见索引的使用。
`QUERY_REWRITE_ENABLED`	启用或禁用优化器的查询重写功能。 `TRUE` ，这是默认设置，使优化器能够利用物化视图来提高性能。 `FALSE` 禁用优化器的查询重写功能，并指示优化器不要使用物化视图重写查询，即使未优化查询的估计查询成本较低。 `FORCE` 启用优化器的查询重写功能，并指示优化器使用物化视图重写查询，即使未优化查询的估计查询成本较低。
`OPTIMIZER_USE_SQL_QUARANTINE`	确定优化器在为 SQL 语句选择执行计划时是否考虑 SQL 隔离配置。要禁用现有 SQL 隔离配置的使用，请设置 `OPTIMIZER_USE_SQL_QUARANTINE` 为 `FALSE` （默认为 `TRUE` ）。
`QUERY_REWRITE_INTEGRITY`	确定强制执行查询重写的程度。默认情况下，完整性级别设置为 `ENFORCED` 。在此模式下，必须验证所有约束。数据库不使用依赖于非强制约束的查询重写转换。因此，如果您使用 `ENABLE NOVALIDATE RELY` ，某些类型的查询重写可能不起作用。要在约束处于 `NOVALIDATE` 模式时启用查询重写，完整性级别必须为 `TRUSTED` or `STALE_TOLERATED` 。在 `TRUSTED` 模式中，优化器相信维度和 `RELY` 约束中声明的关系是正确的。在 `STALE_TOLERATED` 模式下，优化器使用有效但包含陈旧数据的物化视图以及包含新数据的物化视图。此模式提供最大的重写能力，但会产生生成不准确结果的风险。
`RESULT_CACHE_MODE`	控制数据库是将 SQL 查询结果缓存用于所有查询，还是仅用于使用结果缓存提示注释的查询。当设置为 `MANUAL` （默认）时，您必须使用 `RESULT_CACHE` 提示来指定将特定结果存储在缓存中。设置为时 `FORCE` ，数据库将所有结果存储在缓存中。相应的选项 `MANUAL TEMP` 并 `FORCE TEMP` 指定查询结果可以驻留在临时表空间中，除非被提示禁止。设置此参数时，请考虑结果缓存如何处理 PL/SQL 函数。数据库使用跟踪 PL/SQL 函数的数据依赖性的相同机制使结果缓存中的查询结果无效，但允许缓存包含 PL/SQL 函数的查询。由于 PL/SQL 函数结果缓存失效不会跟踪所有类型的依赖关系（例如对序列、 `SYSDATE` 、 `SYS_CONTEXT` 和包变量的依赖），因此在调用此类函数的查询上不加区分地使用查询结果缓存可能会导致结果发生变化，即，结果不正确。因此，在选择启用结果缓存时，请考虑正确性和性能，尤其是在设置 `RESULT_CACHE_MODE` 为时 `FORCE` 。
`RESULT_CACHE_MAX_SIZE`	指定结果缓存可以使用的最大 SGA 内存量（以字节为单位）。缺省是从的值导出 `SHARED_POOL_SIZE` ， `SGA_TARGET` 和 `MEMORY_TARGET` 。此参数的值四舍五入为不大于指定值的 32 KB 的最大倍数。该值 `0` 禁用缓存。
`RESULT_CACHE_MAX_RESULT`	指定 `RESULT_CACHE_MAX_SIZE` 任何单个结果可以使用的百分比。默认值为 `5` ，但您可以指定 `1` 和之间的任何百分比值 `100` 。
`RESULT_CACHE_MAX_TEMP_RESULT`	指定一个缓存查询可以消耗的临时表空间内存的最大百分比。默认值为 `5` 。此参数只能在系统级别修改。
`RESULT_CACHE_MAX_TEMP_SIZE`	指定结果缓存可以在 PDB 中消耗的最大临时表空间内存量。此参数只能在系统级别修改。默认值为的默认值或初始化值的 10 倍 `RESULT_CACHE_MAX_SIZE` 。下面的任何正值 `5` 都四舍五入为 `5` 。指定的值不能超过 `SYS` 模式中当前估计的总可用临时表空间的 10% 。该值 `0` 禁用该功能。
`RESULT_CACHE_REMOTE_EXPIRATION`	指定依赖于远程数据库对象的结果保持有效的分钟数。默认值为 `0` ，这意味着数据库不应使用远程对象缓存结果。将此参数设置为非零值可能会产生过时的答案，例如，如果远程数据库修改了结果中引用的表。
`STAR_TRANSFORMATION_ENABLED`	使优化器能够为星型查询（如果 `true` ）进行星型转换。星形转换结合了各种事实表列上的位图索引。

2. 提示

提示主要是在语句上加入相关提示，以强制数据库按照提示方式执行。部分示例如下：

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;
--单表
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM   employees 
WHERE  department_id > 50;
--多表
SELECT /*+ LEADING(e j) */ *
FROM   employees e, departments d, job_history j
WHERE  e.department_id = d.department_id
AND    e.hire_date = j.start_date;

3.DBMS_STATS

主要定期收集统计信息，以供Oracle优化器选择最优路径。详细信息，后续博客录入。

4.SQL配置文件

SQL 配置文件是查询的辅助统计信息的集合，包括查询中引用的所有表和列。

配置文件以内部格式存储在数据字典中。用户界面是DBA_SQL_PROFILES字典视图。优化程序在优化期间使用此信息来确定最佳计划。实现sql配置文件示例：

DECLARE
  my_sqlprofile_name VARCHAR2(30);
BEGIN
  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'STA_SPECIFIC_EMP_TASK'
,   name         => 'my_sql_profile'
,   profile_type => DBMS_SQLTUNE.PX_PROFILE
,   force_match  => true 
);
END;
/
--列出已有的配置文件
COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;
--更改配置文件
VARIABLE pname my_sql_profile
BEGIN 
  DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
     name            =>  :pname
,    attribute_name  =>  'CATEGORY'
,    value           =>  'DEFAULT'   
);
END;
--删除配置文件
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
    name => 'my_sql_profile' 
);
END;
/

5.sql计划管理

SQL 计划管理使用一种称为 SQL 计划基线的机制，这是一组允许优化器用于 SQL 语句的可接受计划。

其主要目的是--SQL 计划管理可防止由计划更改引起的性能回归。

SQL 配置文件和 SQL 计划基线都通过确保优化器仅使用最佳计划来帮助提高 SQL 语句的性能。

配置文件和基线都是使用提示在内部实现的。但是，这些机制存在显着差异，包括：

通常，SQL 计划基线是主动的，而 SQL 配置文件是被动的。
SQL 计划基线再现特定计划，而 SQL 配置文件则更正优化器成本估算。

也可参考：

http://blog.itpub.net/29487349/viewspace-2765000/

详细情况可参考Oracle官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/overview-of-sql-plan-management.html#GUID-B51FBAA9-EE95-4F19-AD4B-F5F4C2910688

【OPTIMIZATION】Oracle影响优化器选择的相关技术

1.初始化参数

2. 提示

3.DBMS_STATS

4.SQL配置文件

5.sql计划管理