Oracle SLEEP
Version 20c

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
How Oracle has four separate ways to induce a sleep into PL/SQL. They are: The USER_LOCK package is not part of the default installation. Follow the link at page bottom to install before running the demo.
 
Arguments set linesize 121
col package_name format a20
col argument_name format a17
col data_type format a14
col pls_type format a14

SELECT package_name, argument_name, data_type, data_length,
data_precision, pls_type
FROM all_arguments
WHERE object_name = 'SLEEP'
ORDER BY 1;
Precision set serveroutput on

DECLARE
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
BEGIN
  stime := SYSTIMESTAMP;
  dbms_backup_restore.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_drs.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_lock.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  user_lock.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);
END;
/

DECLARE
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
BEGIN
  stime := SYSTIMESTAMP;
  dbms_backup_restore.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_drs.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_lock.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  user_lock.sleep(100);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);
END;
/

Related Topics
DBMS_BACKUP_RESTORE
DBMS_DRS
DBMS_LOCK
DBMS_SESSION
USER_LOCK
What's New In 19c
What's New In 20c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
DBSecWorx