I'm trying to understand Oracle behaviour when performing a partition full scan with partition interval of 15 periods .
For the following example we've a partitioned table BIG_TBL with 15 periods interval. Each period represents a day. In this example the low value of partition is 4641 and included high value is 4655. If today is the period 2414, all rows inserted will have value 2414 for column named period. For tomorrow 2415 and so on.
For the low value and high value the number of physical reads and consistent gets is similar and it does not make sense to me. I expected to have more reads/gets when we run the full partition scan on the high value.
....
...
SQL> set lines 300
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT p.col1, p.col2 from BIG_TBL p where p.period = 4641 ;
753869 rows selected.
Elapsed: 00:00:36.07
Execution Plan
----------------------------------------------------------
Plan hash value: 4036287023
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 753K| 18M| 329K (1)| 00:00:13 | | |
| 1 | PARTITION RANGE SINGLE| | 753K| 18M| 329K (1)| 00:00:13 | 12 | 12 |
|* 2 | TABLE ACCESS FULL | BIG_TBL | 753K| 18M| 329K (1)| 00:00:13 | 12 | 12 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."period"=4641)
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
1259484 consistent gets
1213004 physical reads
0 redo size
26814509 bytes sent via SQL*Net to client
553379 bytes received via SQL*Net from client
50259 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
753869 rows processed
SQL> SELECT p.col1, p.col2 from BIG_TBL p where p.period = 4655 ;
1481907 rows selected.
Elapsed: 00:00:23.04
Execution Plan
----------------------------------------------------------
Plan hash value: 4036287023
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1481K| 36M| 329K (1)| 00:00:13 | | |
| 1 | PARTITION RANGE SINGLE| | 1481K| 36M| 329K (1)| 00:00:13 | 12 | 12 |
|* 2 | TABLE ACCESS FULL | BIG_TBL | 1481K| 36M| 329K (1)| 00:00:13 | 12 | 12 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."period"=4655)
Statistics
----------------------------------------------------------
87 recursive calls
0 db block gets
1304271 consistent gets
1213004 physical reads
0 redo size
49311382 bytes sent via SQL*Net to client
1087275 bytes received via SQL*Net from client
98795 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1481907 rows processed