[20210926]DBA_HIST_SQLSTAT.ELAPSED_TIME_DEL他的單位.txt

[20210926]DBA_HIST_SQLSTAT.ELAPSED_TIME_DEL他的單位.txt

--//記錄一下自己一個錯誤,把DBA_HIST_SQLSTAT.ELAPSED_TIME_DEL他的單位搞錯了,這樣前面awr報表遇到的問題許多就很好解析了..
--//前幾天awr分析,遇到一些我無法理解的問題,測試看看.DBA_HIST_SQLSTAT.ELAPSED_TIME_DEL他的單位是什麼.

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秒,看來我前面的測試時間搞錯了,不是毫秒.而是微秒.


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.