[20180601]函数与标量子查询2.txt

--//昨天看http://www.cnblogs.com/kerrycode/p/9099507.html链接,里面提到:

通俗来将，当使用标量子查询的时候，ORACLE会将子查询结果缓存在哈希表中， 如果后续的记录出现同样的值，优化器通过缓存在哈希

表中的值，判断重复值不用重复调用函数，直接使用上次计算结果即可。从而减少调用函数次数，从而达到优化性能的效果。另外在 ORACLE 10和11中， 哈希表只包含了255个Buckets，也就是说它能存储255个不同值，如果超过这个范围，就会出现散列冲突，那些出现 散列冲突的值就会重复调用函数，即便如此，依然能达到大幅改善性能的效果。

--//我自己也重复测试验证看看.

1.环境:

SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

grant execute on sys.dbms_lock to scott;

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.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.测试:

--//建立测试脚本: set autot traceonly select rowid,t.*,(select sleep(id2) from dual) s from t ; --//select rowid,t.*,(select sleep(id1) from dual) s from t ;

SCOTT@book> select rowid,t.*,(select sleep(id2) from dual) s from t ;

510 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1032660217 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   510 |  4080 |     4   (0)| 00:00:01 | |   1 |  FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 | |   2 |  TABLE ACCESS FULL| T    |   510 |  4080 |     4   (0)| 00:00:01 | -------------------------------------------------------------------------- Statistics ----------------------------------------------------------         282  recursive calls           0  db block gets           9  consistent gets           0  physical reads           0  redo size       16624  bytes sent via SQL*Net to client         542  bytes received via SQL*Net from client           4  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         510  rows processed

--//从递归看调用函数282次.注意为了测试准确,多执行几次,才能比较准确测出函数调用次数.

--//按照前面的介绍,难道存在hash冲突.

--//如果查询where id2 in ( 48 , 75) ,可以发现递归调用3次,返回4行.

--//为什么不是4次呢? SCOTT@book> select rowid,t.*,(select sleep(id2) from dual) s from t where id2 in ( 48 , 75) ; Execution Plan ---------------------------------------------------------- Plan hash value: 1032660217 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     4 |    32 |     4   (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           0  db block gets           7  consistent gets           0  physical reads           0  redo size         878  bytes sent via SQL*Net to client         520  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           4  rows processed

--//修改函数定义

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

SCOTT@book> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;

ROWID                     ID1        ID2          S ------------------ ---------- ---------- ---------- AAAWP4AAEAAAAJbAAv         48         48         48 AAAWP4AAEAAAAJbABK         75         75         75 AAAWP4AAEAAAAJdAAv         48         48         48 AAAWP4AAEAAAAJdABK         75         75         75 Elapsed: 00:00:19.81

SCOTT@book> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 49 , 75) ;

ROWID                     ID1        ID2          S ------------------ ---------- ---------- ---------- AAAWP4AAEAAAAJbAAw         49         49         49 AAAWP4AAEAAAAJbABK         75         75         75 AAAWP4AAEAAAAJdAAw         49         49         49 AAAWP4AAEAAAAJdABK         75         75         75 Elapsed: 00:00:12.41

--// 48+75+75 = 198, 49+75 = 124

--// 可以发现从时间看,查询 id2 in ( 48 , 75) 时,调用sleep1(48)1次,sleep1(75)2次. --// 而查询 id2 in ( 49 , 75) 时,调用sleep1(49)1次,sleep1(75)1次. --// 换一句话讲我的理解,调用48,75存在冲突,都进入bucket中,而出现冲突时会重复调用函数.

--//如果我建立索引:

SCOTT@book> create index i_t_id2 on t(id2); Index created.

SCOTT@book> set timing on

