Re: Select from dual return 3 rows !

From: Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk>
Date: Wed, 09 Nov 2005 10:07:10 +0000
Message-Id: <s371ca58.039@environment-agency.gov.uk>

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

Received on Wed Nov 09 2005 - 04:11:42 CST