ITPub博客

首页 > 数据库 > Oracle > 影响Oracle标量子查询性能的三个因素

影响Oracle标量子查询性能的三个因素

Oracle 作者:记录每一次错误 时间:2020-12-24 09:42:58 0 删除 编辑

在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
性格开朗,有较强的学习能力,对oracle数据库的体系结构,搭建RAC,timesten,goldengate,分布式数据库,dataguard,系统调优有较深入的了解, 尤其是oracle优化,深入学习的主机命令,对数据库的优化,SQL语句的优化有深入的认识,目前正在shell脚本,mysql,以后会有计划学习大数据和python。

注册时间:2018-07-23

  • 博文量
    183
  • 访问量
    338098