资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/一条包含rowid的SQL引发的血案

一条包含rowid的SQL引发的血案

IT那活儿 / 2614人阅读
一条包含rowid的SQL引发的血案
某公司核心系统从11.2.0.3升级到11.2.0.4，升级后CPU市盈率下降幅度很大，系统平稳，然而在第二天的业务高峰期某核心语句执行时间从原先的几毫秒变成几百秒，严重影响业务。很显然，在数据量变化不大的情况下，SQL语句执行效率下降，那大概率是执行计划发生了变化，至于执行计划变化的原因，因为系统版本变化，考虑如下：
1）统计信息变化
2）CBO优化器变化导致问题
3）优化器BUG
语句其实很简单，模拟如下：
▼▼▼
create table t as select * from dba_objects;
--表t的object_id列有索引，其实这里的last_ddl_time也是有索引的，而且可以走索引，为了简化，不进行模拟
create index idx_t on t(object_id);
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>t,no_invalidate=>false);
SQL> select count(*) from t;
  COUNT(*)
----------
    261898
原始语句：
select *
from(
select rowid,t.*
from t where t.object_id in
(
 select object_id
 from(
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
    ) where rownum<=100
)   and t.status=VALID
 and t.last_ddl_time > trunc(sysdate-200)
 order by last_ddl_time
) where rownum<=100;

本来在11.2.0.3上平稳运行，执行计划走NESTED LOOPS，子查询结果作为驱动，然后驱动外层表，从而走object_id索引。但是升级后的执行计划却是这样的：

▼▼▼
--执行12分钟还没有出现结果
SQL> set autotrace traceonly
SQL> select *
  2  from(
  3  select rowid,t.*
  4  from t where t.object_id in
  5  (
  6     select object_id
  7     from(
  8             select object_id
  9             from t
 10             where mod(object_id,10)=0 
 11             and status=VALID
 12             and last_ddl_time > trunc(sysdate-200)
                order by last_ddl_time
 13   14      ) where rownum<=100
 15  )   and t.status=VALID
 16             and t.last_ddl_time > trunc(sysdate-200)
 17             order by last_ddl_time
 18  ) where rownum<=100;
^Cselect *
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:12:05.73

