引言
语句性能问题分析与解决是DBA日常维护的主要职责之一，大部分的性能问题与统计信息的准确与否有关。而针对常规表由于数据量、变化规律等相对固定，可以综合运用系统定时任务、特殊表分析策略等相结合的方案得到较好解决。针对包括临时表等在内的特殊对象，会对SQL执行计划造成哪些影响，如何有针对性的开展优化，生产就碰到一个典型问题。
一、问题出现
11月3号11：00，应用反应某批量程序缓慢，分别在凌晨00：00，4：00，8：00吊起的批量还没有跑结束，该程序为日常批量程序功能用于生成报表，平时几分钟就能产生报表。
二、定位迷雾
作为一个dba，很容易想到可能是语句的执行计划发生了改变。通过awr报告，得到如下语句的执行计划发生改变：
语句如下：
INSERT INTOFEDW.TMP_BD_KPI_MEASURE_DATA (POLYMER_CODE, KPI_SET_CODE,
KPI_CODE, DATA_DATE, ORGAN_CODE,CURRENCY_TYPE, MEASURE_TYPE,
DATA_VALUE) SELECT MAX(POLYMER_CODE)AS POLYMER_CODE, KPI_SET_CODE,
KPI_CODE, DATA_DATE, ORGAN_CODE,CURRENCY_TYPE, MEASURE_TYPE,
SUM(DATA_VALUE) FROM ( SELECTD.POLYMER_CODE, D.KPI_SET_CODE,
D.KPI_CODE, D.DATA_DATE, O.SUP_ORGANAS ORGAN_CODE, D.CURRENCY_TYPE,
D.MEASURE_TYPE, D.DATA_VALUE FROMFEDW.TMP_BD_KPI_MEASURE_DATA D,
FEDW.PRM_TMP_ORGAN O,GSISDATA.PRM_KPI_DEFINE K WHERE D.ORGAN_CODE =
O.ORGAN_CODE AND O.IS_VIRTUAL='0'AND ORGAN_LEVEL = '5' AND
K.KPI_SET_CODE = D.KPI_SET_CODE ANDK.KPI_CODE = D.KPI_CODE AND
K.START_DATE <='20181101' ANDK.END_DATE>'20181101' AND
NVL(K.ORGAN_SUM_TYPE,'1') = '1' ANDEXISTS (SELECT 1 FROM
FEDW.PRM_KPI_MEASURE_CONFIG C WHEREC.KPI_SET_CODE = D.KPI_SET_CODE AND
C.KPI_CODE = D.KPI_CODE ANDC.KPI_MEASURE_TYPE = D.MEASURE_TYPE) )X
GROUP BY KPI_SET_CODE, KPI_CODE,DATA_DATE, ORGAN_CODE, CURRENCY_TYPE,
MEASURE_TYPE
改变前较好的执行计划：
改变后较差的执行计划：
原来较优的执行计划走的hash join，改变后较差的执行计划走的nestloop。再看语句，涉及到两表（PRM_KPI_DEFINE，TMP_BD_KPI_MEASURE_DATA）关联的where条件都都是等值链接。这种情况下执行计划应该走hashjoin才对，cbo选择走nestloop应该是觉得某张表为空。查看两张表的统计信息后发现统计信息均为空，且两张表均为全局临时表。那么下面就是搜集两张临时表的统计信息问题应该就可以解决。
三、知识拓展
全局临时表的统计信息搜集跟一般的表是有区别的。全局临时表被用在那些结果集被临时保存的情况下。临时表的定义是所有session共享的，但是临时表中的数据是session独立的，换句话说只有往临时表中插入过数据的session才能看到临时表中的数据。由于临时表的会话性质，系统表分析作业是拿不到统计信息的，所以显示为0。全局临时表分为基于事务型和基于会话型。基于事务型全局临时表，在每一次commit后会由系统truncate掉临时表。基于会话型全局临时表，在每一次会话退出后由系统truncate掉临时表。
全局临时表上也可以创建索引，索引中的数据也分为事务型和会话型。和一般的表不同，临时表以及它们的索引在创建时是不分配段空间的，当在第一次执行插入数据的时候分配段空间。临时表中的数据是不能备份和恢复的，所以我们在使用临时表时一定要非常谨慎明确场景。下面举例说明临时表的统计信息搜集。
基于事务的全局临时表。
创建临时表TA。
Create global temporary table TA1(idint,name varchar(20)) on commit delete rows;
搜集统计信息
execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA1');
可以看到统计信息为0.
SQL> select num_rows fromdba_tab_statistics where table_name='TA';
NUM_ROWS
----------
0
往临时表中插入两行数据
SQL> insert into TA1 values(1,'bo');
1 row created.
SQL> insert into TA1 values(2,'yang');
1 row created.
再次搜集统计信息
SQL> eexecute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA');
查看临时表统计信息，可以看到统计信息仍为0：
SQL> select num_rows from dba_tab_statisticswhere table_name='TA';
NUM_ROWS
----------
0
结论：基于commit的临时表无法搜集统计信息。
再来看基于session的临时表。
Create global temporary table TA2(idint,name varchar(20)) on commit preserve rows;
搜集统计信息：
execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA2');
SQL> select num_rows from dba_tab_statistics where table_name='TA2';
NUM_ROWS
----------
0
插入两行数据
SQL> insert into TA2 values(1,'bo');
1 row created.
SQL> insert into TA2 values(2,'yang');
1 row created.
Commit；
SQL> select * from TA2;
ID NAME
--------------------------------------------------
1 bo
2 yang
继续搜集统计信息：
SQL> select num_rows from dba_tab_statistics where table_name='TA2';
NUM_ROWS
----------
2
打开另一个窗口查看统计信息：
SQL> select num_rows fromdba_tab_statistics where table_name='TA2';
NUM_ROWS
----------
2
统计信息搜集有效。
在新窗口中再插入2行数据：
SQL> insert into TA values(1,'bo');
1 row created.
SQL> insert into TA values(2,'yang');
回到原窗口中搜集统计信息：
execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA2');
查看统计信息是否被更新：
SQL> select num_rows fromdba_tab_statistics where table_name='TA2';
NUM_ROWS
----------
2
可以看到，这里的num_rows并没有更新为4，为了应征这点我们回到新窗口中再次搜集统计信息：
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA2');
PL/SQL procedure successfully completed.
SQL> select num_rows fromdba_tab_statistics where table_name='TA2';
NUM_ROWS
----------
4
这里可以看到，统计信息被更新为了4，此时再回到原来窗口查看统计信息,可以看到num_rows也被更新为4.
SQL> select num_rows from dba_tab_statisticswhere table_name='TA2';
NUM_ROWS
----------
4
四、问题解决
好了回到这次生产问题，在查看了表定义后发现这次统计信息改变的临时表为基于会话的临时表。下一步就是搜集这两张表的统计信息，由于全局临时表的会话独立性，我们必须在同一个session中完成如下步骤。
1.向临时表中插入数据
2.搜集临时表统计信息
3.锁定临时表统计信息
制定好方案后就开始实施。具体步骤如下：
使用oracle用户执行以下内容，对临时表做统计分析（需要在一个会话事务中）：
DECLARE
BEGIN
--1.插入数据
INSERTINTO FEDW.PRM_TMP_ORGAN
(ORGAN_CODE,
SUP_ORGAN,
ORGAN_LEVEL,
SUP_ORGAN_STR,
IS_VIRTUAL,
ORGAN_CODE1,
ORGAN_CODE2,
ORGAN_CODE3,
ORGAN_CODE4,
ORGAN_CODE5,
ORGAN_CODE6,
ORGAN_CODE7,
ORGAN_CODE8,
ORGAN_CODE9,
ORGAN_CODE10,
ORGAN_CODE11,
ORGAN_CODE12,
ORGAN_CODE13,
ORGAN_CODE14,
ORGAN_CODE15,
ORGAN_CODE16,
ORGAN_CODE17,
ORGAN_CODE18,
ORGAN_CODE19,
ORGAN_CODE20)
SELECT
ORGAN_CODE,
SUP_ORGAN,
ORGAN_LEVEL,
SUP_ORGAN_STR,
IS_VIRTUAL,
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 1),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 2),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 3),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 4),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 5),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 6),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 7),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 8),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 9),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 10),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 11),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 12),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 13),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 14),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 15),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 16),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 17),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 18),
FEDW.fun_get_sup_organ(SUP_ORGAN_STR,',',10, 19),
VIRTUAL_FLAG AS organ_code20
FROM(SELECT A.ORGAN_CODE,SUP_ORGAN,LEVELORGAN_LEVEL,
LTRIM(SYS_CONNECT_BY_PATH(SUP_ORGAN,','), ',') SUP_ORGAN_STR,
'0' IS_VIRTUAL,'0' VIRTUAL_FLAG
FROM (SELECT ORGAN_CLASS,ORGAN_CODE,SUP_ORGAN
FROM GSISDATA.V_PRM_ORGAN_SUM
WHEREGSISDATA.PACK_VIEW_PARAM.SETSTRVALUE1('20181031') = '20181031') A
START WITH A.ORGAN_CODE = '0010100000'
CONNECT BY A.SUP_ORGAN = PRIOR A.ORGAN_CODE
UNION ALL
SELECT DISTINCTC.ORGAN_CODE,SUP_ORGAN,LEVEL ORGAN_LEVEL,
LTRIM(SYS_CONNECT_BY_PATH(SUP_ORGAN, ','),',') SUP_ORGAN_STR,
'1' IS_VIRTUAL,IS_VIRTUAL AS VIRTUAL_FLAG
FROM (SELECT ORGAN_CLASS,ORGAN_CODE,SUP_ORGAN,IS_VIRTUAL
FROM FEDW.PRM_ORGAN_VIRTUAL
WHERE START_DATE<='20181031' ANDEND_DATE>'20181031') C
START WITH C.ORGAN_CODE IN (SELECTORGAN_CODE FROM FEDW.PRM_ORGAN_VIRTUAL WHERE ORGAN_LEVEL='1'
AND START_DATE<='20181031'AND END_DATE>'20181031')
CONNECT BY C.SUP_ORGAN = PRIORC.ORGAN_CODE);
--2.表分析
executedbms_stats.gather_table_stats('FEDW','PRM_TMP_ORGAN');
--3.锁定表分析
executedbms_stats.lock_table_stats('FEDW','PRM_TMP_ORGAN');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
完成后使用explain plan for方式查看语句执行计划恢复正常，通知应用进行验证。该语句之前10小时未完成，在我们固定完临时表统计信息后4分钟不到就跑出了结果。
六、小结
本次生产事件是由临时表引起的典型问题。临时表跟一般表由于设计初衷不同，使用方式和场景也不一样。生产中建议将临时表用作简单查询，不做复杂关联。同时对数据量变化较大的临时表，统计信息宜提前搜集固定。除了统计信息不准确会导致执行计划的改变外，表结构的更改（索引失效），数据量增大等原因也会造成执行计划的改变。对于影响执行计划的因素（对象）平时应该有效的管理起来，对于生产上自动搜集统计信息的任务，索引等需要有监控确保统计信息搜集成功，索引有效。针对一些特殊对象的处理应该上升到技术规范，比如修改表结构后需要重建索引，备机切主机后需要重启数据库等等。
