ANBOB™

像TanelPoder的snapper 工具一样，有时比如需要让一个SQL执行多少秒，当然不是为了应用代码里故意给后期应用优化留下点“小惊喜”，目前能想到的几种方式

1， snapper中使用的

define   snapper_sleep=10;
define   snapper_count=1;

declare
ash_date1 date:= sysdate; 
begin
if 1=1 then 
    while sysdate < (ash_date1 + (&snapper_sleep/86400)) loop
	     null;
        dbms_lock.sleep( greatest(0.1,(least(1,&snapper_sleep*&snapper_count/100))) );
    end loop;
else
    dbms_lock.sleep( ((ash_date1+(&snapper_sleep/86400)) - sysdate)*86400 ); 
    null;
end if;
end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.15

2, sys.standard.current_timestamp

select count(*) from dual connect by sys.standard.current_timestamp - current_timestamp <= interval'3' second; COUNT(*) ---------- 812372 Elapsed: 00:00:03.05 SQL> select count(*) from dual connect by sys.standard.current_timestamp - curre                                                                              nt_timestamp <= interval'10'second;
select count(*) from dual connect by sys.standard.current_timestamp - current_ti                                                                              mestamp <= interval'10'second * ERROR at line 1: ORA-30009: Not enough memory for CONNECT BY operation Elapsed: 00:00:05.98 SQL> ho oerr ora 30009
30009, 0000, "Not enough memory for %s operation"
// *Cause: The memory size was not sufficient to process all the levels of the
//         hierarchy specified by the query.
// *Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
//          a reasonably larger value.
//          Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
//          reasonably larger value.

3, with function

SQL> var n number;
SQL> exec :n:=5;

PL/SQL procedure successfully completed.

SQL> print n

         N
----------
         5

SQL> with function sleep (ps number)return number 
is
begin 
dbms_lock.sleep(ps);
return ps;
end;
select sleep(:n)d from dual;

         D
----------
         5

Elapsed: 00:00:05.47

打赏

微信扫一扫，打赏作者吧～