[20180602]函数与标量子查询3.txt
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到:
通俗来将，当使用标量子查询的时候，ORACLE会将子查询结果缓存在哈希表中， 如果后续的记录出现同样的值，优化器通过缓存在哈希
表中的值，判断重复值不用重复调用函数，直接使用上次计算结果即可。从而减少调用函数次数，从而达到优化性能的效果。另外在
ORACLE 10和11中， 哈希表只包含了255个Buckets，也就是说它能存储255个不同值，如果超过这个范围，就会出现散列冲突，那些出现
散列冲突的值就会重复调用函数，即便如此，依然能达到大幅改善性能的效果。
--//昨天的测试有一些问题自己没有很好的理解.晚上看链接http://blog.itpub.net/267265/viewspace-2144765/
--//当时可以发现,如果邻近的值一样,函数调用仅仅1次.
--//这样才明白oracle如何操作的.还是通过例子说明问题.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(1);
RETURN seconds;
END;
/
create table t as select rownum id1,mod(rownum-1,255)+1 id2 from dual connect by level<=255;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
insert into t select * from t;
commit ;
--//分析表略.
2.我前面的测试带入参数48,75存在hash冲突.
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID ID1 ID2 S
------------------ ---------- ---------- ----------
AAAaQxAAJAAAAC7AAv 48 48 48
AAAaQxAAJAAAAC7ABK 75 75 75
AAAaQxAAJAAAAC+AAv 48 48 48
AAAaQxAAJAAAAC+ABK 75 75 75
Elapsed: 00:00:19.82
SCOTT@test01p> set autot traceonly
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
Elapsed: 00:00:19.83
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 32 | 8 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 4 | 32 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"=48 OR "ID2"=75)
Statistics
----------------------------------------------------------
3 recursive calls
....
4 rows processed
--//注:多执行几次,这样反应的递归调用结果比较准确.
--//(48+75+75)/10= 19.80
--//哈希表只包含了255个Buckets，也就是说它能存储255个不同值，
--//还真不好表达,我前面的递规调用3次.sleep1(48) 1次,sleep1(75) 2次.
--//第1次,先执行sleep1(48),结果放入哈希表,
--//第2次,然后执行sleep1(75),由于48与75存在hash冲突,只能重复调用函数sleep1(75),但是结果不放入哈希表.
--//第3次,接着执行sleep1(48),这个已经存在哈希表,不必调用sleep1(48).
--//第4次,执行sleep1(75),由于前面执行的结果不放入哈希表,只能重复调用函数sleep1(75).
3.如果执行如下:
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual
union all
select 48 a from dual
union all
select 75 a from dual);
A S
---------- ----------
48 48
75 75
75 75
48 48
75 75
Elapsed: 00:00:19.83
SCOTT@test01p> set autot traceonly
--//再次执行:
Statistics
----------------------------------------------------------
3 recursive calls
--//(48+75+75)/10 = 19.8
--//从结果看sleep1(48) 2次,sleep1(75) 3次.
--//第1次,先执行sleep1(48),结果放入哈希表,
--//第2次,然后执行sleep1(75),由于48与75存在hash冲突,只能重复调用函数sleep1(75),但是结果不放入哈希表.
--//第3次,接着执行sleep1(75),由于前面执行sleep1(75),虽然结果不存在哈希表,因为可以从前面的执行知道结果,不必调用sleep1(75).
--//说明: 也就是如果邻近的值一样,函数调用仅仅1次.
--//第4次,执行sleep1(48),由于结果存在哈希表,不必调用sleep1(48).
--//第5次,执行sleep1(75),由于前面的查询sleep1(48),并且sleep1(78)的结果不存在哈希表,只能重复调用函数sleep1(75).
--//这样sleep1(48) 1次,sleep1(75) 2次.按照这个模式分析,就知道调用测试.
select a ,(select sleep1(a) from dual) s from
(select 75 a from dual
union all
select 48 a from dual
union all
select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual);
A S
---------- ----------
75 75
48 48
48 48
75 75
75 75
Elapsed: 00:00:12.30
SCOTT@test01p> set autot traceonly
SCOTT@test01p> /
Statistics
----------------------------------------------------------
2 recursive calls
--//(48+75)/10 = 12.3
--//按照前面的模式分析,就知道调用测试.
--//从结果看sleep1(48) 1次,sleep1(75) 1次.
3.总结:
--//如果你仔细看连接http://blog.itpub.net/267265/viewspace-2144765/
--//可以发现与11g的不同.但是如果执行如下测试:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select rowid,t.*,(select sleep(id2) from dual) s from t ;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54u1r6skg6pt7, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t
Plan hash value: 1032660217
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 514 (100)| | 510 |00:00:00.01 | 10 |
| 1 | FAST DUAL | | 282 | 1 | | 2 (0)| 00:00:01 | 282 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T | 1 | 510 | 4080 | 4 (0)| 00:00:01 | 510 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
20 rows selected.
--//id=2 fast dual 执行282次.说明递归才282次.还是许多问题不理解,有计划还是在11g下测试看看.
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到:
通俗来将，当使用标量子查询的时候，ORACLE会将子查询结果缓存在哈希表中， 如果后续的记录出现同样的值，优化器通过缓存在哈希
表中的值，判断重复值不用重复调用函数，直接使用上次计算结果即可。从而减少调用函数次数，从而达到优化性能的效果。另外在
ORACLE 10和11中， 哈希表只包含了255个Buckets，也就是说它能存储255个不同值，如果超过这个范围，就会出现散列冲突，那些出现
散列冲突的值就会重复调用函数，即便如此，依然能达到大幅改善性能的效果。
--//昨天的测试有一些问题自己没有很好的理解.晚上看链接http://blog.itpub.net/267265/viewspace-2144765/
--//当时可以发现,如果邻近的值一样,函数调用仅仅1次.
--//这样才明白oracle如何操作的.还是通过例子说明问题.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(1);
RETURN seconds;
END;
/
create table t as select rownum id1,mod(rownum-1,255)+1 id2 from dual connect by level<=255;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
insert into t select * from t;
commit ;
--//分析表略.
2.我前面的测试带入参数48,75存在hash冲突.
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID ID1 ID2 S
------------------ ---------- ---------- ----------
AAAaQxAAJAAAAC7AAv 48 48 48
AAAaQxAAJAAAAC7ABK 75 75 75
AAAaQxAAJAAAAC+AAv 48 48 48
AAAaQxAAJAAAAC+ABK 75 75 75
Elapsed: 00:00:19.82
SCOTT@test01p> set autot traceonly
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
Elapsed: 00:00:19.83
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 32 | 8 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 4 | 32 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"=48 OR "ID2"=75)
Statistics
----------------------------------------------------------
3 recursive calls
....
4 rows processed
--//注:多执行几次,这样反应的递归调用结果比较准确.
--//(48+75+75)/10= 19.80
--//哈希表只包含了255个Buckets，也就是说它能存储255个不同值，
--//还真不好表达,我前面的递规调用3次.sleep1(48) 1次,sleep1(75) 2次.
--//第1次,先执行sleep1(48),结果放入哈希表,
--//第2次,然后执行sleep1(75),由于48与75存在hash冲突,只能重复调用函数sleep1(75),但是结果不放入哈希表.
--//第3次,接着执行sleep1(48),这个已经存在哈希表,不必调用sleep1(48).
--//第4次,执行sleep1(75),由于前面执行的结果不放入哈希表,只能重复调用函数sleep1(75).
3.如果执行如下:
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual
union all
select 48 a from dual
union all
select 75 a from dual);
A S
---------- ----------
48 48
75 75
75 75
48 48
75 75
Elapsed: 00:00:19.83
SCOTT@test01p> set autot traceonly
--//再次执行:
Statistics
----------------------------------------------------------
3 recursive calls
--//(48+75+75)/10 = 19.8
--//从结果看sleep1(48) 2次,sleep1(75) 3次.
--//第1次,先执行sleep1(48),结果放入哈希表,
--//第2次,然后执行sleep1(75),由于48与75存在hash冲突,只能重复调用函数sleep1(75),但是结果不放入哈希表.
--//第3次,接着执行sleep1(75),由于前面执行sleep1(75),虽然结果不存在哈希表,因为可以从前面的执行知道结果,不必调用sleep1(75).
--//说明: 也就是如果邻近的值一样,函数调用仅仅1次.
--//第4次,执行sleep1(48),由于结果存在哈希表,不必调用sleep1(48).
--//第5次,执行sleep1(75),由于前面的查询sleep1(48),并且sleep1(78)的结果不存在哈希表,只能重复调用函数sleep1(75).
--//这样sleep1(48) 1次,sleep1(75) 2次.按照这个模式分析,就知道调用测试.
select a ,(select sleep1(a) from dual) s from
(select 75 a from dual
union all
select 48 a from dual
union all
select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual);
A S
---------- ----------
75 75
48 48
48 48
75 75
75 75
Elapsed: 00:00:12.30
SCOTT@test01p> set autot traceonly
SCOTT@test01p> /
Statistics
----------------------------------------------------------
2 recursive calls
--//(48+75)/10 = 12.3
--//按照前面的模式分析,就知道调用测试.
--//从结果看sleep1(48) 1次,sleep1(75) 1次.
3.总结:
--//如果你仔细看连接http://blog.itpub.net/267265/viewspace-2144765/
--//可以发现与11g的不同.但是如果执行如下测试:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select rowid,t.*,(select sleep(id2) from dual) s from t ;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54u1r6skg6pt7, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t
Plan hash value: 1032660217
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 514 (100)| | 510 |00:00:00.01 | 10 |
| 1 | FAST DUAL | | 282 | 1 | | 2 (0)| 00:00:01 | 282 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T | 1 | 510 | 4080 | 4 (0)| 00:00:01 | 510 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
20 rows selected.
--//id=2 fast dual 执行282次.说明递归才282次.还是许多问题不理解,有计划还是在11g下测试看看.
