关于表中添加列有缺省值和是否有not null约束在数据库各个版本的变化测试

oracle 11.2.0.4测试:

SQL> conn xiaoyu/xiaoyu
Connected.
SQL> create table t_null01 as select object_id,object_name,owner from dba_objects;

Table created.

SQL> alter session set events '10046 trace name context level 12';

Session altered.

SQL> alter table t_null01 add address varchar2(32) default 'Chain';

Table altered.

SQL> alter table t_null01 add name varchar2(32) default 'test' not null;

Table altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

[oracle@redhat-ora ~]$ more /home/oracle/tkprof01.txt

TKPROF: Release 11.2.0.4.0 - Development on Thu Oct 8 19:44:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL ID: 61648vzfjwfwu Plan Hash: 0

LOCK TABLE "T_NULL01" IN EXCLUSIVE MODE  NOWAIT

SQL ID: 1f1r5s0xwm0ag Plan Hash: 0

alter table t_null01 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0      11379          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0      11381          1           0

SQL ID: 887ywuvpskfyu Plan Hash: 1758867652

update "T_NULL01" set "ADDRESS"='Chain'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.60       0.70        113      11255     228645       86881
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.60       0.70        113      11256     228645       86881

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  T_NULL01 (cr=9727 pr=83 pw=0 time=618066 us)
    181898     181898     181898   TABLE ACCESS FULL T_NULL01 (cr=2006 pr=113 pw=0 time=67418 us cost=138 size=1212516 card=67362)

--------------------------------------------------------------------------------

SQL ID: 6zuvfjvdaq5dp Plan Hash: 0

LOCK TABLE "T_NULL01" IN ROW EXCLUSIVE MODE  NOWAIT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
--------------------------------------------------------------------------------

SQL ID: 1f1r5s0xwm0ag Plan Hash: 0

alter table t_null01 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3         26           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3         26           0

在oracle 11g中新添加的列有缺省值时,如果有not null的约束,oracle不会去全量更新表中的数据行,而是将表信息、列信息和列的缺省值记录到了oracle的数据字典sys.ecol$中,这里记录缺省值的是BINARYDEFVAL这个列,该列是blob字段类型

SQL> select column_name,column_id,data_default from dba_tab_columns  where table_name='T_NULL01' and owner='XIAOYU';

COLUMN_NAME                     COLUMN_ID DATA_DEFAULT
------------------------------ ---------- --------------------------------------------------------------------------------
OBJECT_ID                               1
OBJECT_NAME                             2
OWNER                                   3
ADDRESS                                 4 'Chain'
NAME                                    5 'test'

SQL> select object_id from dba_objects where object_name='T_NULL01' and owner='XIAOYU';

 OBJECT_ID
----------
     89526

SQL> select * from sys.ecol$ where tabobj#=89526;

   TABOBJ#     COLNUM BINARYDEFVAL
---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
     89526          5 74657374

但是如果没有指定not null约束,oracle需要对这个表进行全量的更新操作,这个会对表中数据行加上行级锁,如果表中的数据量很大,这个操作会长时间对数据行加上TX mode 6级别的排他锁,可能会对并发的应用造成严重的影响,很多情况下表中的数据列不能保证新增加有default值的列有not null的约束,在oracle 11g的版本下我们依然可以采用下列的方式来添加列,以最小程度的减少对表中数据行的排他锁定:

SQL> create table t_null02 as select object_id,object_name from dba_objects;

Table created.

SQL> alter table t_null02 ADD address varchar2(32);

Table altered.

SQL> alter table t_null02 modify address varchar2(32) default 'Chain';

Table altered.

SQL> select count(*) from t_null02 where address='Chain';

  COUNT(*)
----------
         0

SQL> insert into t_null02(object_id,object_name) values(10000,'OWP');

1 row created.

SQL> select * from t_null02 where address='Chain';

 OBJECT_ID OBJECT_NAME                                                                                                                      ADDRESS
---------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------
     10000 OWP                                                                                                                              Chain

这种添加方式第一次add和modify新增加的列并不会更新表中已经存在的数据,只会对新增加的数据设置default值,而对于之前表中的数据可以用游标取出分批进行更新,这样就不会对表中数据行长时间的锁定而造成应用级别的阻塞。

而在oracle 10g中无论新增加有default值的列是否能够添加not null的约束,oracle都需要对表中的数据进行更新,对于oracle 10g可以采取上面介绍的先添加新列、然后modify赋予default值,最后再用游标来分批更新减少在添加列时对表中的数据进行长时间的锁定而阻塞应用。

oracle 12.1.0.2.0

SQL> create table t_null12 as select object_id,object_name,owner from dba_objects;

Table created.

SQL> select spid from v$process where addr in (select paddr from v$session where sid=userenv('SID'));

SPID
------------------------
7830

SQL> alter table t_null12 add address varchar2(32) default 'Chain';

Table altered.

SQL ID: 7r9g8pkxta6q2 Plan Hash: 0

LOCK TABLE "T_NULL12" IN ROW EXCLUSIVE MODE  NOWAIT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
--------------------------------------------------------------------------------

SQL ID: bv747sa0guj2j Plan Hash: 0

alter table t_null12 add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          6          0           0
Execute      1      0.00       0.00          0        110         25           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0        116         25           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103

在oracle 12c后,添加列时即使没有指定not null的约束,oracle也不会对数据进行更新,实现方式和oracle 11g类似,也是由数据字典基表存储对应的表、列和列的默认值关系,只不过相对于oracle 11g而言sys.ecol$基表新增加了GUARD_ID列

SQL> desc sys.ecol$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABOBJ#                                            NUMBER
 COLNUM                                             NUMBER
 BINARYDEFVAL                                       BLOB
 GUARD_ID                                           NUMBER
Posted in oracle | 关于表中添加列有缺省值和是否有not null约束在数据库各个版本的变化测试已关闭评论

复合索引的创建剖析—包含in的三个条件SQL语句复合索引的创建

之前文章中提过复合索引的创建思路:
1 前导列尽可能让更多的核心业务SQL能够使用
2 单个SQL语句索引的前导列尽量选择等值条件做为索引的前导列

这里我们如果在对in的谓词、三个条件的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 t09 as select * from dba_objects;

Table created.

SQL> create index ind_owner_type_objid on t09(owner,object_type,object_id);

Index created.

SQL> create index ind_type_owner_objid on t09(object_type,owner,object_id);

Index created.

SQL> set autotrace traceonly;
SQL> analyze table t09 compute statistics for all indexes;

Table analyzed.

select /*+index(t09 ind_owner_type_objid)*/* from t09 where owner=’SYS’ and object_type in (‘TABLE’,’INDEX’) and object_id>30000 and object_id<310000;该sql语句需要在owner、object_type、object_id上创建复合索引,这个复合索引创建顺序如何,这里我们只考虑让该sql的执行计划最优秀,不用考虑别的SQL能够共用该索引,下面我们来看看两种复合索引的性能和执行计划。

索引(owner+object_type+object_id):
SQL> select /*+index(t09 ind_owner_type_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_OWNER_TYPE_OBJID |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
              "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed
索引(object_type+owner+object_id):
SQL> select /*+index(t09 ind_type_owner_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TYPE_OWNER_OBJID |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND "OWNER"='SYS' AND
              "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed

看出来对于where后面的谓词条件owner=’SYS’ and object_type in (‘TABLE’,’INDEX’) and object_id>30000 and object_id<310000,无论是选择(owner+object_type+object_id)还是(object_type+owner+object_id)的复合索引,优化器在索引范围扫描过程中都可以直接对谓词条件走access,而不需要走filter,这里优化器做了INLIST ITERATOR执行计划,这个类似一个oracle index skip range的执行计划,当对in条件中的第一组做index range scan后,会重新跳跃到分支块上再做index range scan,这个相比oracle的另一种执行计划CONCATENATION要更加高效点,因为不用再从根节点来重新走分支块最后到叶块。

如果我们优化器回到8I,这两个SQL的执行计划依然一样
SQL> select /*+optimizer_features_enable('8.1.7') index(t09 ind_owner_type_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     4 |
|   1 |  INLIST ITERATOR             |                      |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     4 |
|*  3 |    INDEX RANGE SCAN          | IND_OWNER_TYPE_OBJID |     1 |       |     3 |
-------------------------------------------------------------------------------------

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

   3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR
              "OBJECT_TYPE"='TABLE') AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

Note
-----
   - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL> select /*+optimizer_features_enable('8.1.7') index(t09 ind_type_owner_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     4 |
|   1 |  INLIST ITERATOR             |                      |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     4 |
|*  3 |    INDEX RANGE SCAN          | IND_TYPE_OWNER_OBJID |     1 |       |     3 |
-------------------------------------------------------------------------------------

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

   3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
              "OWNER"='SYS' AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

Note
-----
   - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed
Posted in oracle, sql tuning and troubleshooting | 复合索引的创建剖析—包含in的三个条件SQL语句复合索引的创建已关闭评论

同样执行计划、业务逻辑下——数据表的列包含表达式、函数对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都需要特别关注。

Posted in oracle, sql tuning and troubleshooting | 同样执行计划、业务逻辑下——数据表的列包含表达式、函数对sql响应时间的影响已关闭评论

唯一索引扫描消耗数十万的逻辑读——触发器被重复计算到父sql级别中

问题现象:sql_id 49q3u07d1gq00在最近awr周期的早上9点到9点半时段进入了awr top logical sql中,该sql是唯一索引扫描,单次执行平均消耗了10万左右的逻辑读

基本信息:
sql_id 49q3u07d1gq00的历史执行信息:

sys@CRMDB3>@sqlhis_add.sql
Enter value for sql_id: 49q3u07d1gq00
old  27:            and a.sql_id = '&sql_id'
new  27:            and a.sql_id = '49q3u07d1gq00'

BEGIN_TIME          INSTANCE_NUMBER MODULE                                        PLAN_HASH_VALUE       EXEC    PER_GET   PER_ROWS    TIME_MS   PER_READ
------------------- --------------- --------------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
2015-07-14 09:00:14               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937       1035     101810       .507     735.73       7.92
2015-07-13 09:00:21               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937      16297       4035         .5      32.51        .73
2015-07-12 09:00:28               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        737      80142       .512     547.93       4.13
2015-07-11 09:30:17               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        721      42485        .49    1099.47      54.38
2015-07-11 09:00:09               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        101      42784       .653   28242.53    1746.62
2015-07-10 09:00:30               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        626     124180         .5     888.19        8.6
2015-07-10 00:30:28               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937         73     153057       .589   25345.94    1602.25
2015-07-10 00:00:15               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937         32      52617       .563   54712.96    3174.03
2015-07-09 09:00:44               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        676      82156       .506     587.43       8.35
2015-07-08 09:00:09               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        729     142419       .513     950.26       7.52
2015-07-07 09:00:27               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        791     129498       .508     893.71       8.07
2015-07-06 09:00:21               1 tpengine@wbgapp1 (TNS V1-V3)                       1710364937        608      88258       .484     627.47       9.02
。。。。。。

sql_id 49q3u07d1gq00的执行计划:

sys@CRMDB3>@plan.sql
Enter value for hash_value: 49q3u07d1gq00

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  49q3u07d1gq00, child number 0
-------------------------------------
 UPDATE SUBS_SPSERVICE SET BILLINGFLAG = :BILLINGFLAG WHERE OID = :OID
AND NVL(BILLINGFLAG, '0') <> :BILLINGFLAG AND REGION = 14

Plan hash value: 1710364937

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                      |       |       |     4 (100)|          |       |       |
|   1 |  UPDATE                             | SUBS_SPSERVICE       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                      |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SUBS_SPSERVICE       |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX UNIQUE SCAN               | PK_CM_SUBS_SPSERVICE |     1 |       |     3   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(NVL("BILLINGFLAG",0)<>:BILLINGFLAG)
   4 - access("OID"=TO_NUMBER(:OID) AND "REGION"=14)

取了cursor cache中的某个绑定变量的具体值:

sys@CRMDB3>set autotrace traceonly;
sys@CRMDB3>select *
  2    from tbcs.SUBS_SPSERVICE
  3   wHERE OID = '88369193732011'
  4     AND NVL(BILLINGFLAG, '0') <> 1
  5     AND REGION = 14;

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2043816665

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                      |     1 |   121 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                      |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SUBS_SPSERVICE       |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  3 |    INDEX UNIQUE SCAN               | PK_CM_SUBS_SPSERVICE |     1 |       |     3   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(NVL("BILLINGFLAG",0)<>1)
   3 - access("OID"=88369193732011 AND "REGION"=14)


Statistics
----------------------------------------------------------
         36  recursive calls
          0  db block gets
        152  consistent gets
          7  physical reads
          0  redo size
       3005  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

带入具体的bind value逻辑读也只有100多。

可能的原因:
1 表中的倾斜值:
由于是唯一索引扫描,对于这个sql语句而言是不太有倾斜值的,所以排除了这个问题。

2 构造CR块引起的:
在业务闲暇时段我们采取了select /*+full(SUBS_SPSERVICE)*/count(*) from tbcs.SUBS_SPSERVICE让oracle对SUBS_SPSERVICE做一次全表扫描,此时可以有效的解决延迟块清除、构造CR块等问题的影响,但是排除延迟块后在第二天早上9点到10点还是进入了awr top sql行列,并且单次执行的逻辑读依然达到了十多万左右,这个也排除了是因为CR块等影响。

3 触发器:
跟表tbcs.SUBS_SPSERVICE相关的有6个触发器,分别为:
tbcs.TRG_CHECK_SUBS_SPSERVICE
tbcs.TRG_CHECK_SUBS_SPSERVICE_STM
tbcs.TRG_CS_REC_SUBS_SPSVC_TRYOUT
tbcs.TRG_CBS_SUBS_SPSERVICE
tbcs.TRG_CBS_SUBS_SPSERVICE_15
tbcs.TRG_SPBINDPRD_SUBS_SPSERVICE

其中触发器TBCS.TRG_CBS_SUBS_SPSERVICE中存在如下的代码:

if :new.status=0 and :old.status=0 and :new.enddate=:old.enddate then
      NULL;
  else
      update cs_cu_subs_spservice_list
                  set expiretime=:new.statusdate
                  where subsid=:new.subsid
                  and spcode=:new.spid
                  and servicecode=:new.spbizid
                  and region = :new.region
                  and (expiretime=:old.enddate or (expiretime is null or expiretime>=to_date('20370101','yyyymmdd')) and status=1 );
  end if;
--修正预约失效的业务在月结处理进程中失效时间被更新为零点之后的问题end

else if UPDATING then  --是计费标示变更
     update cs_cu_subs_spservice_list
                  set BILLINGFLAG=:new.BILLINGFLAG
                  where subsid=:new.subsid  —1419200000285166
                  and spcode=:new.spid  —810630
                  and startdate=:new.startdate
                  and servicecode=:new.spbizid  —9980018984
                  and region = :new.region
                  and (expiretime=:old.enddate or (expiretime is null or expiretime>=to_date('20370101','yyyymmdd')) and status=1 );
     --和秦工确认,BILLINGFLAG修改后直接退出
     RETURN;
    end if;
end if ;

上述代码也进入了awr top logical sql中。

