from OTN:

有的时候开发人员过多的从dual中"取数据"
会带来严重的性能问题

Oracle Database 10g: The Top 20 Features for DBAs

Use DUAL Freely

How many developers (and DBAs, too) do you think use this command often?

select USER into <some variable> from DUAL

Far too many, probably. Each call to the DUAL creates logical I/Os, which the database can do without. In some cases the call to DUAL is inevitable as in the line <somevariable> := USER. Because Oracle code treats DUAL as a special table, some ideas for tuning tables may not apply or be relevant.

Oracle Database 10g makes all that worry simply disappear: Because DUAL is a special table, the consistent gets are considerably reduced and the optimization plan is different as seen from the event 10046 trace.

set autotrace on

select user from dual;

In Oracle9i
============================
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (FULL) OF 'DUAL'
          

In 10g
============================
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   FAST DUAL

SQL> connect scott/tiger
Connected.
SQL> set autotrace on
SQL> select user from dual;

USER
------------------------------
SCOTT


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8 Card=8168)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=8 Card=8168)

对DUAL的全表扫瞄:TABLE ACCESS (FULL) OF 'DUAL'  


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets   //注意这里
          0  physical reads
          0  redo size
        378  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

SQL> connect scott/tiger
Connected.
SQL> set autotrace on
SQL> select user from dual;

USER
------------------------------
SCOTT


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)
   1    0   FAST DUAL (Cost=2 Card=1)

访问路径是 fast dual  cost 也很低



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets //难道oracle 对DUAL的开销都小到这个样子了 ?!
          0  physical reads
          0  redo size
        392  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

数据库都是'新鲜"启动的

1 recursive calls
难道没有影响吗？

QUOTE:

---

最初由 xzh2000 发布
1 recursive calls
难道没有影响吗？

---

那一般是第一次存在，以后就不存在的了

感觉dual真的变成纯虚表了。
可能现在的实现机制和sybase的select @a = 5类型？

1 recursive calls
调用什么？
sql_trace 看不到。