诊断PROCEDURE 的一个奇怪问题

来源：互联网发布：tcp延迟确认算法编辑：程序博客网时间：2024/06/07 01:36

今天又个奇怪的问题，一条update语句单独执行很快，一放到存储过程中就非常慢，下面我们来做个试验重新这个问题，并做诊断：

create table t_all_objects as select * from user_objects;
create table t_dba_objects as select * from user_objects;
select * from t_all_objects;
select b.object_name, b.object_type from t_dba_objects b where b.object_id = 52766;

update t_all_objects a
   set (a.STATUS, a.object_type) =
   (select b.STATUS, b.object_type from t_dba_objects b
     where a.object_id = b.object_id and b.object_id = 52766)
   where exists(select 1 from t_dba_objects b where a.object_id = b.object_id and b.object_id = 52766);

单独执行上面的SQL语句非常快，0.1秒。

CREATE OR REPLACE PROCEDURE UPDATE_TEST(object_id IN number) as
BEGIN
update t_all_objects a
     set (a.STATUS, a.object_type) = (select b.STATUS, b.object_type
                                        from t_dba_objects b
                                       where a.object_id = b.object_id
                                         and b.object_id = object_id)
   where exists (select 1
            from t_dba_objects b
           where a.object_id = b.object_id
             and b.object_id = object_id);
       commit;
END UPDATE_TEST;

call UPDATE_TEST(44);--非常慢，执行了10分钟，t_all_objects中有5万条数据。

CREATE OR REPLACE PROCEDURE UPDATE_TEST(o_id IN number) as
BEGIN
update t_all_objects a
     set (a.STATUS, a.object_type) = (select b.STATUS, b.object_type
                                        from t_dba_objects b
                                       where a.object_id = b.object_id
                                         and b.object_id = o_id)
   where exists (select 1
            from t_dba_objects b
           where a.object_id = b.object_id
             and b.object_id = o_id);
   commit;
END UPDATE_TEST;

把存储过程中的变量换一个名称，再次call UPDATE_TEST(44);--非常快，执行了0.1秒。

为什么会慢呢？用10046诊断的对比结果：

SQL>alter session set session_cached_cursors =0;
SQL>alter session set events '10046 trace name context forever ,level 12' ;

SQL>update t_all_objects a
set (a.STATUS, a.object_type) =
(select b.STATUS, b.object_type from t_dba_objects b
where a.object_id = b.object_id and b.object_id = 44)
where exists(select 1 from t_dba_objects b where a.object_id = b.object_id and b.object_id = 44);
SQL>call UPDATE_TEST(44);
SQL>alter session set events '10046 trace name context off' ;

E:\oracle\product\10.2.0\admin\orcl\udump>tkprof orcl_ora_5432.trc 5432.txt sys=no aggregate=no

update t_all_objects a
set (a.STATUS, a.object_type) =
(select b.STATUS, b.object_type from t_dba_objects b
where a.object_id = b.object_id and b.object_id = 44)
where exists(select 1 from t_dba_objects b where a.object_id = b.object_id and b.object_id = 44);
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.05 0 3 0 0
Execute 1 0.00 0.13 319 1384 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 0.19 319 1387 3 1

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

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T_ALL_OBJECTS (cr=1384 pr=319 pw=0 time=138978 us)
1 HASH JOIN SEMI (cr=694 pr=319 pw=0 time=132012 us)
1 TABLE ACCESS FULL T_ALL_OBJECTS (cr=690 pr=319 pw=0 time=131625 us)
1 TABLE ACCESS FULL T_DBA_OBJECTS (cr=4 pr=0 pw=0 time=41 us)
1 FILTER (cr=690 pr=0 pw=0 time=6859 us)
1 TABLE ACCESS FULL T_DBA_OBJECTS (cr=690 pr=0 pw=0 time=29 us)

call UPDATE_TEST(44);

UPDATE T_ALL_OBJECTS A
SET (A.STATUS, A.OBJECT_TYPE) = (SELECT B.STATUS, B.OBJECT_TYPE
FROM T_DBA_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND B.OBJECT_ID = OBJECT_ID)
WHERE EXISTS (SELECT 1
FROM T_DBA_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND B.OBJECT_ID = OBJECT_ID);

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 400.75 416.89 0 34386840 51152 49834
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 400.75 416.89 0 34386840 51152 49834

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

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T_ALL_OBJECTS (cr=34386840 pr=0 pw=0 time=416895490 us)
49834 HASH JOIN RIGHT SEMI (cr=1380 pr=0 pw=0 time=1335936 us)
49835 TABLE ACCESS FULL T_DBA_OBJECTS (cr=690 pr=0 pw=0 time=199435 us)
49834 TABLE ACCESS FULL T_ALL_OBJECTS (cr=690 pr=0 pw=0 time=400440 us)
49834 TABLE ACCESS FULL T_DBA_OBJECTS (cr=34385460 pr=0 pw=0 time=412909087 us)

诊断结果：49834*690=34385460 等于cr=34385460，说明条件B.OBJECT_ID = OBJECT_ID恒为真，在exists括号中，oracle把OBJECT_ID当做是表T_DBA_OBJECTS的字段，导致每返回一行数据则把T_DBA_OBJECTS扫描一遍。

EXISTS (SELECT 1
FROM T_DBA_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND B.OBJECT_ID = OBJECT_ID)