[20181015] for what is 3 seconds. txt
--//previously tested: Why is the connection http://blog.itpub.net/267265/viewspace-2144765/=> 12 sec. txt.
--//'s weird. 12.1. The 0.1 version tests 12 seconds (Windows version), while 11g is 3 seconds (in the case of using a scalar subquery). I don't know why.
--//under the 12.2.0.1 test to see:
1. Environment:
[Email protected]> @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
[Email protected]> Grant EXECUTE on Dbms_lock to Scott;
Grant succeeded.
2. Create a function:
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. Test:
[Email protected]> set timing on
[Email protected]> Set Feedback only
[Email protected]> select empno, ename, Deptno, Get_dept (deptno) C20 from EMP;
EMPNO ename DEPTNO C20
---------- ---------- ---------- --------------------
Rows selected.
elapsed:00:00:14.00
--//14 seconds, which is correct, 14 records. Call 14 times takes 14 seconds.
--//to scalar quantum query:
[Email protected]> select empno, ename, Deptno, (select Get_dept (DEPTNO) from dual) C20 from EMP;
EMPNO ename DEPTNO C20
---------- ---------- ---------- --------------------
Rows selected.
elapsed:00:00:03.03
The--//execution time is 3 seconds, this time is correct, because the scalar subquery caches the results, and there are only 3 departments in the EMP table. So 3 seconds is right.
4. Continue to explore:
[Email protected]> set Timing off
[email protected]> alter session set Statistics_level=all;
Session altered.
[Email protected]> select empno, ename, Deptno, (select Get_dept (DEPTNO) from dual) C20 from EMP;
EMPNO ename DEPTNO C20
---------- ---------- ---------- --------------------
Rows selected.
[Email protected]> Set feedback on
[Email protected]> @ 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/[Email protected]$2
2-sel$1/[Email protected]$1
--//from the execution plan can also be found that fast dual performed 3. Again, there is a problem with version 12.1.
--//also demonstrates again that any xx.1 version of Oracle is not available in production systems.
5. Continue testing using deterministic Functions:
--//generally, if an index is defined in a function, deterministic is required to indicate that the return result is fixed. In fact, even if it is not fixed, it can be defined.
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;
/
[Email protected]> Show Array
ArraySize 200
--//arraysize=200
[Email protected]> set timing on
[Email protected]> select empno, ename, Deptno, Get_dept (deptno) C20 from EMP;
...
Rows selected.
elapsed:00:00:04.06
--//this time it's 4 seconds. You can set up your own array=2 and other situations.
--//Why? You can see what I wrote. http://blog.itpub.net/267265/viewspace-2138042/=>[20170426] for what is 4 seconds. txt
6. Finally, add the test result cache condition:
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;
/
[Email protected]> select empno, ename, Deptno, Get_dept (deptno) C20 from EMP;
Rows selected.
elapsed:00:00:03.07
[Email protected]> select empno, ename, Deptno, Get_dept (deptno) C20 from EMP;
elapsed:00:00:00.07
--//executes for the 1th time for 3 seconds and executes for the 2nd time for 0 seconds because the result is cached. The second execution results directly. Modify the results as follows.
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;
/
Summarize:
--//again verifies my previous conclusion that any xx.1 version of Oracle should not be used in production systems.
[20181015] for what is 3 seconds. txt