[20180602]函式與標量子查詢3.txt
[20180602]函式與標量子查詢3.txt
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html連結,裡面提到:
通俗來將，當使用標量子查詢的時候，ORACLE會將子查詢結果快取在雜湊表中， 如果後續的記錄出現同樣的值，最佳化器透過快取在雜湊
表中的值，判斷重複值不用重複呼叫函式，直接使用上次計算結果即可。從而減少呼叫函式次數，從而達到最佳化效能的效果。另外在
ORACLE 10和11中， 雜湊表只包含了255個Buckets，也就是說它能儲存255個不同值，如果超過這個範圍，就會出現雜湊衝突，那些出現
雜湊衝突的值就會重複呼叫函式，即便如此，依然能達到大幅改善效能的效果。
--//昨天的測試有一些問題自己沒有很好的理解.晚上看連結http://blog.itpub.net/267265/viewspace-2144765/
--//當時可以發現,如果鄰近的值一樣,函式呼叫僅僅1次.
--//這樣才明白oracle如何操作的.還是透過例子說明問題.
1.環境:
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
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(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.我前面的測試帶入引數48,75存在hash衝突.
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID ID1 ID2 S
------------------ ---------- ---------- ----------
AAAaQxAAJAAAAC7AAv 48 48 48
AAAaQxAAJAAAAC7ABK 75 75 75
AAAaQxAAJAAAAC+AAv 48 48 48
AAAaQxAAJAAAAC+ABK 75 75 75
Elapsed: 00:00:19.82
SCOTT@test01p> set autot traceonly
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
Elapsed: 00:00:19.83
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 32 | 8 (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
....
4 rows processed
--//注:多執行幾次,這樣反應的遞迴呼叫結果比較準確.
--//(48+75+75)/10= 19.80
--//雜湊表只包含了255個Buckets，也就是說它能儲存255個不同值，
--//還真不好表達,我前面的遞規呼叫3次.sleep1(48) 1次,sleep1(75) 2次.
--//第1次,先執行sleep1(48),結果放入雜湊表,
--//第2次,然後執行sleep1(75),由於48與75存在hash衝突,只能重複呼叫函式sleep1(75),但是結果不放入雜湊表.
--//第3次,接著執行sleep1(48),這個已經存在雜湊表,不必呼叫sleep1(48).
--//第4次,執行sleep1(75),由於前面執行的結果不放入雜湊表,只能重複呼叫函式sleep1(75).
3.如果執行如下:
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual
union all
select 48 a from dual
union all
select 75 a from dual);
A S
---------- ----------
48 48
75 75
75 75
48 48
75 75
Elapsed: 00:00:19.83
SCOTT@test01p> set autot traceonly
--//再次執行:
Statistics
----------------------------------------------------------
3 recursive calls
--//(48+75+75)/10 = 19.8
--//從結果看sleep1(48) 2次,sleep1(75) 3次.
--//第1次,先執行sleep1(48),結果放入雜湊表,
--//第2次,然後執行sleep1(75),由於48與75存在hash衝突,只能重複呼叫函式sleep1(75),但是結果不放入雜湊表.
--//第3次,接著執行sleep1(75),由於前面執行sleep1(75),雖然結果不存在雜湊表,因為可以從前面的執行知道結果,不必呼叫sleep1(75).
--//說明: 也就是如果鄰近的值一樣,函式呼叫僅僅1次.
--//第4次,執行sleep1(48),由於結果存在雜湊表,不必呼叫sleep1(48).
--//第5次,執行sleep1(75),由於前面的查詢sleep1(48),並且sleep1(78)的結果不存在雜湊表,只能重複呼叫函式sleep1(75).
--//這樣sleep1(48) 1次,sleep1(75) 2次.按照這個模式分析,就知道呼叫測試.
select a ,(select sleep1(a) from dual) s from
(select 75 a from dual
union all
select 48 a from dual
union all
select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual);
A S
---------- ----------
75 75
48 48
48 48
75 75
75 75
Elapsed: 00:00:12.30
SCOTT@test01p> set autot traceonly
SCOTT@test01p> /
Statistics
----------------------------------------------------------
2 recursive calls
--//(48+75)/10 = 12.3
--//按照前面的模式分析,就知道呼叫測試.
--//從結果看sleep1(48) 1次,sleep1(75) 1次.
3.總結:
--//如果你仔細看連線http://blog.itpub.net/267265/viewspace-2144765/
--//可以發現與11g的不同.但是如果執行如下測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select rowid,t.*,(select sleep(id2) from dual) s from t ;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54u1r6skg6pt7, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t
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 | | | 514 (100)| | 510 |00:00:00.01 | 10 |
| 1 | FAST DUAL | | 282 | 1 | | 2 (0)| 00:00:01 | 282 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T | 1 | 510 | 4080 | 4 (0)| 00:00:01 | 510 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
20 rows selected.
--//id=2 fast dual 執行282次.說明遞迴才282次.還是許多問題不理解,有計劃還是在11g下測試看看.
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html連結,裡面提到:
通俗來將，當使用標量子查詢的時候，ORACLE會將子查詢結果快取在雜湊表中， 如果後續的記錄出現同樣的值，最佳化器透過快取在雜湊
表中的值，判斷重複值不用重複呼叫函式，直接使用上次計算結果即可。從而減少呼叫函式次數，從而達到最佳化效能的效果。另外在
ORACLE 10和11中， 雜湊表只包含了255個Buckets，也就是說它能儲存255個不同值，如果超過這個範圍，就會出現雜湊衝突，那些出現
雜湊衝突的值就會重複呼叫函式，即便如此，依然能達到大幅改善效能的效果。
--//昨天的測試有一些問題自己沒有很好的理解.晚上看連結http://blog.itpub.net/267265/viewspace-2144765/
--//當時可以發現,如果鄰近的值一樣,函式呼叫僅僅1次.
--//這樣才明白oracle如何操作的.還是透過例子說明問題.
1.環境:
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
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(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.我前面的測試帶入引數48,75存在hash衝突.
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID ID1 ID2 S
------------------ ---------- ---------- ----------
AAAaQxAAJAAAAC7AAv 48 48 48
AAAaQxAAJAAAAC7ABK 75 75 75
AAAaQxAAJAAAAC+AAv 48 48 48
AAAaQxAAJAAAAC+ABK 75 75 75
Elapsed: 00:00:19.82
SCOTT@test01p> set autot traceonly
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
Elapsed: 00:00:19.83
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 32 | 8 (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
....
4 rows processed
--//注:多執行幾次,這樣反應的遞迴呼叫結果比較準確.
--//(48+75+75)/10= 19.80
--//雜湊表只包含了255個Buckets，也就是說它能儲存255個不同值，
--//還真不好表達,我前面的遞規呼叫3次.sleep1(48) 1次,sleep1(75) 2次.
--//第1次,先執行sleep1(48),結果放入雜湊表,
--//第2次,然後執行sleep1(75),由於48與75存在hash衝突,只能重複呼叫函式sleep1(75),但是結果不放入雜湊表.
--//第3次,接著執行sleep1(48),這個已經存在雜湊表,不必呼叫sleep1(48).
--//第4次,執行sleep1(75),由於前面執行的結果不放入雜湊表,只能重複呼叫函式sleep1(75).
3.如果執行如下:
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual
union all
select 48 a from dual
union all
select 75 a from dual);
A S
---------- ----------
48 48
75 75
75 75
48 48
75 75
Elapsed: 00:00:19.83
SCOTT@test01p> set autot traceonly
--//再次執行:
Statistics
----------------------------------------------------------
3 recursive calls
--//(48+75+75)/10 = 19.8
--//從結果看sleep1(48) 2次,sleep1(75) 3次.
--//第1次,先執行sleep1(48),結果放入雜湊表,
--//第2次,然後執行sleep1(75),由於48與75存在hash衝突,只能重複呼叫函式sleep1(75),但是結果不放入雜湊表.
--//第3次,接著執行sleep1(75),由於前面執行sleep1(75),雖然結果不存在雜湊表,因為可以從前面的執行知道結果,不必呼叫sleep1(75).
--//說明: 也就是如果鄰近的值一樣,函式呼叫僅僅1次.
--//第4次,執行sleep1(48),由於結果存在雜湊表,不必呼叫sleep1(48).
--//第5次,執行sleep1(75),由於前面的查詢sleep1(48),並且sleep1(78)的結果不存在雜湊表,只能重複呼叫函式sleep1(75).
--//這樣sleep1(48) 1次,sleep1(75) 2次.按照這個模式分析,就知道呼叫測試.
select a ,(select sleep1(a) from dual) s from
(select 75 a from dual
union all
select 48 a from dual
union all
select 48 a from dual
union all
select 75 a from dual
union all
select 75 a from dual);
A S
---------- ----------
75 75
48 48
48 48
75 75
75 75
Elapsed: 00:00:12.30
SCOTT@test01p> set autot traceonly
SCOTT@test01p> /
Statistics
----------------------------------------------------------
2 recursive calls
--//(48+75)/10 = 12.3
--//按照前面的模式分析,就知道呼叫測試.
--//從結果看sleep1(48) 1次,sleep1(75) 1次.
3.總結:
--//如果你仔細看連線http://blog.itpub.net/267265/viewspace-2144765/
--//可以發現與11g的不同.但是如果執行如下測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select rowid,t.*,(select sleep(id2) from dual) s from t ;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54u1r6skg6pt7, child number 0
-------------------------------------
select rowid,t.*,(select sleep(id2) from dual) s from t
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 | | | 514 (100)| | 510 |00:00:00.01 | 10 |
| 1 | FAST DUAL | | 282 | 1 | | 2 (0)| 00:00:01 | 282 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T | 1 | 510 | 4080 | 4 (0)| 00:00:01 | 510 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
20 rows selected.
--//id=2 fast dual 執行282次.說明遞迴才282次.還是許多問題不理解,有計劃還是在11g下測試看看.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2155558/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180602]函式與標量子查詢4.txt函式
- [20180625]函式與標量子查詢13(補充)函式
- 標量子查詢
- [20180612]函式與標量子查詢10.txt函式
- [20180626]函式與標量子查詢14.txt函式
- 20180601]函式與標量子查詢2.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- 標量子查詢（二）
- 標量子查詢(一)
- 用WITH…AS改寫標量子查詢
- 都是標量子查詢惹的禍
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- 用LEFT JOIN優化標量子查詢優化
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- 帶彙總的標量子查詢改寫
- 標量子查詢優化（用group by 代替distinct）優化
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- MySQL之集合函式與分組查詢MySql函式
- Solr複雜查詢一：函式查詢Solr函式
- pytest標記：查詢測試策略、標記測試函式函式
- 影響Oracle標量子查詢效能的三個因素Oracle
- 宣告與函式、函式指標函式指標
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- [20150709]慎用標量子查詢.txt
- PostgreSQL 查詢替換函式SQL函式
- Solr的函式查詢（FunctionQuery）Solr函式Function
- 子串查詢函式strstr函式
- [20200325]慎用標量子查詢.txt
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- Vlookup大叔與一對多查詢（Excel函式集團）Excel函式
- 二分查詢 - 手寫模板與自帶函式函式
- Java-MySql-函式、多表查詢JavaMySql函式
- [Mysql 查詢語句]——集合函式MySql函式
- SQL查詢中用到的函式SQL函式
- iPhone查詢序列號生成函式iPhone函式