u1@ORCL> select b.NAME,a.VALUE from v$mystat a,v$statname b where b.name like '%table scans%' and a.STATISTIC#=b.STATISTIC#;
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 5
table scans (long tables) 0
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
u1@ORCL> variable n1 number;
u1@ORCL> variable n2 number;
u1@ORCL> exec :n1 := dbms_utility.get_time;
PL/SQL 过程已成功完成。
u1@ORCL> exec :n2 := dbms_utility.get_cpu_time;
PL/SQL 过程已成功完成。
u1@ORCL> select emp.emp_id,dept.name from emp,dept where emp.dept_id=dept.dept_id;
EMP_ID NAME
---------- --------------------------------------------------
1 aaa
2 bbb
3 ccc
u1@ORCL> select b.NAME,a.VALUE from v$mystat a,v$statname b where b.name like '%table scans%' and a.STATISTIC#=b.STATISTIC#;
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 7
table scans (long tables) 0
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_time-:n1) || ' hsecs...' );
2022 hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n2) || ' cpu hsecs...' );
2 cpu hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_time-:n1) || ' hsecs...' );
4154 hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n2) || ' cpu hsecs...' );
2 cpu hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec :n1 := dbms_utility.get_time;
PL/SQL 过程已成功完成。
u1@ORCL> exec :n2 := dbms_utility.get_cpu_time;
PL/SQL 过程已成功完成。
u1@ORCL> select emp.emp_id,(select dept.name from dept where emp.dept_id=dept.dept_id) name from emp;
EMP_ID NAME
---------- --------------------------------------------------
1 aaa
2 bbb
3 ccc
u1@ORCL> select b.NAME,a.VALUE from v$mystat a,v$statname b where b.name like '%table scans%' and a.STATISTIC#=b.STATISTIC#;
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 11
table scans (long tables) 0
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_time-:n1) || ' hsecs...' );
1841 hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n2) || ' cpu hsecs...' );
2 cpu hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_time-:n1) || ' hsecs...' );
2815 hsecs...
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n2) || ' cpu hsecs...' );
2 cpu hsecs...
PL/SQL 过程已成功完成。
下面是执行计划:
u1@ORCL> select emp.emp_id,dept.name from emp,dept where emp.dept_id=dept.dept_id;
EMP_ID NAME
---------- --------------------------------------------------
1 aaa
2 bbb
3 ccc
执行计划
----------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 27 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 27 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 3 | 18 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 3 | 9 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPT_ID"="DEPT"."DEPT_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
537 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
u1@ORCL> select emp.emp_id,(select dept.name from dept where emp.dept_id=dept.dept_id) name from emp;
EMP_ID NAME
---------- --------------------------------------------------
1 aaa
2 bbb
3 ccc
执行计划
----------------------------------------------------------
Plan hash value: 1571744956
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 3 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 3 | 18 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPT"."DEPT_ID"=:B1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
537 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
可以看出:
1、两个sql会得到同样的结果,只是因为写法不同,性能上会存在巨大的差别。
select emp.emp_id,dept.name from emp,dept where emp.dept_id=dept.dept_id;
--这里会把两个表个扫描一次,然后根据谓词条件来连接。所以会做两次全表扫描,两个表个扫描一次。
select emp.emp_id,(select dept.name from dept where emp.dept_id=dept.dept_id) name from emp;
--这里会把emp表扫描一次,然后每一行再去扫描dept表(emp表总共有3行记录),所以最后会做4次全表扫描。
2、get_time 是获取的总共的时间,空闲等待时间也计算在内。get_cpu_time 只是计算的CPU时间,空闲等待不计算在内。