[20181015] for what is 3 seconds. txt

Source: Internet
Author: User
Tags dname scalar

[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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.