SQL> set autotrace traceonly
SQL> select * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 54 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 9 | 54 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+CARDINALITY(test,1000000) */ * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 5859K| 6 (50)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000K| 5859K| 6 (50)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+CARDINALITY(test,1000000000000000000000000) */ * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18E| 15E| 3115P(100)|999:59:59 |
| 1 | TABLE ACCESS FULL| TEST | 18E| 15E| 3115P(100)|999:59:59 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> set timing on
SQL> select * from test;
NAME CERT
---------------------------------------- --------------------
bai a
cai a
dai a
bai b
cai b
dai b
bai c
cai c
dai c
已选择9行。
已用时间: 00: 00: 00.00
SQL> select /*+CARDINALITY(test,1000000000000000000000000) */ * from test;
NAME CERT
---------------------------------------- --------------------
bai a
cai a
dai a
bai b
cai b
dai b
bai c
cai c
dai c
已选择9行。
已用时间: 00: 00: 00.00
SQL>
SQL> select * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 54 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 9 | 54 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+CARDINALITY(test,1000000) */ * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 5859K| 6 (50)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000K| 5859K| 6 (50)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+CARDINALITY(test,1000000000000000000000000) */ * from test;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18E| 15E| 3115P(100)|999:59:59 |
| 1 | TABLE ACCESS FULL| TEST | 18E| 15E| 3115P(100)|999:59:59 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
741 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> set autotrace off
SQL> set timing on
SQL> select * from test;
NAME CERT
---------------------------------------- --------------------
bai a
cai a
dai a
bai b
cai b
dai b
bai c
cai c
dai c
已选择9行。
已用时间: 00: 00: 00.00
SQL> select /*+CARDINALITY(test,1000000000000000000000000) */ * from test;
NAME CERT
---------------------------------------- --------------------
bai a
cai a
dai a
bai b
cai b
dai b
bai c
cai c
dai c
已选择9行。
已用时间: 00: 00: 00.00
SQL>
看到执行计划中的cost,time只是根据公式计算出来的,并不代表实际的执行时间