触发器中的sql语句的逻辑读会被重复计算到了父级别的sql语句中,而最可能消耗大量逻辑读就是上面的TBCS.TRG_CBS_SUBS_SPSERVICE触发器的相关代码

该代码的执行计划为:

UPDATE CS_CU_SUBS_SPSERVICE_LIST
   SET BILLINGFLAG = :B1
 WHERE SUBSID = :B7
   AND SPCODE = :B6
   AND STARTDATE = :B5
   AND SERVICECODE = :B4
   AND REGION = :B3
   AND (
       EXPIRETIME = :B2 OR
       (EXPIRETIME IS NULL OR EXPIRETIME >= TO_DATE('20370101', 'yyyymmdd'))
       AND STATUS = 1
       )


Plan hash value: 1591679211

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                           |       |       |    24 (100)|          |       |       |
|   1 |  UPDATE                             | CS_CU_SUBS_SPSERVICE_LIST |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                           |     1 |    84 |    24   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| CS_CU_SUBS_SPSERVICE_LIST |     1 |    84 |    24   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_SUBS_SPSERVICELIST    |    20 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("STARTDATE"=:B5 AND "REGION"=:B3 AND ((("EXPIRETIME" IS NULL OR "EXPIRETIME">=TO_DATE(' 2037-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND TO_NUMBER("STATUS")=1) OR "EXPIRETIME"=:B2)))
   4 - access("SUBSID"=:B7 AND "SPCODE"=:B6 AND "SERVICECODE"=:B4)

目前该sql走的是subsid、SPCODE、SERVICECODE的索引

但是实际查询发现表cs_cu_subs_spservice_list的subsid、SPCODE、SERVICECODE有倾斜数据,最高达到了40多万:

SQL> select *
  2    from (select /*+parallel(CS_CU_SUBS_SPSERVICE_LIST 20)*/SUBSID||'', SPCODE, SERVICECODE, region,count(*)
  3            from tbcs.CS_CU_SUBS_SPSERVICE_LIST
  4           group by SUBSID, SPCODE, SERVICECODE,region
  5           order by count(*) desc)
  6   where rownum < 20
  7  ;
 
SUBSID||''                               SPCODE               SERVICECODE              REGION   COUNT(*)
---------------------------------------- -------------------- ------------------------ ------ ----------
1419200000285166                         810630               9980018984                   14     424111
1419200000285166                         900137               11000002                     14      52303
1419300014905486                         701001               500231883000                 14      43596
1419200001247851                         698026               YDWB                         14      25345
1419300023013352                         600902               6009020705                   14      16066
1419200007950949                         698026               YDWB                         14      10369
1633300001231000                         910008               -XXWDHY                      16       6872
1419200011168237                         801234               110301                       14       6370
1419300014622929                         801234               112324                       14       5992
1419200001450109                         698026               YDWB                         14       5317
1633200020544477                         910008               -XXWDHY                      16       4605
1419300014667224                         801234               112323                       14       4521
1419200006732616                         900675               03203322                     14       4209
1419200006732616                         900635               30040712                     14       4209
1419200006732616                         698001               100100                       14       3993
1419200007887731                         900501               08090006                     14       3881
1419200007887731                         698026               YDWB                         14       3869
1419200001196946                         698026               YDWB                         14       3381
1419200005964444                         698026               YDWB                         14       3122
 
19 rows selected

为了模拟这种现象,我们通过取tbcs.CS_CU_SUBS_SPSERVICE_LIST表的最大倾斜数据,反推出TBCS.SUBS_SPSERVICE的OID和BILLINGFLAG字段,然后重复赋值给原唯一索引扫描sql语句来看消耗的资源:

select OID,BILLINGFLAG
  from TBCS.SUBS_SPSERVICE
 where subsid = 1419200000285166
   and spid = '810630'
   and spbizid = '9980018984'
   and region = 14
   and status <> 0
   and rownum < 10

OID              BILLINGFLAG
---------------  -----------
88375756944223   2
SQL> oradebug setmypid
SQL> oradebug event 10046 trace name context forever,level 12
SQL> set timing on
SQL> set autotrace traceonly;
SQL> UPDATE TBCS.SUBS_SPSERVICE SET BILLINGFLAG = 2 WHERE OID =88375756944223
  2  AND NVL(BILLINGFLAG, '0') <> 3 AND REGION = 14;

1 row updated.

Elapsed: 00:27:26.43

Execution Plan
----------------------------------------------------------
Plan hash value: 1710364937

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                      |     1 |   121 |     4   (0)| 00:00:01 |       |       |
|   1 |  UPDATE                             | SUBS_SPSERVICE       |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                      |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SUBS_SPSERVICE       |     1 |   121 |     4   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX UNIQUE SCAN               | PK_CM_SUBS_SPSERVICE |     1 |       |     3   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(NVL("BILLINGFLAG",0)<>3)
   4 - access("OID"=88375756944223 AND "REGION"=14)


Statistics
----------------------------------------------------------
         38  recursive calls
         60  db block gets
     420009  consistent gets
     272579  physical reads
      12224  redo size
        831  bytes sent via SQL*Net to client
        872  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

oracle这里将触发器的资源消耗也记录到了父级别的sql语句中。

10046 event trace中也看见这个倾斜值造成了大量的逻辑读:

SQL ID: 62kbdd8s4wnnw Plan Hash: 1591679211

UPDATE CS_CU_SUBS_SPSERVICE_LIST SET EXPIRETIME=:B1
WHERE
 SUBSID=:B6 AND SPCODE=:B5 AND SERVICECODE=:B4 AND REGION = :B3 AND
  (EXPIRETIME=:B2 OR (EXPIRETIME IS NULL OR EXPIRETIME>=TO_DATE('20370101',
  'yyyymmdd')) AND STATUS=1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     71.61    1415.46     272538     420225          9           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     71.61    1415.46     272538     420225          9           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  CS_CU_SUBS_SPSERVICE_LIST (cr=420227 pr=272543 pw=0 time=1415554526 us)
         1          1          1   PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=420225 pr=272537 pw=0 time=1415454032 us cost=24 size=84 card=1)
         1          1          1    TABLE ACCESS BY LOCAL INDEX ROWID CS_CU_SUBS_SPSERVICE_LIST PARTITION: KEY KEY (cr=420225 pr=272537 pw=0 time=1415454015 us cost=24 size=84 card=1)
    425187     425187     425187     INDEX RANGE SCAN IDX_SUBS_SPSERVICELIST PARTITION: KEY KEY (cr=5122 pr=4865 pw=0 time=43992007 us cost=4 size=0 card=20)(object id 1025693)

。。。。。。
Posted in oracle, sql tuning and troubleshooting | 唯一索引扫描消耗数十万的逻辑读——触发器被重复计算到父sql级别中已关闭评论

绑定变量知识—sql语句和pl/sql语句中如何使用绑定变量

关于绑定变量的用法:

之前的文章介绍了绑定变量对于系统的重要性,这里对绑定变量的使用做进一步的分析和说明。

1)在sql语句中如何带入bind value

SQL> variable x number;
SQL> exec :x:=100;

PL/SQL procedure successfully completed.

SQL> select * from t where id=:x;

no rows selected

SQL> exec :x:=101;

SQL> select * from t where id=:x;

no rows selected

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID        SQL_TEXT                                 EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
3yxwagyspybax select * from t where id=:x                       1           1

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID        SQL_TEXT                                 EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
3yxwagyspybax select * from t where id=:x                       2           2

对于sql语句直接是用类似variable的方式定义,然后exec赋值,就可以在sql语句中使用绑定变量。

需要注意&x的方式并不是使用绑定变量的正确方式,请看下列例子:

SQL> select * from t where id=&op;
Enter value for op: 10
old   1: select * from t where id=&op
new   1: select * from t where id=10

no rows selected

SQL> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS from v$sql where sql_text like 'select * from t where id=%';

SQL_ID        SQL_TEXT                                 EXECUTIONS PARSE_CALLS
------------- ---------------------------------------- ---------- -----------
6wxj4tkdajgbx select * from t where id=10                       1           1
3yxwagyspybax select * from t where id=:x                       2           2

关于pl/sql中bind value的方式:

pl/sql中有两种变量的形式,一个是pl/sql定义的变量,另一种是绑定变量,这两种变量都可以让优化器减少sql的解析次数,下面简单看下:

1 pl/sql中定义的变量:

SQL> declare
  2  a number;
  3  v_name varchar2(128);
  4  begin
  5  for i in 1..100 loop
  6  delete from t where object_id=i+1 returning object_name into v_name;
  7  dbms_output.put_line(v_name);
  8  commit;
  9  end loop;
end;
 11  /

SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%delete from t%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
g3bug3dp68bqt DELETE FROM T WHERE OBJECT_ID=:B1 +1 RETURNING OBJECT_NAME INTO :O0
391g7kanthzt9 declare a number; v_name varchar2(128); begin for i in 1..100 loop delete from t where object_id=i+1
               returning object_name into v_name; dbms_output.put_line(v_name); commit; end loop; end;

oracle这里将代码全部解析为DELETE FROM T WHERE OBJECT_ID=:B1 +1 RETURNING OBJECT_NAME INTO :O0的cursor,减少了优化器反复解析sql而造成的资源消耗。

2 pl/sql中的绑定变量:
pl/sql程序中是以execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值]的这种语法,其中using后面接的就是前面sql的绑定变量的值,有多少个绑定变量后面using就接多少个具体值,这些值是按照顺序来代替前面的sql中的绑定变量的值。

SQL> set serveroutput on;
SQL> declare
  2  v_name varchar2(32);
  3  begin
  4  execute immediate 'select object_name from xiaoyu01 where object_id=:1 and owner=:1' into v_name using 10,'SYS';
  5  dbms_output.put_line(v_name);
  6  end;
  7  /

PL/SQL procedure successfully completed.

这里的into是在pl/sql中一种赋值给变量的方式,这个变量v_name并不是绑定变量

注意1:绑定变量在pl/sql中只能用using来赋值,而pl/sql中的变量则是用into来赋值

注意2:跟sql语句中绑定不同的地方是,pl/sql语句中即使绑定变量的名称完全相同也不一定表示这两个绑定变量的具体值是相同,pl/sql中的绑定变量完全按照顺序读取using后面的具体值。

注意3:execute immediate sql_text是声明动态sql语句的,这里的sql_text必须满足sql引擎的语法,而非动态sql则必须满足pl/sql存储引擎的语法。

注意4:动态sql语句虽然被pl/sql程序体所封装,sql引擎依然会按照动态sql的原文本(不会进行大小写转换)来生成单独的cursor对象。

SQL> select sql_id,sql_text from v$sql where sql_text like 'select object_name from xiaoyu01%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
g9zuzpp1r1jh0
select object_name from xiaoyu01 where object_id=:1 and owner=:1

pl/sql中的非动态sql语句如何生成cursor cache:
sql和pl/sql其实是由两个各自的存储引擎来工作的,pl/sql中的sql语句会交给sql引擎来处理,对于动态sql语句通过上面的测试看出来oracle是根据动态sql语句的原sql_text文本来生成cursor,而如果不是动态sql语句,oracle是如何存储这个pl/sql对应的cursor了,其实这里有两种情况:

1 如果是select into类型的语句,oracle存储在cursor cache中的就只有这个pl/sql程序体

SQL> declare
  2  v_name varchar2(64);
  3  begin
  4  select object_name into v_name from t where rownum<2;
end;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%select object_name into v_name from t where rownum<2%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1chbdafu44tcy declare     v_name varchar2(64);     begin     select object_name into v_name from t where rownum<2;
               end;

bnprfwc84nv7u select sql_id,sql_text from v$sql where lower(sql_text) like '%select object_name into v_name from t
               where rownum<2%'

2 如果是DML类型的语句,oracle即会存储原来的pl/sql,也会将pl/sql中原来的DML语句全部修改为大写的,从而生成一个新的cursor存储在cursor cache中

SQL> alter system flush shared_pool;

System altered.

SQL> declare
  2      v_name varchar2(64);
  3      begin
  4      delete from t where rownum<2;
end;
  6     /

PL/SQL procedure successfully completed.
SQL> select sql_id,sql_text,first_load_time from v$sql where lower(sql_text) like '%delete from t where rownum<2%';

SQL_ID        SQL_TEXT                                                                                             FIRST_LOAD_TIME
------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------
cffprycyhasgg declare     v_name varchar2(64);     begin     delete from t where rownum<2; end;                    2015-05-22/00:35:14
3h7ddvwzdg35d DELETE FROM T WHERE ROWNUM<2                                                                         2015-05-22/00:35:14
1k8m36uynvrpy select sql_id,sql_text,first_load_time from v$sql where lower(sql_text) like '%delete from t where r 2015-05-22/00:36:14
              ownum<2%'

pl/sql程序体中DML语句使用绑定变量:

1 insert into语句中使用绑定变量

SQL> declare
  2  begin
  3  execute immediate 'insert into t(object_id,object_name) values(:1,:2)' using 100,'xiaoyu';
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

2 delete from语句中使用绑定变量

SQL> declare
  2  begin
  3  execute immediate 'delete from t where object_id=:1' using 100;
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;
SQL> declare
  2  v_name varchar2(128);
  3  v_sql varchar2(128);
  4  begin
  5  v_sql:='delete from t where rownum=:1 returning object_name into :2';
  6  execute immediate v_sql using 1 returning into v_name;
  7  dbms_output.put_line(v_name);
  8  commit;
  9  end;
 10  /

C_COBJ#

PL/SQL procedure successfully completed.

SQL> select sql_text,sql_id from v$sql where sql_text like 'delete from t where rownum=:1%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
delete from t where rownum=:1 returning object_name into :2
784c1dh672vvc

补充returning into的知识:returning [column_name] into [variable]就是将dml语句中影响的数据行的列值记录到变量中,在pl/sql procedure和sql语句中都可以按照语法来使用:

PL/SQL procedure中能够返回insert、update、delete操作影响的数据行的列值,记录修改的数据行的列值是用returning into的方式来记录的。

SQL> declare
  2  v_name varchar2(128);
  3  begin
  4  delete from t where rownum<2 returning object_name into v_name;
  5  dbms_output.put_line(v_name);
  6  commit;
  7  end;
  8  /

PROXY_ROLE_DATA$

PL/SQL procedure successfully completed.

在sql语句中也可以直接加上returning into记录修改行的某些列值:

SQL> variable v_name varchar2(32);
SQL> delete from t where rownum<2 returning object_name into :v_name;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> print :v_name;

V_NAME
--------------------------------------------------------------------------------
I_IND1

由于pl/sql引擎每次处理sql语句都需要和sql引擎交换(这里sql语句不仅仅是动态sql语句,就是select、insert、update、delete等sql语句),而如果批量的存储引擎交换是很消耗资源的,比如如下两种形式:

1 显式游标或参考游标需要fetch操作时,循环操作需要pl/sql引擎来处理,而fetch一条记录对应要执行的sql语句则需要sql引擎来处理
2 显式游标或参考游标的循环内部需要执行sql语句时

如果能一次fetch一批记录或者一次执行一批sql语句,则可以有效减少pl/sql引擎和sql引擎的交互次数,当然pl/sql代码的性能也会得到一定的提升。

简单说下开发相关的知识,%type和%rowtype、type typename is table of datatype 、sys_refcursor、constant pls_integer

%type是用来说明一个变量的类型与另一个已经定义的变量或者表的某一列类型相同,oracle就提供了%Type定义方式,如果被参照的那个变量被修改后,这个新变量也会马上自动修改,比如定义eName emp.name%type,如果emp表的name字段类型被修改了,变量ename也会自动修改
%rowtype是用来定义一个变量于表中的所有数据类型一致,比如我们要获取表中一行的所有数据,如果每个列都定义一个变量%type太麻烦,可以直接定义v_emp emp%rowtype,而其中的v_emp.name就是emp表中对应那一行数据的name列的值
sys_refcursor是系统自带的定义游标的方式,跟cursor c is select 。。。 from emp等不同的是,sys_refcursor多用于动态游标,就是open状态时才予以赋值给游标,而cursor那种定义方式是在定义时就知道了游标的具体值
type typename is table of datatype是声明一种自定义的数据类型(还可以声明数组),比如type type1 is table of emp.ename%type;type type2 is table of emp$rowtype;type type3 is table of varchar2(32);

介绍上面的开发方面的知识后,来看看如何批量fetch游标给具体的数组变量例子:

declare
cur_t sys_refcursor;
type typelist is table of varchar2(32);  定义数组类型typelist
type_name typelist;
v_sql varchar2(100);
CN_BATCH_SIZE constant pls_integer := 1000; constant pls_integer定义常量
begin
v_sql:='select name from t where id=:id';
open cur_t for v_sql using 100;  给游标cur_t赋值,动态游标是打开时候来赋值,这点与静态游标不同
loop
fetch cur_t bulk collect into type_name limit CN_BATCH_SIZE; 将游标再赋值给数组,这里采用了fetch cursor_name bulk collect into 自定义的数组 limit CN_BATCH_SIZE来批量fetch
for i in 1..type_name.count loop  循环数组输出,数组.count表示数据的总长度,数组(i)表示是哪个数组
dbms_output.put_line(type_name(i));
end loop;
exit when type_name.count<CN_BATCH_SIZE;
end loop;
close cur_t;
end;
/

批量执行sql的崔华的blog上有相应的文章模板,这里只说明批量执行的语法:
forall 1 in 1..[自定义数组的长度]
execute immediate [带绑定变量的目标sql] using [对应绑定变量的具体输入值];

关于绑定变量的初步使用就到这里为止,后面会陆续介绍关于绑定变量的窥视、分级和使用的一些需要注意的知识点!

Posted in oracle | 绑定变量知识—sql语句和pl/sql语句中如何使用绑定变量已关闭评论

绑定变量知识—sql语句的代码中哪些列适合使用绑定变量

最近在挖掘系统中未使用绑定变量的sql语句,碰到了一些本该使用绑定变量的sql语句没有使用绑定变量和一些不适合使用绑定变量的sql语句又使用了绑定变量,那么对于绑定变量这个dba、开发人员都经常提及的知识点,究竟该如何使用,什么时候该用绑定变量、什么时候不该用绑定变量了。

首先来看下列几行代码:

SELECT TO_CHAR(ORDERID) ORDERID FROM test_tab WHERE REGION = 11 AND STATUS = :STATUS AND PID BETWEEN 10 AND 19 ORDER BY RECDATE DESC
SELECT TO_CHAR(ORDERID) ORDERID FROM test_tab WHERE REGION = 11 AND STATUS = :STATUS AND PID BETWEEN 20 AND 29 ORDER BY RECDATE DESC
。。。。。。。
SELECT TO_CHAR(ORDERID) ORDERID FROM test_tab WHERE REGION = 11 AND STATUS = :STATUS AND PID BETWEEN 90 AND 99 ORDER BY RECDATE DESC

这类sql语句在数据库有10个,都是因为pid between and 条件不同而产生,这10个sql每个每半个小时都要执行300次左右,每个都需要对test_tab做全表扫描扫描,这10个类似的sql语句都进入了top cpu行列,每个消耗1%左右的cpu time,由于是并发级别的全表扫描,伴随着较严重的latch:cache buffer chains,消耗了较多的cpu和IO资源。

sql历史执行信息(这里以其中一个sql_id为例)
sys@CRMDB4>@sqlhis_add.sql
Enter value for sql_id: 3h51205b2v9ad
old  27:            and a.sql_id = '&sql_id'
new  27:            and a.sql_id = '3h51205b2v9ad'

BEGIN_TIME          INSTANCE_NUMBER MODULE                                        PLAN_HASH_VALUE       EXEC    PER_GET   PER_ROWS    TIME_MS   PER_READ
------------------- --------------- --------------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
2015-04-15 08:00:10               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        299      26443       .334     623.88          0
2015-04-15 07:30:45               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        310      26440       .045     620.93          0
2015-04-15 07:00:37               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440       .006     615.68          0
2015-04-15 06:30:30               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440          0     612.13          0
2015-04-15 06:00:23               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        319      26440       .003     614.18          0
2015-04-15 05:30:16               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440       .003     615.92          0
2015-04-15 05:00:09               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26523       .006     615.29          0
2015-04-15 04:30:02               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .006     616.01          0
2015-04-15 04:00:03               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        307      26445       .463     623.61          0
2015-04-15 03:00:41               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        298      26452       .993     623.88          0
2015-04-14 23:30:24               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        319      26440       .016     621.73          0
2015-04-14 23:00:14               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .025     625.03          0
2015-04-14 22:30:02               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        315      26440       .032     639.28          0
2015-04-14 22:00:26               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        309      26441       .071     628.96          0
2015-04-14 21:30:19               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        307      26441       .147     632.39          0
2015-04-14 20:00:11               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        266      26447       .977     630.42          0
2015-04-14 19:30:03               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        257      26448      1.198     638.93          0
2015-04-14 13:00:14               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        268      26349       1.16     627.38          0
2015-04-14 08:00:08               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        296      26442       .294     637.55          0
2015-04-14 07:30:00               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        316      26440       .044     618.39          0
2015-04-14 07:00:12               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        314      26524       .003     618.68          0
2015-04-14 06:30:05               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        319      26357       .006      614.1          0
2015-04-14 06:00:39               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        311      26440        .01     611.94          0
2015-04-14 05:30:32               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440       .003     614.99          0
2015-04-14 05:00:25               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        314      26440       .006     615.15          0
2015-04-14 04:30:18               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .003     617.47          0
2015-04-14 04:00:11               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        316      26440       .009     618.47          0
2015-04-14 03:30:04               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        295      26542      1.217     625.07          0
2015-04-14 02:30:50               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        285      26453      1.211     632.62          0
2015-04-13 23:30:04               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        316      26440       .025     623.16          0
2015-04-13 23:00:32               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        308      26354       .019     625.77          0
2015-04-13 22:30:23               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .028     625.29          0
2015-04-13 22:00:11               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        315      26440       .063     629.55          0
2015-04-13 21:30:01               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        309      26441       .126      632.3          0
2015-04-13 20:30:22               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        282      26443       .592     635.75          0
2015-04-13 20:00:15               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        276      26445       .739     627.51          0
2015-04-13 13:00:15               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        267      26447      1.086     639.39          0
2015-04-13 08:00:25               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        301      26442       .299     621.48          0
2015-04-13 07:30:18               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        314      26356       .057     618.21          0
2015-04-13 07:00:08               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .013     614.38          0
2015-04-13 06:30:01               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        319      26440       .006     611.62          0
2015-04-13 06:00:54               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        306      26440          0     613.45          0
2015-04-13 05:30:46               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440       .009     612.89          0
2015-04-13 05:00:39               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440          0     615.62          0
2015-04-13 04:30:32               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        318      26440          0     618.19          0
2015-04-13 04:00:24               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26357          0     630.38          0
2015-04-13 03:30:17               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .035     617.05          0
2015-04-12 23:30:23               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        317      26440       .013     629.93          0
2015-04-12 23:00:15               1 tpengine@wbgapp1 (TNS V1-V3)                       2160085106        315      26440       .022     631.47          0

49 rows selected.

cursor cache的执行计划:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6mbkzm0qpkgwq, child number 0
-------------------------------------
SELECT TO_CHAR(ORDERID) ORDERID   FROM test_tab  WHERE
REGION = 11    AND STATUS = :STATUS  AND PID BETWEEN 30 AND 39  ORDER
BY RECDATE DESC

Plan hash value: 2160085106

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                         |       |       |       |  6415 (100)|          |       |       |
|   1 |  SORT ORDER BY            |                         | 43894 |  1200K|  1912K|  6415   (1)| 00:01:17 |       |       |
|   2 |   PARTITION RANGE ITERATOR|                         | 43894 |  1200K|       |  6068   (1)| 00:01:13 |     1 |    27 |
|*  3 |    TABLE ACCESS FULL      | test_tab                | 43894 |  1200K|       |  6068   (1)| 00:01:13 |     1 |    27 |
-----------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("PID"<=39 AND "REGION"=11 AND "PID">=30 AND "STATUS"=TO_NUMBER(:STATUS)))

这里需要对test_tab做全表扫描,test_tab表是按照(region+recdate)做的range分区,由于这里where条件中只用到了分区条件region,目前只能走region条件的分区剪裁

**********************************************************
Table Level
**********************************************************

Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
test_tab                            1,046,180          275,37            0        0     119 YES            52,309 04-15-2015

Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
REGION                                    2   .50000000       1            0         52,309 04-15-2015
RECDATE                              15,949   .00006270       1            0         52,309 04-15-2015
ORDERID                           1,046,180   .00000096       1            0         52,309 04-15-2015
STATUS                                    2   .50000000       1            0         52,309 04-15-2015
PID                                      27   .03703704       1            0         52,309 04-15-2015
ERRMSG                                   28   .03571429       1           40         52,307 04-15-2015

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IDX_test                         2       10,080      1,046,180      1,066,712       1       1    1,013,056 04-15-2015

Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_test                       ORDERID                           1 NUMBER(18,0)

**********************************************************
Partition Level
**********************************************************

Partition              Number                        Empty Average    Chain Average Global Date
Name                  of Rows          Blocks       Blocks   Space    Count Row Len Stats  MM-DD-YYYY
-------------- -------------- --------------- ------------ ------- -------- ------- ------ ----------
P_R_11_201304               0            0,00            0       0        0       0 YES    09-05-2013
P_R_11_201305               0            0,00            0       0        0       0 YES    09-05-2013
P_R_11_201306               0            0,00            0       0        0       0 YES    09-05-2013
P_R_11_201307               0            0,00            0       0        0       0 YES    09-05-2013
P_R_11_201308              52            0,19            0       0        0     120 YES    09-05-2013
P_R_11_201309               2            0,46            0       0        0     126 YES    10-01-2013
P_R_11_201310          25,339           20,14            0       0        0     353 YES    11-01-2013
P_R_11_201311           9,247            2,38            0       0        0     121 YES    12-01-2013
P_R_11_201312             461            1,10            0       0        0     123 YES    01-01-2014
P_R_11_201401               7            1,10            0       0        0     132 YES    02-01-2014
P_R_11_201402               4            1,74            0       0        0     130 YES    03-01-2014
P_R_11_201403              45            1,74            0       0        0     126 YES    04-01-2014
P_R_11_201404              21            1,10            0       0        0     115 YES    05-01-2014
P_R_11_201405               7            2,38            0       0        0     128 YES    06-01-2014
P_R_11_201406              10            1,10            0       0        0     127 YES    07-01-2014
P_R_11_201407              18            1,10            0       0        0     129 YES    08-01-2014
P_R_11_201408         178,600           40,30            0       0        0     113 YES    09-01-2014
P_R_11_201409          77,960           20,14            0       0        0     113 YES    10-01-2014
P_R_11_201410          39,410            8,78            0       0        0     113 YES    11-01-2014
P_R_11_201411         428,280           90,94            0       0        0     113 YES    12-01-2014
P_R_11_201412         289,540           60,46            0       0        0     113 YES    01-01-2015
P_R_11_201501              10            3,66            0       0        0     123 YES    02-01-2015
P_R_11_201502               6            9,42            0       0        0     119 YES    03-01-2015
P_R_11_201503              22            4,94            0       0        0     124 YES    04-01-2015
P_R_11_201504               6            1,74            0       0        0      45 YES    04-15-2015
P_R_11_PMAX                 0            0,00            0       0        0       0 YES    03-20-2015
P_R_99_201304              39            0,46            0       0        0      27 YES    07-30-2014
P_R_99_201305               0            0,00            0       0        0       0 YES    09-05-2013
P_R_99_201306               0            0,00            0       0        0       0 YES    09-05-2013
P_R_99_201307               0            0,00            0       0        0       0 YES    09-05-2013
P_R_99_201308               0            0,00            0       0        0       0 YES    09-05-2013
P_R_99_201309               0            0,00            0       0        0       0 YES    09-05-2013
P_R_99_201310               0            0,00            0       0        0       0 YES    09-28-2013
P_R_99_201311               0            0,00            0       0        0       0 YES    10-26-2013
P_R_99_201312               0            0,00            0       0        0       0 YES    11-23-2013
P_R_99_201401               0            0,00            0       0        0       0 YES    12-20-2013
P_R_99_201402               0            0,00            0       0        0       0 YES    01-21-2014
P_R_99_201403               0            0,00            0       0        0       0 YES    02-21-2014
P_R_99_201404               0            0,00            0       0        0       0 YES    03-26-2014
P_R_99_201405               0            0,00            0       0        0       0 YES    04-25-2014
P_R_99_201406               0            0,00            0       0        0       0 YES    05-28-2014
P_R_99_201407               0            0,00            0       0        0       0 YES    06-25-2014
P_R_99_201408               0            0,00            0       0        0       0 YES    07-25-2014
P_R_99_201409               0            0,00            0       0        0       0 YES    08-27-2014
P_R_99_201410               0            0,00            0       0        0       0 YES    09-28-2014
P_R_99_201411               0            0,00            0       0        0       0 YES    10-24-2014
P_R_99_201412               0            0,00            0       0        0       0 YES    11-21-2014
P_R_99_201501               0            0,00            0       0        0       0 YES    12-25-2014
P_R_99_201502               0            0,00            0       0        0       0 YES    01-21-2015
P_R_99_201503               0            0,00            0       0        0       0 YES    02-13-2015
P_R_99_201504               0            0,00            0       0        0       0 YES    03-20-2015
P_R_99_PMAX                 0            0,00            0       0        0       0 YES    03-20-2015

绑定变量STATUS分析:

查看status字段的数据倾斜程度:

sys@CRMDB4>select status,count(*) from tbcs.test_tab group by status;

    STATUS   COUNT(*)
---------- ----------
         0         52
         1    1046886

2 rows selected.

查看绑定变量的历史值status都是等于0,从sql写法上来看这个sql是做任务分发的,推断可能是将status=0的数据分布出去,这里需要业务进行核实,如果业务上都是查询status等于0的业务,由于status=0的数据是少数,可以考虑创建status+pid字段的联合索引,而实际上业务往往就是需要取这一批少量的数据。

创建(status+pid)联合索引:

SQL> create index tbcs.ind_status_pid on tbcs.test_tab(STATUS,PID) local;
SQL> SELECT /*+index(test_tab ind_status_pid)*/TO_CHAR(ORDERID) ORDERID
  2    FROM test_tab
  3   WHERE REGION = 11
  4     AND STATUS = 0
  5     AND PID BETWEEN 0 AND 9
  6   ORDER BY RECDATE DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1488219961

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         | 34500 |   943K|       | 21652   (1)| 00:04:20 |       |       |
|   1 |  SORT ORDER BY                      |                         | 34500 |   943K|  1504K| 21652   (1)| 00:04:20 |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                         | 34500 |   943K|       | 21379   (1)| 00:04:17 |     1 |    27 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| test_tab                | 34500 |   943K|       | 21379   (1)| 00:04:17 |     1 |    27 |
|*  4 |     INDEX RANGE SCAN                | IND_STATUS_PID          | 69208 |       |       |   219   (1)| 00:00:03 |     1 |    27 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("REGION"=11)
   4 - access("STATUS"=0 AND "PID">=0 AND "PID"<=9)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         46  consistent gets
         10  physical reads
          0  redo size
        335  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed
逻辑读降低到了46

优化这个sql语句很简单,我们只需要对业务核实下status传递过来的是不是少量的0值,如果是我们就创建(status+pid)联合索引,那么这个sql语句中的STATUS = :STATUS适合使用绑定变量吗,这里其实是不适合的。

不该出现绑定变量的字段:
1 对于能够枚举的列(比如状态类型字段,这部分类型字段往往不同值较少,一般都在十个到几十个不同值左右,有的甚至都在10个以下,在sql语句中尽量不要写绑定变量),因为状态字段不同值往往比较少,即使不写绑定变量也只会产生极少数的sql_id,这部分的oracle解析消耗微乎其微,如果写成绑定变量后不仅仅可能导致优化分析难度增大(需要去挖掘历史的绑定变量值、还要开启了11g的自适应游标、绑定变量窥视特性才可能走到索引范围扫描)

出现绑定变量的字段:
1 主键或者唯一索引的字段
2 唯一性很高并且并发查询的字段

如下主键或者唯一索引字段:

SELECT REC_ID FROM test_order WHERE city_id = 11 and order_id = '211201503265891243316'
SELECT REC_OID FROM test_order WHERE city_id = 11 and order_id = '211201504108244756588'
SELECT CITY_ID FROM test_order WHERE city_id = 11 and order_id = '211201504087931133804'

该sql语句中order_id+city_id是唯一索引键,开发人员应该使用绑定变量形式减少这部分sql的解析压力

唯一性很高并且并发查询的字段:

SELECT 1
FROM test1 t,
  test2 a
WHERE t.subsid   = a.subsoid
AND a.servnumber = '15061628285'
AND a.opentype   = '0'
AND t.appdstatus = '0'
AND a.subsoid   IS NOT NULL
AND a.installed  = '1'
AND a.batrectype = 'AgentPreBound'

该sql语句中由于a.servnumber = ‘15061628285’根据字段含义是电话号码,该字段唯一性很高,同样开发人员应该将a.servnumber = ‘15061628285’修改为绑定变量a.servnumber = :B1绑定变量形式来减少数据库解析的压力

Posted in oracle | 绑定变量知识—sql语句的代码中哪些列适合使用绑定变量已关闭评论

分区剪裁深入剖析

做表结构设计时我们经常会将大表做分区或者分表规划,oracle数据库中由于非常强大的分区功能可以不用分表的办法而直接使用分区表来规划,而我们使用分区表一个很重要的特性就是分区裁剪,这里将对分区表的分区裁剪简单的分析和探究:

分区剪裁就是对于分区表或者分区索引来说,优化器可以自动从from和where中根据分区键直接提取出需要访问的分区,从而避免扫描所有的分区,降低了IO请求。分区剪裁可以细分为静态分区剪裁和动态分区剪裁,其中静态分区剪裁发生在sql语句编译阶段,而动态分区剪裁则发生在sql语句执行阶段,对于分区键是常量值优化器在会走静态分区剪裁的,如果分区键是变量形式优化器只会走动态分区剪裁。

静态分区剪裁:

sys@CRMDB3>SELECT ORDERID,
  2         REGION,
  3         ORDERTYPE,
  4         TO_CHAR(GROUPNO) GROUPNO,
  5         CUSTTYPE,
  6         TO_CHAR(CUSTID) CUSTID,
  7         CUSTNAME,
  8         ACCESSTYPE,
  9         SERVNUMBER,
 10         RECDATE,
 11         OPERID,
 12         ORGID,
 13         TO_CHAR(OPERSESSONID) OPERSESSONID,
 14         PRIORITY,
 15         TO_CHAR(LINKORDERID) LINKORDERID,
 16         NOTES,
 17         STATUS,
 18         STATUSDATE,
 19         LINKTYPE,
 20         LINKREGION
 21    FROM tbcs.CS_CUST_ORDER
 22   WHERE REGION = 13
 23     AND SERVNUMBER = '13951759932'
 24     AND RECDATE >= TO_DATE('2015-04-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 25     AND RECDATE <= TO_DATE('2015-04-09 23:59:59', 'YYYY-MM-DD HH24:MI:SS')  ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1007517641

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |     1 |   108 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                           |     1 |   108 |     6   (0)| 00:00:01 |    31 |    31 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| CS_CUST_ORDER             |     1 |   108 |     6   (0)| 00:00:01 |    31 |    31 |
|*  3 |    INDEX RANGE SCAN                | IDX_CUST_ORDER_SERVNUMBER |     2 |       |     3   (0)| 00:00:01 |    31 |    31 |
--------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RECDATE">=TO_DATE(' 2015-04-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "REGION"=13 AND
              "RECDATE"<=TO_DATE(' 2015-04-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("SERVNUMBER"='13951759932')


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

静态分区剪裁中pstart和pstop都是具体的分区编号值,比如这里分区剪裁就是走编号31的分区

动态分区剪裁:

sys@CRMDB3>explain plan for
SELECT ORDERID,
       REGION,
       ORDERTYPE,
       TO_CHAR(GROUPNO) GROUPNO,
       CUSTTYPE,
       TO_CHAR(CUSTID) CUSTID,
       CUSTNAME,
       ACCESSTYPE,
       SERVNUMBER,
       RECDATE,
       OPERID,
       ORGID,
       TO_CHAR(OPERSESSONID) OPERSESSONID,
       PRIORITY,
       TO_CHAR(LINKORDERID) LINKORDERID,
       NOTES,
       STATUS,
       STATUSDATE,
       LINKTYPE,
       LINKREGION
  FROM tbcs.CS_CUST_ORDER
 WHERE REGION = 13
   AND SERVNUMBER = :SERVERNUMBER
   AND RECDATE >= TO_DATE(:STARTDATE, 'YYYY-MM-DD HH24:MI:SS')
   AND RECDATE <= TO_DATE(:ENDDATE, 'YYYY-MM-DD HH24:MI:SS')                

sys@CRMDB3>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 126530307

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |     1 |   107 |   328   (0)| 00:00:04 |       |       |
|*  1 |  FILTER                             |                           |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                           |     1 |   107 |   328   (0)| 00:00:04 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| CS_CUST_ORDER             |     1 |   107 |   328   (0)| 00:00:04 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_CUST_ORDER_SERVNUMBER |     9 |       |   319   (0)| 00:00:04 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:ENDDATE,'YYYY-MM-DD HH24:MI:SS')>=TO_DATE(:STARTDATE,'YYYY-MM-DD HH24:MI:SS'))
   3 - filter("REGION"=13 AND "RECDATE">=TO_DATE(:STARTDATE,'YYYY-MM-DD HH24:MI:SS') AND
              "RECDATE"<=TO_DATE(:ENDDATE,'YYYY-MM-DD HH24:MI:SS'))
   4 - access("SERVNUMBER"=:SERVERNUMBER)

19 rows selected.

动态分区剪裁执行计划中pstart、pstop都是key的形式,这里扫描的分区无法确定,因为有变量的因素导致无法确定recdate分区键的范围,所以这里的pstart和pstop都是key形式

sys@CRMDB3>@parttab_key.sql
Enter value for table_name: CS_CUST_ORDER
old   8:  where a.name = upper('& Table_name')
new   8:  where a.name = upper('CS_CUST_ORDER')
Enter value for owner: tbcs
old   9:    and a.owner = upper('&owner')
new   9:    and a.owner = upper('tbcs')

COLUMN_NAME                                        COLUMN_POSITION OBJEC PARTITION_COUNT PARTITION SUBPARTIT
-------------------------------------------------- --------------- ----- --------------- --------- ---------
RECDATE                                                          2 TABLE             160 RANGE     NONE
REGION                                                           1 TABLE             160 RANGE     NONE

上面动态分区剪裁中:表CS_CUST_ORDER是以region+recdate的range分区,sql语句中由于只有region条件是静态值,而recdate是绑定变量的形式,优化器这里走的分区剪裁分为两部分:通过region条件的谓词走静态分区剪裁;通过recdate条件的谓词走动态分区剪裁。

静态分区剪裁和动态分区剪裁在IO性能上是否有差异:

sys@CRMDB3>variable SERVERNUMBER varchar2(32);
sys@CRMDB3>exec :SERVERNUMBER:='13951759932';
sys@CRMDB3>variable STARTDATE varchar2(32);
sys@CRMDB3>exec :STARTDATE:='2015-04-06 00:00:00';
sys@CRMDB3>variable ENDDATE varchar2(32);
sys@CRMDB3>exec :ENDDATE:='2015-04-09 23:59:59';
sys@CRMDB3>SELECT ORDERID,
  2         REGION,
  3         ORDERTYPE,
  4         TO_CHAR(GROUPNO) GROUPNO,
  5         CUSTTYPE,
  6         TO_CHAR(CUSTID) CUSTID,
  7         CUSTNAME,
  8         ACCESSTYPE,
  9         SERVNUMBER,
 10         RECDATE,
 11         OPERID,
 12         ORGID,
 13         TO_CHAR(OPERSESSONID) OPERSESSONID,
 14         PRIORITY,
 15         TO_CHAR(LINKORDERID) LINKORDERID,
 16         NOTES,
 17         STATUS,
 18         STATUSDATE,
 19         LINKTYPE,
 20         LINKREGION
 21    FROM tbcs.CS_CUST_ORDER
 22   WHERE REGION = 13
 23     AND SERVNUMBER = :SERVERNUMBER
 24     AND RECDATE >= TO_DATE(:STARTDATE, 'YYYY-MM-DD HH24:MI:SS')
 25     AND RECDATE <= TO_DATE(:ENDDATE, 'YYYY-MM-DD HH24:MI:SS')  ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 126530307

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |     1 |   107 |   328   (0)| 00:00:04 |       |       |
|*  1 |  FILTER                             |                           |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                           |     1 |   107 |   328   (0)| 00:00:04 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| CS_CUST_ORDER             |     1 |   107 |   328   (0)| 00:00:04 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_CUST_ORDER_SERVNUMBER |     9 |       |   319   (0)| 00:00:04 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:ENDDATE,'YYYY-MM-DD HH24:MI:SS')>=TO_DATE(:STARTDATE,'YYYY-MM-DD HH24:MI:SS'))
   3 - filter("REGION"=13 AND "RECDATE">=TO_DATE(:STARTDATE,'YYYY-MM-DD HH24:MI:SS') AND
              "RECDATE"<=TO_DATE(:ENDDATE,'YYYY-MM-DD HH24:MI:SS'))
   4 - access("SERVNUMBER"=:SERVERNUMBER)


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

