1. 首页
  2. IT资讯

[20180607]函数与标量子查询8.txt

[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 😡 := ‘
           || 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 😡 := 1;
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;
@ http://192.168.100.40/sqllaji//dpc ” ”

exec 😡 := 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2155755/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182822.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code