Oracle 12.1.0.2
I found a query which was doing multiple full scans on a small (100 row) table in a nested loop join, despite there being a suitable unique index in place. The reason for this is that the optimizer calculated the cost of a single full scan as 0.77, which is cheaper than an index scan, so it chose this plan. However, in practice, with an outer table containing a large number of rows (a million rows for example), a million full scans resulted in 7,000,000 logical I/Os, and took 10s, whereas a million index unique scans resulted in 5 logical I/Os, and took 1s.
This raises two questions :
- Why would the optimizer consider a cost of less than 1. Given the concept of cost, surely this is meaningless, and likely to skew these kind of queries in favour of multiple full scans.
- Why is the reported logical I/O for the index scan so low? Surely, a million index scans would require at least a million logical I/Os?
Full details and test case below.
System Stats
IOSEEKTIM = 12
IOTFRSPEED = 19644
MBRC = 8 (not set, assumed to default to this)
Block size = 8KB
Table Stats
Blocks = 5
Optimizer calculation of full scan based on noworkload stats
SB Read Time = IOSEEKTIM + Block Size / IOTFRSPEED
MB Read Time = IOSEEKTIM + MBRC * Block Size / IOTFRSPEED
Cost = MB Read Time / SB Read Time * Blocks / MBRC
Cost = 0.772
Worked example
CREATE TABLE t1
( id NUMBER
, data VARCHAR2(1000)
);
INSERT INTO t1
SELECT level
, RPAD('X',200,'X')
FROM dual
CONNECT BY level <= 100;
exec DBMS_STATS.GATHER_TABLE_STATS(null,'T1')
CREATE UNIQUE INDEX ix1 ON t1 (id);
SET TIMING ON
SET AUTOTRACE ON
-- Query is hinted to ensure nested loop is used
-- but optimizer chooses not to use the index
WITH driver AS (
SELECT /*+ CARDINALITY(1000000) */ ora_hash(rownum,1e9) AS id
FROM dual
CONNECT BY level <= 1000000
)
SELECT /*+ LEADING(d t) USE_NL(t) */ COUNT(data)
FROM t1 t
JOIN driver d
ON d.id = t.id;
-- Forcing the optimizer to use the index to compare the performance
WITH driver AS (
SELECT /*+ CARDINALITY(1000000) */ ora_hash(rownum,1e9) AS id
FROM dual
CONNECT BY level <= 1000000
)
SELECT /*+ LEADING(d t) USE_NL(t) INDEX(t) */ COUNT(data)
FROM t1 t
JOIN driver d
ON d.id = t.id;
SET AUTOTRACE OFF
DROP TABLE t1 PURGE;
Autotrace output - full scan
COUNT(DATA)
-----------
1
Elapsed: 00:00:09.84
Execution Plan
----------------------------------------------------------
Plan hash value: 2974508220
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 775K (1)| 00:00:31 |
| 1 | SORT AGGREGATE | | 1 | 217 | | |
| 2 | NESTED LOOPS | | 1000K| 206M| 775K (1)| 00:00:31 |
| 3 | VIEW | | 1000K| 12M| 2 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL | T1 | 1 | 204 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(LEVEL<=1000000)
7 - filter("D"."ID"="T"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7000000 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Autotrace output - index unique scan
COUNT(DATA)
-----------
1
Elapsed: 00:00:01.21
Execution Plan
----------------------------------------------------------
Plan hash value: 27500795
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 1000K (1)| 00:00:40 |
| 1 | SORT AGGREGATE | | 1 | 217 | | |
| 2 | NESTED LOOPS | | 1000K| 206M| 1000K (1)| 00:00:40 |
| 3 | NESTED LOOPS | | 1000K| 206M| 1000K (1)| 00:00:40 |
| 4 | VIEW | | 1000K| 12M| 2 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING| | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | IX1 | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 204 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(LEVEL<=1000000)
8 - access("D"."ID"="T"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed