[20180607]函数与标量子查询8.txt
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到:
通俗来将，当使用标量子查询的时候，ORACLE会将子查询结果缓存在哈希表中， 如果后续的记录出现同样的值，优化器通过缓存在哈希
表中的值，判断重复值不用重复调用函数，直接使用上次计算结果即可。从而减少调用函数次数，从而达到优化性能的效果。另外在
ORACLE 10和11中， 哈希表只包含了255个Buckets，也就是说它能存储255个不同值，如果超过这个范围，就会出现散列冲突，那些出现
散列冲突的值就会重复调用函数，即便如此，依然能达到大幅改善性能的效果。
--//昨天我测试11.2.0.4 for linux下,哈希表不止255个Buckets.
--//今天测试看看10g下到底有多少个Buckets.因为我感觉10g可能哈希表的buckets可能不大.
1.环境:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
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(0.01);
RETURN seconds;
END;
/
create table t as select rownum id1,mod(rownum-1,4000)+1 id2 from dual connect by level<=8000;
--//ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
--//注意插入数据的顺序,我以前的插入有1点问题,导致id2显示不按照1-4000,1-4000显示(执行select * from t).
--//导致测试出现一些奇怪情况.
2.测试:
SELECT 'exec :x := '
|| LEVEL
|| ';'
|| CHR (10)
|| 'select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;'
|| CHR (10)
|| '@ &r/dpc '''' '''''
FROM DUAL
CONNECT BY LEVEL <= 4000;
--//建立脚本ay.txt:
alter session set statistics_level=all;
set feed on
variable x number;
exec :x := 1;
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;
@ http://192.168.100.40/sqllaji//dpc '' ''
...
exec :x := 4000;
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;
@ http://192.168.100.40/sqllaji//dpc '' ''
--//多执行几次,避免其它递归影响.
spool cz.txt
@ ay.txt
spool off
--//取出数字
$ egrep 'FAST DUAL|rows selected' cz.txt | sed '/^29 rows selected./d' > c1.txt
$ grep "rows selected." c1.txt | cut -f1 -d' '> c2.txt
$ grep "FAST DUAL" c1.txt | cut -f5 -d"|" > c3.txt
$ paste c2.txt c3.txt -d"," > c4.txt
SCOTT@book> create table t1 ( b number ,a number);
Table created.
--//注b在前,表示查询记录数量,a表示执行fast dual次数,也就是递归次数.
--//修改b4.txt ,改写成inert插入表t1.执行如下:
:%s/^/insert into t1 values(/g
:%s/$/);/g
select max(id2) from (
SELECT id2, r, rp
FROM ( SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp
FROM t1
ORDER BY b/2)
WHERE r - rp = 1 order by id2);
MAX(ID2)
----------
3152
--//3152值还会进入backupset,后面的数字带入都是出现hash 冲突的情况.
SELECT id2, r, rp
FROM ( SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp
FROM t1
ORDER BY b/2)
WHERE r - rp >= 2 and id2<=3152 ;
--//输出太长,一共2640个值,略,这个结果就是在1-3152之间,出现hash冲突的值.
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152
and id2 not in
(
SELECT /*+ NL_AJ */ id2
FROM ( SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp
FROM t1
ORDER BY b/2)
WHERE r - rp >= 2 and id2<=3152
);
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7r8dyxjmdwucp, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152 and id2 not in ( SELECT /*+ NL_AJ */ id2 FROM ( SELECT b / 2
id2, a r, LAG (a) OVER (ORDER BY b) rp FROM t1 ORDER BY b/2) WHERE r - rp >= 2 and id2<=3152 )
Plan hash value: 4130365942
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | FAST DUAL | | 512 | 1 | | 2 (0)| 00:00:01 | 512 |00:00:00.01 | 0 | | | |
| 2 | NESTED LOOPS ANTI | | 1 | 6304 | 283K| 46284 (32)| 00:09:16 | 1024 |00:00:07.65 | 40 | | | |
|* 3 | TABLE ACCESS FULL | T | 1 | 6306 | 44142 | 6 (0)| 00:00:01 | 6304 |00:00:00.01 | 24 | | | |
|* 4 | VIEW | | 6304 | 1 | 39 | 7 (29)| 00:00:01 | 5280 |00:00:07.65 | 16 | | | |
| 5 | SORT ORDER BY | | 6304 | 4000 | 28000 | 7 (29)| 00:00:01 | 4096K|00:00:06.14 | 16 | 160K| 160K| 142K (0)|
| 6 | WINDOW SORT | | 1024 | 4000 | 28000 | 7 (29)| 00:00:01 | 4096K|00:00:04.10 | 16 | 196K| 196K| 174K (0)|
| 7 | TABLE ACCESS FULL| T1 | 1 | 4000 | 28000 | 5 (0)| 00:00:01 | 4000 |00:00:00.01 | 16 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$8771BF6C
3 - SEL$8771BF6C / T@SEL$1
4 - SEL$4 / from$_subquery$_003@SEL$3
5 - SEL$4
7 - SEL$4 / T1@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID2"<=3152)
4 - filter(("ID2"="ID2" AND "R"-"RP">=2 AND "ID2"<=3152))
35 rows selected.
--//正好512,说明10.2.0.4,哈希表只包含了512个Buckets，也就是说它能存储512个不同值，
--//删除冲突的记录看看.
delete from t where id2 in (SELECT id2
FROM ( SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp
FROM t1
ORDER BY b/2)
WHERE r - rp >= 2 and id2<=3152 );
commit;
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152;
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9q5bnk36nnq68, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152
Plan hash value: 1032660217
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 1 | FAST DUAL | | 512 | 1 | | 2 (0)| 00:00:01 | 512 |00:00:00.01 | 0 |
|* 2 | TABLE ACCESS FULL| T | 1 | 6306 | 44142 | 6 (0)| 00:00:01 | 1024 |00:00:00.01 | 24 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"<=3152)
24 rows selected.
--//这样如果
select rowid,t.*,(select sleep(id2) from dual) s from t where (id2<=3152 and id2<>1693) or id2=:x;
--//:x 选择 3153-4000 任何一个 ,fast dual 的starts都是514,也就是存在冲突.大家可以自行验证.
--//dpc脚本如下:
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
