[20150608]dbms_random.value.txt
--11.2.0.3与11.2.0.4下，调用dbms_random.value存在很大的差异，测试看看：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
set timing on
declare
n number;
begin
for i in 1..1e8
loop
n:=dbms_random.value(0,10000);
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:03:03.84
SYS@dbendg> @ &r/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
SYS@dbendg> set timing on
declare
n number;
begin
for i in 1..1e8
loop
n:=dbms_random.value(0,10000);
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:05:50.83
--而11.2.0.3的服务器是4cpu。
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.00GHz
--而11.2.0.4的服务器是24cpu。
processor : 23
vendor_id : GenuineIntel
cpu family : 6
model : 62
model name : Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz
--主频基本一样，但是时间存在很大差异。
$ grep -i "package.*dbms_random" *.sql
dbmsrand.sql:CREATE OR REPLACE PACKAGE dbms_random AUTHID DEFINER AS
dbmsrand.sql:CREATE OR REPLACE PACKAGE BODY dbms_random AS
--包dbms_random的定义在dbmsrand.sql文件中。
--拷贝在一台机器使用diff对比：
$ diff -Nur dbmsrand11203.sql dbmsrand11204.sql
--- dbmsrand11203.sql 2015-06-08 09:50:57.000000000 +0800
+++ dbmsrand11204.sql 2015-06-08 09:50:05.000000000 +0800
@@ -1,10 +1,10 @@
Rem
-Rem $Header: rdbms/admin/dbmsrand.sql /main/9 2009/01/15 13:45:55 traney Exp $
+Rem $Header: rdbms/admin/dbmsrand.sql /st_rdbms_11.2.0/1 2013/03/11 01:24:43 yujwang Exp $
Rem
Rem dbmsrand.sql
Rem
-Rem Copyright (c) 1997, 2009, Oracle and/or its affiliates.
-Rem All rights reserved.
+Rem Copyright (c) 1997, 2013, Oracle and/or its affiliates.
+Rem All rights reserved.
Rem
Rem NAME
Rem dbmsrand.sql - RANDom number generation package
@@ -14,6 +14,7 @@
Rem
Rem NOTES
Rem MODIFIED (MM/DD/YY)
+Rem yberezin 02/26/13 - record and replay random number - bug 12676338
Rem traney 01/08/09 - add authid definer
Rem ssonawan 09/20/06 - bug 5527875: add parallel_enable clause
Rem rjenkins 05/09/02 - bug 2383801: fix string()
@@ -100,6 +101,14 @@
RETURN VARCHAR2 PARALLEL_ENABLE; -- string of characters
PRAGMA restrict_references (string, WNDS);
+ -- external C function to record random value
+ PROCEDURE record_random_number(val IN NUMBER);
+ PRAGMA restrict_references (record_random_number, WNDS);
+
+ -- external C function to replay random value
+ FUNCTION replay_random_number RETURN NUMBER;
+ PRAGMA restrict_references (replay_random_number, WNDS);
+
-- Obsolete, just calls seed(val)
PROCEDURE initialize(val IN BINARY_INTEGER);
PRAGMA restrict_references (initialize, WNDS);
@@ -182,13 +191,37 @@
END LOOP;
END LOOP;
END seed;
-
+
+
+ PROCEDURE record_random_number(val IN NUMBER) IS
+ LANGUAGE C
+ NAME "kecrRecordRandomNumber"
+ LIBRARY dbms_workload_capture_lib
+ WITH CONTEXT
+ PARAMETERS
+ ( CONTEXT,
+ val OCINumber );
+
+ FUNCTION replay_random_number RETURN NUMBER IS
+ LANGUAGE C
+ NAME "kecpReplayRemappedRandomNumber"
+ LIBRARY dbms_workload_replay_lib
+ WITH CONTEXT
+ PARAMETERS
+ ( CONTEXT,
+ RETURN INDICATOR );
-- give values to the user
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION value RETURN NUMBER PARALLEL_ENABLE IS
randval NUMBER;
BEGIN
+
+ randval := replay_random_number(); -- null if not in replay mode
+ IF randval IS NOT NULL THEN
+ RETURN randval;
+ END IF;
+
counter := counter + 1;
IF counter >= 55 THEN
@@ -215,6 +248,9 @@
END IF;
counter := 0;
END IF;
+
+ record_random_number(mem(counter)); -- no-op if not in recording
+
RETURN mem(counter);
END value;
--可以发现11.2.0.4增加了两个函数record_random_number,replay_random_number,在调用value时，也调用这两个函数，导致执行时间
--增加。