Global temporary table之统计信息

程序执行的过程中报临时表空间不足，先前通过在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/454477

IBM 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以上是支持的。