[20191223]Wait for Java.txt

--//链接:https://jonathanlewis.wordpress.com/2019/12/18/wait-for-java/,重复测试.

1.环境:
@ ver1

2.测试脚本:
--//java_wait_for_cpu.sql
rem
rem     Script:         java_wait_for_cpu.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem
rem     Based on an email from Jack van Zanen to Oracle-L
rem

set time on

create or replace procedure milli_sleep(i_milliseconds in number)
as
        language java
        name 'java.lang.Thread.sleep(int)';
/

set pagesize 60
set linesize 132
set trimspool on

column sample_time format a32
column event       format a32
column sql_text    format a60
column sql_id      new_value m_sql_id

set echo on
execute milli_sleep(1e4)

select
        sample_time, sample_id, session_state, sql_id, event
from
        v$active_session_history
where
        session_id = sys_context('userenv','sid')
and     sample_time > sysdate - 1/1440
order by
        sample_time
;

select sql_id, round(cpu_time/1e6,3) cpu_time, round(elapsed_time/1e6,3) elapsed, sql_text from v$sql where sql_id = '&m_sql_id';



--//输出如下:
SCOTT@book> @ n1.txt
Procedure created.

08:52:41 SCOTT@book> execute milli_sleep(1e4)
PL/SQL procedure successfully completed.

08:52:52 SCOTT@book>
08:52:52 SCOTT@book> select
08:52:52   2          sample_time, sample_id, session_state, sql_id, event
08:52:52   3  from
08:52:52   4          v$active_session_history
08:52:52   5  where
08:52:52   6          session_id = sys_context('userenv','sid')
08:52:52   7  and     sample_time > sysdate - 1/1440
08:52:52   8  order by
08:52:52   9          sample_time
08:52:52  10  ;

SAMPLE_TIME                       SAMPLE_ID SESSION SQL_ID        EVENT
-------------------------------- ---------- ------- ------------- --------------------------------
2019-12-23 08:52:42.545             5076973 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:43.555             5076974 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:44.555             5076975 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:45.555             5076976 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:46.555             5076977 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:47.555             5076978 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:48.555             5076979 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:49.555             5076980 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:50.555             5076981 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:51.565             5076982 ON CPU  4tbmywxw2ndt1
2019-12-23 08:52:52.565             5076983 ON CPU  6tp6b74pup4jw

11 rows selected.

08:52:52 SCOTT@book>
08:52:52 SCOTT@book> select sql_id, round(cpu_time/1e6,3) cpu_time, round(elapsed_time/1e6,3) elapsed, sql_text from v$sql where sql_id = '&m_sql_id';
old   1: select sql_id, round(cpu_time/1e6,3) cpu_time, round(elapsed_time/1e6,3) elapsed, sql_text from v$sql where sql_id = '&m_sql_id'
new   1: select sql_id, round(cpu_time/1e6,3) cpu_time, round(elapsed_time/1e6,3) elapsed, sql_text from v$sql where sql_id = '6tp6b74pup4jw'
SQL_ID          CPU_TIME    ELAPSED SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------
6tp6b74pup4jw        .04        .04 select         sample_time, sample_id, session_state, sql_id
                                    , event from         v$active_session_history where
                                    session_id = sys_context('userenv','sid') and     sample_tim
                                    e > sysdate - 1/1440 order by         sample_time

--//最后的输出有点问题:
08:52:52 SCOTT@book> select sql_id, round(cpu_time/1e6,3) cpu_time, round(elapsed_time/1e6,3) elapsed, sql_text from v$sql where sql_id = '4tbmywxw2ndt1';
SQL_ID          CPU_TIME    ELAPSED SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------
4tbmywxw2ndt1       .222     10.656 BEGIN milli_sleep(1e4); END;

--//可以发现CPU_TIME仅仅消耗.222.