Oracle的临时表和统计信息的处理
- 临时表介绍
Oracle的临时表分为会话级和事务级,创建语句:
--会话级:
create global temporary table tmp_sess_dept on commit preserve rows as select * from dept ;
--事务级
create global temporary table tmp_tran_dept on commit delete rows as select * from dept
会话级的临时表,在一个会话期间内,表的数据都会存在。
事务级的临时表,当事务结束的时候表的数据会自动的删除。。
临时表数据存在于排序段,排序段是会话所专有的,每个会话是隔离的,每个会话只能见到自己的数据。
验证表是否为临时表,以及临时表的生命周期:
select table_name,LOGGING,TEMPORARY,DURATION from user_tables;
TMP_TRAN_DEPT NO Y SYS$TRANSACTION
TMP_SESS_DEPT NO Y SYS$SESSION
多个会话同时使用临时表的时候,会发现有多个排序段在活动。每个会话只是使用临时表在系统表空间中的定义,所以DROP的时候不会去回收站,而是直接从字典中删除。
验证有多少个用户在使用排序段(查看权限SELECT ANY DICTIONARY):
SELECT TABLESPACE_NAME,CURRENT_USERS FROM V$SORT_SEGMENT;
验证临时表:
insert into tmp_sess_dept select * from dept;
select * from tmp_sess_dept;
commit;
select * from tmp_sess_dept;
insert into tmp_tran_dept select * from dept;
select * from tmp_tran_dept;
commit;
select * from tmp_tran_dept;
- 临时表的统计信息处理
目的:
临时表是会话隔离的,收集临时表的统计信息是没有任何用处,还可能造成错误的执行计划。临时表的统计信息要删除,并且锁定临时表的统计信息,或者关闭数据库的自动收集统计信息任务,改成手动去收集。
数据库优化器模式
查看数据库的优化器模式:
SQL> show parameter mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
remote_dependencies_mode string TIMESTAMP
result_cache_mode string MANUAL
修改数据库的优化器选择模式:
alter system set optimizer_mode='CHOOSE' scope=both;
测试1:不同的session收集统计信息
建立一个session,对临时表插入数据;
新建一个session,收集临时表统计信息:
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_SESS_DEPT');
end;
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
查看临时表的统计信息:
select * from dba_tab_statistics a
where a.TABLE_NAME in ('TMP_SESS_DEPT','TMP_TRAN_DEPT');
结构显示统计项为0,因为临时表是会话隔离的,所以统计信息为0.
测试2:在一个session内收集统计信息
建立一个session,对临时表插入数据;
收集统计信息
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_SESS_DEPT');
dbms_stats.gather_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/
查看统计信息:
select *
from dba_tab_statistics a
where a.TABLE_NAME in ('TMP_SESS_DEPT', 'TMP_TRAN_DEPT');
查询结果显示会话级的临时表有相应的统计信息,事务级的临时表统计信息显示的行数为0,这是因为在收集统计信息的时候进行了事务的提交。
测试3:演示错误执行计划
用事务级的临时表进行测试,在3张表的关联,测试语句如下:
select *
from emp e, SALGRADE s, tmp_tran_dept d
where e.sal between s.losal and s.hisal
and e.deptno = d.deptno
查看产生的执行计划:
select a.SQL_TEXT,a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like &stat
select * from table(dbms_xplan.display_cursor(&sqlid,0));
在一个事务里使用事务级的临时表,执行计划显示产生了笛卡尔连接。
正常的执行计划为:
为了避免数据库引用错误的临时表统计信息:
- 建议:
begin
dbms_stats.delete_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/
begin
dbms_stats.lock_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/