[20180612]函数与标量子查询10.txt

简介: [20180612]函数与标量子查询10.txt --//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到: 通俗来将,当使用标量子查询的时候,ORACLE会将子查询结果缓存在哈希表中, 如果后续的记录出现同样的值,优化器通过缓存在哈希 表中的值,判断重复值不用重复调用函数,直接使用上次计算结果即可。

[20180612]函数与标量子查询10.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下到底有512个Buckets.今天测试11g是多少,我感觉11g应该是2048,这意味着测试数据量可能需要很大,我原来的测试
--//方法需要的时间更长.

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,20000)+1 id2 from dual connect by level<=40000;
--//ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
--//注意插入数据的顺序,我以前的插入有1点问题,导致id2显示不按照1-20000,1-20000显示(执行select * from t).
--//导致测试出现一些奇怪情况.
--//分析表略.

2.测试:
--//建立脚本by.txt:
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
select t.*,(select sleep(id2) from dual) s from t where id2<=:x;
set term on
@ &r/dpc '' ''
quit

--//建立shell脚本by.sh:
#! /bin/bash
# rm -f ez.txt
for i in $(seq 20000)
do
    sqlplus -s -l scott/book @by.txt $i >> ez.txt
done

--//这样执行脚本就ok了.
--//取出数字
$ egrep 'FAST DUAL' ez.txt  | cut -f5 -d"|" > e2.txt
$ egrep 'SELECT STATEMENT' ez.txt | cut -f10 -d"|"  > e3.txt
$ paste e2.txt e3.txt -d"," > e4.txt

SCOTT@book> create table t1 ( a number ,b number);
Table created.
--//注:a表示执行fast dual次数,也就是递归次数.b表示查询记录数量,
--//修改e4.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)
----------
      9234

--//9234还会进入backupset,后面的数字带入都是出现hash 冲突的情况.没想到这么小,这样估计不是2048个buckets.


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<=9234 ;

--//输出太长,一共8210个值,略,这个结果就是在1-9234之间,出现hash冲突的值.
select count(*) 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 >= 2 and id2<=9234 );

  COUNT(*)
----------
      8210

--// 9234-8210 = 1024 ,可以看出11.2.0.4标量子查询的哈希表大小是1024个buckets.

select  rowid,t.*,(select sleep(id2) from dual) s from t where id2<=9234
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<=9234
);

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fpd89t0b5dzc8, child number 0
-------------------------------------
select  rowid,t.*,(select sleep(id2) from dual) s from t where
id2<=9234 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<=9234 )
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 |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |   291K(100)|          |   2048 |00:00:47.55 |     138 |       |       |          |
|   1 |  FAST DUAL            |      |   1024 |      1 |       |     2   (0)| 00:00:01 |   1024 |00:00:00.01 |       0 |       |       |          |
|   2 |  NESTED LOOPS ANTI    |      |      1 |  18468 |   865K|   291K (18)| 00:58:22 |   2048 |00:00:47.55 |     138 |       |       |          |
|*  3 |   TABLE ACCESS FULL   | T    |      1 |  18469 |   162K|    25   (0)| 00:00:01 |  18468 |00:00:00.01 |      93 |       |       |          |
|*  4 |   VIEW                |      |  18468 |      1 |    39 |    16  (19)| 00:00:01 |  16420 |00:00:47.53 |      45 |       |       |          |
|   5 |    SORT ORDER BY      |      |  18468 |  20000 |   195K|    16  (19)| 00:00:01 |     40M|00:00:44.63 |      45 |   761K|   499K|  676K (0)|
|   6 |     WINDOW SORT       |      |   2048 |  20000 |   195K|    16  (19)| 00:00:01 |     40M|00:00:30.57 |      45 |   761K|   499K|  676K (0)|
|   7 |      TABLE ACCESS FULL| T1   |      1 |  20000 |   195K|    13   (0)| 00:00:01 |  20000 |00:00:00.01 |      45 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
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"<=9234)
   4 - filter(("ID2"="ID2" AND "R"-"RP">=2 AND "ID2"<=9234))

--//正好1024,说明11.2.0.4,哈希表只包含了1024个Buckets,也就是说它能存储1024个不同值,

