建立测试表test
DROP TABLE TEST;
/
CREATE TABLE TEST AS SELECT SYSDATE AS T1 FROM DUAL
/
SET TIMING ON
第一个试验,不加DETERMINISTIC 传入参数不一样
CREATE OR REPLACE FUNCTION F_SCN(V_VAL NUMBER) RETURN VARCHAR2
--DETERMINISTIC
AS
V_DATE DATE := SYSDATE + 3 / 24 / 60 / 60;
V_SCN NUMBER;
V_T1 TEST.T1%TYPE;
BEGIN
V_SCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
SELECT MAX(T1) INTO V_T1 FROM TEST WHERE ROWNUM <=1;
LOOP
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
RETURN TO_CHAR(V_T1, 'HH24:MI:SS') || '->' || TO_CHAR(V_SCN) || '->' || TO_CHAR(SCN_TO_TIMESTAMP(V_SCN), 'HH24:MI:SS') || '->' || TO_CHAR(SYSDATE, 'HH24:MI:SS');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DECLARE
V_DATE DATE := SYSDATE + 18 / 24 / 60 / 60;
BEGIN
LOOP
UPDATE TEST SET T1 = SYSDATE;
COMMIT;
DBMS_LOCK.SLEEP(3);
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
END;',
START_DATE => SYSDATE,
ENABLED => TRUE,
AUTO_DROP => TRUE);
DBMS_LOCK.SLEEP(5);
END;
/
COL SCN1_____SCNTIME___SYSDATE FORMAT A30
COL T1_SCN2_____SCNTIME___SYSDATE FORMAT A40
/*FUNCTION传入参数一不样*/
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) ||
'->' || TO_CHAR(SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER), 'HH24:MI:SS') ||
'->' || TO_CHAR(SYSDATE, 'HH24:MI:SS') AS SCN1_____SCNTIME___SYSDATE,
F_SCN(LEVEL) AS T1_SCN2_____SCNTIME___SYSDATE
FROM DUAL
CONNECT BY LEVEL <= 5;
SCN1_____SCNTIME___SYSDATE T1_SCN2_____SCNTIME___SYSDATE
------------------------------ ----------------------------------------
2201784->14:08:01->14:08:03 14:08:01->2201784->14:08:01->14:08:06
2201787->14:08:04->14:08:03 14:08:04->2201787->14:08:04->14:08:09
2201790->14:08:07->14:08:03 14:08:07->2201790->14:08:07->14:08:12
2201793->14:08:10->14:08:03 14:08:10->2201793->14:08:10->14:08:15
2201796->14:08:13->14:08:03 14:08:13->2201796->14:08:13->14:08:18
Executed in 14.813 seconds
第二个试验,不加DETERMINISTIC 但传入参数一样
CREATE OR REPLACE FUNCTION F_SCN(V_VAL NUMBER) RETURN VARCHAR2
--DETERMINISTIC
AS
V_DATE DATE := SYSDATE + 3 / 24 / 60 / 60;
V_SCN NUMBER;
V_T1 TEST.T1%TYPE;
BEGIN
V_SCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
SELECT MAX(T1) INTO V_T1 FROM TEST WHERE ROWNUM <=1;
LOOP
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
RETURN TO_CHAR(V_T1, 'HH24:MI:SS') || '->' || TO_CHAR(V_SCN) || '->' || TO_CHAR(SCN_TO_TIMESTAMP(V_SCN), 'HH24:MI:SS') || '->' || TO_CHAR(SYSDATE, 'HH24:MI:SS');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DECLARE
V_DATE DATE := SYSDATE + 18 / 24 / 60 / 60;
BEGIN
LOOP
UPDATE TEST SET T1 = SYSDATE;
COMMIT;
DBMS_LOCK.SLEEP(3);
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
END;',
START_DATE => SYSDATE,
ENABLED => TRUE,
AUTO_DROP => TRUE);
DBMS_LOCK.SLEEP(5);
END;
/
COL SCN1_____SCNTIME___SYSDATE FORMAT A30
COL T1_SCN2_____SCNTIME___SYSDATE FORMAT A40
/*FUNCTION传入参数一样*/
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) ||
'->' || TO_CHAR(SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER), 'HH24:MI:SS') ||
'->' || TO_CHAR(SYSDATE, 'HH24:MI:SS') AS SCN1_____SCNTIME___SYSDATE,
F_SCN(1) AS T1_SCN2_____SCNTIME___SYSDATE
FROM DUAL
CONNECT BY LEVEL <= 5;
SCN1_____SCNTIME___SYSDATE T1_SCN2_____SCNTIME___SYSDATE
------------------------------ ----------------------------------------
2202369->14:11:09->14:11:11 14:11:09->2202369->14:11:09->14:11:14
2202372->14:11:12->14:11:11 14:11:12->2202372->14:11:12->14:11:17
2202375->14:11:15->14:11:11 14:11:15->2202375->14:11:15->14:11:20
2202378->14:11:18->14:11:11 14:11:18->2202378->14:11:18->14:11:23
2202381->14:11:21->14:11:11 14:11:21->2202381->14:11:21->14:11:26
Executed in 14.641 seconds
第三个实验 使用参数DETERMINISTIC 传入参数不一样
CREATE OR REPLACE FUNCTION F_SCN(V_VAL NUMBER) RETURN VARCHAR2
DETERMINISTIC
AS
V_DATE DATE := SYSDATE + 3 / 24 / 60 / 60;
V_SCN NUMBER;
V_T1 TEST.T1%TYPE;
BEGIN
V_SCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
SELECT MAX(T1) INTO V_T1 FROM TEST WHERE ROWNUM <=1;
LOOP
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
RETURN TO_CHAR(V_T1, 'HH24:MI:SS') || '->' || TO_CHAR(V_SCN) || '->' || TO_CHAR(SCN_TO_TIMESTAMP(V_SCN), 'HH24:MI:SS') || '->' || TO_CHAR(SYSDATE, 'HH24:MI:SS');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DECLARE
V_DATE DATE := SYSDATE + 18 / 24 / 60 / 60;
BEGIN
LOOP
UPDATE TEST SET T1 = SYSDATE;
COMMIT;
DBMS_LOCK.SLEEP(3);
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
END;',
START_DATE => SYSDATE,
ENABLED => TRUE,
AUTO_DROP => TRUE);
DBMS_LOCK.SLEEP(5);
END;
/
COL SCN1_____SCNTIME___SYSDATE FORMAT A30
COL T1_SCN2_____SCNTIME___SYSDATE FORMAT A40
/*FUNCTION传入参数一不样*/
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) ||
'->' || TO_CHAR(SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER), 'HH24:MI:SS') ||
'->' || TO_CHAR(SYSDATE, 'HH24:MI:SS') AS SCN1_____SCNTIME___SYSDATE,
F_SCN(LEVEL) AS T1_SCN2_____SCNTIME___SYSDATE
FROM DUAL
CONNECT BY LEVEL <= 5;
SCN1_____SCNTIME___SYSDATE T1_SCN2_____SCNTIME___SYSDATE
------------------------------ ----------------------------------------
2202453->14:12:54->14:12:56 14:12:54->2202453->14:12:54->14:12:59
2202456->14:12:57->14:12:56 14:12:57->2202456->14:12:57->14:13:02
2202459->14:13:00->14:12:56 14:13:00->2202459->14:13:00->14:13:05
2202462->14:13:03->14:12:56 14:13:03->2202462->14:13:03->14:13:08
2202465->14:13:06->14:12:56 14:13:06->2202465->14:13:06->14:13:11
Executed in 14.578 seconds
第四个实验 使用参数DETERMINISTIC 传入参数一样
CREATE OR REPLACE FUNCTION F_SCN(V_VAL NUMBER) RETURN VARCHAR2
DETERMINISTIC
AS
V_DATE DATE := SYSDATE + 3 / 24 / 60 / 60;
V_SCN NUMBER;
V_T1 TEST.T1%TYPE;
BEGIN
V_SCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
SELECT MAX(T1) INTO V_T1 FROM TEST WHERE ROWNUM <=1;
LOOP
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
RETURN TO_CHAR(V_T1, 'HH24:MI:SS') || '->' || TO_CHAR(V_SCN) || '->' || TO_CHAR(SCN_TO_TIMESTAMP(V_SCN), 'HH24:MI:SS') || '->' || TO_CHAR(SYSDATE, 'HH24:MI:SS');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DECLARE
V_DATE DATE := SYSDATE + 18 / 24 / 60 / 60;
BEGIN
LOOP
UPDATE TEST SET T1 = SYSDATE;
COMMIT;
DBMS_LOCK.SLEEP(3);
EXIT WHEN SYSDATE >= V_DATE;
END LOOP;
END;',
START_DATE => SYSDATE,
ENABLED => TRUE,
AUTO_DROP => TRUE);
DBMS_LOCK.SLEEP(5);
END;
/
COL SCN1_____SCNTIME___SYSDATE FORMAT A30
COL T1_SCN2_____SCNTIME___SYSDATE FORMAT A40
/*FUNCTION传入参数一样*/
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) ||
'->' || TO_CHAR(SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER), 'HH24:MI:SS') ||
'->' || TO_CHAR(SYSDATE, 'HH24:MI:SS') AS SCN1_____SCNTIME___SYSDATE,
F_SCN(1) AS T1_SCN2_____SCNTIME___SYSDATE
FROM DUAL
CONNECT BY LEVEL <= 5;
SCN1_____SCNTIME___SYSDATE T1_SCN2_____SCNTIME___SYSDATE
------------------------------ ----------------------------------------
2202526->14:14:46->14:14:49 14:14:47->2202526->14:14:46->14:14:52
2202529->14:14:49->14:14:49 14:14:47->2202526->14:14:46->14:14:52
2202529->14:14:49->14:14:49 14:14:47->2202526->14:14:46->14:14:52
2202529->14:14:49->14:14:49 14:14:47->2202526->14:14:46->14:14:52
2202529->14:14:49->14:14:49 14:14:47->2202526->14:14:46->14:14:52
Executed in 3 seconds
可以看到,在传入固定参数时,DETERMINISTIC作用很大