[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時，也呼叫這兩個函式，導致執行時間
--增加。