--//删除冲突的记录看看.
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<=9234 );
16420 rows deleted.
SCOTT@book> commit ;
Commit complete.


select t.*,(select sleep(id2) from dual) s from t where id2<=9234;

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7g5wrr9a7g7v8, child number 0
-------------------------------------
select t.*,(select sleep(id2) from dual) s from t where id2<=9234
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 |        |       |    25 (100)|          |   2048 |00:00:00.01 |      93 |
|   1 |  FAST DUAL        |      |   1024 |      1 |       |     2   (0)| 00:00:01 |   1024 |00:00:00.01 |       0 |
|*  2 |  TABLE ACCESS FULL| T    |      1 |  18469 |   162K|    25   (0)| 00:00:01 |   2048 |00:00:00.01 |      93 |
--------------------------------------------------------------------------------------------------------------------
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"<=9234)

--//这样如果
select rowid,t.*,(select sleep(id2) from dual) s from t where (id2<=9234 ) or id2=:x;
--//:x 选择 9235-20000 任何一个 ,fast dual 的starts都是1026,也就是存在冲突.大家可以自行验证.

--//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

目录
相关文章
|
SQL Oracle 关系型数据库
[20151217]12c标量子查询.txt
[20151217]12c标量子查询.txt --我曾经写过blog,提到许多开发没有根据情况滥用子查询。 --而在12c下呢? So starting with Oracle 12c, the CBO transformation engine c...
1072 0
|
5月前
|
数据挖掘 数据处理 索引
python str.extract提取小数+表inner内连接后,行数多于之前
python str.extract提取小数+表inner内连接后,行数多于之前
39 0
python str.extract提取小数+表inner内连接后,行数多于之前
|
SQL 关系型数据库 MySQL
嵌套套娃,MySQL子查询,单行与多行子查询,相关和不相关(关联)子查询,完整详细可收藏
嵌套套娃,MySQL子查询,单行与多行子查询,相关和不相关(关联)子查询,完整详细可收藏
172 0
嵌套套娃,MySQL子查询,单行与多行子查询,相关和不相关(关联)子查询,完整详细可收藏
|
SQL 关系型数据库 MySQL
MySQL基础-标量子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
292 0
|
关系型数据库 MySQL
字符串变量作mysql查询条件
字符串变量作mysql查询条件
106 0
|
Oracle 关系型数据库 测试技术
[20180626]函数与标量子查询14.txt
[20180626]函数与标量子查询14.txt --//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到: 通俗来将,当使用标量子查询的时候,ORACLE会将子查询结果缓存在哈希表中, 如果后续的记录出现同样的值,优化器通过缓存在哈希 表中的值,判断重复值不用重复调用函数,直接使用上次计算结果即可。
1302 0
|
测试技术 Shell
[20180625]函数与标量子查询13(补充)
[20180625]函数与标量子查询13(补充).txt --//最近一段时间一直在测试标量子查询视buckets的数量,我前面的测试方法纯粹蛮力测试. --//参考链接:http://blog.
1304 0
|
Oracle 关系型数据库 SQL
[20180611]函数与标量子查询9.txt
[20180611]函数与标量子查询9.txt --//前几天网友给一个链接,https://blogs.oracle.com/oraclemagazine/on-caching-and-evangelizing-sql --//也证明我测试的例子.
1287 0
|
Oracle 关系型数据库 vr&ar
[20180607]函数与标量子查询8.txt
[20180607]函数与标量子查询8.txt --//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到: 通俗来将,当使用标量子查询的时候,ORACLE会将子查询结果缓存在哈希表中, 如果后续的记录出现同样的值,优化器通过缓存在哈希 表中的值,判断重复值不用重复调用函数,直接使用上次计算结果即可。
1053 0
|
SQL 存储 Oracle
[20180602]函数与标量子查询4.txt
[20180602]函数与标量子查询4.txt --//前面看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到: 通俗来将,当使用标量子查询的时候,ORACLE会将子查询结果缓存在哈希表中, 如果后续的记录出现同样的值,优化器通过缓存在哈希 表中的值,判断重复值不用重复调用函数,直接使用上次计算结果即可。
1007 0