SQL> create table t1 as select * from dba_objects;
SQL> create index i1_idx on t1(owner);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 't1',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
SQL> select * from t1 where owner='TEST';
已选择3328行。
执行计划
----------------------------------------------------------
Plan hash value: 1104552013
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K| 13M| 4536 (1)| 00:00:55 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 149K| 13M| 4536 (1)| 00:00:55 |
|* 2 | INDEX RANGE SCAN | I1_IDX | 152K| | 361 (1)| 00:00:05 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='TEST')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
946 consistent gets
258 physical reads
18188 redo size
309758 bytes sent via SQL*Net to client
2846 bytes received via SQL*Net from client
223 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
SQL> select /*+ parallel(a 4)*/ * from t1 a where owner='TEST';
已选择3328行。
执行计划
----------------------------------------------------------
Plan hash value: 2494645258
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K| 13M| 4012 (1)| 00:00:49 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 149K| 13M| 4012 (1)| 00:00:49 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 149K| 13M| 4012 (1)| 00:00:49 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T1 | 149K| 13M| 4012 (1)| 00:00:49 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OWNER"='TEST')
统计信息
----------------------------------------------------------
13 recursive calls
0 db block gets
131144 consistent gets
65875 physical reads
0 redo size
218981 bytes sent via SQL*Net to client
2846 bytes received via SQL*Net from client
223 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3328 rows processed
INDEX RANGE SCAN 单块读,无法走并行