Re: Select from dual return 3 rows !

Morning all,

just thought I'd throw this into the mix, seeing as no-one else has
yet. Skip to the end for the best bit - 10g FAST DUAL access.

In 8i, SELECT SYSDATE FROM DUAL results in this from autotrace (every
time it is called) :

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        434  bytes sent via SQL*Net to client
        436  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed


Selecting from USER_USERS gets this the first call :

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (FULL) OF 'USER_ASTATUS_MAP'
   7    5             TABLE ACCESS (CLUSTER) OF 'USER$'
   8    7               INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
   9    4           TABLE ACCESS (CLUSTER) OF 'TS$'
  10    9             INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  11    3         TABLE ACCESS (CLUSTER) OF 'TS$'
  12   11           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  13    2       TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE$'
  14   13         INDEX (RANGE SCAN) OF 'I_PROFILE' (NON-UNIQUE)
  15    1     TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE$'
  16   15       INDEX (RANGE SCAN) OF 'I_PROFILE' (NON-UNIQUE)




Statistics
----------------------------------------------------------
        475  recursive calls
          6  db block gets
        149  consistent gets
         17  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
         57  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed


and these stats on subsequent calls :

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         61  consistent gets
          0  physical reads
          0  redo size
        208  bytes sent via SQL*Net to client
        128  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Which looks much 'nastier'.  With 9i it looks like this for DUAL on all
calls (Stats only) :

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

Note only 3 consistent gets this time. Again, stats only for USER_USERS
I get this first time :

Statistics
----------------------------------------------------------
        250  recursive calls
          0  db block gets
        153  consistent gets
         15  physical reads
          0  redo size
        200  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed

and the following on subsequent attempts :

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         80  consistent gets
          0  physical reads
          0  redo size
        199  bytes sent via SQL*Net to client
        275  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


But the good news for 10g users is this, there is a new FAST DUAL
access path when selecting something from dual where that something is
not a column of dual. If you select * or DUMMY from dual, you won't get
fast dual access. Fast dual results in 0 (zero) logical I/Os which is
nice.

Unfortunately due to my laptop eating its own hard drive, I'm currently
without Oracle 10g and cannot test against USER_USERS but I suspect the
stats are somewhat similar to the above.

I'd say that with the new protected DUAL where you can't put extra rows
in and 10g's access path, DULA is still the way to go.

Cheers,
Norman.

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051


Information in this message may be confidential and may be legally privileged. 
If you have received this message by mistake, please notify the sender 
immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should 
still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under 
the Freedom of Information Act, Data Protection Act or for litigation.  Email 
messages and attachments sent to or from any Environment Agency address may 
also be accessed by someone other than the sender or recipient, for business 
purposes.

If we have sent you information and you wish to use it please read our terms 
and conditions which you can get by calling us on 08708 506 506.  Find out more 
about the Environment Agency at www.environment-agency.gov.uk
--
http://www.freelists.org/webpage/oracle-l

Other related posts: