温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
有些失去是注定的,有些缘分是永远不会有结果的,爱一个人不一定要拥有,但拥有了一个人就一定要珍惜,不要等到伤害的时候才去乞求原谅,不要等到失去的时候再去挽回。
LOFTER精选
阅读(475)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_084',
blogTitle:'使用SQL_Trace/10046事件进行数据库诊断',
blogAbstract:'SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具.在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。\r\n当客户抱怨某个应用比较慢时，我们使用TOP SQL 等工具也无法抓到此应用比较慢的SQL语句等信息时，我们可以使用SQL_TRACE或者10046事件对其应用跟踪，就可以得到此应用的执行过程中SQL的一些信息。然后我们就可以通过对此跟踪信息的分析，找出真正的导致应用比较慢的“罪魁祸首“。\r\n&一、 使用SQL_Trace/10046事件的介绍\r\n(a) 如何使用SQL_TRACE',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:5,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'有些失去是注定的,有些缘分是永远不会有结果的,爱一个人不一定要拥有,但拥有了一个人就一定要珍惜,不要等到伤害的时候才去乞求原谅,不要等到失去的时候再去挽回。\r\n',
hmcon:'-1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}后使用快捷导航没有帐号？
查看: 509|回复: 4
执行计划+Hint+统计信息+并行+绑定变量+sql跟踪和10046事件+10053事件
高级会员, 积分 518, 距离下一级还需 482 积分
论坛徽章:4
123 执行计划+Hint+统计信息+并行+绑定变量+sql跟踪和10046事件+10053事件
谭怀远书一 5至11章读书笔记
执行计划1.执行计划中的rows表示CBO从一个行源预期获取的记录数，这个行源可能是一个表，也可能是一个索引。2.rows值对CBO做出正确的执行计划至关重要，如果CBO获取的rows值不准确（通常是没收集统计信息或统计信息过旧导致），在计算执行成本时就会出现偏差，从而制定出错误的执行计划。3.收集统计信息 exec dbms_stats.gather_table_stats('username','tablename',cascade=&true);
& && & exec dbms_stats.gather_table_stats('username','tablename'); --小括号内大小写均可
& && && && && & cascade=&true表上相应索引的全局和分区级别也会被分析。4.在多表关联查询或sql中有子查询时，每个关联表或是子查询的rows值对主查询的影响非常大，CBO就是依赖于各个关联表或子查询的rows值来计算出最后的执行计划。对于多表查询，CBO使用每个关联表返回的行数决定使用什么样的访问方式来做表关联（nested loops join或hasn join）；对于子查询，它的rows值将决定子查询是使用索引还是使用全表扫描的方式访问数据。5.如果一条sql的性能出了问题，首先要看它的执行计划，以便确定（或猜测）问题的所在。6.生成sql的执行计划是oracle在对sql做硬分析时的一个重要步骤，执行计划告诉oracle执行这条sql时以什么方式访问数据：索引还是全表扫描，是hash join 还是nested loop join等，比如某条sql通过使用索引的方式访问数据时最节省的，结果CBO做出的执行计划却是全表扫描，那么这条sql的性能必然是比较差的。7.sqlplus 显示执行计划和统计信息 set autot trace exp stat8.执行计划的执行顺序：从右往左，从下往上，两行缩进一样时，上面的先执行。9.执行计划中的time列为oracle估算当前操作的时间。P9910.执行计划下方（虚线方框外）是谓词信息和数据获取方式，表示制定第几行的执行计划时用了什么过滤条件。谓词信息如果是access将影响数据的访问路径（走表还是索引），如果是filter则不会，只起到过滤作用。看执行计划时要注意谓词，如果是access，就要思考对于谓词的条件，使用的访问路径是否正确。11.如果表没有作分析，那么CBO可以通过动态采样的方式来分析数据，也可以获得正确的执行计划；如果表分析过，但统计信息过旧，这时候CBO不会使用动态采样，而是使用旧的统计数据，从而做出可能错误的执行计划。12.看执行计划时，不能只看执行计划本身，还要看谓词信息和统计信息。13.Oracle官方文档《Oracle Database Performance Tuning guide》中的Using EXPLAIN PLAN一章对执行计划有非常详细的讲解。
1．对于hint，谭怀远的看法是，这个技术应该属于DBA而不是开发人员，开发人员不应该在代码中使用它，它更像是一个oracle提供给DBA用来分析的工具。开发人员在一些sql中加入了足以约束sql执行计划的hint，可能导致非常严重的后果，因为数据库是变化的，某个时刻使用这个执行计划是最优的，但另外一个时刻却可能是最差的，这也是CBO取代RBO的原因。
2．使用hint时需要注意的一点：并非在任何时候hint都会起作用，这也是有人问“为什么我设定了一个hint，CBO却没有选择它”的原因。一个重要原因是：如果CBO认为使用hint会导致错误的结果时，hint将被忽略。3．如果sql中使用了表的别名，那么hint中也要使用表的别名，否则CBO会忽略hint。例如：
select /*+ full(a) */ count(*)& & --此为正确的写法Hint分类：
1.优化器hint
/*+ all_rows */和/*+ first_rows */告诉oracle使用哪种优化器模式来做sql解析。三种设定优化器类型的方式：
系统级：alter system set optimizer_mode=all_
会话级：alter session set optimizer_mode=all_
sql中提示：/*+ all_rows */如果在代码中加入hint会有好处的话，谭怀远认为all_rows和first_rows应该被推荐。如果有分页显示业务 optimizer_mode为first_rows(n)是最优的。可以在all_rows模式（一般用于OLAP）下，通过hint来使用first_rows，也可以在first_rows模式（一般用户OLTP）下，通过hint来使用all_rows。hint的优先级高于参数文件中指定的参数&&优先级顺序？hint&session&system2.访问路径hint
这一部分hint将直接影响sql的执行计划，所以在使用时要小心，在代码中不要轻易使用它，但它对于DBA分析sql的性能非常有用，DBA可以让sql使用不同的hint得到不同的执行计划，通过比较不同的执行计划效率来分析当前sql性能。
下面t表为dba_objects的前1000条记录，索引idx_t为t表object_id列主键索引。
select /*+ full(t) */ *&&--全表扫描提示
select /*+ index(t idx_t) */ * from t where object_id&1;& &--索引提示
select /*+ no_index(t idx_t) */ * from t where object_id=1;& &--不使用索引提示
select /*+ index_desc(t idx_t) */ * from t where object_id=1;& &--对索引降序访问提示
select /*+ index_combine(t idx_bm_t) */ *& &--位图索引提示
select /*+ index_ffs(t idx_t) */ * from t where object_id&100;&&--索引fast full scan提示，为什么不是范围
select /*+ index_join(t idx_bm_t idx_t) */ object_id&&from t where object_id&100 and status='valid';& &--索引关联提示（当谓词中引用的列上都有索引的时候，可以通过索引关联的方式来访问数据）create index idx_t on t(id,object_name);
select /* index_ss(t idx_t) */ * from t where object_name='TEST';& &--索引skip scan提示，当在一个联合索引中，某些谓词条件不在联合索引的第一列时，可以通过index skip scan来访问索引获得数据，当联合索引第一列的重复值很多时，使用这种方式比全表扫描效率要高。
以下较难理解：
如果谓词中没有联合索引的第一个字段，CBO会选择全表扫描；
如果联合索引第一个字段的值重复率很高（多数是相同的值）时，扫描联合索引的效率会比全表扫描要高，此时可以使用提示index skip scan。
如果联合索引第一个字段的值重复率很低（多数是不同的值）时，全表扫描的效率会比扫描联合索引要高，此时可以使用全表扫描提示。3.表关联hint
select /*+ leading(t1,t) */ t.* from t,t1 where t1.id=t. --t1作为驱动表，首先访问t1表的数据
select /*+ ordered */ t.* from t,t1 where t1.id=t. --按照from表的顺序选择驱动表。建议用上一种方式。4.表关联操作hint
hash join的工作方式：将小一点的那个表做hash运算，将列数据存到hash列表中，从另一个表中抽取记录，做hash运算，到hash列表中找到相应的值做匹配。
nested loops的工作方式：从一张表中读取数据，访问另一张表（通常是索引）来匹配，netsted loops适用的场合是当一个关联表比较小的时候，效率会比较高。
merge join的工作方式：首先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做很多的排序，所以消耗的资源更多一些，通常来讲，能够使用merge join的地方，hash join都可以更好的发挥性能。
select /*+ use_nl(t1,t) */ t.* from t,t1 where t1.id=t.& & --netsted loops提示
select /*+ use_hash(t1,t) */ t.* from t,t1 where t1.id=t.& &--hash join
select /*+ use_merge(t1,t) */ t.* from t,t1 where t1.id=t.& &--merge join
trace文件中的query列值跟执行计划后的统计信息中的consistent gets都为一致性读。
当多表关联时，其中一个表很小，并且列上有索引时，nested loop的效率更高。这三个提示的相反提示：
/*+ no_use_nl(t1,t) */&&--此时优化器用hash join
/*+ no_use_hash(t1,t) */&&--优化器用merge join
/*+ no_use_merge(t1,t) */&&--优化器用hash join5. 并行提示
select /*+ parallel(t 4) */ count(*) --并行提示，这个值会覆盖表自身设定的并行度，如果这个值为default，则表示使用系统参数值。select degree from user_tables where table_name='TEST';&&--查TEST表本身的并行度
select /*+ no_parallel(t) */ count(*)& &--禁止使用并行6. 其他hint
/*+ append */& & --直接加载提示
select /*+ dynamic_sampling(t 4) */ * from t where id&314;& &--动态采样级别提示
select /*+ driving_site(departments) */&&* from employees e,departments@rsite d where e.id=d.&&--如果没有这个提示，oracle会在远端机器上执行departments表的查询，将结果返回本地，再和employees表关联。如果使用driving_site(departments)，oracle将查询本地表employees，将结果送到远端，在远端数据库上与表departments关联，然后将查询结果返回本地。如果departments查询结果非常大，或者employees非常小，并且两表关联后的结果集非常小，那么就考虑把本地的结果集发到远端，在远端执行完后，再将较小的结果返回本地。
不建议在代码中加入hint，特别是可能影响CBO选择执行计划的hint，比如sql的访问路径、表关联方式、表关联按顺序等，那样可能产生严重的后果，它可能导致执行计划选择错误、sql的执行效率低下的后果。
如果在实际生产环境中，遇到CBO执行计划选择错误的情况，我们最先应该考虑的是我们是否有什么地方做的有失误，比如表的分析是否是最新的，是否对相关的列做了直方图，是否对分区表做了全局或分区分析，而不是武断的使用hint来构造一个死板的sql。
分析（统计信息）及动态采样
是否对表进行了分析，可通过两个视图（user_tables、user_indexes）来确认：
SQL& select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='CONTACT';
& &NUM_ROWS AVG_ROW_LEN& &&&BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
& && && &2& && && & 26& && && & 5
11:0SQL& select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='CONTACT';
& &&&BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
& && && &0& && && &&&1& && && && & 2
11:0exec dbms_stats.gather_table_stats('username','t'); --默认情况下，对表及其索引和每一列的列值都做了分析。
dbms_stats包对表的分析分为三个层次：
表自身的分析(包括表中的行数、数据块数、行长等)
列的分析（包括列值的重复数、列上的空值、数据在列上的分布情况）
索引的分析（包括索引叶块的数量、索引的深度、索引的聚合因子等）直方图指的是数据在列上的分布情况，oracle做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket（桶），这样CBO就可以非常容易地知道这个列上数值的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算中。对于数据分布非常倾斜的表，做直方图分析是非常有用的。
默认情况下，dbms_stats包会对所有的列做直方图分析，可以从user_histograms中获得相关直方图信息：
SQL& select table_name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CONTACT'; ???删除直方图（即删除列的统计信息）：exec dbms_stats.delete_column_stats('username','t','id');
--用户名，表名，列名DBMS_STATS包
分为性能数据的收集、设置、删除、备份和恢复
1.性能数据的收集包括：gather_database_stats/gather_dictionary_stats/gather_fixed_objects_stats/
gather_index_stats/gather_schema_stats/gather_system_stats/gather_table_statsgather_table_stats中如果想知道某个参数的默认值，可以通过以下方式获得：
select dbms_stats.get_param('参数名称')参数degree用来指定分析时使用的并行度，有以下三种值：
1.null 将使用被分析表的并行度
2.一个数值 显示指定分析时使用的并行度
3.default_degree 使用参数文件中的并行参数来设定并行度表分析原则：①看一下新进来的数据量在全表中所占的比例，如果所占比例不是很大，那么可以考虑不做全局分析，否则就考虑，依据是业务的实际运行情况。②采样比例。如果载入的数据量非常大，比如成千万，就要把采样比例尽量设置的小，但底线是不影响CBO做出正确的执行计划，采样比例的上限是不能消耗太多的资源而影响到业务的正常运行。③新加载的数据应该要做分区级的数据分析。执行gather_table_stats后，gather_index_stats、gather_column_stats的信息也被收集了。2.分析数据的设置
set_column_stats/set_index_stats/set_system_stats/set_table_stats
用于当相应的指标不准确导致执行计划失败时，可以使用这种方法手工地来为这些性能数据赋值，还可以做测试用，可以轻易地给对象赋予我们希望的值，以达到模拟一个测试环境的目的。
exec dbms_stats.set_table_stats('username','tablename',numrows=&100000); --把统计信息中的表行数设为100003.删除分析数据
delete_column_stats/delete_database_stats/delete_table_stats/delete_schema_stats/delete_index_stats/delete_dictionary_stats/delete_fixed_objects_stats4.导出和导入分析数据
export_table_stats/export_index_stats/export_schema_stats/export_database_stats/export_dictionary
import_table_stats/import_index_stats/import_schema_stats/import_database_stats/import_dictionary例子：
exec dbms_stats.create_stat_table('scott','stat_tab'); --创建一个表用于存放分析数据
exec dbms_stats.export_table_stats('scott','emp',stattab=&'stat_tab',cascade=&true); --导出EMP的分析数据
exec dbms_stats.set_table_stats('scott','emp',numrows=&1000);&&--把分析数据中的表行数设定为1000行
exec dbms_stats.delete_table_stats('scott','emp');&&--删除emp的分析数据
exec dbms_stats.import_table_stats('scott','emp',stattab=&'stat_tab',cascade=&true); --导入分析数据5.锁定/解除锁定分析数据
dbms_stats.lock_schema_stats/lock_table_stats/ unlock_schema_stats/unlock_table_stats
用于当前统计信息非常好，执行计划很准确，并且表中数据几乎不变化，那么可以锁定统计信息，不允许对表进行分析或设定分析数据。6.恢复分析数据
reset_param_defaults/restore_schema_stats/restore_system_stats/restore_table_stats
用于将统计信息恢复到历史上的某个时刻。
select dbms_stats.get_stats_history_&&--查询恢复分析数据的最早时间点
exec dbms_stats.restore_table_stats('scott','emp','时间戳');在OLAP中，将动态采样设定为3或4，在OLTP中，不应该使用动态采样。凡是sql的执行发生效率上的异常，多少会跟分析有关，在对sql优化或性能故障分析时，对数据分析要非常有把握，至少心里有数。
并行处理在OLAP中非常有用，如果CPU较多，并行就比串行快很多。但对于OLTP并行不合适，因为OLTP以访问索引为主，并且返回的结果集非常小，这样sql处理的非常快，不需要启用并行。alter table t parallel 4; select * from t wehre id=100;尽管表上启用了并行，但CBO并没有选择使用并行，原因id字段的重复率非常小，访问索引的代价小，只有几个逻辑读，所以没必要使用并行。
select object_type,count(*) from t group by object_ 则使用了并行。并行处理的机制：库启动时，首先根据初始化参数parallel_min_servers=n（11g中该值默认为0）来分配n个并行服务进程，当一条sql被CBO判断需要使用并行时，协调进程启动n个并行服务进程，如果启动的并行服务进程不足以满足并行度的要求，则并行协调进程将额外启动并行服务进程以满足业务需要。并行服务进程处理完后将结果发给协调进程，协调进程将处理结果汇总后发给用户。并行执行等待事件：PX Deq Credit:send blkd 当并行服务进程向协调进程QC发送消息时，同一时间只有一个并行服务进程可以向上一层进程发送消息，这时如果有其他的并行服务进程也要发送消息，就只能等在那里，直到获得一个发送消息的信用信息credit，这时候就会触发这个等待事件，这个等待事件的超时时间为2秒钟。
如果我们启动了太多的并行服务进程，而实际上CPU或QC无法及时处理并行服务发送的数据，那么等待不可避免，此时，降低并行度是必要的。
parallel_max_servers=240 库最多可启用240个并行进程，11g中该值默认为20，默认值还是很合理的。
direct path read/db scattered read通常是并行操作
PX Deq Credit:send blkd等待时间超时是2秒钟，如果平均等待时间也差不多是2秒钟，说明是下层并行服务进程无事可做，处于空闲状态。如果等待事件的平均时长小于2秒钟，如81ms，是并行服务之间的竞争导致的，因为并行服务进程在很短的等待后，就获得了资源，此时问题就是并行操作太多导致了等待。并行操作的使用范围：
并行查询、并行DDL（如建表、建索引）、并行DML（如增删改）1.并行查询可以在查询语句、子查询中使用，但是不可以用在远程dblink的对象上。
select /*+ parallel(t 4) */ count(*) from t where object_name in (select /*+ parallel(t1 4) */ object_name from t1);
并行查询满足的条件：
sql中有提示如parallel或parallel_index
sql中引用的对象被设置了并行属性。
在多表关联中，至少有一个表执行了全表扫描或跨分区的 index range sacn。2.并行ddl
以下表操作可以使用并行：
create table t parallel 4 as select * from dba_
alter table t move partition p1 parallel 4;& &--表整理可以使用并行吗？测试确认是可以的，如下：
SQL& alter table emp move parallel 2;
Table altered
alter table t split partition p1 at (5000) into (partition p1,partition p2) parallel 4;
alter table t coalesce partition parallel 4;？？？？？3.创建索引时使用并行
在系统资源充足时使用并行创建索引会使性能大大提高，特别是在大表上建索引更是如此。以下索引操作可使用并行：
create index t_ind on t(id) parallel 4;
alter index t_ind rebuild parallel 4;&&
alter index t_ind rebuild parallel 4& &索引重建并移动表空间
alter index t_ind rebuild partition p1 parallel4;&&--分区表中的索引重建
alter index t_ind split partition p1 at (5000) into (partition p11,partition p12) parallel 4;&&???4.并行dml
如果在dml中使用并行，必须在会话中显示地执行：alter session如果无此设定，即使sql中指定了并行，oracle也会忽略它。对于insert into … select … 可以并行，insert into … values …不能并行
alter session
insert /*+ parallel(t,4) */ into t select /*+parallel(t,4) */&&* from t1;对于delete、update、merge操作，只有操作的对象是分区表时，oracle才会启用并行操作（11g中均可以使用）。
下面的例子中，t为分区表,t1、t2为未分区表。
alter session
delete /*+ parallel(t 2) */&&--使用了并行，删除分区表t
update /*+ parallel(t 2) */ t set name=name||'';& &name加了一个空格
merge /*+ parallel(t 2) */ into t using t1 on (条件) when matched then update set t.name=t1.并行参数；
parallel_min_servers=n 实例启动时，启动n个并行服务进程
parallel_max_servers=n 当前实例总共启动的并行服务进程不能超过该值
parallel_adaptive_multi_user=true|false&&默认为true，此参数为根据系统负载，动态调整并行度，以取得最佳性能。
parallel_min_percent 默认为0，并行进程最小百分比，为null时至少为2并行度的三种设定&&原则上oracle使用并行度最高的值，作为当前执行的并行度
hint 、alter session force parallel 、表或索引上设定并行度，如下：
select /*+ parallel(t 4) */ count(*)
alter session enable parallel dml|ddl|&&alter session force parallel dml|ddl|query parallel 4;
alter table t paralle 4;
并行度的优先级从高到低 hint&alter session force parallel &表或索引的设定 &系统参数
直接加载：数据直接追加到高水位后面，不需要花费时间寻找空间，不经过data buffer 直接写到数据文件中
数据插入或数据加载时通过append提示进行数据加载，如下：
insert into /*+ append */ into t select * from dba_
直接加载最常见的是和nologging一起使用，此时可以有效减少redo和undo的数据量，如下：
insert inot /*+ append */ into t select * from dba_
直接加载通常是针对一些数据量非常大的表，如果这些表存在索引，将带来很大性能影响，这时可以考虑将索引disable掉（或drop掉），然后加载数据，之后再重建索引。直接加载和并行，直接加载可以和并行一起使用，这样可以并行的向表中插入数据，如下：
alter session
insert /*+ append parallel(t 2) */ into t select * from t1;
对insert使用/*+ parallel */和/*+ parallel append */执行方式是一样的，因为insert使用并行时，oracle自动使用直接加载，此时append提示可以没有。sql loader中使用直接加载：
sqlldr userid=scott/tiger control=xxx.ctl direct=true parallel=true log=log.txt 使用直接加载和并行
sql loader 进行数据批量加载，比传统加载方式更高效，因为绕开了sql解析和数据缓冲区，直接把数据加载到了数据文件中，对于OLAP非常有用。
sql loader直接加载对索引的影响：1.如果索引为非约束性，直接加载可以在加载完毕后维护约束的完整性。2.如果索引为约束性索引，比如主键，直接加载仍然会将数据载入库，但会将索引置为unusable。
sql loader时使用了直接加载+并行，表上有索引，将导致sql loader失败，可以跳过索引，如下：
sqlldr userid=scott/tiger control=tt.ctl direct=true parallel=true skip_index_maintenance=true log=log.txt 加载完成后索引状态变为unusable，需要手工rebuild index。
sql执行过程：对sql做hash运算，到share pool中寻找是否有该hash值的执行计划，如果找到了，则拿该执行计划去执行该sql，然后把结果返回用户，如果没有找到执行计划，就按下列步骤执行：
语法检查，如检查关键字from&&2.语义检查，如检查有无对象及权限&&3.生成执行计划&&4.执行sql
生成sql执行计划的过程十分消耗资源，称为硬解析。share pool中找到了sqlhash值的执行计划则为软解析。
典型的绑定变量使用与未使用：
for i in 1..10000 loop
execute immediate 'select * from t where object_id=:i'
for i in 1..10000 loop
execute immediate 'select * from t where object_id='||i;
绑定变量的原理：oracle只需要硬解析最早的一条sql，后续的sql都可以使用第一条sql的执行计划，只在sql执行的时候，oracle使用实际谓词来替换变量，从而省去了很耗资源的硬分析过程。OLTP与OLAP很多时候是相反的，比如OLTP要求db buffer的命中率越高越好，而OLAP却不需要，实际上OLAP是不大可能db buffer 命中率很高的，OLTP需要绑定变量，而OLAP却不应该使用绑定变量。bind peaking——绑定变量窥视或绑定变量窥探，是指sql在做硬分析时，oracle看一下当前sql谓词的值，以便生成最佳执行计划。只在硬分析时发生，之后不再发生。
exec :n:=7788;
select * from emp where empno=:n;& &测试sql跟踪中如果有以下则证明是硬分析：Misses in liarbry cache during parse:1&&--1表示硬解析,0为软解析
sql跟踪和10046事件
sql跟踪将sql执行的过程输出到一个trace文件中，可以通过阅读这个trace文件来了解sql执行过程中Oracle究竟做了哪些事情。trace文件默认保存于udump下（11g已经没有udump目录，所有跟踪文件位于trace目录下）。
alter system set sql_trace=可以对所有sql做跟踪，但这种方式代价大，跟踪sql太多。
一般为：alter session set tracefile_identifier= 'mytrace';& & --指定跟踪文件名，没用！！！
& && &&&alter session set sql_trace=& &--开始跟踪
& && &&&alter session set sql_trace=& &--结束跟踪&&也可以通过结束会话来结束跟踪，如退出sqlplus
通过tkprof来处理跟踪文件，如tkprof&&orcl_ora_8569.trc&&trace.txt
tkprof参数：1.explain=username/password 输入sql的执行计划和执行路径，如果不使用则只显示执行路径
2.sys=yes/no 跟踪文件中是否显示sys用户的操作，默认为yes，实际上设置为no，跟踪文件更具有可读性。
3.aggregate=yes/no&&默认yes，tkprof工具将所有相同的sql在文件中合并，设置为no，则列出每条sql信息。
查看跟踪报告：纵向为：每条sql包括三个步骤：parse分析、execute执行、fetch数据提取。
横向为：count 表示当前操作被执行了多少次。cpu表示当前操作消耗cpu的时间（单位秒）
elapsed 当前操作用时多少（包括cpu时间和等待时间）
disk 当前操作的物理读，磁盘io次数
query 当前操作一致性读的数据块数，通常是查询
current 修改数据的块数
rows 当前处理的数据行数
P265&&select t1.* from t1,emp where t1.object_id=emp.
tkprof处理过的trace文件，统计了sql在运行过程中的各种资源消耗，这对于分析性能有问题的sql很重要。但是这个报告对于只是一个汇总的结果集，如果想确切知道sql每一步的执行是如何操作的，就需要分析原始的trace文件，这个trace文件尽管没有使用tkprof工具处理后易读，但是能够确切的知道sql在哪个点在干什么。10046事件 分为4个级别：level 1等同于sql_trace的功能& & level4在level1基础上增加绑定变量信息
level 8 为在level1基础上增加等待事件信息，level12等同于level4+level8，同时收集绑定变量和等待事件信息。举例如下：
开始跟踪：alter session set events '10046 trace name context forever,level 12';
结束跟踪：alter session set events '10046 trace name context off';
level4可以获取绑定变量的信息，但是绑定变量值只能在原始的trace文件中获取，tkprof之后的文件则看不到。
level8 中有每个操作的等待事件的信息对其他会话做跟踪：
select sid,serial# from v$session where sid=158;
①sql_trace
exec dbms_system.set_sql_in_session(158,1368,true); --开始对158会话进行跟踪
execute dbms_system.set_sql_in_session(158,1368,false); --停止对158会话进行跟踪
②10046事件
exec dbms_monitor.session_trace_enable(158,1368,waits=&true,binds=&true); --开始跟踪
exec dbms_monitor.session_trace_ disable(158,1368);& &--停止跟踪
SQL& create table t1 as select * from dba_
SQL& create index idx_id on t1(object_id);
SQL& alter session set sql_trace=
SQL& select t1.* from t1,emp where t1.object_id=emp.
SQL& alter session set sql_trace=
tkprof orcl_ora_8569.trc 8569.txt sys=no explain=scott/tiger aggregate=no
当我们看到sql执行计划时，并不知道CBO为什么这么做，特别是执行计划明显失真时。通过10053事件可以可以知道CBO指定执行计划的过程演示给我们看，让我们窥视到这里面究竟发生了什么。用法如下：
alter session set events '10053 trace name context forever,level 1';&&--开始跟踪
alter session set events '10053 trace name context off';& & --停止跟踪
10053事件同样会在udump下产生trace文件，但这个跟踪文件不能用rkprof处理，直接阅读即可。
从Predicate Move-Around（PM）开始进入10053的信息部分
Clustering Factor——索引聚合因子，该值越小越有利于索引使用。
CBO计算每个对象单独访问的代价，通过比较所有的数据访问代价，选出代价最小的一种访问方式。
Card Original：50800 表示实际行数&&Card Rounded：96通过条件过滤，预计返回的记录数CBO一定是选择代价最低的数据访问路径作为sql的执行计划，如果觉得执行计划不正确，就去分析每一个数据访问方式，看我们提供的分析信息是否真实。10053实际上远没有10046适用范围广，分析sql主要还是看sql执行计划和sql_trace信息。对于某条sql的执行计划弄不清楚为什么是这样时，才会做一个10053事件，就可以找到问题答案。
金牌会员, 积分 2610, 距离下一级还需 390 积分
论坛徽章:14
不错，感谢分享。
注册会员, 积分 140, 距离下一级还需 60 积分
论坛徽章:0
不错的文章，一定要赞一下
高级会员, 积分 795, 距离下一级还需 205 积分
论坛徽章:2
不错， 一起学习了下}