[20210926]DBA_HIST_SQLSTAT.ELAPSED_TIME_DELTA的单位.txt

--//记录一下自己一个错误,把DBA_HIST_SQLSTAT.ELAPSED_TIME_DELTA的单位搞错了,这样前面awr报表遇到的问题许多就很好解析了..
--//前几天awr分析,遇到一些我无法理解的问题,测试看看.DBA_HIST_SQLSTAT.ELAPSED_TIME_DELTA的单位是什么.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试:

SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;

COUNT(*)
----------
105413504

SCOTT@book> set timing on
SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;
COUNT(*)
----------
105413504

Elapsed: 00:00:04.26
SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;
COUNT(*)
----------
105413504

Elapsed: 00:00:04.22
--//执行3次总共消耗12.XX秒上下.

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
2779624423 gau6fcukuvcz7            0 a5adb3e7
Elapsed: 00:00:00.04

SCOTT@book> @ cr_s
PL/SQL procedure successfully completed.
--//生成awr snap.
Elapsed: 00:00:00.92

SCOTT@book> @ sqlh gau6fcukuvcz7 0
@ sqlh sql_id [snap_id]
   SNAP_ID INSTANCE_NUMBER PLAN_HASH_VALUE BEGIN_INTERVAL_TIME      END_INTERVAL_TIME        ELAPSED_TIME_DELTA CPU_TIME_DELTA EXECUTIONS_DELTA EXECUTIONS_TOTAL ROWS_PROCESSED_DELTA BUFFER_GETS_DELTA LOADED_VERSIONS
---------- --------------- --------------- ------------------------ ------------------------ ------------------ -------------- ---------------- ---------------- -------------------- ----------------- ---------------
      4177               1      2048615215 2021-09-26 11:00:22.918 2021-09-26 11:12:09.053            12733395       12705068                3                3                    3               115               1
Elapsed: 00:00:00.02
--//噢,我前面搞错了,单位应该是微秒不是毫秒.

SCOTT@book> @ tpt/sql_id gau6fcukuvcz7 %
Show SQL text, child cursors and execution stats for SQLID gau6fcukuvcz7 child %
HASH_VALUE CH# SQL_TEXT
---------- ---- ------------------------------------------------
2779624423    0 select count(*) from emp,emp,emp,emp,emp,emp,emp

Elapsed: 00:00:00.03
CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS       LIOS       PIOS      SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 000000007DD4DCF0 000000007CD9A9C0 2048615215          3          1          3          3              3 12705.068 12733.395        115          3         18               0

--//12733395/10^6 = 12.733395秒,看来我前面的测试时间搞错了,不是毫秒.而是微秒.

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3183.htm#REFRN23447
ELAPSED_TIME_DELTA NUMBER   Delta value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching

--//金山词霸的查询结果.
microsecond
[5maIkrEJsekEnd]
n
微秒，百万分之一秒

microsecond
[5maIkrEJsekEnd]
n.一百万分之一秒, 微秒

--//不知道为什么看到micro,第一反应是毫,实际上这个表示微. 毫秒的英文是 millisecond.