[20181015]為什么是3秒.txt

--//以前測試:連接http://blog.itpub.net/267265/viewspace-2144765/=>為什么是12秒.txt.
--//很奇怪12.1.0.1版本測試12秒(windows版本),而11g是3秒(在使用標量子查詢的情況下).不知道為什么?
--//在12.2.0.1下測試看看:

1.環境:
SCOTT@test01p> @ver1
PORT_STRING??????????????????? VERSION??????? BANNER?????????????????????????????????????????????????????????????????????????????? CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0?????????? 12.2.0.1.0???? Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production????????????? 0

SYS@test01p> grant EXECUTE ON? dbms_lock to scott;
Grant succeeded.

2.建立函數:
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
?? RETURN dept.dname%TYPE
IS
?? l_dname?? dept.dname%TYPE;
BEGIN
?? DBMS_LOCK.sleep (1);

?? SELECT dname
???? INTO l_dname
???? FROM dept
??? WHERE deptno = p_deptno;

?? RETURN l_dname;
END;
/

3.測試:
SCOTT@test01p> set timing on
SCOTT@test01p> set feedback only
SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20 from emp;
???? EMPNO ENAME????????? DEPTNO C20
---------- ---------- ---------- --------------------

14 rows selected.
Elapsed: 00:00:14.00
--//14秒,這是正確的,14條記錄.調用14次需要14秒.

--//換成標量子查詢:
SCOTT@test01p> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
???? EMPNO ENAME????????? DEPTNO C20
---------- ---------- ---------- --------------------

14 rows selected.
Elapsed: 00:00:03.03

--//執行時間是3秒,這次是正確的,因為標量子查詢緩存結果,而僅僅有3個部門在emp表.這樣3秒就正確了.

4.繼續探究:
SCOTT@test01p> set timing off
SCOTT@test01p> alter session set statistics_level=all;

Session altered.

SCOTT@test01p> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
???? EMPNO ENAME????????? DEPTNO C20
---------- ---------- ---------- --------------------

14 rows selected.

SCOTT@test01p> set feedback on
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID? apagxtf1p2puy, child number 1
-------------------------------------
select empno, ename, deptno, (select get_dept(deptno) from dual )c20
from emp
Plan hash value: 1340320406
--------------------------------------------------------------------------------------------------------------------
| Id? | Operation???????? | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time?? | A-Rows |?? A-Time?? | Buffers |
--------------------------------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |????? 1 |??????? |?????? |???? 9 (100)|????????? |???? 14 |00:00:00.01 |?????? 8 |
|?? 1 |? FAST DUAL??????? |????? |????? 3 |????? 1 |?????? |???? 2?? (0)| 00:00:01 |????? 3 |00:00:00.01 |?????? 0 |
|?? 2 |? TABLE ACCESS FULL| EMP? |????? 1 |???? 14 |?? 182 |???? 3?? (0)| 00:00:01 |???? 14 |00:00:00.01 |?????? 8 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
?? 1 - SEL$2 / DUAL@SEL$2
?? 2 - SEL$1 / EMP@SEL$1
--//從執行計劃也可以發現fast dual執行了3.再次說明12.1版本有問題.
--//也再次說明oracle任何XX.1版本不能在生產系統使用.

5.繼續測試使用 DETERMINISTIC Functions:
--//一般如果在在某個函數定義索引，需要DETERMINISTIC,表示返回結果固定。其實即使不固定，也可以這樣定義。
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
?? RETURN dept.dname%TYPE
?? DETERMINISTIC
IS
?? l_dname?? dept.dname%TYPE;
BEGIN
?? DBMS_LOCK.sleep (1);

?? SELECT dname
???? INTO l_dname
???? FROM dept
??? WHERE deptno = p_deptno;

?? RETURN l_dname;
END;
/
SCOTT@test01p> show array
arraysize 200
--//arraysize=200

SCOTT@test01p> set timing on
SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20? from emp;
...
14 rows selected.
Elapsed: 00:00:04.06
--//這次正確了4秒.大家可以自行設置array=2等各種情況.
--//為什么?大家可以看看我寫的.http://blog.itpub.net/267265/viewspace-2138042/=>[20170426]為什么是4秒.txt

6.最后補充測試RESULT CACHE的情況:

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
?? RETURN dept.dname%TYPE
?? RESULT_CACHE
IS
?? l_dname?? dept.dname%TYPE;
BEGIN
?? DBMS_LOCK.sleep (1);

?? SELECT dname
???? INTO l_dname
???? FROM dept
??? WHERE deptno = p_deptno;

?? RETURN l_dname;
END;
/

SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20? from emp;
14 rows selected.
Elapsed: 00:00:03.07

SCOTT@test01p> select empno, ename, deptno, get_dept(deptno) c20? from emp;
Elapsed: 00:00:00.07

--//第1次執行3秒,第2次執行0秒,因為結果緩存了.第二次執行直接取結果.修改如下結果一樣.
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
?? RETURN dept.dname%TYPE
?? RESULT_CACHE
?? DETERMINISTIC
IS
?? l_dname?? dept.dname%TYPE;
BEGIN
?? DBMS_LOCK.sleep (1);

?? SELECT dname
???? INTO l_dname
???? FROM dept
??? WHERE deptno = p_deptno;

?? RETURN l_dname;
END;
/

總結:
--//再次驗證我以前的結論oracle 任何xx.1版本不要在生產系統使用.

轉載于:https://www.cnblogs.com/lfree/p/9794865.html

總結

以上是生活随笔為你收集整理的[20181015]为什么是3秒.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。