程序执行的过程中报临时表空间不足,先前通过在session级别禁用系统参数/*+ OPT_PARAM('_optimizer_mjc_enabled','false') */ 解决了一步分。但是仔细想想后发现,基于cost选择执行计划,oracle不应该倾向于选择MERGE JOIN CARTESIAN。之后又通过在em中监控过程执行过程中sql语句的执行计划发现,查询中用到的临时表估计返回值都是1.这显然是不能容忍的,因为临时表在执行过程中有很多数据插入,如果按照返回值是1选择查询计划的话,对于整个查询将会照成巨大错误。对于我这个实例来看,造成的错误就是产生了笛卡尔乘机,表关联返回的结果集将100g的临时表空间撑满了。
由于临时表空默认在统计信息收集的时候是不进行统计的,我们可以手工添加统计信息进去,添加方法如下。
统计信息解除锁定
BEGIN dbms_stats.unlock_table_stats('DM','DM_MID_WO_DEV_D2');END;
添加统计信息
由于临时表在会话或者事务结束后表为空,使用dbms_stats和analys虽然能收集统计信息,但是收集的结果都是0,没有意义。
BEGIN dbms_stats.gather_table_stats('DM','DM_MID_WO_DEV_D2');END;
ANALYZE TABLE DM.DM_MID_WO_DEV_D2 COMPUTE STATISTICS;
根据经验手工添加统计信息进去
BEGIN dbms_stats.set_table_stats(ownname => 'DM',tabname => 'DM_MID_WO_DEV_D2',numrows =>10000 );END;
锁定统计信息
BEGIN dbms_stats.lock_table_stats('DM','DM_MID_WO_DEV_D2');END;
之后在查看的执行计划变成正常
参考博文
http://duqiangatom.blog.163.com/blog/static/1150251820115553219944/
Global temporary table之统计信息
临时表是:应用中需要存储一个行集由一个会话或者事务(不夸session)时使用.
1、临时表创建时不分配初始extent,只在运行时才在当前用户的temporary tablespace中分配存储空间。
2、DDL有昂贵的代价,一次创建临时表,为db中每个存储过程都使用。
3、由于优化器无法直接获得临时表的统计信息,故使用CBO时有如下几种方法:
a、动态采样dynamic_sample
j加hint,其中2代表level2,比9i默认级别取更多样本/*dynamic_sample(temporary_tab_name 2) */
b、exec dbms_stats.gather_schema_stats(user,gather_temp=>true);
on commit preserve rows有效;on commit delete rows型的临时表无效。
c、人工将统计信息写入并锁定:
exec dbms_stats.set_table_stats(ownname,tabname,numrows,numblks,avgrlen);
exec dbms_stats.lock_table_stats(ownname => ,tabname => ,stattype => );
http://logzgh.itpub.net/post/3185/454477IBM rpm系统(oracle global temporary table)的调优
我们公司有一套ibm rpm系统,运行在oracle数据库上,今天听说这套系统一直以来都很慢。让IBM调优,IBM也拿不出什么优化方案,说这套系统跑在db2上就很快。听到这儿我就觉的奇怪,肯定是哪方面出了问题。
从交流中得知这套系统有大量的temporary table。
登陆系统查看果然是这样,总共600多张表格,其中450来张表格是temporary table表格。ibm这个系统真是奇怪。^_^
查看v$session_wait发现direct path read/write有不少,于是猜测是执行计划不对。把sql抓出来查看执行计划,都是走hash join的。查看统计信息,发现都有统计信息,但是临时表格上面没有统计信息。
对这么多的临时表格,只有永久表有统计信息,那么当永久表和临时表之间有关联操作时,sql语句的执行计划非常容易出错。
于是先偿试将统计信息全部干掉,速度有所提升,但是还不理想。
后来我想rpm的这些临时表,估计在实际运行当中也不会存在大量的数据,不同的时候其中的数据相差应该也不会大,那么对临时表做统计信息分析的话,应该是有用的。
于是先用dbms_stat对整个schema做统计信息分析,但是发现临时表上面还是没有统计信息。后来改用analyze table后,统计信息就有了。说明dbms_stats无法对临时表做统计信息分析。
这样分析后,再登陆系统,整个系统的速度就非常快了。
总结一下有两个知识点:
1.dbms_stats不能对临时表做分析,如果要对临时表做分析,需要采用analyze table。
2.对有不少临时表的系统,如果采有cost的话,对临时表也要做分析,否则会带来性能极大的恶化。这里还需要注意8i临时表是不支持统计信息的,9i以上是支持的。