--问题执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3028954274
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   219 |  2100K  (2)| 07:00:08 |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |     1 |   219 |  2100K  (2)| 07:00:08 |
|*  3 |    SORT ORDER BY STOPKEY     |      |     1 |   100 |  2100K  (2)| 07:00:08 |
|*  4 |     FILTER                   |      |       |       |            |          |
|*  5 |      TABLE ACCESS FULL       | T    |  4936 |   482K|   855   (3)| 00:00:11 |
|*  6 |      FILTER                  |      |       |       |            |          |
|*  7 |       COUNT STOPKEY          |      |       |       |            |          |
|   8 |        VIEW                  |      |    49 |   637 |   851   (2)| 00:00:11 |
|*  9 |         SORT ORDER BY STOPKEY|      |    49 |  1960 |   851   (2)| 00:00:11 |
|*  10 |          TABLE ACCESS FULL   | T    |    49 |  1960 |   850   (2)| 00:00:11 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   4 - filter( EXISTS ()
   5 - filter("T"."STATUS"=VALID AND
              "T"."LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
   6 - filter("OBJECT_ID"=:B1)
   7 - filter(ROWNUM<=100)
   9 - filter(ROWNUM<=100)
  10 - filter("STATUS"=VALID AND MOD("OBJECT_ID",10)=0 AND
              "LAST_DDL_TIME">TRUNC(SYSDATE@!-200))

 

和原先预计的走NESTED LOOPS不一样，走了FILTER操作，FILTER这种有2个子节点的，说明子查询未展开，也就是查询转换失败。一般遇到这种情况，首先为了快速解决问题，肯定是用SQL PROFILE之类的工具先绑定正确的执行计划，然而使用SQL PROFILE无效。那只能进一步分析解决问题了。查看10053：
▼▼▼
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery removal for query block SEL$3 (#3)
RSW: Not valid for subquery removal SEL$3 (#3)
Subquery unchanged.
Subquery Unnesting on query block SEL$2 (#2)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$2 (#2).
SU:   Checking validity of unnesting subquery SEL$3 (#3)
SU:     SU bypassed: Subquery in a view with rowid reference.                   
--含有ROWID的subquery unnest失败
SU:   Validity checks failed.


10053显示因为子查询的视图含有rowid导致subquery unnest失败，遇到这种情况要么去MOS上看看是不是BUG，要么就是改写语句。通过查询MOS，发现含有rowid的查询有不少问题：

通过搜索"subquery rowid"找到个比较相近的：
Query Referencing ROWID of Subquery With Join Fails With ORA-01445 (Doc ID 1929880.1)
上面的内容如下：

SYMPTOMS

▼▼▼
A query referencing a rowid from a subquery with a join fails with the following error:
SQL> select rowid
from
  (
    select e.empno
    from emp e left outer join (select deptno from dept) d
      on (e.deptno = d.deptno)
  );

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table


CAUSE

▼▼▼
The error is observed in query with ANSI joins because the way the query is written.
A rowid is only defined for individual rows in a table, so it is not legal to select a rowid from a subquery unless each row from that subquery can be guaranteed to be uniquely associated with exactly one row in one table.  Therefore, the subquery may have only one item in its FROM list; that item must also have a rowid; and the subquery must not use DISTINCT, GROUP BY, or anything else that might gather multiple rows into one.


SOLUTION
▼▼▼
Reference the rowid when it is valid as an explicit select list item:
SQL> select rid as "ROWID"
from
  ( select e.empno, e.rowid as rid
    from emp e left outer join (select deptno from dept) d
      on (e.deptno = d.deptno)
  );

可以按照上面的思路将rowid改写成别名，再在最外层将别名改回来，以保证SQL语句的查询列名一致，改写如下：

▼▼▼
——改写方案       
select rd as "ROWID",object_id,object_name,last_ddl_time
from(
select rowid rd,t.*
from t where t.object_id in
(
 select object_id
 from(
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
    ) where rownum<=100
)   and t.status=VALID
 and t.last_ddl_time > trunc(sysdate-200)
 order by last_ddl_time
) where rownum<=100;

 改写后的执行计划正确，如下所示：

▼▼▼
——改写方案       
selectExecution Plan
----------------------------------------------------------
Plan hash value: 16082276
-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    49 |  4900 |   931   (2)| 00:00:12 |
|*  1 |  COUNT STOPKEY                 |          |       |       |            |          |
|   2 |   VIEW                         |          |    49 |  4900 |   931   (2)| 00:00:12 |
|*  3 |    SORT ORDER BY STOPKEY       |          |    49 |  5145 |   931   (2)| 00:00:12 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T        |     1 |   100 |     2   (0)| 00:00:01 |
|   5 |      NESTED LOOPS              |          |    49 |  5145 |   930   (2)| 00:00:12 |
|   6 |       VIEW                     | VW_NSO_1 |    49 |   245 |   851   (2)| 00:00:11 |
|   7 |        HASH UNIQUE             |          |    49 |   245 |            |          |
|*  8 |         COUNT STOPKEY          |          |       |       |            |          |
|   9 |          VIEW                  |          |    49 |   245 |   851   (2)| 00:00:11 |
|* 10 |           SORT ORDER BY STOPKEY|          |    49 |  1960 |   851   (2)| 00:00:11 |
|* 11 |            TABLE ACCESS FULL   | T        |    49 |  1960 |   850   (2)| 00:00:11 |
|* 12 |       INDEX RANGE SCAN         | IDX_T    |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   4 - filter("T"."STATUS"=VALID AND "T"."LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
   8 - filter(ROWNUM<=100)
  10 - filter(ROWNUM<=100)
  11 - filter("STATUS"=VALID AND MOD("OBJECT_ID",10)=0 AND
              "LAST_DDL_TIME">TRUNC(SYSDATE@!-200))
  12 - access("T"."OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4026  consistent gets
          0  physical reads
          0  redo size
       7402  bytes sent via SQL*Net to client
        586  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed
) where rownum<=100;

现在执行计划正确走NESTED LOOPS，并且被驱动表走OBJECT_ID索引。再次思考一个问题，从SQL语句的语义和业务上分析，这个语句其实就是按照条件查询然后排序，查询出前100行的rowid和指定列，也就是没有必要用子查询或关联查询，可以将语句进一步简化：

▼▼▼
--其实原来的业务就是这么简单
select rowid,object_id,object_name,last_ddl_time
from (
 select object_id
 from t
 where mod(object_id,10)=0 
 and status=VALID
 and last_ddl_time > trunc(sysdate-200)
 order by timestamp,last_ddl_time
 )
) where rownum<=100;

通过业务分析后改写的SQL很简单，其实本质就是查询之后排序，然后找前100行。由于原始语句写成子查询并且又包含了rowid,导致触发优化器的限制，从而子查询无法unnest,最终走了FILTER操作影响执行效率。

通过这个案例，我们知道，一些oracle的key words,在做别名的时候还是需要谨慎，尽量避免使用key words作为别名，以防在不同版本中触发oracle的限制或bug。

END


更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器 云服务器 电信的短信多少钱一条 中的包含 一条能运行的c语言 html的包含

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129912.html

相关文章

  • 由for update引发的血案

    摘要：微信公众号后端进阶，专注后端技术分享框架分布式中间件服务治理等等。 微信公众号「后端进阶」，专注后端技术分享：Java、Golang、WEB框架、分布式中间件、服务治理等等。 老司机倾囊相授，带你一路进阶，来不及解释了快上车！ 公司的某些业务用到了数据库的悲观锁 for update，但有些同事没有把 for update 放在 Spring 事务中执行，在并发场景下发生了严重的线程阻...

    roundstones 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<