oracle执行计划中的cost，time与实际执行时间

来源：互联网发布：小白管理器家庭网络编辑：程序博客网时间：2024/05/22 06:13

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 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只是根据公式计算出来的，并不代表实际的执行时间

0 0