这里看出来静态和动态分区剪裁在IO性能上并没有差异。

如果分区键被用作表达式或者函数运算,将导致优化器无法走分区剪裁,这个跟索引列被用作表达式或者函数运算一样:

SQL> create table t_local01 partition by range(object_id)
  2      (partition p1 values less than(10000),
  3      partition p2 values less than(20000),
  4      partition p3 values less than(30000),
  5      partition p4 values less than(40000),
  6      partition p5 values less than(maxvalue))
  7      as select * from dba_objects;

Table created.

SQL> create index ind_dataobjid on t_local01(data_object_id) local;

Index created.

SQL> select * from t_local01 where object_id<10000 and data_object_id=110;


Execution Plan
----------------------------------------------------------
Plan hash value: 4200285870

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     2 |   414 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |               |     2 |   414 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_LOCAL01     |     2 |   414 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | IND_DATAOBJID |     2 |       |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------

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

   3 - access("DATA_OBJECT_ID"=110)

SQL> select * from t_local01 where object_id+99<10000 and data_object_id=110;


Execution Plan
----------------------------------------------------------
Plan hash value: 990146937

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |    38 |  7866 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |               |    38 |  7866 |     7   (0)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_LOCAL01     |    38 |  7866 |     7   (0)| 00:00:01 |     1 |     5 |
|*  3 |    INDEX RANGE SCAN                | IND_DATAOBJID |    15 |       |     6   (0)| 00:00:01 |     1 |     5 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"+99<10000)
   3 - access("DATA_OBJECT_ID"=110)

