Oracle 数据分析和动态采样

引言：Oracle数据库性能调优最需要重视的也最常遇到的就是SQL执行效率，而反映SQL效率最直观的工具就是CBO生成的执行计划，那么如何让CBO生成最精准的效率最高的执行计划成为我们当前需要研究的课题。同一条语句，好的执行计划能带来飞一样的速度，坏的执行计划让我们痛苦不堪，下面我们从原理到实践来把如何产生高效计划的方法教给大家。一 CBO介绍CBO全称叫Cost Based optimization基于代价优化器，它是一个数学模型，同一个SQL语句在不同的oracle版本中计算出来的代价结果也是不一样的，因为每个版本CBO优化器的设计结构有很大不同，现在还不是很完善很智能很通人性，因此我们不能完全依赖它，只能辅助我们。如何生成精确的执行计划：公式数据+CBO=执行计划，传入CBO的数据越精确得到结果越精确，我们能做的保证输入数据更准确，通过精确数据计算出精确执行计划
二演示一个表分析后执行计划比动态采样更准确的例子动态采样：顾名思义就是oracle自动为你进行的初步数据分析，由于是随机在表上取一些数据，因此并不能保证得出的执行计划很准确，只能作为一种辅助分析手段，在不得已的情况下来分析数据，有一定的局限性。场景：当表没有分析信息时，oracle会使用动态采样技术，而且动态采样是在SQL硬解析的时候发生的，传入->CBO参数->生成执行计划。级别：oracle 10g oracle 11g默认动态采样级别是2，它有level1-10，设置的级别越高采集的数据块越多，结果越精确，运行时间越长，level10对所有数据进行采样分析。实验LEO1@LEO1> drop table leo1 purge;清理环境Table dropped.LEO1@LEO1> drop table leo2 purge;Table dropped.LEO1@LEO1> create table leo1 as select * from dba_objects;创建leo1表Table created.LEO1@LEO1> create table leo2 as select * from leo1;创建leo2表，采用leo1一样数据和结构Table created.LEO1@LEO1> col segment_name for a10LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in （"LEO1","LEO2"）;SEGMENT_NA EXTENTS BLOCKS---------- ---------- -------------- -------------- ----LEO1 24 1152LEO2 24 1152查询leo1和leo2表这两个段对象存储参数，都是占用24个区，1152个块，2个表一模一样嘛LEO1@LEO1> col table_name for a10LEO1@LEO1> select table_name,num_rows,blocks,status from dba_tables wheretable_name in （"LEO1","LEO2"）;TABLE_NAME NUM_ROWS BLOCKS STATUS---------- ---------- ---------- ------------ ---------- ------------LEO1 VALIDLEO2 VALID在这个数据字典里只显示表名和当前状态（有效），没有行信息和块信息，这是为神马呢，嗯从上面的操作可以看出，我们只是建立了表，但没有分析表统计信息，现在我们分析一下后看看效果LEO1@LEO1> execute dbms_stats.gather_table_stats（"LEO1","LEO1"）;对LEO1表进行统计分析PL/SQL procedure successfully completed.LEO1@LEO1> select table_name,num_rows,blocks,status from dba_tables wheretable_name in （"LEO1","LEO2"）;TABLE_NAME NUM_ROWS BLOCKS STATUS---------- ---------- ---------- ------------ ---------- ------------LEO1 71968 1051 VALIDLEO2 VALID现在leo1表已经有行信息和块信息了，leo2由于没有进行表分析现在还什么都没有LEO1@LEO1> set autotrace trace expLEO1@LEO1> select * from leo1 where object_id=10000;Execution Plan----------------------------------------------------------Plan hash value: 2716644435--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 97 | 287 （1）| 00:00:04 ||* 1 | TABLE ACCESS FULL| LEO1 | 1| 97 | 287 （1）| 00:00:04 |--------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"OBJECT_ID"=10000）Leo1表进行了统计分析，执行计划显示Rows=1，嗯由于object_id字段是不重复的，我们都知道object_id=10000只有一条记录，这和执行计划的判断结果是一致，所以表分析可以让oracle收集表数据的信息，让CBO了解表数据分布情况，有多少条，占用多少空间等。
总之“分析”就是让oracle理解我们的数据是怎么样一个情况，以便更好的去处理它LEO1@LEO1> select * from leo2 where object_id=10000;Execution Plan----------------------------------------------------------Plan hash value: 2258638698--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2484 | 287 （1）| 00:00:04 ||* 1 | TABLE ACCESS FULL| LEO2 | 12| 2484 | 287 （1）| 00:00:04 |--------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"OBJECT_ID"=10000）Note----- -dynamic sampling used for this statement （level=2）Leo2表没有进行统计分析，oracle采用了动态采样技术（红色字显示），执行计划显示Rows=12，由于动态采样只是随机在表上取一些数据来评估，没有进行全表完整分析，固然得出的执行计划没有leo1表更精确更好。所以说“动态采样”只是一个补救措施。

Linux下的Oracle按周7个文件覆盖备份Oracle 11g direct path read 等待事件的理解相关资讯 Oracle数据库 Oracle 数据分析 Oracle动态采样

Oracle数据库全球化（03月01日）
Oracle数据库日期过滤方法性能比较（02/02/2015 13:20:26）
Oracle数据库安装中端口被占用问题（10/29/2014 07:42:24）

在CentOS 6.6上搭建C++运行环境并（10/10/2015 19:44:40）
Oracle数据库无法使用localhost和（11/14/2014 16:39:10）
Oracle 多数据库的数据同时更新（06/16/2014 21:52:23）

本文评论查看全部评论（0）