[20181015] for what is 3 seconds. txt

[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