SQL> select * from t_local01 where trunc(object_id)<10000 and data_object_id=110;


Execution Plan
----------------------------------------------------------
Plan hash value: 990146937

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |    38 |  7866 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |               |    38 |  7866 |     7   (0)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_LOCAL01     |    38 |  7866 |     7   (0)| 00:00:01 |     1 |     5 |
|*  3 |    INDEX RANGE SCAN                | IND_DATAOBJID |    15 |       |     6   (0)| 00:00:01 |     1 |     5 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TRUNC("OBJECT_ID")<10000)
   3 - access("DATA_OBJECT_ID"=110)

SQL> variable a number;
SQL> exec :a:=10000;

PL/SQL procedure successfully completed.

SQL> select * from t_local01 where trunc(object_id)<:a and data_object_id=110;


Execution Plan
----------------------------------------------------------
Plan hash value: 990146937

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |   207 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |               |     1 |   207 |     7   (0)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_LOCAL01     |     1 |   207 |     7   (0)| 00:00:01 |     1 |     5 |
|*  3 |    INDEX RANGE SCAN                | IND_DATAOBJID |     1 |       |     6   (0)| 00:00:01 |     1 |     5 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TRUNC("OBJECT_ID")<TO_NUMBER(:A))
   3 - access("DATA_OBJECT_ID"=110)
Posted in oracle, sql tuning and troubleshooting | 分区剪裁深入剖析已关闭评论

oracle执行计划中的执行步骤并不需要真正执行的场景

oracle对于逻辑存在错误的sql语句并不需要按照执行计划中的步骤执行,而是直接通过逻辑判断来终止某些执行计划中的步骤,也就是oracle的执行计划中的某些执行步骤在某些特定情况下并不需要真正执行。

单个表访问:
1 关于常量值存在逻辑错误的:

SQL> select * from t where 1<>1;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    98 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    | 86415 |  8270K|   343   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1343  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

由于1<>1是不成立的,所以优化器这里做了一个filter(NULL IS NOT NULL)的查询转换,并不需要真正扫描表段。

2 关于绑定变量带入的值存在逻辑错误的:

SQL> variable a number;
SQL> variable b number;
SQL> set autotrace traceonly;
SQL> set linesize 180
SQL> exec :a:=10;

PL/SQL procedure successfully completed.

SQL> exec :b:=10;

PL/SQL procedure successfully completed.

