国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

一條包含rowid的SQL引發的血案

IT那活兒 / 2615人閱讀
一條包含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那活兒

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129912.html

相關文章

  • 由for update引發的血案

    摘要：微信公眾號后端進階，專注后端技術分享框架分布式中間件服務治理等等。 微信公眾號「后端進階」，專注后端技術分享：Java、Golang、WEB框架、分布式中間件、服務治理等等。 老司機傾囊相授，帶你一路進階，來不及解釋了快上車！ 公司的某些業務用到了數據庫的悲觀鎖 for update，但有些同事沒有把 for update 放在 Spring 事務中執行，在并發場景下發生了嚴重的線程阻...

    roundstones 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<