ver: 11.2.0.4
Why is a table access full estimated by optimizer to be long time but actual run time significantly less.
About 500k rows in table. select is going to take back about 98k
Table pk is on event_Date
Partitioned by a record_id not in the predicates.
Takes 2 seconds to run as expected but whilst lookng for something else I noticed Plan says it will take 5 min 58. Why is this if the Full table scan only takes 2 seconds at actual runtime? stats are all good.
set autotrace traceonly
select * from t1
where event_date=(select trunc(sysdate)-1 from dual)
and account = 'xxxxxx'
98184 rows selected.
Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3222437112
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98868 | 9075K| 29756 (1)| 00:05:58 | | |
| 1 | PARTITION HASH ALL| | 98868 | 9075K| 29754 (1)| 00:05:58 | 1 | 32 |
|* 2 | TABLE ACCESS FULL| T1 | 98868 | 9075K| 29754 (1)| 00:05:58 | 1 | 32 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."ACCOUNT"='xxxxxxxx' AND "A"."event_Date"= (SELECT TRUNC(SYSDATE@!)-1 FROM
"SYS"."DUAL" "DUAL"))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
102895 consistent gets
0 physical reads
0 redo size
7823983 bytes sent via SQL*Net to client
72471 bytes received via SQL*Net from client
6547 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98184 rows processed