[20180626]函式與標量子查詢14.txt

[20180626]函式與標量子查詢14.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.11.2.0.4是1024.
--//昨天看連結http://www.cnblogs.com/kerrycode/p/9223093.html =>ORACLE中Scalar subquery Caching的hash table大小測試淺析
--//裡面建立一個函式,很容易確定呼叫函式的次數.重複測試,另外前面的測試有點慢,看看我現在的測試是否快一點.^_^ .

1.環境:
SCOTT@book> @ &r/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

create or replace function f( x in varchar2 ) return number
as
begin
        dbms_application_info.set_client_info(userenv('client_info')+1 );
        return length(x);
end;
/

2.建立測試指令碼:
create table t as select rownum id1,mod(rownum-1,10000)+1 id2 from dual connect by level<=20000;
--//分析表略.
create table t1 ( a number ,b number);
--//欄位a 表示呼叫函式次數.

--//建立指令碼cy.txt
exec dbms_application_info.set_client_info(0);
set term off
exec :x := &&1;
select count(distinct f_id2) from (select id2,(select f(id2) from dual) as f_id2 from t where id2 <= :x );
set term on
insert into t1 values (userenv('client_info') ,:x) ;
commit ;

--//建立shell指令碼cy.sh:
#! /bin/bash
sqlplus -s -l scott/book <<EOF >> hz.txt
variable x number;
$(seq 10000 | xargs -I{} echo @cy.txt {})
quit
EOF

3.另外的測試指令碼:
--//自己也寫PL/SQL指令碼.僅僅為了學習...
declare
x number;
  begin
   for i in 1..10000 loop
     dbms_application_info.set_client_info(0);
     select count(distinct f_id2) into x from (select id2,(select f(id2) from dual) as f_id2 from t where id2 <= i ); 
     insert into t1 values (userenv('client_info') ,i) ;
     commit ;
   end loop;
end;
/

--//在家裡的12c for windows測試看看:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

select max(id2) from (
SELECT id2, r, rp
  FROM (  SELECT b id2, a r, LAG (a) OVER (ORDER BY b) rp
            FROM t1
        ORDER BY b)
WHERE r - rp = 1 order by id2);

  MAX(ID2)
----------
      9234
--//說明:我前面的測試id2 記錄的數量加倍,而這次記錄的是變數:X,這樣不要在除以2.

SELECT id2, r, rp
  FROM (  SELECT b  id2, a r, LAG (a) OVER (ORDER BY b) rp
            FROM t1
        ORDER BY b)
WHERE r - rp >= 2 and id2<=9234 ;
...
8210 rows selected.

--// 9234-8210 = 1024 ,與前面測試一樣.

4.驗證測試是否正確:
--//執行以上指令碼.cy.sh,等....

select max(id2) from (
SELECT id2, r, rp
  FROM (  SELECT b id2, a r, LAG (a) OVER (ORDER BY b) rp
            FROM t1
        ORDER BY b)
WHERE r - rp = 1 order by id2);
    MAX(ID2)
------------
        9234
--//9234還會進入buckupset,後面的數字帶入都是出現hash 衝突的情況.

SELECT id2, r, rp
  FROM (  SELECT b id2, a r, LAG (a) OVER (ORDER BY b) rp
            FROM t1
        ORDER BY b)
WHERE r - rp >= 2 and id2<=9234 ;
...

8210 rows selected.
 
--//輸出太長,一共8210個值,略,這個結果就是在1-9234之間,出現hash衝突的值.
select count(*) from
(SELECT id2, r, rp
  FROM (  SELECT b id2, a r, LAG (a) OVER (ORDER BY b) rp
            FROM t1
        ORDER BY b)
WHERE r - rp >= 2 and id2<=9234 );

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

--//9234-8210 = 1024 ,可以看出11.2.0.4標量子查詢的雜湊表大小是1024個buckets.
--//後面的測試不再做了.