SQL> select * from t where :a<>:b and object_id=100;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    98 |   343   (1)| 00:00:05 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    98 |   343   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter(:A<>:B)
   2 - filter("OBJECT_ID"=100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1343  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

由于绑定变量:a<>:b是不成立的,oracle不需要真正扫描该表段,这里看出来sql语句仅仅只有一个递归请求的IO消耗。

SQL> select * from t where :a=:b and object_id=100;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    98 |   343   (1)| 00:00:05 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    98 |   343   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter(:A=:B)
   2 - filter("OBJECT_ID"=100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1254  consistent gets
       1252  physical reads
          0  redo size
       1343  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

由于绑定变量:a=:b是成立的,oracle是需要扫描该表段的,消耗了1254的逻辑读。

虽然这里对于绑定变量的sql语句,从执行计划来看oracle并没有filter(NULL IS NOT NULL)的谓词信息,但是实际带入值执行时是会进行逻辑校验的,如果谓词条件中存在恒不成立的逻辑,优化器就会进行调整优化,不会实际执行该sql语句的对应执行步骤,比如表扫描等。

同样的违反表中的约束的sql语句也不会真正的执行:

SQL> create table t01 as select * from dba_objects;

Table created.

SQL> alter table t01 modify object_id number not null;

Table altered.

SQL> set autotrace traceonly;

第二次执行:
SQL> select * from t01 where object_id is null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1091157851

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   207 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T01  | 86967 |    17M|   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1343  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

如果多表关联时驱动表返回了0条数据,那么被驱动表对应的执行计划也是不需要执行的:

hash join、nested loop、merge join等的过程,如果hash join时驱动表返回0行数据,那么被驱动表对应的执行计划是不需要真正执行的,以前一直只以为nested loop是这样,而hash join其实也是如此,换句话说就是任何表关联操作,只要驱动表返回的rows是0,那么被驱动表是不需要去真正读的

SQL_ID  auch1syhaaaqs, child number 0
-------------------------------------
select /*+gather_plan_statistics ab*/distinct (B.custid) from
tbcs.grp_job_list A, tbcs.group_account B    where A.jobid = B.custid  
 and A.PLANEXECTIME < trunc(sysdate)    and A.Rectype =
'CheckOffLineCustJob'    and A.region = 13 and B.region = 13
 
Plan hash value: 1034937689
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |      1 |        |      0 |00:00:00.83 |    3024 |       |       |          |
|   1 |  SORT UNIQUE             |               |      1 |      6 |      0 |00:00:00.83 |    3024 |  1024 |  1024 |          |
|*  2 |   HASH JOIN              |               |      1 |  65693 |      0 |00:00:00.83 |    3024 |   977K|   977K|  156K (0)|
|   3 |    PARTITION RANGE SINGLE|               |      1 |   6053 |      0 |00:00:00.83 |    3024 |       |       |          |
|*  4 |     TABLE ACCESS FULL    | GRP_JOB_LIST  |      1 |   6053 |      0 |00:00:00.83 |    3024 |       |       |          |
|   5 |    PARTITION RANGE SINGLE|               |      0 |    121K|      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |     TABLE ACCESS FULL    | GROUP_ACCOUNT |      0 |    121K|      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("B"."CUSTID"=TO_NUMBER("A"."JOBID"))
   4 - filter(("A"."RECTYPE"='CheckOffLineCustJob' AND "A"."PLANEXECTIME"<TRUNC(SYSDATE@!) AND "A"."REGION"=13))
   6 - filter("B"."REGION"=13)

上面这个例子group_account表在这个hash join的关联过程中并没有真正的对这个表进行扫描,这个从buffers是0可以得出。

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  an0c7ytkuvwvk, child number 0
-------------------------------------
select /*+gather_plan_statistics ab use_merge(a
b)*/a.object_type,a.owner from tne01 a,tne02 b where
a.object_id=1000000000000000000 and a.object_name=b.table_name

Plan hash value: 2777831816

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|   1 |  MERGE JOIN         |       |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|   2 |   SORT JOIN         |       |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |  1024 |  1024 |          |
|*  3 |    TABLE ACCESS FULL| TNE01 |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|*  4 |   SORT JOIN         |       |      0 |   2816 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   5 |    TABLE ACCESS FULL| TNE02 |      0 |   2816 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("A"."OBJECT_ID"=1000000000000000000)
   4 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
       filter("A"."OBJECT_NAME"="B"."TABLE_NAME")


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bwbah1z39b3vq, child number 0
-------------------------------------
select /*+gather_plan_statistics ab use_nl(a b)*/a.object_type,a.owner
from tne01 a,tne02 b where a.object_id=1000000000000000000 and
a.object_name=b.table_name

Plan hash value: 2731828895

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |00:00:00.01 |    1236 |   1233 |
|   1 |  NESTED LOOPS      |       |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |
|*  2 |   TABLE ACCESS FULL| TNE01 |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |
|*  3 |   TABLE ACCESS FULL| TNE02 |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("A"."OBJECT_ID"=1000000000000000000)
   3 - filter("A"."OBJECT_NAME"="B"."TABLE_NAME")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b1vk3f06k88fk, child number 0
-------------------------------------
select /*+gather_plan_statistics ab*/a.object_type,a.owner,b.table_name
from tne01 a,tne02 b where a.object_id=1000000000000000000

Plan hash value: 130894238

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN|       |      1 |  38199 |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|*  2 |   TABLE ACCESS FULL  | TNE01 |      1 |     14 |      0 |00:00:00.01 |    1236 |   1233 |       |       |          |
|   3 |   BUFFER SORT        |       |      0 |   2816 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   4 |    TABLE ACCESS FULL | TNE02 |      0 |   2816 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("A"."OBJECT_ID"=1000000000000000000)

对于merge sort、nested loop、hash join、MERGE JOIN CARTESIAN都是如此,只要驱动表返回结果为0,被驱动表对应的执行计划并不需要真正的执行。

Posted in oracle, sql tuning and troubleshooting | oracle执行计划中的执行步骤并不需要真正执行的场景已关闭评论

将分区关键字从函数中拆分,分区裁剪的魅力

sql执行频率:sql每半个小时执行100次左右,每次消耗80多万的逻辑读,平均响应时间达到了4000ms到5000ms之间。
sql_text:
SELECT DISTINCT E.OPERID,
                D.OPERNAME,
                D.CONTACTPHONE,
                D.ORGID,
                E.AGIOPERCENT,
                G.OPROLEID,
                G.CYCLETYPE,
                (SELECT COUNT(1) AS COUNT
                   FROM tbcs.CS_LOG_APPLY_DISCOUNT
                  WHERE OPERID = E.OPERID
                    AND STATUS = 3
                    AND REGION = 17
                    AND TO_CHAR(APPLYDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY')) =
                        TO_CHAR(SYSDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY'))) AS USEDTIMES
  FROM tbcs.OPERATOR D,
       (SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
          FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
         WHERE A.OPERNAME LIKE :OPERNAME
           AND A.REGION IN (17, 99)
           AND A.STATUS = 1
           AND A.OPERID = B.OPERID
           AND B.STATUS = 1
           AND B.ROLEID = C.OPROLEID
           AND C.REGION IN (17, 99)
         GROUP BY B.OPERID) E,
      tbcs.OPERATOR_WORKGROUP F,
       tbcs.CS_DB_ROLE_DISCOUNT G
 WHERE D.OPERID = E.OPERID
   AND F.OPERID = E.OPERID
   AND G.OPROLEID = F.ROLEID
   AND G.AGIOPERCENT = E.AGIOPERCENT
 ORDER BY E.OPERID

Explained.

sys@CRMDB2>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3769650655

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                         |     1 |   113 |  1461   (2)| 00:00:18 |       |       |
|   1 |  SORT AGGREGATE                      |                         |     1 |    22 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR           |                         |     1 |    22 |    58   (0)| 00:00:01 |    39 |    77 |
|*  3 |    TABLE ACCESS FULL                 | CS_LOG_APPLY_DISCOUNT   |     1 |    22 |    58   (0)| 00:00:01 |    39 |    77 |
|   4 |  SORT UNIQUE                         |                         |     1 |   113 |  1460   (2)| 00:00:18 |       |       |
|   5 |   NESTED LOOPS OUTER                 |                         |     1 |   113 |  1459   (2)| 00:00:18 |       |       |
|   6 |    NESTED LOOPS                      |                         |     1 |   104 |  1458   (2)| 00:00:18 |       |       |
|   7 |     NESTED LOOPS                     |                         |     1 |    65 |  1456   (2)| 00:00:18 |       |       |
|*  8 |      HASH JOIN                       |                         |     3 |   129 |  1453   (2)| 00:00:18 |       |       |
|   9 |       VIEW                           |                         |     2 |    44 |  1424   (2)| 00:00:18 |       |       |
|  10 |        SORT GROUP BY                 |                         |     2 |   154 |  1424   (2)| 00:00:18 |       |       |
|* 11 |         HASH JOIN OUTER              |                         |     2 |   154 |  1423   (2)| 00:00:18 |       |       |
|* 12 |          HASH JOIN                   |                         |     2 |   136 |  1422   (2)| 00:00:18 |       |       |
|* 13 |           TABLE ACCESS FULL          | CS_DB_ROLE_DISCOUNT     |     7 |   140 |    29   (0)| 00:00:01 |       |       |
|* 14 |           TABLE ACCESS BY INDEX ROWID| T_UCP_STAFFROLE         |    10 |   250 |    15   (0)| 00:00:01 |       |       |
|  15 |            NESTED LOOPS              |                         |   399 |  9264 |  1393   (2)| 00:00:17 |       |       |
|* 16 |             TABLE ACCESS FULL        | T_UCP_STAFFBASICINFO    |    19 |   437 |  1233   (2)| 00:00:15 |       |       |
|* 17 |             INDEX RANGE SCAN         | IX_UCP_STAFFROLE_STAFF  |    21 |       |     2   (0)| 00:00:01 |       |       |
|  18 |          INDEX FULL SCAN             | PK_UCP_STAFFMAC         |   112 |  1008 |     1   (0)| 00:00:01 |       |       |
|  19 |       TABLE ACCESS FULL              | CS_DB_ROLE_DISCOUNT     |    21 |   441 |    29   (0)| 00:00:01 |       |       |
|* 20 |      INDEX UNIQUE SCAN               | PK_UCP_STAFFROLE        |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|  21 |     TABLE ACCESS BY INDEX ROWID      | T_UCP_STAFFBASICINFO    |     1 |    39 |     2   (0)| 00:00:01 |       |       |
|* 22 |      INDEX UNIQUE SCAN               | PK_T_UCP_STAFFBASICINFO |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 23 |    INDEX RANGE SCAN                  | PK_UCP_STAFFMAC         |     1 |     9 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("OPERID"=:B1 AND "STATUS"=3 AND "REGION"=17 AND
              TO_CHAR(INTERNAL_FUNCTION("APPLYDATE"),DECODE(:B2,'MON','MM','YEAR','YYYY'))=TO_CHAR(SYSDATE@!,DECODE(:B3,'MON','MM','YE
              AR','YYYY')))
   8 - access("G"."AGIOPERCENT"="E"."AGIOPERCENT")
  11 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))
  12 - access("STAFFROLE"."ROLEID"="C"."OPROLEID")
  13 - filter("C"."REGION"=17 OR "C"."REGION"=99)
  14 - filter("STAFFROLE"."STATUS"=1)
  16 - filter("BASIC"."STAFFNAME" LIKE :OPERNAME AND ("BASIC"."REGION"=17 OR "BASIC"."REGION"=99) AND
              TO_NUMBER(DECODE("BASIC"."STAFFIDSTATUS",'01','1','02','2','03','0',"BASIC"."STAFFIDSTATUS"))=1)
  17 - access("BASIC"."STAFFID"="STAFFROLE"."STAFFID")
  20 - access("G"."OPROLEID"="STAFFROLE"."ROLEID" AND "STAFFROLE"."STAFFID"="E"."OPERID")
  22 - access("BASIC"."STAFFID"="E"."OPERID")
  23 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))

48 rows selected.

从业务逻辑上来看,该sql语句的含义分为两部分:

1.部分1为获取tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C关联条件后分组GROUP BY B.OPERID后每组MIN(C.AGIOPERCENT) AS AGIOPERCENT,这个组成新表E表
(SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
WHERE A.OPERNAME LIKE :OPERNAME
AND A.REGION IN (17, 99)
AND A.STATUS = 1
AND A.OPERID = B.OPERID
AND B.STATUS = 1
AND B.ROLEID = C.OPROLEID
AND C.REGION IN (17, 99)
GROUP BY B.OPERID) E

2.部分2为新表e的结果集和tbcs.OPERATOR D,tbcs.OPERATOR_WORKGROUP F,tbcs.CS_DB_ROLE_DISCOUNT G做关联
tbcs.OPERATOR D,
(SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
WHERE A.OPERNAME LIKE :OPERNAME
AND A.REGION IN (17, 99)
AND A.STATUS = 1
AND A.OPERID = B.OPERID
AND B.STATUS = 1
AND B.ROLEID = C.OPROLEID
AND C.REGION IN (17, 99)
GROUP BY B.OPERID) E,
tbcs.OPERATOR_WORKGROUP F,
tbcs.CS_DB_ROLE_DISCOUNT G
WHERE D.OPERID = E.OPERID
AND F.OPERID = E.OPERID
AND G.OPROLEID = F.ROLEID
AND G.AGIOPERCENT = E.AGIOPERCENT

总体而言就是求tbcs.OPERATOR、tbcs.OPERATOR_WORKGROUP、tbcs.CS_DB_ROLE_DISCOUNT关联后按照GROUP BY B.OPERID分组后取min(AGIOPERCENT)的那一行数据,业务逻辑上除了用分析函数改写后让其只扫描表一次,其余没有什么合适的办法。

该sql语句只有一个绑定变量就是:OPERNAME,查看绑定变量的历史值都是%,我们带入了实际值查看该sql语句每个执行步骤的资源消耗:

SQL_ID  ckyxzw6nyvud1, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics ab*/DISTINCT E.OPERID,
                D.OPERNAME,
                D.CONTACTPHONE,
                D.ORGID,
                E.AGIOPERCENT,
                G.OPROLEID,
                G.CYCLETYPE,
                (SELECT COUNT(1) AS COUNT
                   FROM tbcs.CS_LOG_APPLY_DISCOUNT
                  WHERE OPERID = E.OPERID
                    AND STATUS = 3
                    AND REGION = 17
                    AND TO_CHAR(APPLYDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY')) =
                        TO_CHAR(SYSDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY'))) AS USEDTIMES
  FROM tbcs.OPERATOR D,
       (SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
          FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
         WHERE A.OPERNAME LIKE :OPERNAME
           AND A.REGION IN (17, 99)
           AND A.STATUS = 1
           AND A.OPERID = B.OPERID
           AND B.STATUS = 1
           AND B.ROLEID = C.OPROLEID
           AND C.REGION IN (17, 99)
         GROUP BY B.OPERID) E,
      tbcs.OPERATOR_WORKGROUP F,
       tbcs.CS_DB_ROLE_DISCOUNT G
 WHERE D.OPERID = E.OPERID
   AND F.OPERID = E.OPERID
   AND G.OPROLEID = F.ROLEID
   AND G.AGIOPERCENT = E.AGIOPERCENT
 ORDER BY E.OPERID

Plan hash value: 3769650655

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                         |      1 |        |   4050 |00:00:06.83 |     820K|       |       |          |
|   1 |  SORT AGGREGATE                      |                         |   4041 |      1 |   4041 |00:00:03.15 |     686K|       |       |          |
|   2 |   PARTITION RANGE ITERATOR           |                         |   4041 |      1 |      0 |00:00:03.14 |     686K|       |       |          |
|*  3 |    TABLE ACCESS FULL                 | CS_LOG_APPLY_DISCOUNT   |    157K|      1 |      0 |00:00:03.00 |     686K|       |       |          |
|   4 |  SORT UNIQUE                         |                         |      1 |      1 |   4050 |00:00:06.83 |     820K|   549K|   457K|  487K (0)|
|   5 |   NESTED LOOPS OUTER                 |                         |      1 |      1 |   4050 |00:00:03.58 |     133K|       |       |          |
|   6 |    NESTED LOOPS                      |                         |      1 |      1 |   4050 |00:00:03.56 |     133K|       |       |          |
|   7 |     NESTED LOOPS                     |                         |      1 |      1 |   4050 |00:00:03.50 |     127K|       |       |          |
|*  8 |      HASH JOIN                       |                         |      1 |      3 |  18077 |00:00:03.48 |     124K|  1645K|  1645K| 1548K (0)|
|   9 |       VIEW                           |                         |      1 |      2 |   4035 |00:00:03.46 |     124K|       |       |          |
|  10 |        SORT GROUP BY                 |                         |      1 |      2 |   4035 |00:00:03.46 |     124K|   302K|   302K|  268K (0)|
|* 11 |         HASH JOIN OUTER              |                         |      1 |      2 |   5060 |00:00:03.45 |     124K|  1321K|  1321K| 1459K (0)|
|* 12 |          HASH JOIN                   |                         |      1 |      2 |   5060 |00:00:02.65 |     124K|  1393K|  1393K| 1338K (0)|
|* 13 |           TABLE ACCESS FULL          | CS_DB_ROLE_DISCOUNT     |      1 |      7 |     13 |00:00:00.01 |     125 |       |       |          |
|* 14 |           TABLE ACCESS BY INDEX ROWID| T_UCP_STAFFROLE         |      1 |     10 |    140K|00:00:03.86 |     124K|       |       |          |
|  15 |            NESTED LOOPS              |                         |      1 |    399 |    153K|00:00:01.32 |   30253 |       |       |          |
|* 16 |             TABLE ACCESS FULL        | T_UCP_STAFFBASICINFO    |      1 |     19 |  13101 |00:00:00.37 |    5492 |       |       |          |
|* 17 |             INDEX RANGE SCAN         | IX_UCP_STAFFROLE_STAFF  |  13101 |     21 |    140K|00:00:00.87 |   24761 |       |       |          |
|  18 |          INDEX FULL SCAN             | PK_UCP_STAFFMAC         |      1 |    112 |    119 |00:00:00.01 |       1 |       |       |          |
|  19 |       TABLE ACCESS FULL              | CS_DB_ROLE_DISCOUNT     |      1 |     21 |     23 |00:00:00.01 |     125 |       |       |          |
|* 20 |      INDEX UNIQUE SCAN               | PK_UCP_STAFFROLE        |  18077 |      1 |   4050 |00:00:00.10 |    2830 |       |       |          |
|  21 |     TABLE ACCESS BY INDEX ROWID      | T_UCP_STAFFBASICINFO    |   4050 |      1 |   4050 |00:00:00.05 |    6157 |       |       |          |
|* 22 |      INDEX UNIQUE SCAN               | PK_T_UCP_STAFFBASICINFO |   4050 |      1 |   4050 |00:00:00.02 |    2092 |       |       |          |
|* 23 |    INDEX RANGE SCAN                  | PK_UCP_STAFFMAC         |   4050 |      1 |     43 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("OPERID"=:B1 AND "STATUS"=3 AND "REGION"=17 AND TO_CHAR(INTERNAL_FUNCTION("APPLYDATE"),DECODE(:B2,'MON','MM','YEAR','YYYY'))=T
              O_CHAR(SYSDATE@!,DECODE(:B3,'MON','MM','YEAR','YYYY'))))
   8 - access("G"."AGIOPERCENT"="E"."AGIOPERCENT")
  11 - access("BASIC"."STAFFID"="MAC"."STAFFID")
  12 - access("STAFFROLE"."ROLEID"="C"."OPROLEID")
  13 - filter(("C"."REGION"=17 OR "C"."REGION"=99))
  14 - filter("STAFFROLE"."STATUS"=1)
  16 - filter(("BASIC"."STAFFNAME" LIKE :OPERNAME AND INTERNAL_FUNCTION("BASIC"."REGION") AND
              TO_NUMBER(DECODE("BASIC"."STAFFIDSTATUS",'01','1','02','2','03','0',"BASIC"."STAFFIDSTATUS"))=1))
  17 - access("BASIC"."STAFFID"="STAFFROLE"."STAFFID")
  20 - access("G"."OPROLEID"="STAFFROLE"."ROLEID" AND "STAFFROLE"."STAFFID"="E"."OPERID")
  22 - access("BASIC"."STAFFID"="E"."OPERID")
  23 - access("BASIC"."STAFFID"="MAC"."STAFFID")

该sql的IO资源消耗主要是在以下几部:

部分1:
|* 14 |           TABLE ACCESS BY INDEX ROWID| T_UCP_STAFFROLE         |      1 |     10 |    140K|00:00:03.86 |     124K|       |       |          |
|  15 |            NESTED LOOPS              |                         |      1 |    399 |    153K|00:00:01.32 |   30253 |       |       |          |
|* 16 |             TABLE ACCESS FULL        | T_UCP_STAFFBASICINFO    |      1 |     19 |  13101 |00:00:00.37 |    5492 |       |       |          |
|* 17 |             INDEX RANGE SCAN         | IX_UCP_STAFFROLE_STAFF  |  13101 |     21 |    140K|00:00:00.87 |   24761 |       |       |          |

部分2:
|   2 |   PARTITION RANGE ITERATOR           |                         |   4041 |      1 |      0 |00:00:03.14 |     686K|       |       |          |
|*  3 |    TABLE ACCESS FULL                 | CS_LOG_APPLY_DISCOUNT   |    157K|      1 |      0 |00:00:03.00 |     686K|       |       |          |

部分1是优化器估算T_UCP_STAFFBASICINFO的谓词条件后返回只有19行数据,而实际上返回了13101行数据,进而选择了不合理的nested loop执行计划,这部分消耗了124k的逻辑读
部分2是最消耗IO资源,由于标量子查询部分只能选择类似nested loop的方式的filter的执行计划,更由于CS_LOG_APPLY_DISCOUNT是全表全分区扫描的方式,这部分消耗了686K的逻辑读

CS_LOG_APPLY_DISCOUNT表相关信息:该表是个range分区,分区键是(REGION+APPLYDATE),优化器这里只能走一个region条件的分区剪裁,由于APPLYDATE条件被嵌套在表达式中
TO_CHAR(APPLYDATE,
DECODE(G.CYCLETYPE,
‘MON’,
‘MM’,
‘YEAR’,
‘YYYY’)) =
TO_CHAR(SYSDATE,
DECODE(G.CYCLETYPE,
‘MON’,
‘MM’,
‘YEAR’,
‘YYYY’))

优化器这里没办法判断applydate的取值,也就不能进一步走applydate的分区剪裁

这二部分总的IO资源消耗基本和系统中查询到的80万左右的逻辑读基本相符合,下面我们主要优化第二部分查询:
关于这个不能进一步走CS_LOG_APPLY_DISCOUNT表分区键APPLYDATE的条件,如果能够不对分区键APPLYDATE做to_char的函数运算,那么优化器就有可能走进一步走APPLYDATE的动态分区剪裁,我们将这个sql语句进行一次改写,主要将标量子查询中的tbcs.CS_LOG_APPLY_DISCOUNT进行改写

case when改写:

sys@CRMDB2>variable OPERNAME varchar2(32);
sys@CRMDB2>exec :OPERNAME:='%';

PL/SQL procedure successfully completed.

sys@CRMDB2> SELECT DISTINCT E.OPERID,
  2                   D.OPERNAME,
  3                   D.CONTACTPHONE,
  4                   D.ORGID,
  5                   E.AGIOPERCENT,
  6                   G.OPROLEID,
  7                   G.CYCLETYPE,
  8                   (case
  9                     when G.CYCLETYPE = 'MON' then
 10                      (SELECT COUNT(1) AS COUNT
 11                         FROM tbcs.CS_LOG_APPLY_DISCOUNT
 12                        WHERE OPERID = E.OPERID
 13                          AND STATUS = 3
 14                          AND REGION = 17
 15                          and APPLYDATE >= trunc(sysdate, 'MM')
 16                          and APPLYDATE < trunc(add_months(sysdate, 1), 'MM'))
 17                     when G.CYCLETYPE = 'YEAR' then
 18                      (SELECT COUNT(1) AS COUNT
 19                         FROM tbcs.CS_LOG_APPLY_DISCOUNT
 20                        WHERE OPERID = E.OPERID
 21                          AND STATUS = 3
 22                          AND REGION = 17
 23                          and APPLYDATE >= trunc(sysdate, 'YYYY')
 24                          and APPLYDATE <
 25                              trunc(add_months(sysdate, 12), 'YYYY'))
 26                   end) AS USEDTIMES
 27     FROM tbcs.OPERATOR D,
 28          (SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
 29             FROM tbcs.OPERATOR            A,
 30                  tbcs.OPERATOR_WORKGROUP  B,
 31                  tbcs.CS_DB_ROLE_DISCOUNT C
 32            WHERE A.OPERNAME LIKE :OPERNAME
 33              AND A.REGION IN (17, 99)
 34              AND A.STATUS = 1
 35              AND A.OPERID = B.OPERID
 36              AND B.STATUS = 1
 37              AND B.ROLEID = C.OPROLEID
 38              AND C.REGION IN (17, 99)
 39            GROUP BY B.OPERID) E,
 40          tbcs.OPERATOR_WORKGROUP F,
 41          tbcs.CS_DB_ROLE_DISCOUNT G
 42    WHERE D.OPERID = E.OPERID
 43      AND F.OPERID = E.OPERID
 44      AND G.OPROLEID = F.ROLEID
 45      AND G.AGIOPERCENT = E.AGIOPERCENT
 46    ORDER BY E.OPERID
 47  ;

4051 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3931917816

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |     1 |   113 |  1461   (2)| 00:00:18 |       |       |
|   1 |  SORT AGGREGATE                        |                           |     1 |    53 |            |          |       |       |
|*  2 |   FILTER                               |                           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR            |                           |     1 |    53 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID  | CS_LOG_APPLY_DISCOUNT     |     1 |    53 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |      INDEX RANGE SCAN                  | IDX_APPLY_DISCOUNT_OPERID |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   6 |    SORT AGGREGATE                      |                           |     1 |    22 |            |          |       |       |
|*  7 |     FILTER                             |                           |       |       |            |          |       |       |
|   8 |      PARTITION RANGE ITERATOR          |                           |     1 |    22 |    70   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID| CS_LOG_APPLY_DISCOUNT     |     1 |    22 |    70   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |        INDEX RANGE SCAN                | IDX_APPLY_DISCOUNT_OPERID |   130 |       |     6   (0)| 00:00:01 |   KEY |   KEY |
|  11 |  SORT UNIQUE                           |                           |     1 |   113 |  1460   (2)| 00:00:18 |       |       |
|  12 |   NESTED LOOPS OUTER                   |                           |     1 |   113 |  1459   (2)| 00:00:18 |       |       |
|  13 |    NESTED LOOPS                        |                           |     1 |   104 |  1458   (2)| 00:00:18 |       |       |
|  14 |     NESTED LOOPS                       |                           |     1 |    65 |  1456   (2)| 00:00:18 |       |       |
|* 15 |      HASH JOIN                         |                           |     3 |   129 |  1453   (2)| 00:00:18 |       |       |
|  16 |       VIEW                             |                           |     2 |    44 |  1424   (2)| 00:00:18 |       |       |
|  17 |        SORT GROUP BY                   |                           |     2 |   154 |  1424   (2)| 00:00:18 |       |       |
|* 18 |         HASH JOIN OUTER                |                           |     2 |   154 |  1423   (2)| 00:00:18 |       |       |
|* 19 |          HASH JOIN                     |                           |     2 |   136 |  1422   (2)| 00:00:18 |       |       |
|* 20 |           TABLE ACCESS FULL            | CS_DB_ROLE_DISCOUNT       |     7 |   140 |    29   (0)| 00:00:01 |       |       |
|* 21 |           TABLE ACCESS BY INDEX ROWID  | T_UCP_STAFFROLE           |    10 |   250 |    15   (0)| 00:00:01 |       |       |
|  22 |            NESTED LOOPS                |                           |   399 |  9264 |  1393   (2)| 00:00:17 |       |       |
|* 23 |             TABLE ACCESS FULL          | T_UCP_STAFFBASICINFO      |    19 |   437 |  1233   (2)| 00:00:15 |       |       |
|* 24 |             INDEX RANGE SCAN           | IX_UCP_STAFFROLE_STAFF    |    21 |       |     2   (0)| 00:00:01 |       |       |
|  25 |          INDEX FULL SCAN               | PK_UCP_STAFFMAC           |   112 |  1008 |     1   (0)| 00:00:01 |       |       |
|  26 |       TABLE ACCESS FULL                | CS_DB_ROLE_DISCOUNT       |    21 |   441 |    29   (0)| 00:00:01 |       |       |
|* 27 |      INDEX UNIQUE SCAN                 | PK_UCP_STAFFROLE          |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|  28 |     TABLE ACCESS BY INDEX ROWID        | T_UCP_STAFFBASICINFO      |     1 |    39 |     2   (0)| 00:00:01 |       |       |
|* 29 |      INDEX UNIQUE SCAN                 | PK_T_UCP_STAFFBASICINFO   |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 30 |    INDEX RANGE SCAN                    | PK_UCP_STAFFMAC           |     1 |     9 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,1),'fmmm')>TRUNC(SYSDATE@!,'fmmm'))
   4 - filter("STATUS"=3 AND "REGION"=17 AND "APPLYDATE">=TRUNC(SYSDATE@!,'fmmm') AND
              "APPLYDATE"<TRUNC(ADD_MONTHS(SYSDATE@!,1),'fmmm'))
   5 - access("OPERID"=:B1)
   7 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,12),'fmyyyy')>TRUNC(SYSDATE@!,'fmyyyy'))
   9 - filter("STATUS"=3 AND "REGION"=17 AND "APPLYDATE">=TRUNC(SYSDATE@!,'fmyyyy') AND
              "APPLYDATE"<TRUNC(ADD_MONTHS(SYSDATE@!,12),'fmyyyy'))
  10 - access("OPERID"=:B1)
  15 - access("G"."AGIOPERCENT"="E"."AGIOPERCENT")
  18 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))
  19 - access("STAFFROLE"."ROLEID"="C"."OPROLEID")
  20 - filter("C"."REGION"=17 OR "C"."REGION"=99)
  21 - filter("STAFFROLE"."STATUS"=1)
  23 - filter("BASIC"."STAFFNAME" LIKE :OPERNAME AND ("BASIC"."REGION"=17 OR "BASIC"."REGION"=99) AND
              TO_NUMBER(DECODE("BASIC"."STAFFIDSTATUS",'01','1','02','2','03','0',"BASIC"."STAFFIDSTATUS"))=1)
  24 - access("BASIC"."STAFFID"="STAFFROLE"."STAFFID")
  27 - access("G"."OPROLEID"="STAFFROLE"."ROLEID" AND "STAFFROLE"."STAFFID"="E"."OPERID")
  29 - access("BASIC"."STAFFID"="E"."OPERID")
  30 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))

