[20181130]hash衝突導致查詢緩慢.txt
[20181130]hash衝突導致查詢緩慢.txt
--//昨天看了連結演示了Shrink Space導致
--//執行語句緩慢的情況,我自己重複測試,實際上這樣發生的機率還是很低的,我個人認為,至於Shrink Space是否好壞,
--//我個人還是根據實際的情況來確定.
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
SCOTT@book> rename emp to empxxx;
Table renamed.
--//作者測試的表名與scoot使用者下衝突,我先修改原系統的表名.
2.建立測試指令碼:
create table emp(
dept_no not null,
sal,
emp_no not null,
padding,
constraint e_pk primary key(emp_no)
)
as
with generator as (
select null
from dual
connect by
level <= 1e4 -- > comment to avoid wordpress format issue
)
select
mod(rownum,6),
rownum,
rownum,
rpad('x',60)
from
generator v1,
generator v2
where
rownum <= 2e4 -- > comment to avoid wordpress format issue
;
insert into emp values(432, 20001, 20001, rpad('x',60));
delete /*+ full(emp) */ from emp where emp_no <= 1000; -- > comment to avoid wordpress format issue
--//注:執行時要刪除後面的註解.不然報錯.作者應該把分號放在最後才能正常執行ok.
commit;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'EMP',
method_opt => 'for all columns size 1'
);
end;
/
3.測試:
SCOTT@book> alter session set statistics_level = all;
Session altered.
select
/*+ gather_plan_statistics pre-shrink */
count(*)
from (
select /*+ no_merge */
outer.*
from
emp outer
where
outer.sal > (
select /*+ no_unnest */
avg(inner.sal)
from
emp inner
where
inner.dept_no = outer.dept_no
)
)
;
COUNT(*)
----------
9998
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9bkx1f5cpcv14, child number 1
-------------------------------------
select /*+ gather_plan_statistics pre-shrink */
count(*) from ( select /*+ no_merge */
outer.* from emp outer where
outer.sal > ( select /*+ no_unnest */
avg(inner.sal) from
emp inner where
inner.dept_no = outer.dept_no
) )
Plan hash value: 322796046
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 569 (100)| | 1 |00:00:00.04 | 1912 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.04 | 1912 |
| 2 | VIEW | | 1 | 143 | | 569 (1)| 00:00:07 | 9998 |00:00:00.04 | 1912 |
|* 3 | FILTER | | 1 | | | | | 9998 |00:00:00.03 | 1912 |
| 4 | TABLE ACCESS FULL | EMP | 1 | 20001 | 156K| 71 (0)| 00:00:01 | 20001 |00:00:00.01 | 239 |
| 5 | SORT AGGREGATE | | 7 | 1 | 8 | | | 7 |00:00:00.02 | 1673 |
|* 6 | TABLE ACCESS FULL| EMP | 7 | 2857 | 22856 | 71 (0)| 00:00:01 | 20001 |00:00:00.01 | 1673 |
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / OUTER@SEL$2
5 - SEL$3
6 - SEL$3 / INNER@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OUTER"."SAL">)
6 - filter("INNER"."DEPT_NO"=:B1)
--//你可以發現內層迴圈掃描emp表7次.因為有7個部門.也就是oracle快取了執行過.雖然最後1個記錄是dept_no=432存在衝突,僅僅1條,
--//影響不大.
4.測試Shrink Space後:
SCOTT@book> alter table emp enable row movement;
Table altered.
SCOTT@book> alter table emp shrink space compact;
Table altered.
SCOTT@book> select * from emp where rownum<=4;
DEPT_NO SAL EMP_NO PADDING
---------- ---------- ---------- ---------
432 20001 20001 x
4 19978 19978 x
5 19979 19979 x
0 19980 19980 x
--//這樣dept_no=432被移動到前面.
select
/*+ gather_plan_statistics post-shrink */
count(*)
from (
select /*+ no_merge */
outer.*
from emp outer
where outer.sal >
(
select /*+ no_unnest */ avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
)
;
COUNT(*)
----------
9498
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gx7xb7rhfd2zf, child number 0
-------------------------------------
select /*+ gather_plan_statistics post-shrink */
count(*) from ( select /*+ no_merge */
outer.* from emp outer where outer.sal >
( select /*+ no_unnest */ avg(inner.sal)
from emp inner where
inner.dept_no = outer.dept_no ) )
Plan hash value: 322796046
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 569 (100)| | 1 |00:00:03.43 | 783K|
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:03.43 | 783K|
| 2 | VIEW | | 1 | 143 | | 569 (1)| 00:00:07 | 9498 |00:00:03.43 | 783K|
|* 3 | FILTER | | 1 | | | | | 9498 |00:00:03.43 | 783K|
| 4 | TABLE ACCESS FULL | EMP | 1 | 20001 | 156K| 71 (0)| 00:00:01 | 19001 |00:00:00.01 | 247 |
| 5 | SORT AGGREGATE | | 3172 | 1 | 8 | | | 3172 |00:00:03.42 | 783K|
|* 6 | TABLE ACCESS FULL| EMP | 3172 | 2857 | 22856 | 71 (0)| 00:00:01 | 10M|00:00:02.71 | 783K|
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / OUTER@SEL$2
5 - SEL$3
6 - SEL$3 / INNER@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OUTER"."SAL">)
6 - filter("INNER"."DEPT_NO"=:B1)
--//注:我的測試機器比較快,沒有作者測試的9秒,僅僅接近4秒完成,不過還是看出比原來執行慢.注意看id=6,迴圈執行次數是3172.
--//也就是dept_no=432與dept_no=0,1,2,3,4,5存在hash衝突,這樣每次執行內層迴圈dept_no=:B1是都要重複呼叫.
SCOTT@book> select dept_no,count(*) from emp group by dept_no order by 1;
DEPT_NO COUNT(*)
---------- ----------
0 3167
1 3167
2 3167
3 3166
4 3166
5 3167
432 1
7 rows selected.
--//假設與dept_no=1出現hash衝突.
--//dept_no=432 迴圈1次
--//dept_no=0 迴圈1次
--//dept_no=1 迴圈3167次
--//dept_no=2 迴圈1次
--//dept_no=3 迴圈1次
--//dept_no=4 迴圈1次
--//dept_no=5 迴圈1次
--//這樣累加: 1+1+3167+1+1+1+1 = 3173 ,不對相差1.我做了一些細節,證明hash衝突是dept_no=4.
5.其它有趣的測試:
--//執行如下,只要dept_no in 裡面包括4,432查詢就很慢(至少查詢3個部門).就會有點慢.
--//也就是證明hash衝突的是dept_no=4.
select
/*+ gather_plan_statistics post-shrink */
count(*)
from (
select /*+ no_merge */
outer.*
from emp outer
where outer.sal >
(
select /*+ no_unnest */ avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
) where dept_no in (432,4,5)
;
--//如果你執行如下,你會發現執行很快:
select
/*+ gather_plan_statistics post-shrink */
count(*)
from (
select /*+ no_merge */
outer.*
from emp outer
where outer.sal >
(
select /*+ no_unnest */ avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
) where dept_no in (432,4)
;
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9v9984wd6k9t5, child number 0
-------------------------------------
select /*+ gather_plan_statistics post-shrink */
count(*) from ( select /*+ no_merge */
outer.* from emp outer where outer.sal >
( select /*+ no_unnest */ avg(inner.sal)
from emp inner where
inner.dept_no = outer.dept_no ) ) where dept_no
in (432,4)
Plan hash value: 322796046
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 214 (100)| | 1 |00:00:00.01 | 741 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.01 | 741 |
| 2 | VIEW | | 1 | 143 | | 214 (1)| 00:00:03 | 1583 |00:00:00.01 | 741 |
|* 3 | FILTER | | 1 | | | | | 1583 |00:00:00.01 | 741 |
|* 4 | TABLE ACCESS FULL | EMP | 1 | 5715 | 45720 | 71 (0)| 00:00:01 | 3167 |00:00:00.01 | 247 |
| 5 | SORT AGGREGATE | | 2 | 1 | 8 | | | 2 |00:00:00.01 | 494 |
|* 6 | TABLE ACCESS FULL| EMP | 2 | 2857 | 22856 | 71 (0)| 00:00:01 | 3167 |00:00:00.01 | 494 |
------------------------------------------------------------------------------------------------------------------------
--//迴圈僅僅2次.這是因為參考連結:
http://blog.itpub.net/267265/viewspace-2155927/
https://blogs.oracle.com/oraclemagazine/on-caching-and-evangelizing-sql
-//摘要如下:
When you're using a scalar subquery, Oracle Database will set up a small in-memory hash table for the subquery and its
results each time it runs the query. So, when you run the previous query, Oracle Database sets up in memory a hash table
that looks like this:
Oracle Database will use this hash table to remember the scalar subquery and the inputs to it—just :DEPTNO in this case
—and the output from it. At the beginning of every query execution, this cache is empty, but suppose you run the query
and the first PROJECTS row you retrieve has a DEPTNO value of 10. Oracle Database will assign the number 10 to a hash
value between 1 and 255 (the size of the hash table cache in Oracle Database 10g and Oracle Database 11g currently) and
will look in that hash table slot to see if the answer exists. In this case, it will not, so Oracle Database must run
the scalar subquery with the input of 10 to get the answer. If that answer (count) is 42, the hash table may look
something like this:
//注:補充說明我測試10.2.0.5,buckets=512而不是255.有機會測試11.2.0.4的情況.
Select count(*) from emp where emp.deptno = :deptno
:deptno Count(*)
You'll have saved the DEPTNO value of 10 and the answer (count) of 42 in some slot—probably not the first or last slot,
but whatever slot the hash value 10 is assigned to. Now suppose the second row you get back from the PROJECTS table
includes a DEPTNO value of 20. Oracle Database will again look in the hash table after assigning the value 20, and it
will discover "no result in the cache yet." So it will run the scalar subquery, get the result, and put it into the hash
table cache. Now the cache may look like this:
Select count(*) from emp where emp.deptno = :deptno
:deptno Count(*)
Select count(*) from emp where emp.deptno = :deptno
:deptno Count(*)
… …
10 42
Now suppose the query returns a third row and it again includes a DEPTNO value of 10. This time, Oracle Database will
see DEPTNO = 10, find that it already has that value in the hash table cache, and will simply return 42 from the cache
instead of executing the scalar subquery. In fact, it will never have to run that scalar subquery for the DEPTNO values
of 10 or 20 again for that query—it will already have the answer.
What happens if the number of unique DEPTNO values exceeds the size of the hash table? What if there are more than 255
values? Or, more generally, if more than one DEPTNO value is assigned to the same slot in the hash table, what happens
in a hash collision?
The answer is the same for all these questions and is rather simple: Oracle Database will not be able to cache the
second or nth value to that slot in the hash table. For example, what if the third row returned by the query contains
the DEPTNO = 30 value? Further, suppose that DEPTNO = 30 is to be assigned to exactly the same hash table slot as DEPTNO
= 10. The database won't be able to effectively cache DEPTNO = 30 in this case—the value will never make it into the
hash table. It will, however, be "partially cached." Oracle Database still has the hash table with all the previous
executions, but it also keeps the last scalar subquery result it had "next to" the hash table. That is, if the fourth
row also includes a DEPTNO = 30 value, Oracle Database will discover that the result is not in the hash table but is
"next to" the hash table, because the last time it ran the scalar subquery, it was run with an input of 30. On the other
hand, if the fourth row includes a DEPTNO = 40 value, Oracle Database will run the scalar subquery with the DEPTNO = 40
value (because it hasn't seen that value yet during this query execution) and overwrite the DEPTNO = 30 result. The next
time Oracle Database sees DEPTNO = 30 in the result set, it'll have to run that scalar subquery again.
--//答案在這一段落中,如果查詢結果臨近它會從前面的查詢獲得結果,而不用進入迴圈.我僅僅查詢dept_no in (432,4),這樣後面全部是
--//dept_no=4返回,這樣可以從臨近的查詢獲得結果.實際上你看作者的表設計就知道答案:
SCOTT@book> select * from emp where rownum<=10;
DEPT_NO SAL EMP_NO PADDING
---------- ---------- ---------- --------
432 20001 20001 x
4 19978 19978 x
5 19979 19979 x
0 19980 19980 x
1 19981 19981 x
2 19982 19982 x
3 19983 19983 x
4 19984 19984 x
5 19985 19985 x
0 19986 19986 x
10 rows selected.
--//dept_no記錄不是聚集在一起的.
總結:
--//實際上這個例子我記憶在作者<基於成本的最佳化>的書中提到過.當時想作者如何知道那個數存在衝突.感覺作者很厲害.
--//另外寫一篇blog猜測那些hash存在衝突的.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2222228/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Mysql索引型別建立錯誤導致SQL查詢緩慢MySql索引型別
- Oracle資料庫非同步IO導致查詢響應緩慢Oracle資料庫非同步
- 查詢maven衝突Maven
- 統計資訊過期導致SQL進行NESTED LOOPS查詢緩慢SQLOOP
- Servlet版本衝突導致頁面404Servlet
- dba_jobs_running查詢緩慢
- MySQL:RR模式下insert也可能導致查詢慢MySql模式
- 硬體或軟體衝突導致當機
- 並行查詢緩慢的問題分析並行
- 查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢
- EM自動任務導致資料庫緩慢資料庫
- 關於hash衝突的解決
- [20140807]hash_value sql_id衝突.txtSQL
- springboot衝突導致的發版失敗Spring Boot
- RAC環境關閉CLUSTER後導致連線緩慢
- Sql Server 聯合查詢的排序規則衝突SQLServer排序
- hash解決衝突的方法優缺點
- Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升MySql索引優化
- SQL調優--表統計資訊未及時更新導致查詢超級慢SQL
- 域名解析導致資料庫連線緩慢(hosts :files dns)資料庫DNS
- DNS導致資料庫登入緩慢的問題解決DNS資料庫
- sysbench花式採坑之二:自增值導致的主鍵衝突
- 效能分析(7)- 未利用系統快取導致 I/O 緩慢案例快取
- PostgreSQL、KingBase 資料庫 ORDER BY LIMIT 查詢緩慢案例SQL資料庫MIT
- MySQL 慢查詢MySql
- MySQL慢查詢MySql
- Redis 慢查詢Redis
- 由於回收站存在大量物件導致查詢表空間使用率較慢物件
- 等於NULL的查詢條件導致查詢結果不正確Null
- 解決記錄日誌導致VS2013緩慢的問題
- 兩個IO管腳佈局衝突導致Vivado不能生成bit檔案
- android studio 引用module 導致的v4包衝突的解決Android
- mongodb慢查詢分析MongoDB
- mysqldumpslow慢查詢MySql
- 連結伺服器查詢導致的阻塞伺服器
- mybatis lambdaQuery 查詢條件導致空指標MyBatis指標
- 程式碼審查中的暴力衝突
- Microsoft承認Windows由於永久性記憶體而導致啟動緩慢ROSWindows記憶體