只要使用过oracle的用户都知道dual这个特殊的表。这个表很奇怪，说它有记录，但是可以查询出根本不属于该表的数据，比如当前日期等；说它没记录，但是这个表里确实是有数据的。下面就对该表做一个深入的讨论，并对此而引出的性能问题做一个探讨。
1. dual表的描述和测试
根据oracle的官方文档对于dual表的解释，可以知道，dual表是一个实际的表，是一个属于数据字典
的表，它与其他的数据字典表没有本质上的区别。它只有一个列：dummy，类型为varchar2(1)。该表主要是为了在写应用程序的过程中，使得获取oracle函数返回值、oracle常量值、oracle伪列、oracle表达式或者oracle的序列号值的方式能够与一般的select数据的方式一样，而存在的。在该过程中，dual表作为查询的“伪表”而出现。
我们看看数据字典里的dual到底是什么样的表。
SQL> select owner,object_name,object_type from dba_objects where object_name='DUAL';
OWNEROBJECT_NAM OBJECT_TYPE
---------- ---------- ----------
SYSDUALTABLE
PUBLICDUALSYNONYM
可以看到，我们日常使用的dual实际是一个公共的同名词，可以供所有schema使用。该同名词指向的是属于sys用户的dual表。我们再来看看dual表的定义信息。
SQL> desc dual;
名称是否为空?类型
----------------------------------------- -------- ----------------------------
DUMMYVARCHAR2(1)
然后，我们根据oracle对dual表的说明，来做几个测试。注意，以下所做的测试必须在测试环境中进行，永远不要在生产库上进行。否则很有可能引起数据库的崩溃。
SQL> select user from dual;--获取oracle的全局变量
USER
------------------------------
MCS
SQL> select 'abc' from dual;--获取常量
'ABC'
-----
Abc
SQL> select 1+2 from dual;--获取表达式
1+2
----------
3
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获取oracle函数
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2006-12-27 11:54:22
从上面的测试，可以看出，dual在获取这些并没有实际存在于表里的数据时，起到了一个“伪表”的作用。其目的就是能够让这些数据能够像正常存在于表里的数据一样的形式展现出来。
我们来实际看看dual表里到底存放了什么数据。
SQL> select * from dual;
DUMMY
-----
X
可以看到，dual表里只存放了一条记录。于是，我们对该表做一些insert。
SQL> insert into dual values ('Y');
SQL> insert into dual values ('Z');
SQL> commit;
SQL> select count(*) from dual;
COUNT(*)
----------
3
SQL> select * from dual;
DU
--
X
可以看到，实际上dual表里确实有3条记录，但是检索该表时只能返回第一条记录。我们把这个时候的dual表所在的数据块转储出来，看看里面到底含有哪些数据。
SQL> select file_id,block_id from dba_extents where segment_name='DUAL';
FILE_IDBLOCK_ID
---------- ----------
11617
SQL> alter system dump datafile 1 block 1618;--第一个block是段头，所以第二个block含有数据
System altered
我们打开生成的转储文件，找到存放实际数据的部分，可以看到类似下面的数据：
tab 0, row 0, @0x1f9b
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f96
tl: 5 fb: --H-FL-- lb: 0x0cc: 1
col0: [ 1]59
tab 0, row 2, @0x1f91
tl: 5 fb: --H-FL-- lb: 0x0cc: 1
col0: [ 1]5a
tab 0, row 3, @0x1f8c
tl: 5 fb: --H-FL-- lb: 0x2cc: 1
col0: [ 1]58
可以很明显的看到，dual表里确实存放了3条记录，分别是59、58和5a。我们把这三条记录转化为字符，如下所示。
SQL> select chr(to_number('58','xx')),chr(to_number('59','xx')),chr(to_number('5a','xx')) from dual;
CHR(TO CHR(TO CHR(TO
------ ------ ------
XYZ
可以看到，这3条记录正是原来就存在的’X’和我们前面插入的’Y’、’Z’。所以我们可以很肯定的说，oracle在处理含有dual的语句时，内部一定做了某些特殊的转换，以尽量保证最多只能返回一条记录。而这个转换，是在SQL*PLUS里完成的。实际上，如果我们不使用SQL*PLUS，而使用其他的工具，比如pl/sql developer或者toad，我们就会发现，检索dual表的数据将返回3条记录。这时，如果该SQL语句位于存储过程中，则会返回错误。
SQL> create or replace procedure p_test_dual
2as
3ls_dummy dual.dummy%type;
4begin
5select dummy into ls_dummy from dual;
6dbms_output.put_line('the values in dual are : ' || ls_dummy);
7end p_test_dual;
8/
过程已创建。
SQL> select * from dual;
D
-
X
SQL> exec p_test_dual;
the values in dual are : X
SQL> insert into dual values('Y');
SQL> commit;
SQL> select * from dual;
D
-
X
SQL> exec p_test_dual;
BEGIN p_test_dual; END;
*
ERROR位于第1行:
ORA-01422:实际返回的行数超出请求的行数
ORA-06512:在"SYS.P_TEST_DUAL", line 5
ORA-06512:在line 1
可以看到，这个时候，即使是在SQL*PLUS下执行该存储过程，也会发生实际返回的行数超出请求的行数这样的错误，这就是因为dual表里存放了两条记录的原因。从这里也可以证明，当dual表里含有多条记录时，如果通过SQL*PLUS检索dual表的时候，SQL*PLUS会做些特殊的处理，从而只返回一条记录，我估计SQL*PLUS在把带有dual表的SQL语句传给oracle优化器时，会自动添加rownum=1的过滤条件，而其他工具则不会自动添加rownum=1的条件。
我们再来试着删除该表里的记录，看看会发生什么。
SQL> delete dual where dummy='Z';
已删除1行。
SQL> commit;
提交完成。
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> select * from dual;
D
-
X
SQL> delete dual;
已删除1行。
SQL> commit;
提交完成。
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> select * from dual;
D
-
Y
SQL> delete dual;
已删除1行。
SQL> commit;
提交完成。
SQL> select * from dual;
未选定行
SQL> select sysdate from dual;
未选定行
可以看到，在删除了’Z’以后的dual的表现还是可以理解的，也就是count(*)返回2条记录，而检索dual表时只返回第一条记录，也就是’X’。但是当我们在不带任何条件删除dual表时，并没有将dual表里所有的记录删除，而只是删除了dual表里的第一条记录，也就是’X’。第二次不带任何条件删除dual表时才将dual表的记录全部删除。这个时候，就不能正常使用dual表了，我们可以看到，查询当前日期已经不能返回记录了。
我们还可以看到在删除dual表的时候，不论在SQL*PLUS里还是在其他诸如pl/sql developer等工具里，一次都只能删除一条记录，也就是说，oracle优化器在解析delete dual的时候，总是会自动添加rownum=1的条件。
实际上，在dual的记录都被删除的情况下，oracle不能对其进行特殊处理从而使其仍然能够返回1条记录。这个时候，我们只有再次手工插入一条记录才能够使dual表恢复正常。
SQL> insert into dual values('X');
SQL> commit;
SQL> select sysdate from dual;
SYSDATE
----------
27-12月-06
2. dual表的性能优化
在基于oracle数据库的应用程序里，不可避免的会大量用到dual表。比如获得当前时间、获得当前
用户等等，都会用到dual表。但是有很多人都忽视了对于dual表的性能问题的研究。实际上，正是因为dual表的应用非常广泛而且频繁，才更需要关注这个表所带来的性能影响。
我们先来看一个statspack报表，这个报表是从真实的生产环境中摘录出来的，时间跨度是从早上6点半到早上8点半。节选内容如下：
CPUElapsd
Buffer GetsExecutionsGets per Exec%Total Time (s)Time (s) Hash Value
--------------------------- -------------------- -------- --------- ----------
6,007,3532,000,5463.01.1192.13171.781817891629
Module: JDBC Thin Client
SELECT user from sys.dual
这段内容出现在报表中最消耗buffer gets的部分里，可以看到就这么一句简单的SQL，虽然每次只消
耗了3个buffers，但是由于执行非常频繁，所以导致最后总共消耗了六百多万个buffers。
我们来仔细研究一下这样的一句SQL语句，看看其执行计划。注意，我们先把dual表进行了缓存。
SQL> select user from dual;
SQL> set autotrace traceonly exp stat;
SQL> select user from dual;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT ptimizer=CHOOSE
10TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
3consistent gets
0physical reads
0redo size
373bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
可以看到该语句对dual表进行了全表扫描，导致访问3个buffers。而实际上，这个dual表只有1个数据块，而且我们在显示执行计划之前，已经执行了一遍该SQL，理想中应该已经将dual表的记录缓存了。但是第二次执行时，还是显示访问了3个buffers。实际上，不管你执行多少遍该SQL语句，只要是涉及到dual，就一定访问3个buffers。很明显，这3个buffers中有2个是对数据字典的访问，也就是说每次访问dual表，就必然要访问一次其他相关的数据字典表。即便你将sys.dual表设置到keep类型的buffer pool里，按照我的测试，仍然会导致3个buffer gets。
SQL> alter system set db_keep_cache_size=3M;
SQL> alter table dual cache storage(buffer_pool keep);
SQL> select user from dual;
SQL> select user from dual;
SQL> set autotrace traceonly exp stat;
SQL> select user from dual;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT ptimizer=CHOOSE
10TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
3consistent gets
0physical reads
0redo size
373bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
经过反复测试，都不能将consistent gets降低。所以通过将dual表放入keep池不能降低它的buffer gets。
我们已经知道dual表只是一个“伪表”，也就是说是一个假的表，该表唯一特殊的地方就是最多只能返回一条记录。既然是这样，我们完全可以想一个办法创建一个我们自己的“伪表”，然后让该“伪表”只含有一条记录，同时人为控制不能向该“伪表”插入多于一条的记录即可。
SQL> create table mydual
2(dummyvarchar2(1) null,
3constraint pk_mydual primary key (dummy)
4)
5organization index;
SQL> insert into mydual values('X');
SQL> commit;
SQL> execute dbms_stats.gather_table_stats(user,'mydual');
SQL> set autotrace traceonly exp stat;
SQL> select user from mydual;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1)
10INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
1consistent gets
0physical reads
0redo size
376bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
我们可以看到，通过创建mydual表，来模拟dual表，从而使得consistent gets从3降低到了1，这1个buffer就是mydual表所处的数据块。这样，我们可以在sys用户下创建mydual表，然后将公共同名词dual按照下面的方式，从表sys.dual转向sys.mydual。从而，实现不用改变现有应用程序的前提下，将涉及到dual表的SQL语句所访问的buffer gets降低到最低。
SQL> create or replace public synonym dual for mydual;
在9i下，还可以有一个不是很常用的方法，能够将buffer gets降低到0。但是这个方法需要访问X$视图，因此对于没有sysdba权限的用户来说，无法显示其执行计划。
SQL> create view bestdual as select * from x$dual;
SQL> create or replace public synonym dual for sys.bestdual;
SQL> grant select on bestdual to public;
在sys用户下，来测试对于bestdual的执行计划。
SQL> select user from bestdual;
SQL> select user from bestdual;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT ptimizer=CHOOSE
10FIXED TABLE (FULL) OF 'X$DUAL'
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
0consistent gets
0physical reads
0redo size
373bytes sent via SQL*Net to client
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13515953/viewspace-592113/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13515953/viewspace-592113/
980
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