Statistics
----------------------------------------------------------
        109  recursive calls
          0  db block gets
     133787  consistent gets
          3  physical reads
          0  redo size
     183993  bytes sent via SQL*Net to client
        960  bytes received via SQL*Net from client
         42  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       4051  rows processed

逻辑读降低到了133787,表CS_LOG_APPLY_DISCOUNT也使用了OPERID字段的索引来进行索引范围扫描

这里对于类似于标量子查询中filter执行计划中重复的子查询部分,还可以考虑用with as构造临时表来代替之前子查询中的执行计划的IO成本:
将标量子查询的tbcs.CS_LOG_APPLY_DISCOUNT独立出来,用with as的改写方式改写为临时表,那么优化器做这部分子查询时就只会扫描表段tbcs.CS_LOG_APPLY_DISCOUNT一次就行,而后的查询都只需要扫描这个表生成的临时结果集:

sys@CRMDB2>with tab_temp as (SELECT /*+MATERIALIZE*/OPERID, APPLYDATE
                   FROM tbcs.CS_LOG_APPLY_DISCOUNT
                  WHERE STATUS = 3
                    AND REGION = 17
                   )
SELECT DISTINCT E.OPERID,
                D.OPERNAME,
                D.CONTACTPHONE,
                D.ORGID,
                E.AGIOPERCENT,
                G.OPROLEID,
                G.CYCLETYPE,
                (SELECT COUNT(1) AS COUNT
                   FROM tab_temp
                  WHERE OPERID = E.OPERID
                     AND TO_CHAR(APPLYDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY')) =
                        TO_CHAR(SYSDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY'))) AS USEDTIMES
  FROM tbcs.OPERATOR D,
       (SELECT B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
          FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
         WHERE A.OPERNAME LIKE '%'
           AND A.REGION IN (17, 99)
           AND A.STATUS = 1
           AND A.OPERID = B.OPERID
           AND B.STATUS = 1
           AND B.ROLEID = C.OPROLEID
           AND C.REGION IN (17, 99)
         GROUP BY B.OPERID) E,
      tbcs.OPERATOR_WORKGROUP F,
       tbcs.CS_DB_ROLE_DISCOUNT G
 WHERE D.OPERID = E.OPERID
   AND F.OPERID = E.OPERID
   AND G.OPROLEID = F.ROLEID
   AND G.AGIOPERCENT = E.AGIOPERCENT
 ORDER BY E.OPERID

4050 rows selected.

Elapsed: 00:00:02.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3349615405

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |     1 |   113 |  4517   (1)| 00:00:55 |       |       |
|   1 |  SORT AGGREGATE                       |                             |     1 |    27 |            |          |       |       |
|*  2 |   VIEW                                |                             |   514 | 13878 |     2   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL                  | SYS_TEMP_5FD9E65D7_30BC934D |   514 |  8224 |     2   (0)| 00:00:01 |       |       |
|   4 |  TEMP TABLE TRANSFORMATION            |                             |       |       |            |          |       |       |
|   5 |   LOAD AS SELECT                      | SYS_TEMP_5FD9E65D7_30BC934D |       |       |            |          |       |       |
|   6 |    PARTITION RANGE ITERATOR           |                             |   514 | 11308 |    58   (0)| 00:00:01 |    39 |    77 |
|*  7 |     TABLE ACCESS FULL                 | CS_LOG_APPLY_DISCOUNT       |   514 | 11308 |    58   (0)| 00:00:01 |    39 |    77 |
|   8 |   SORT UNIQUE                         |                             |     1 |   113 |  4458   (1)| 00:00:54 |       |       |
|   9 |    NESTED LOOPS OUTER                 |                             |     1 |   113 |  4457   (1)| 00:00:54 |       |       |
|  10 |     NESTED LOOPS                      |                             |     1 |   104 |  4456   (1)| 00:00:54 |       |       |
|  11 |      NESTED LOOPS                     |                             |     1 |    65 |  4454   (1)| 00:00:54 |       |       |
|* 12 |       HASH JOIN                       |                             |    50 |  2150 |  4404   (1)| 00:00:53 |       |       |
|  13 |        TABLE ACCESS FULL              | CS_DB_ROLE_DISCOUNT         |    21 |   441 |    29   (0)| 00:00:01 |       |       |
|  14 |        VIEW                           |                             |    33 |   726 |  4375   (1)| 00:00:53 |       |       |
|  15 |         SORT GROUP BY                 |                             |    33 |  2541 |  4375   (1)| 00:00:53 |       |       |
|* 16 |          HASH JOIN OUTER              |                             |    33 |  2541 |  4374   (1)| 00:00:53 |       |       |
|* 17 |           HASH JOIN                   |                             |    33 |  2244 |  4373   (1)| 00:00:53 |       |       |
|* 18 |            TABLE ACCESS FULL          | CS_DB_ROLE_DISCOUNT         |     7 |   140 |    29   (0)| 00:00:01 |       |       |
|* 19 |            TABLE ACCESS BY INDEX ROWID| T_UCP_STAFFROLE             |    10 |   250 |    15   (0)| 00:00:01 |       |       |
|  20 |             NESTED LOOPS              |                             |  7770 |   181K|  4344   (1)| 00:00:53 |       |       |
|* 21 |              TABLE ACCESS FULL        | T_UCP_STAFFBASICINFO        |   370 |  8510 |  1234   (2)| 00:00:15 |       |       |
|* 22 |              INDEX RANGE SCAN         | IX_UCP_STAFFROLE_STAFF      |    21 |       |     2   (0)| 00:00:01 |       |       |
|  23 |           INDEX FULL SCAN             | PK_UCP_STAFFMAC             |   112 |  1008 |     1   (0)| 00:00:01 |       |       |
|* 24 |       INDEX UNIQUE SCAN               | PK_UCP_STAFFROLE            |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|  25 |      TABLE ACCESS BY INDEX ROWID      | T_UCP_STAFFBASICINFO        |     1 |    39 |     2   (0)| 00:00:01 |       |       |
|* 26 |       INDEX UNIQUE SCAN               | PK_T_UCP_STAFFBASICINFO     |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 27 |     INDEX RANGE SCAN                  | PK_UCP_STAFFMAC             |     1 |     9 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("OPERID"=:B1 AND TO_CHAR(INTERNAL_FUNCTION("APPLYDATE"),DECODE(:B2,'MON','MM','YEAR','YYYY'))=TO_CHAR(SYSDATE@!
              ,DECODE(:B3,'MON','MM','YEAR','YYYY')))
   7 - filter("STATUS"=3 AND "REGION"=17)
  12 - access("G"."AGIOPERCENT"="E"."AGIOPERCENT")
  16 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))
  17 - access("STAFFROLE"."ROLEID"="C"."OPROLEID")
  18 - filter("C"."REGION"=17 OR "C"."REGION"=99)
  19 - filter("STAFFROLE"."STATUS"=1)
  21 - filter(("BASIC"."REGION"=17 OR "BASIC"."REGION"=99) AND
              TO_NUMBER(DECODE("BASIC"."STAFFIDSTATUS",'01','1','02','2','03','0',"BASIC"."STAFFIDSTATUS"))=1 AND "BASIC"."STAFFNAME" LIKE
              '%')
  22 - access("BASIC"."STAFFID"="STAFFROLE"."STAFFID")
  24 - access("G"."OPROLEID"="STAFFROLE"."ROLEID" AND "STAFFROLE"."STAFFID"="E"."OPERID")
  26 - access("BASIC"."STAFFID"="E"."OPERID")
  27 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))

