Hi TOM,
SQL>create table t_20_rows
as select object_name, object_id
from
all_objects
where
rownum < 21;
SQL> EXEC SHOW_SPACE('T_20_ROWS');
Free Blocks.............................0
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................451624
Last Used Block.........................2
PL/SQL procedure successfully completed.
8 BLOCKS ALLOTTED
2 USED BLOCKS
6 UNUSED BLOCKS
SQL> SHOW ARRAYSIZE
arraysize 15
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192Now, make SQL*Plus display statistics about SQL statements:
SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM T_20_ROWS;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 600 | 2 (0)|
| 1 | TABLE ACCESS FULL| T_20_ROWS | 20 | 600 | 2 (0)|
--------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
975 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processedTotal number of used blocks=2
Total number of rows=20
Array Size=15
If you full scan a table T that has N blocks and R rows and you use an array fetch size of A, we will typically perform the following number of consistent gets:
N + R/A
Anticipated that there will be only 3 consistent gets(2 + 20 ROWS/15 ARRAY SIZE = 3).
BUT THERE ARE 9 CONSISTENT GETS, PLEASE EXPLAIN.
Notice the top stat from autotrace:
4 recursive calls
That tells you something else is going on in the background. As this is the first time you've run the query, Oracle needs to parse it. This is where your extra work is coming from.
If you run the statement a second time, you should see the recursive calls go to zero and the consistent gets drop:
SQL> set array 15
SQL> create table t_20_rows
2 as select object_name, object_id
3 from
4 all_objects
5 where
6 rownum < 21;
Table created.
SQL> set autotrace trace
SQL> SELECT * FROM T_20_ROWS;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 387740663
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 600 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_20_ROWS | 20 | 600 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
13 consistent gets
1 physical reads
96 redo size
841 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL> /
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 387740663
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 600 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_20_ROWS | 20 | 600 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
841 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed