oracle dbms_random.value,[20150608]dbms_random.value.txt

[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时，也调用这两个函数，导致执行时间

--增加。