SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
可以通过此方法来更改.如下:
ALTER SESSION SET OPTIMIZER_MODE = 'FIRST_ROWS_1';
1.2 选择性和基数
一个列具有高度选择性，是指表达式能从一张表返回小比例的记录（也就是一列的值重复性的记录很少）。基数是指表达式返回的记录数量：一个100的基数就是100条记录可能被返回的另一种说法。
1.3 成本计算
查询优化器的输入
1.4. 对象统计信息
对象统计信息记录了表、分区表和索引中的数据列和数据的分布。可以在多个视图中来检查这些统计信息，包括DBA_IND_STATISTICS、DBA_TAB_STATISTICS和DBA_TAB_COL_STATISTICS。
1.5 直方图
列直方图的创建，使得Oracle能够在其他值不具备选择性的列中识别出那些具有选择性的值，反之亦然。优化器能够为有选择性的值选择使用索引，为没有选择性的值选择全表扫描。举例如下：
我所在的文思海辉公司在全球有将近3万人，在中国的各城市分布较多如北京，然而在美国分布较少，最少的美国俄亥俄州只有3人（注意：这只是个例子不代表公司实际数据，只是出于学习举个例子而已）分布如下：
SQL> SELECT MAX(emp_id)
FROM emp
WHERE area = ''美国俄亥俄州';
Execution Plan
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
ID | Operation | Name | Rows | Bytes |
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
0 | SELECT STATEMENT | 23 |
1 | SORT AGGREGATE | 23 |
2 | TABLE ACCESS FULL | EMP | 25000 | 79526 |
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
接下来我们为表创建直方图。Oracle通过DBMS_STAT的METHOD_OPT参数来创建直方图。如下：
BEGIN
DBMS_STAT.gather_table_stats(
ownname => 'zyj',
tablename => 'emp',
estimate_percent =>dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade =>true,
degree => 7
);
END;
直方图创建后再来执行上面的语句：
SQL> SELECT MAX(emp_id)
FROM emp
WHERE area = ''美国俄亥俄州';
Execution Plan
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
ID | Operation | Name | Rows |
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
0 | SELECT STATEMENT | |
1 | SORT AGGREGATE | EMP 3 |
2 | INDEX RANGE SCAN | EMP _INDX | 3 |
- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - -- - - - - - -- - - - - -
直方图创建后当我们查询中国北京的员工人数时候就会用到全表扫描，当查询美国俄亥俄州的员工数的时候就会用到索引。
1.6 绑定变量窥探
在Oracle10g之前，直方图和绑定变量通常是不兼容的。Oracle 10g引入了绑定变量窥探，通过它Oracle使用得到的第一个绑定变量的值来决定执行计划。如在上述的例子中档查询文思海辉美国俄亥俄州的员工的时候，会使用索引扫描，但接下来的每个查询都会使用的索引扫描。请记住执行计划是缓存在共享池中的，因此一旦建立执行计划，通常所有的会话在接下来的执行中都会使用它。不过貌似，绑定变量窥探技术逐渐被弃用了。取而代之的是Oracle 11g引进的自适应游标共享。接下来会讲到。
1.7 自适应游标共享
当使用不同绑定变量的SQL时，它的多个副本可能被缓存，每个副本都会对应不同的执行计划。每个副本被赋予同样的SQL_ID ，但各自却有不同的CHILD_NUMBER。在上述例子中档查询文思海辉中国北京的员工的时候就会使用全表扫描，当查询美国俄亥俄州的员工的时候就会使用索引扫描。
1.8 数据库参数
数据库配置和参数能帮助优化器计算出需要多少机器操作。特别是数据库配置，它能改变可能需要的物理IO数量。如：DB_FILE_MULTIBLOCK_READ_COUNT控制一次单独的IO操作可以从磁盘读取多少个块，常常用在执行全表扫描的时候。通常，我们不会改变默认值，它是一个随系统耳钉的特定值，通常允许单个IO读取512K~1MB的数据。可以通过如下语句设置数据块参数：
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=40
下面列出一些可能影响优化器成本计算的参数。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
db_block_size 数据库的数据块大小
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
db_file_multiblock_read_size 单次IO可以读取的数据块的数量。更高的值将导致优化器调低全表扫描的代价。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
memory_target oracle的SGA与PGA的目标大小
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
optimizer_mode 优化器模式：FIRST_ROWS_N、ALL_ROWS以及其他模式。上面有叙述
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
parallel_threads_per_cpu 每个CPU支持的并行执行线程的数量。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SGA_target oracle SGA的目标大小
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
还有很多数据库参数这里就不一一列举。总之，要理解并实践。
V$SYS_OPTIMIZER_ENV和V$SES_OPTIMIZER_ENV视图列出了很多能够影响优化器创建执行计划的方式的参数和设置。下面的这个查询显示了当前会话的这些值：
SQL> SELECT NAME , e.isdefault , e.VALUE , p.description
FROM v$ses_optimizer_env e LEFT OUTER
JOIN v$parameter p USING (NAME)
JOIN v$session USING ( SID )
WHERE audsid = USERENV ('sessionid')
ORDER BY isdefault , NAME;
1.9 系统统计信息
为了在权衡CPU和IO时做出更好的决策，优化器借助了系统统计信息。这些统计信息可以在SYS.AUX_STATS$ 表中看到。
2 . 使用 DBMS_STAT
概述： DBMS_STAT包手机并管理优化器的统计信息。它有海量的选项，所以需要了解最常见任务和他们的语法。
a . 使用默认设置，收集一张表的统计信息。
DBMS_STATS.gather_table_stats
(
ownname => USER,
tabname => '表名称'
);
b. 收集整个模式的统计信息
DBMS_STATS.gather_schema_stats
(
ownname => 'zyj'
);
c . 创建所有索引列的直方图
DBMS_STATS.gather_schema_stats
(
ownname => 'ZYJ'
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
);
d. 将默认收集方式设置为只为数据倾斜分布的索引列创建直方图
DBMS_STATS.set_database_prefs
(
pname => 'METHOD_OP'
pvalue => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'
);
2.1 DBMS_STATS 的存储过程和参数
DBMS_STATS 为收集、删除、导出和统计信息提供了存储过程。
2.2 . 使用METHOD_OPT 创建直方图
oracle的METHOD_OPT的默认值为 " FOR ALL COLUMNS AUTO "。 太多的直方图会增加收集统计信息额时间，并导致更大的解析开销，以及在11g中产生更多的子游标。因此，依赖Oracle决定何时直方图是有用的并不总是个好想法。可以使用METHOD_OPT参数手动决定直方图的收集。METHOD_OPT语法如下：
FOR [ ALL {INDEXED | HIDDEN} ]
COLUMNS
[ columns_expression ] [size_clause]
[ , [ columns_expression ] [size_clause ] ]
注意： columns_expression通常是一个列的名称。Size_clause 遵照下列语法：
SIZE { bucket_size | REPEAT | AUTO | SKEWONLY }
总结： 直方图增加了优化器成本估算的正确性，但也增加了收集统计信息的成本。当你相信一个列有不规则的数据分布，而且这个列将被包含在WHERE 或者 JOIN 表达式中时，为此列创建直方图通常是值得的。
2.3 . 扩展的统计信息
a . 多列扩展统计信息。举例如下：
BEGIN
DBMS_STATS.gather_table_stats
(
ownname => 'zyj',
tabname =>'staff' ,
method_opt => 'FOR ALL COLUMNS FOR COLUMNS (staff_sex, staff_name) '
)
END ;
这段程序调用除了收集每个单独列的统计信息，也包括性别和名字的组合信息。
2759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
