影響Oracle標量子查詢效能的三個因素
在oracle中返回單行單列的子查詢稱之為標量子查詢，標量子查詢大多數情況出現在select後面，
而標量子查詢的效率跟以下下幾個因素密切相關，
1、主查詢返回資料量的大小
2、子查詢在關聯列是否有高效的索引
3、主查詢關聯列的唯一值高低
下面測試這些因素對標量子查詢效能的影響
建立測試表
SQL> create table test1 as select * from dba_objects; Table created. SQL> create table test3 as select * from dba_objects; Table created.
首先測試標量子查詢的效能和主查詢返回資料量大小有關
SQL> select count(1) from test1 where owner='SYS';
COUNT(1)
----------
30811
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:02:01.01
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
196 recursive calls
0 db block gets
31995626 consistent gets
0 physical reads
0 redo size
1315596 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
30851 rows processed
接下來更改owner減少主查詢返回的資料量
SQL> select owner,count(object_id) from test3 group by owner;
OWNER COUNT(OBJECT_ID)
------------------------------ ----------------
OWBSYS_AUDIT 12
MDSYS 1509
PUBLIC 27702
OUTLN 9
CTXSYS 366
OLAPSYS 719
FLOWS_FILES 12
OWBSYS 2
HR 34
SYSTEM 529
ORACLE_OCM 8
EXFSYS 310
APEX_030200 2406
SCOTT 8
PM 27
OE 127
DBSNMP 57
ORDSYS 2532
ORDPLUGINS 10
SYSMAN 3491
SH 306
IX 55
APPQOSSYS 3
XDB 844
ORDDATA 248
BI 8
SYS 30811
WMSYS 316
SI_INFORMTN_SCHEMA 8
29 rows selected.
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SCOTT';
22 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23901 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
從執行計劃來看差別十分明顯，當主查詢的返回資料量大的時候嚴重影響效能。
測試標量子查詢的效能和子查詢是否有高效索引有關
檢視錶test3上是否有高效的索引
select index_name,column_name from dba_ind_columns where table_name='TEST3'; SQL> select index_name,column_name from dba_ind_columns where table_name='TEST3'; INDEX_NAME COLUMN_NAME ------------------------------ IND_TEST3_NAME OBJECT_NAME
從上面的查詢結果來看並沒有合適的索引，現在建立索引再執行第一條SQL檢視執行效率；
建立索引，
create index ind_text3_id on test3(object_id);
SQL> create index ind_text3_id on test3(object_id);
Index created.
select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:00:00.68
Execution Plan
----------------------------------------------------------
Plan hash value: 569210033
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST3 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEXT3_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."OBJECT_ID"=:B1)
3 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14058 consistent gets
156 physical reads
0 redo size
1315596 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30851 rows processed
從執行資訊上看，在子查詢表上建立了索引之後SQL效能得到了大幅度提高，
接下來測試主查詢關聯列唯一值對標量子查詢效能的影響。
先刪除掉在子查詢表上建立的索引。
drop index ind_text3_id; SQL> drop index ind_text3_id; Index dropped. --把SYS使用者下的object_id 都更新為同一個值。 update test1 set object_id =11 where owner='SYS'; SQL> update test1 set object_id =11 where owner='SYS'; 30851 rows updated. SQL> commit; Commit complete.
然後執行第一條SQL檢視執行效率。
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:00:00.40
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
196 recursive calls
0 db block gets
4176 consistent gets
0 physical reads
0 redo size
531163 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
30851 rows processed
可以看到當主查詢關聯列的唯一值很低的時候，標量子查詢的效率會很高。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31547066/viewspace-2744844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 標量子查詢
- 影響mysql效能的因素都有哪些MySql
- 影響HTTP效能的常見因素HTTP
- 影響MySQL效能的硬體因素MySql
- 標量子查詢（二）
- 標量子查詢(一)
- 哪些因素影響Java呼叫的效能？Java
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- Arraysize的設定以及對查詢效能的影響
- 影響MongoDB資料庫效能的幾個重要因素MongoDB資料庫
- 都是標量子查詢惹的禍
- 影響儲存網路效能的因素有哪些？
- 影響OLTP 系統效能的儲存因素解析
- 用WITH…AS改寫標量子查詢
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- 影響ORACLE優化器的相關因素Oracle優化
- 三個影響SQL Server效能關鍵點SQLServer
- 帶彙總的標量子查詢改寫
- 比特幣跌破5000美元的三大影響因素比特幣
- 影響資料檢索效率的幾個因素
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- 用LEFT JOIN優化標量子查詢優化
- 影響雲伺服器效能的主要因素有哪些？伺服器
- 標量子查詢優化（用group by 代替distinct）優化
- 查詢快取(query_cache)的影響快取
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- 影響rest api版本選擇的因素RESTAPI
- 查詢oracle效能SQLOracleSQL
- oracle 索引升降序及排序條件 對查詢計劃的影響Oracle索引排序
- 影響網站權重的幾大因素網站
- 影響代理IP速度的四大因素
- 影響WiFi體驗的一般因素WiFi
- 當汽車APP成為購車的影響因素APP
- 淺析影響專案執行的因素