同事在对一个表进行merge的时候,因为网络问题导致执行中断,后杀掉表锁进程后select count 非常慢,经查询表空间都正常,也没有表锁,索引也都正常,重启数据库服务后还是查询不出结果,备份表一切正常,咨询同事说可能是表“坏”了,让analyze 一下。然后执行
analyze table table_name compute statistics;执行后正常,但是执行了20多分钟,表数量37W,十几个字段。我是这么理解的不知道对不对,应该是merge异常中断,导致执行计划异常,所以执行的时候不按照正常执行计划分配资源,导致查询不出来,然后重新分析表后修正了执行计划,所以后续就能正常执行了
原因分析来自analyze table的一点用处的类似问题:
今天晚上系统突然出现客户端(BS架构)无法导出数据的问题,并且系统整体运行缓慢。查看服务器,cpu消耗严重,通过top查看到多个oracle(local=no)
的进程正消耗系统资源。通过spid查看对应的sid中正在执行的sql语句,发现全是导出数据用的中间表。查看v$session_wait视图,发现大量等待undo 全局数据的
latch。正是latch竞争导致系统资源严重消耗,且造成数据无法正常导出。
将典型语句(非常简单的语句不过表数据量大2000w左右)复制出来查看执行计划。发现问题:通过联合主键中一个字段等值查询的语句,一般情况应该是走
索引范围扫描才对,但是执行计划确实全索引快速扫描。由此推断可能是数据统计出现了问题。问题表因为是一个中间导数据的表,每天很多数据插入,删除操
作,并且每天定时有截断,这样就很容易出现异常的执行计划,并且系统使用了绑定变量,一次解析后,执行计划就会固定下来,不在改变。
解决:重新对表进行分析,收集当下的数据分布。
第一次使用dbms包进行分析,分析完后通过开发工具连接上的会话执行的计划已经正常,并且很快的可以执行。但是系统资源依然没有释放,并且用户反映
依旧无法导出。查看v$sql_plan表中对应的执行计划,发现该语句依然走的是全索引扫描!虽然对表进行了分析可以由于使用了绑定变量,并不会影响系统原来分
析好的执行计划。 网上查修改执行计划的方法,确实不少:使用sql profile,刷共享池,这些不太敢用, 10.2.4还提供可清空单个对象共享池信息的包,但是自己
的版本太低。后来看到analyze表时也会清空对应的共享池中的关于对象的信息。马上执行了一下analyze table xxx compute statistics;查看v$sql和v$sql_plan果
然关于改对象的信息全清空了。马上导出试试,再看v#sql_plan,执行计划已经正常了,自此系统资源马上释放了,并且可以正常的导出数据。
从网上找到一段ORACLE的analyze使用简介参考部分:
ORACLE数据库的PL/SQL语句执行的优化器,有基于代价的优化器(CBO)和基于规则的优化器(RBO)。
RBO的优化方式,依赖于一套严格的语法规则,只要按照规则写出的语句,不管数据表和索引的内容是否发生变化,不会影响PL/SQL语句的"执行计划"。CBO自
ORACLE7版被引入,ORACLE自7版以来采用的许多新技术都是只基于CBO的,如星型连接排列查询,哈希连接查询,反向索引,索引表,分区表和并行查询等。
CBO计算各种可能"执行计划"的"代价",即cost,从中选用cost最低的方案,作为实际运行方案。各"执行计划"的cost的计算根据,依赖于数据表中数据的统计分
布,ORACLE数据库本身对该统计分布是不清楚的,须要分析表和相关的索引,才能搜集到CBO所需的数据。 CBO是ORACLE推荐使用的优化方式,要想使用好
CBO,使SQL语句发挥最大效能,必须保证统计数据的及时性。
统计信息的生成可以有完全计算法和抽样估算法。SQL例句如下:
完全计算法: analyze table abc compute statistics;
抽样估算法(抽样20%): analyze table abc estimate statistics sample 20 percent;
对表作完全计算所花的时间相当于做全表扫描,抽样估算法由于采用抽样,比完全计算法的生成统计速度要快,如果不是要求要有精确数据的话,尽量采用抽样
分析法。建议对表分析采用抽样估算,对索引分析可以采用完全计算。 我们可以,对数据库的表和索引及簇表定期分析生成统计信息,保证应 用的正常性能。