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

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

[20180607]函式與標量子查詢8.txt

相關文章