dbms_utility获取CPU时间（get_cpu_time）

最新推荐文章于 2021-04-13 13:26:38 发布

事后诸葛亮

最新推荐文章于 2021-04-13 13:26:38 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： DBMS包

本文链接：https://blog.youkuaiyun.com/zq9017197/article/details/8919291

DBMS包专栏收录该内容

14 篇文章

订阅专栏

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时间，空闲等待不计算在内。