[20191223]Wait for Java.txt

[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.