Statistics
----------------------------------------------------------
         37  recursive calls
          8  db block gets
     174491  consistent gets
          1  physical reads
        712  redo size
     183957  bytes sent via SQL*Net to client
        961  bytes received via SQL*Net from client
         42  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       4050  rows processed

改写后的逻辑读将之前的80万降低到了17万,响应时间降低到2.08秒

部分1的nested loop可以强制添加use_hash的hint让其走hash join,资源消耗为:

sys@CRMDB2>with tab_temp as (SELECT /*+MATERIALIZE*/OPERID, APPLYDATE
                   FROM tbcs.CS_LOG_APPLY_DISCOUNT
                  WHERE STATUS = 3
                    AND REGION = 17
                   )
SELECT DISTINCT E.OPERID,
                D.OPERNAME,
                D.CONTACTPHONE,
                D.ORGID,
                E.AGIOPERCENT,
                G.OPROLEID,
                G.CYCLETYPE,
                (SELECT COUNT(1) AS COUNT
                   FROM tab_temp
                  WHERE OPERID = E.OPERID
                     AND TO_CHAR(APPLYDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY')) =
                        TO_CHAR(SYSDATE,
                                DECODE(G.CYCLETYPE,
                                       'MON',
                                       'MM',
                                       'YEAR',
                                       'YYYY'))) AS USEDTIMES
  FROM tbcs.OPERATOR D,
       (SELECT /*+use_hash(A B)*/B.OPERID, MIN(C.AGIOPERCENT) AS AGIOPERCENT
          FROM tbcs.OPERATOR A, tbcs.OPERATOR_WORKGROUP B, tbcs.CS_DB_ROLE_DISCOUNT C
         WHERE A.OPERNAME LIKE '%'
           AND A.REGION IN (17, 99)
           AND A.STATUS = 1
           AND A.OPERID = B.OPERID
           AND B.STATUS = 1
           AND B.ROLEID = C.OPROLEID
           AND C.REGION IN (17, 99)
         GROUP BY B.OPERID) E,
      tbcs.OPERATOR_WORKGROUP F,
       tbcs.CS_DB_ROLE_DISCOUNT G
 WHERE D.OPERID = E.OPERID
   AND F.OPERID = E.OPERID
   AND G.OPROLEID = F.ROLEID
   AND G.AGIOPERCENT = E.AGIOPERCENT
 ORDER BY E.OPERID;

4050 rows selected.

Elapsed: 00:00:01.87

Execution Plan
----------------------------------------------------------
Plan hash value: 599059510

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |     1 |   113 |  7553   (2)| 00:01:31 |       |       |
|   1 |  SORT AGGREGATE                 |                             |     1 |    27 |            |          |       |       |
|*  2 |   VIEW                          |                             |   514 | 13878 |     2   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL            | SYS_TEMP_5FD9E65D8_30BC934D |   514 |  8224 |     2   (0)| 00:00:01 |       |       |
|   4 |  TEMP TABLE TRANSFORMATION      |                             |       |       |            |          |       |       |
|   5 |   LOAD AS SELECT                | SYS_TEMP_5FD9E65D8_30BC934D |       |       |            |          |       |       |
|   6 |    PARTITION RANGE ITERATOR     |                             |   514 | 11308 |    58   (0)| 00:00:01 |    39 |    77 |
|*  7 |     TABLE ACCESS FULL           | CS_LOG_APPLY_DISCOUNT       |   514 | 11308 |    58   (0)| 00:00:01 |    39 |    77 |
|   8 |   SORT UNIQUE                   |                             |     1 |   113 |  7494   (2)| 00:01:30 |       |       |
|   9 |    NESTED LOOPS OUTER           |                             |     1 |   113 |  7493   (2)| 00:01:30 |       |       |
|  10 |     NESTED LOOPS                |                             |     1 |   104 |  7492   (2)| 00:01:30 |       |       |
|  11 |      NESTED LOOPS               |                             |     1 |    65 |  7490   (2)| 00:01:30 |       |       |
|* 12 |       HASH JOIN                 |                             |    50 |  2150 |  7440   (2)| 00:01:30 |       |       |
|  13 |        TABLE ACCESS FULL        | CS_DB_ROLE_DISCOUNT         |    21 |   441 |    29   (0)| 00:00:01 |       |       |
|  14 |        VIEW                     |                             |    33 |   726 |  7411   (2)| 00:01:29 |       |       |
|  15 |         SORT GROUP BY           |                             |    33 |  2541 |  7411   (2)| 00:01:29 |       |       |
|* 16 |          HASH JOIN OUTER        |                             |    33 |  2541 |  7410   (2)| 00:01:29 |       |       |
|* 17 |           HASH JOIN             |                             |    33 |  2244 |  7409   (2)| 00:01:29 |       |       |
|* 18 |            TABLE ACCESS FULL    | CS_DB_ROLE_DISCOUNT         |     7 |   140 |    29   (0)| 00:00:01 |       |       |
|* 19 |            HASH JOIN            |                             |  3867 |   181K|  7380   (2)| 00:01:29 |       |       |
|* 20 |             TABLE ACCESS FULL   | T_UCP_STAFFBASICINFO        |   370 |  8510 |  1234   (2)| 00:00:15 |       |       |
|* 21 |             TABLE ACCESS FULL   | T_UCP_STAFFROLE             |  1427K|    34M|  6135   (2)| 00:01:14 |       |       |
|  22 |           INDEX FULL SCAN       | PK_UCP_STAFFMAC             |   112 |  1008 |     1   (0)| 00:00:01 |       |       |
|* 23 |       INDEX UNIQUE SCAN         | PK_UCP_STAFFROLE            |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|  24 |      TABLE ACCESS BY INDEX ROWID| T_UCP_STAFFBASICINFO        |     1 |    39 |     2   (0)| 00:00:01 |       |       |
|* 25 |       INDEX UNIQUE SCAN         | PK_T_UCP_STAFFBASICINFO     |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 26 |     INDEX RANGE SCAN            | PK_UCP_STAFFMAC             |     1 |     9 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("OPERID"=:B1 AND TO_CHAR(INTERNAL_FUNCTION("APPLYDATE"),DECODE(:B2,'MON','MM','YEAR','YYYY'))=TO_CHAR(SYS
              DATE@!,DECODE(:B3,'MON','MM','YEAR','YYYY')))
   7 - filter("STATUS"=3 AND "REGION"=17)
  12 - access("G"."AGIOPERCENT"="E"."AGIOPERCENT")
  16 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))
  17 - access("STAFFROLE"."ROLEID"="C"."OPROLEID")
  18 - filter("C"."REGION"=17 OR "C"."REGION"=99)
  19 - access("BASIC"."STAFFID"="STAFFROLE"."STAFFID")
  20 - filter(("BASIC"."REGION"=17 OR "BASIC"."REGION"=99) AND
              TO_NUMBER(DECODE("BASIC"."STAFFIDSTATUS",'01','1','02','2','03','0',"BASIC"."STAFFIDSTATUS"))=1 AND
              "BASIC"."STAFFNAME" LIKE '%')
  21 - filter("STAFFROLE"."STATUS"=1)
  23 - access("G"."OPROLEID"="STAFFROLE"."ROLEID" AND "STAFFROLE"."STAFFID"="E"."OPERID")
  25 - access("BASIC"."STAFFID"="E"."OPERID")
  26 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))

Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
      83057  consistent gets
          2  physical reads
        712  redo size
     183957  bytes sent via SQL*Net to client
        961  bytes received via SQL*Net from client
         42  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       4050  rows processed

