[20180612]函式與標量子查詢10.txt

[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