Re: Select from dual return 3 rows !
- From: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <mag.andersen@xxxxxxxxx>, <ineyman@xxxxxxxxxxxxxx>
- Date: Wed, 09 Nov 2005 10:07:10 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