逻辑读降低到了83057万,响应时间为1.87秒,相比之下将分区键applydate从to_char函数拆分出来改写成case when的方式更加高效,这里with as仅仅只是做另一个思路的参考

关于是否添加use_hash的hint,虽然use_hash的执行计划对应的逻辑读只有83057,但是由于hash join需要在内存中构建hash table,hash join相比nested loop会更多的消耗cpu资源,如果驱动表数据增多还可能使用到磁盘来构建hash table,并且sql语句中还使用绑定变量WHERE A.OPERNAME LIKE :OPERNAME,如果:OPERNAME传递的值唯一性比较高的话,nested loop的方式会比hash join要合理,这个要看具体情景,这个案例虽然不难,但是确很典型的说明下分区裁剪的重要性,当然也是对with as优化方法的一个参考。

Posted in oracle, sql tuning and troubleshooting | 将分区关键字从函数中拆分,分区裁剪的魅力已关闭评论

索引空块较多造成index range scan的IO成本较高

通过监控系统发觉sql_id a599ctgam52tg每次需要消耗40000以上的逻辑读

sys@CRMDB2>@plan.sql
Enter value for hash_value: a599ctgam52tg

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a599ctgam52tg, child number 0
-------------------------------------
SELECT LOG.OID              OID,
       LOG.REGION           REGION,
       LOG.ACCEPT_SEQ       ACCEPT_SEQ,
       LOG.PROCESS_CODE     PROCESS_CODE,
       LOG.REQ_CHANNEL      REQ_CHANNEL,
       OPCODE.ROLLBACK_FLAG ROLLBACK_FLAG
  FROM INT_LOG_CRM2BOSS_ON LOG, INT_CRM2BOSS_OPCODE OPCODE
 WHERE LOG.PROCESS_CODE = OPCODE.PROCESS_CODE
   AND LOG.REQ_TIME < SYSDATE - 5 / (3600 * 24)
   AND (OPCODE.ROLLBACK_FLAG = 1 OR OPCODE.ROLLBACK_FLAG = 2)
   AND OPCODE.STATUS = 1
   AND ROWNUM < 200

Plan hash value: 4158953647

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |       |       |    43 (100)|          |       |       |
|*  1 |  COUNT STOPKEY                        |                            |       |       |            |          |       |       |
|   2 |   MERGE JOIN                          |                            |    14 |  1022 |    43   (3)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID        | INT_CRM2BOSS_OPCODE        |    10 |   210 |    14   (0)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN                   | PK_INT_CRM2BOSS_OPCODE     |    18 |       |     1   (0)| 00:00:01 |       |       |
|*  5 |    SORT JOIN                          |                            |    15 |   780 |    29   (4)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL               |                            |    15 |   780 |    28   (0)| 00:00:01 |     1 |     5 |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| INT_LOG_CRM2BOSS_ON        |    15 |   780 |    28   (0)| 00:00:01 |     1 |     5 |
|*  8 |       INDEX RANGE SCAN                | IDX_INT_LOG_CRM2BOSS_ON_RT |    15 |       |    13   (0)| 00:00:01 |     1 |     5 |
------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<200)
   3 - filter((INTERNAL_FUNCTION("OPCODE"."ROLLBACK_FLAG") AND "OPCODE"."STATUS"=1))
   5 - access("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
       filter("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
   8 - access("LOG"."REQ_TIME"<SYSDATE@!-.00005787037037037037037037037037037037037037)

从执行计划来看,IO资源消耗较大的步骤可能首先是PK_INT_CRM2BOSS_OPCODE全索引快速扫描,然后就是IDX_INT_LOG_CRM2BOSS_ON_RT的index range scan,而关联方式是merge join,这个关联并不会导致IO资源消耗增加,只会增加cpu资源的消耗。

该sql的响应时间并不长,我们完全可以执行一次sql,查看执行计划每个步骤的IO资源消耗和内存消耗,这里介绍一个hint gather_plan_statistics,先添加该hint执行目前sql语句,然后查看到cursor cache对应的sql_id,用dbms_xplan.display_cursor查看每个步骤的IO资源和内存消耗。

sys@CRMDB2>select * from table(dbms_xplan.display_cursor('9h657qq1jfj4y',null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9h657qq1jfj4y, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics ab*/
 LOG.OID              OID,
 LOG.REGION           REGION,
 LOG.ACCEPT_SEQ       ACCEPT_SEQ,
 LOG.PROCESS_CODE     PROCESS_CODE,
 LOG.REQ_CHANNEL      REQ_CHANNEL,
 OPCODE.ROLLBACK_FLAG ROLLBACK_FLAG
  FROM tbcs.INT_LOG_CRM2BOSS_ON LOG, tbcs.INT_CRM2BOSS_OPCODE OPCODE
 WHERE LOG.PROCESS_CODE = OPCODE.PROCESS_CODE
   AND LOG.REQ_TIME < SYSDATE - 5 / (3600 * 24)
   AND (OPCODE.ROLLBACK_FLAG = 1 OR OPCODE.ROLLBACK_FLAG = 2)
   AND OPCODE.STATUS = 1
   AND ROWNUM < 200

Plan hash value: 4158953647

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |      1 |        |      0 |00:00:01.07 |   46448 |       |       |          |
|*  1 |  COUNT STOPKEY                        |                            |      1 |        |      0 |00:00:01.07 |   46448 |       |       |          |
|   2 |   MERGE JOIN                          |                            |      1 |     14 |      0 |00:00:01.07 |   46448 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID        | INT_CRM2BOSS_OPCODE        |      1 |     10 |      8 |00:00:00.01 |       7 |       |       |          |
|   4 |     INDEX FULL SCAN                   | PK_INT_CRM2BOSS_OPCODE     |      1 |     18 |      9 |00:00:00.01 |       1 |       |       |          |
|*  5 |    SORT JOIN                          |                            |      8 |     15 |      0 |00:00:01.07 |   46441 | 18432 | 18432 |16384  (0)|
|   6 |     PARTITION RANGE ALL               |                            |      1 |     15 |    181 |00:00:00.03 |   46441 |       |       |          |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| INT_LOG_CRM2BOSS_ON        |      5 |     15 |    181 |00:00:01.07 |   46441 |       |       |          |
|*  8 |       INDEX RANGE SCAN                | IDX_INT_LOG_CRM2BOSS_ON_RT |      5 |     15 |    181 |00:00:01.07 |   46300 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<200)
   3 - filter((INTERNAL_FUNCTION("OPCODE"."ROLLBACK_FLAG") AND "OPCODE"."STATUS"=1))
   5 - access("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
       filter("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
   8 - access("LOG"."REQ_TIME"<SYSDATE@!-.00005787037037037037037037037037037037037037)


36 rows selected.

通过执行我们发觉IO资源消耗主要在IDX_INT_LOG_CRM2BOSS_ON_RT索引的index range scan部分,优化器估算返回15rows,实际返回181rows,但是这个差异也不应该造成index range scan有这么高的IO资源,那么完全有可能怀疑该索引有较多的空块,加上又没有走分区剪裁,导致index range scan时候逻辑读达到了46300多。

此时我们需要查看该表和索引统计信息:

**********************************************************
Table Level
**********************************************************

Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
INT_LOG_CRM2BOSS_ON                        15            6,26            0        0      66 YES                15 03-18-2015

Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OID                                      15   .06666667       1            0             15 03-18-2015
REGION                                    3   .33333333       1            0             15 03-18-2015
REQ_TIME                                  8   .12500000       1            0             15 03-18-2015
。。。。。。

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
IDX_INT_LOG_CRM2BOSS_ON_RT       2            3              0             15       0       0           15 03-18-2015
IND_INT_LOG_CRM2BOSS_ON_OID      2            4              0             16       0       0           16 03-18-2015

Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_INT_LOG_CRM2BOSS_ON_RT     REQ_TIME                          1 DATE
IND_INT_LOG_CRM2BOSS_ON_OID    REGION                            1 NUMBER(5,0) NOT NULL
                               OID                               2 NUMBER(22)
**********************************************************
Partition Level
**********************************************************

Partition              Number                        Empty Average    Chain Average Global Date
Name                  of Rows          Blocks       Blocks   Space    Count Row Len Stats  MM-DD-YYYY
-------------- -------------- --------------- ------------ ------- -------- ------- ------ ----------
P_R_12                      2            1,10            0       0        0      67 YES    03-18-2015
P_R_17                      2            1,10            0       0        0      67 YES    03-18-2015
P_R_18                      8            1,10            0       0        0      67 YES    03-18-2015
P_R_19                      3            0,46            0       0        0      67 YES    03-18-2015
P_R_99                      0            2,50            0       0        0       0 YES    03-18-2015
。。。。。。

Index                          Partition                  Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           Name           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ -------------- --- ------------ -------------- -------------- ------- ------- ------------ ----------
IDX_INT_LOG_CRM2BOSS_ON_RT     P_R_12           2            1              2              2       1       1            2 03-18-2015
                               P_R_17           2            1              1              2       1       2            2 03-18-2015
                               P_R_18           1            1              5              8       1       1            8 03-18-2015
                               P_R_19           2            1              0              3       0       0            3 03-18-2015
                               P_R_99           0            0              0              0       0       0            0 03-18-2015
IND_INT_LOG_CRM2BOSS_ON_OID    P_R_12           1            1              2              2       1       1            2 03-18-2015
                               P_R_17           2            1              2              2       1       1            2 03-18-2015
                               P_R_18           1            1              8              8       1       1            8 03-18-2015
                               P_R_19           2            1              0              3       0       0            3 03-18-2015
                               P_R_99           0            0              0              0       0       0            0 03-18-2015

可以发现表中存在严重的高水位线问题,然后索引的blevel很高,但是leaf blocks和number of rows都很小,可见由于这个表的dml数量较多,基本每天都删除和插入,而且索引IDX_INT_LOG_CRM2BOSS_ON_RT对应的字段是一个时间段字段REQ_TIME,由于索引不和heap table一样,要重复利用索引的空间,必须保证插入的key和删除的key是同一个范围内的(关于索引空间重用这一块比较复杂,实在找不到一个合适的词语来说索引重用的条件,就用了一个范围内的词,这个后续xiaoyu会与大家分享索引重用这块),而req_time这个时间字段是不断递增插入的,删除时又是删除之前小的req_time的key,这就导致了索引的空块特别多,索引的blevel增加到2.

这个表距离上次收集统计信息后的修改量:

sys@CRMDB2>select partition_name,
  2         subpartition_name,
  3         inserts,
  4         updates,
  5         deletes,
  6         timestamp,
  7         truncated,
  8         drop_segments
  9    from dba_tab_modifications
 10   where table_owner = upper('&owner')
 11     and table_name = upper('&tab_name')
 12  /
Enter value for owner: tbcs
old  10:  where table_owner = upper('&owner')
new  10:  where table_owner = upper('tbcs')
Enter value for tab_name: INT_LOG_CRM2BOSS_ON
old  11:    and table_name = upper('&tab_name')
new  11:    and table_name = upper('INT_LOG_CRM2BOSS_ON')

PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP           TRU DROP_SEGMENTS
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --- -------------
                                                                    2057          0       3702 2015-03-18 03:30:21 NO              0
P_R_12                                                               513          0        996 2015-03-18 03:30:21 NO              0
P_R_17                                                               365          0        641 2015-03-18 03:30:21 NO              0
P_R_18                                                               400          0        762 2015-03-18 03:30:21 NO              0
P_R_19                                                               774          0       1293 2015-03-18 03:30:21 NO              0

sys@CRMDB2>select sysdate from dual;

SYSDATE
-------------------
2015-03-18 12:15:30
在容灾环境重建索引:
SQL> alter index tbcs.IDX_INT_LOG_CRM2BOSS_ON_RT rebuild partition P_R_12;

Index altered.

SQL> alter index tbcs.IDX_INT_LOG_CRM2BOSS_ON_RT rebuild partition P_R_17;

Index altered.

SQL> alter index tbcs.IDX_INT_LOG_CRM2BOSS_ON_RT rebuild partition P_R_18;

Index altered.

SQL> alter index tbcs.IDX_INT_LOG_CRM2BOSS_ON_RT rebuild partition P_R_19;

Index altered.

再次执行逻辑读降低到了45
SQL> SELECT /*+gather_plan_statistics ab*/
  2   LOG.OID              OID,
  3   LOG.REGION           REGION,
  4   LOG.ACCEPT_SEQ       ACCEPT_SEQ,
  5   LOG.PROCESS_CODE     PROCESS_CODE,
  6   LOG.REQ_CHANNEL      REQ_CHANNEL,
  7   OPCODE.ROLLBACK_FLAG ROLLBACK_FLAG
  8    FROM tbcs.INT_LOG_CRM2BOSS_ON LOG, tbcs.INT_CRM2BOSS_OPCODE OPCODE
  9   WHERE LOG.PROCESS_CODE = OPCODE.PROCESS_CODE
 10     AND LOG.REQ_TIME < SYSDATE - 5 / (3600 * 24)
 11     AND (OPCODE.ROLLBACK_FLAG = 1 OR OPCODE.ROLLBACK_FLAG = 2)
 12     AND OPCODE.STATUS = 1
 13     AND ROWNUM < 200;

57 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4158953647

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |    14 |  1022 |    33   (4)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                        |                            |       |       |            |          |       |       |
|   2 |   MERGE JOIN                          |                            |    14 |  1022 |    33   (4)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID        | INT_CRM2BOSS_OPCODE        |    10 |   210 |    14   (0)| 00:00:01 |       |       |
|   4 |     INDEX FULL SCAN                   | PK_INT_CRM2BOSS_OPCODE     |    18 |       |     1   (0)| 00:00:01 |       |       |
|*  5 |    SORT JOIN                          |                            |    15 |   780 |    19   (6)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL               |                            |    15 |   780 |    18   (0)| 00:00:01 |     1 |     5 |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| INT_LOG_CRM2BOSS_ON        |    15 |   780 |    18   (0)| 00:00:01 |     1 |     5 |
|*  8 |       INDEX RANGE SCAN                | IDX_INT_LOG_CRM2BOSS_ON_RT |    15 |       |    11   (0)| 00:00:01 |     1 |     5 |
------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<200)
   3 - filter(("OPCODE"."ROLLBACK_FLAG"=1 OR "OPCODE"."ROLLBACK_FLAG"=2) AND "OPCODE"."STATUS"=1)
   5 - access("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
       filter("LOG"."PROCESS_CODE"="OPCODE"."PROCESS_CODE")
   8 - access("LOG"."REQ_TIME"<SYSDATE@!-.00005787037037037037037037037037037037037037)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
       3128  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         57  rows processed
Posted in oracle, sql tuning and troubleshooting | 索引空块较多造成index range scan的IO成本较高已关闭评论