SCOTT@book> select /*+ index(t i_t_id2 ) */ rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 49 , 75) ; ROWID                     ID1        ID2          S ------------------ ---------- ---------- ---------- AAAWP4AAEAAAAJbAAw         49         49         49 AAAWP4AAEAAAAJdAAw         49         49         49 AAAWP4AAEAAAAJbABK         75         75         75 AAAWP4AAEAAAAJdABK         75         75         75 Elapsed: 00:00:12.41 --//这样执行时间变成12.4秒,还是不好理解. --//似乎进入buckets时放在前面.这样slee1(75)仅仅调用1次.好烦,那位能解析清楚....

SCOTT@book> drop index i_t_id2;

Index dropped.

--//我前面查询id2=1,255,递归282次,估计不存在1个bucket 3个值的情况.

--//这样有282-255 = 27值存在冲突(假设哈希表只包含了255个Buckets)

3.如何确定hash冲突值呢?

--//建立脚本: variable x number; exec :x := 1; select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;

set autot traceonly

exec :x := 1; / exec :x := 2; / .... exec :x := 252; / exec :x := 253; / exec :x := 254; / exec :x := 255; / set autot off

--//多执行几次,避免其它递归影响.

spool az.txt @ ay.txt spool off

$ egrep 'recursive calls|rows processed' az.txt | paste - -

          1  recursive calls              2  rows processed           2  recursive calls              4  rows processed           3  recursive calls              6  rows processed           4  recursive calls              8  rows processed           5  recursive calls             10  rows processed           6  recursive calls             12  rows processed           7  recursive calls             14  rows processed           8  recursive calls             16  rows processed           9  recursive calls             18  rows processed          10  recursive calls             20  rows processed          11  recursive calls             22  rows processed          12  recursive calls             24  rows processed          13  recursive calls             26  rows processed          14  recursive calls             28  rows processed          15  recursive calls             30  rows processed          16  recursive calls             32  rows processed          17  recursive calls             34  rows processed          18  recursive calls             36  rows processed          19  recursive calls             38  rows processed          20  recursive calls             40  rows processed          21  recursive calls             42  rows processed          22  recursive calls             44  rows processed          23  recursive calls             46  rows processed          24  recursive calls             48  rows processed          25  recursive calls             50  rows processed          26  recursive calls             52  rows processed          27  recursive calls             54  rows processed          28  recursive calls             56  rows processed          29  recursive calls             58  rows processed          30  recursive calls             60  rows processed          31  recursive calls             62  rows processed          32  recursive calls             64  rows processed          33  recursive calls             66  rows processed          34  recursive calls             68  rows processed          35  recursive calls             70  rows processed          36  recursive calls             72  rows processed          37  recursive calls             74  rows processed          38  recursive calls             76  rows processed          39  recursive calls             78  rows processed          40  recursive calls             80  rows processed          41  recursive calls             82  rows processed          42  recursive calls             84  rows processed          43  recursive calls             86  rows processed          44  recursive calls             88  rows processed          45  recursive calls             90  rows processed          46  recursive calls             92  rows processed          47  recursive calls             94  rows processed          48  recursive calls             96  rows processed          49  recursive calls             98  rows processed          50  recursive calls            100  rows processed          51  recursive calls            102  rows processed          52  recursive calls            104  rows processed          53  recursive calls            106  rows processed          54  recursive calls            108  rows processed          55  recursive calls            110  rows processed          56  recursive calls            112  rows processed          57  recursive calls            114  rows processed          58  recursive calls            116  rows processed          59  recursive calls            118  rows processed          60  recursive calls            120  rows processed          61  recursive calls            122  rows processed          62  recursive calls            124  rows processed          63  recursive calls            126  rows processed          64  recursive calls            128  rows processed          65  recursive calls            130  rows processed          66  recursive calls            132  rows processed          67  recursive calls            134  rows processed          68  recursive calls            136  rows processed          69  recursive calls            138  rows processed          70  recursive calls            140  rows processed          71  recursive calls            142  rows processed          72  recursive calls            144  rows processed          73  recursive calls            146  rows processed          74  recursive calls            148  rows processed          76  recursive calls            150  rows processed          77  recursive calls            152  rows processed          78  recursive calls            154  rows processed          79  recursive calls            156  rows processed          80  recursive calls            158  rows processed          81  recursive calls            160  rows processed          82  recursive calls            162  rows processed          83  recursive calls            164  rows processed          84  recursive calls            166  rows processed          86  recursive calls            168  rows processed          87  recursive calls            170  rows processed          88  recursive calls            172  rows processed          90  recursive calls            174  rows processed          91  recursive calls            176  rows processed          93  recursive calls            178  rows processed          94  recursive calls            180  rows processed          95  recursive calls            182  rows processed          96  recursive calls            184  rows processed          98  recursive calls            186  rows processed          99  recursive calls            188  rows processed         100  recursive calls            190  rows processed         102  recursive calls            192  rows processed         103  recursive calls            194  rows processed         104  recursive calls            196  rows processed         105  recursive calls            198  rows processed         106  recursive calls            200  rows processed         107  recursive calls            202  rows processed         108  recursive calls            204  rows processed         110  recursive calls            206  rows processed         112  recursive calls            208  rows processed         113  recursive calls            210  rows processed         114  recursive calls            212  rows processed         115  recursive calls            214  rows processed         116  recursive calls            216  rows processed         118  recursive calls            218  rows processed         119  recursive calls            220  rows processed         120  recursive calls            222  rows processed         121  recursive calls            224  rows processed         122  recursive calls            226  rows processed         123  recursive calls            228  rows processed         124  recursive calls            230  rows processed         125  recursive calls            232  rows processed         126  recursive calls            234  rows processed         127  recursive calls            236  rows processed         128  recursive calls            238  rows processed         129  recursive calls            240  rows processed         130  recursive calls            242  rows processed         132  recursive calls            244  rows processed         133  recursive calls            246  rows processed         134  recursive calls            248  rows processed         135  recursive calls            250  rows processed         136  recursive calls            252  rows processed         137  recursive calls            254  rows processed         138  recursive calls            256  rows processed         139  recursive calls            258  rows processed         140  recursive calls            260  rows processed         141  recursive calls            262  rows processed         142  recursive calls            264  rows processed         143  recursive calls            266  rows processed         144  recursive calls            268  rows processed         145  recursive calls            270  rows processed         146  recursive calls            272  rows processed         147  recursive calls            274  rows processed         148  recursive calls            276  rows processed         150  recursive calls            278  rows processed         151  recursive calls            280  rows processed         152  recursive calls            282  rows processed         153  recursive calls            284  rows processed         154  recursive calls            286  rows processed         155  recursive calls            288  rows processed         156  recursive calls            290  rows processed         157  recursive calls            292  rows processed         158  recursive calls            294  rows processed         159  recursive calls            296  rows processed         160  recursive calls            298  rows processed         161  recursive calls            300  rows processed         162  recursive calls            302  rows processed         164  recursive calls            304  rows processed         165  recursive calls            306  rows processed         166  recursive calls            308  rows processed         167  recursive calls            310  rows processed         168  recursive calls            312  rows processed         169  recursive calls            314  rows processed         170  recursive calls            316  rows processed         171  recursive calls            318  rows processed         172  recursive calls            320  rows processed         173  recursive calls            322  rows processed         174  recursive calls            324  rows processed         176  recursive calls            326  rows processed         177  recursive calls            328  rows processed         178  recursive calls            330  rows processed         179  recursive calls            332  rows processed         180  recursive calls            334  rows processed         181  recursive calls            336  rows processed         183  recursive calls            338  rows processed         184  recursive calls            340  rows processed         186  recursive calls            342  rows processed         187  recursive calls            344  rows processed         188  recursive calls            346  rows processed         189  recursive calls            348  rows processed         190  recursive calls            350  rows processed         192  recursive calls            352  rows processed         193  recursive calls            354  rows processed         194  recursive calls            356  rows processed         195  recursive calls            358  rows processed         196  recursive calls            360  rows processed         197  recursive calls            362  rows processed         198  recursive calls            364  rows processed         199  recursive calls            366  rows processed         200  recursive calls            368  rows processed         201  recursive calls            370  rows processed         202  recursive calls            372  rows processed         203  recursive calls            374  rows processed         204  recursive calls            376  rows processed         205  recursive calls            378  rows processed         206  recursive calls            380  rows processed         207  recursive calls            382  rows processed         208  recursive calls            384  rows processed         210  recursive calls            386  rows processed         211  recursive calls            388  rows processed         213  recursive calls            390  rows processed         215  recursive calls            392  rows processed         216  recursive calls            394  rows processed         217  recursive calls            396  rows processed         218  recursive calls            398  rows processed         219  recursive calls            400  rows processed         220  recursive calls            402  rows processed         221  recursive calls            404  rows processed         222  recursive calls            406  rows processed         223  recursive calls            408  rows processed         224  recursive calls            410  rows processed         226  recursive calls            412  rows processed         227  recursive calls            414  rows processed         228  recursive calls            416  rows processed         229  recursive calls            418  rows processed         230  recursive calls            420  rows processed         231  recursive calls            422  rows processed         232  recursive calls            424  rows processed         233  recursive calls            426  rows processed         234  recursive calls            428  rows processed         235  recursive calls            430  rows processed         237  recursive calls            432  rows processed         238  recursive calls            434  rows processed         239  recursive calls            436  rows processed         240  recursive calls            438  rows processed         241  recursive calls            440  rows processed         242  recursive calls            442  rows processed         243  recursive calls            444  rows processed         244  recursive calls            446  rows processed         245  recursive calls            448  rows processed         246  recursive calls            450  rows processed         247  recursive calls            452  rows processed         248  recursive calls            454  rows processed         250  recursive calls            456  rows processed         251  recursive calls            458  rows processed         252  recursive calls            460  rows processed         254  recursive calls            462  rows processed         255  recursive calls            464  rows processed         256  recursive calls            466  rows processed         258  recursive calls            468  rows processed         259  recursive calls            470  rows processed         260  recursive calls            472  rows processed         261  recursive calls            474  rows processed         262  recursive calls            476  rows processed         263  recursive calls            478  rows processed         264  recursive calls            480  rows processed         265  recursive calls            482  rows processed         266  recursive calls            484  rows processed         267  recursive calls            486  rows processed         269  recursive calls            488  rows processed         270  recursive calls            490  rows processed         272  recursive calls            492  rows processed         273  recursive calls            494  rows processed         274  recursive calls            496  rows processed         275  recursive calls            498  rows processed         276  recursive calls            500  rows processed         277  recursive calls            502  rows processed         278  recursive calls            504  rows processed         279  recursive calls            506  rows processed         281  recursive calls            508  rows processed         282  recursive calls            510  rows processed

--//取出数字

$ egrep 'recursive calls|rows processed' az.txt | paste - - | cut -c9-11,37-40

SCOTT@book> create table t1 ( a number ,b number);

Table created.

--//改写成inert插入表t1.

SELECT id2, r, rp   FROM (  SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp             FROM t1         ORDER BY a) WHERE r - rp >= 2; ID2|R|RP 75|76|74 84|86|84 87|90|88 89|93|91 93|98|96 96|102|100 103|110|108 104|112|110 109|118|116 122|132|130 139|150|148 152|164|162 163|176|174 169|183|181 171|186|184 176|192|190 193|210|208 195|213|211 196|215|213 206|226|224 216|237|235 228|250|248 231|254|252 234|258|256 244|269|267 246|272|270 254|281|279

4.总结:

乱,好多概念不好理解.