同样执行计划、业务逻辑下——数据表的列包含表达式、函数对sql响应时间的影响

在DBA手记里面看见一篇有关sql优化的文章,讲述的是列上面添加表达式前后两种sql的响应时间区别,当然这两个sql业务逻辑含义一致,执行计划一致,仅仅只是写法有点不同,但是sql的响应时间是有一定的区别的

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t01 as select * from dba_objects;

Table created.

SQL> insert into t01 select * from t01;

86451 rows created.

SQL> insert into t01 select * from t01;

172902 rows created.

SQL> insert into t01 select * from t01;

345804 rows created.

SQL> commit;

再进行下面的测试之前,来看看三个简单的表达式,假设object_id上没有索引(没有函数索引和单列索引,这三个表达式的sql语句是同样的执行计划)

object_id<1000000:cbo会将每行object_id取出来跟1000000直接比较 object_id<1000010-10:cbo会将每行object_id取出来跟1000010-10的结果比较,也就是100000进行比较 object_id+10<1000010:第三种表达式:cbo只能将每行object_id取出来,然后都加上10,再去跟100010进行比较 这三个表达式虽然业务含义是一致的,但是oracle的cbo优化器处理这三个表达式的方式并不相同,当然sql的响应时间也会有一定的区别,下面的测试都是第二次执行时sql的响应时间:

SQL> select object_type,count(*) from t01 where object_id<100000 group by object_type;

44 rows selected.

Elapsed: 00:00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1481K|    33M|  2897   (2)| 00:00:35 |
|   1 |  HASH GROUP BY     |      |  1481K|    33M|  2897   (2)| 00:00:35 |
|*  2 |   TABLE ACCESS FULL| T01  |  1481K|    33M|  2860   (1)| 00:00:35 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<100000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10016  consistent gets
          0  physical reads
          0  redo size
       1729  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL> select count(*) from t01 where object_id<100010-10 group by object_type;

44 rows selected.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1481K|    33M|  2897   (2)| 00:00:35 |
|   1 |  HASH GROUP BY     |      |  1481K|    33M|  2897   (2)| 00:00:35 |
|*  2 |   TABLE ACCESS FULL| T01  |  1481K|    33M|  2860   (1)| 00:00:35 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<100000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10009  consistent gets
       9999  physical reads
          0  redo size
       1209  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL> select object_type,count(*) from t01 where object_id+10<100010 group by object_type;

44 rows selected.

Elapsed: 00:00:00.23

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1481K|    33M|  2899   (2)| 00:00:35 |
|   1 |  HASH GROUP BY     |      |  1481K|    33M|  2899   (2)| 00:00:35 |
|*  2 |   TABLE ACCESS FULL| T01  |  1481K|    33M|  2862   (1)| 00:00:35 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"+10<100010)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10016  consistent gets
          0  physical reads
          0  redo size
       1729  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

可以看出来object_id+10<100010的响应时间是最长的,这个差异的时间是因为cbo全表扫描后,需要将每行数据的object_id加上10后才能跟100010进行比较。

SQL> select object_type,count(*) from t01 where object_id>90000 and object_id<100000 group by object_type;

no rows selected

Elapsed: 00:00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   218 |  5232 |  5382   (1)| 00:01:05 |
|   1 |  HASH GROUP BY     |      |   218 |  5232 |  5382   (1)| 00:01:05 |
|*  2 |   TABLE ACCESS FULL| T01  |   218 |  5232 |  5381   (1)| 00:01:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">90000 AND "OBJECT_ID"<100000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19798  consistent gets
       7655  physical reads
          0  redo size
        409  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select object_type,count(*) from t01 where object_id>90010-10 and object_id<100010-10 group by object_type;

no rows selected

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   218 |  5232 |  5382   (1)| 00:01:05 |
|   1 |  HASH GROUP BY     |      |   218 |  5232 |  5382   (1)| 00:01:05 |
|*  2 |   TABLE ACCESS FULL| T01  |   218 |  5232 |  5381   (1)| 00:01:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">90000 AND "OBJECT_ID"<100000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19798  consistent gets
       7752  physical reads
          0  redo size
        409  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select object_type,count(*) from t01 where object_id+10>90010 and object_id+10<100010 group by object_type;

no rows selected

Elapsed: 00:00:00.27

Execution Plan
----------------------------------------------------------
Plan hash value: 138777061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   218 |  5232 |  5383   (1)| 00:01:05 |
|   1 |  HASH GROUP BY     |      |   218 |  5232 |  5383   (1)| 00:01:05 |
|*  2 |   TABLE ACCESS FULL| T01  |   218 |  5232 |  5382   (1)| 00:01:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"+10>90010 AND "OBJECT_ID"+10<100010)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19798  consistent gets
       7864  physical reads
          0  redo size
        409  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

如果运算两次情况下,sql的响应时间区别会更加大

这个case讲解的并不是调整、优化执行计划,而是在数据表的列上做调整:在书写sql代码、优化调整sql时要尽可能保证列不要将数据表的列包含在函数和表达式中,函数和表达式尽量写到常量的一边,这样优化器只需要将常量的那一边计算一次就行,而不需要将数据表的列取出来进行大批量的计算,这方面我们程序员和dba都需要特别关注。

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle, sql tuning and troubleshooting. Bookmark the permalink.