oracle隐式转换问题

之前碰到一个隐式转换的问题,这里回顾总结下

业务需求是每天晚上要从生产环境同步数据到分析数据库，两个库的表按照系统设计之处表结构是要一样的，现在只模拟两个字段。

创建测试表

1 create table source_tab as select  a.col1 pc_id,a.col2 pc_name from sreal_tab a;--模拟生产环境表
2 create table target_tab as select  col1 tpc_id,col2 tpc_name from treal_tab where 1=2;--模拟分析数据库表 
3 create index idx_tpc_id  on target_tab (tpc_id);

如果记录已经在分析环境中存在的情况下，则更新tpc_name(名称字段)；
以下是存储过程实现代码

 1 create procedure proc_data_sync is  
 2 n_num number :=0;
 3 n_cnt number;
 4 begin
 5 for s in (select pc_id,pc_name from source_tab)
 6     loop
 7       n_num := n_num + 1;
 8       begin
 9         n_cnt := 0;
10         select count(*)
11           into n_cnt
12           from target_tab t
13          where s.pc_id = tpc_id;
14 
15         if n_cnt = 0 then
16           insert into target_tab
17             (tpc_id,
18              tpc_name
19              )
20           values
21             (s.pc_id,
22              s.pc_name
23              );
24         else
25           update target_tab
26              set tpc_name = s.pc_name
27            where tpc_id = s.pc_id;
28         end if;
29       exception
30         when others then 
31         dbms_output.put_line('执行到pc_id='||s.pc_id||'时出错'||sqlerrm);
32       end;
33       if mod(n_num, 5000) = 0 then
34         --每5000条提交一次
35         commit;
36       end if;
37     end loop;
38     commit;
39 end proc_data_sync;

在存储过程中为了让
   select count(*)
          into n_cnt
          from target_tab t
         where s.pc_id = tpc_id;
以及update
          update target_tab
             set tpc_name = s.pc_name
           where tpc_id = s.pc_id;

的查询速度创建了下列索引

create index idx_tpc_id  on target_tab (tpc_id);

等待测试很长时间没有结果
看看会话在做什么

1 select sid from v$session where machine='pc-wxc' and status='ACTIVE'  and module='PL/SQL Developer';
2 select c.SAMPLE_TIME,c.SQL_EXEC_ID,c.SQL_OPNAME,c.SESSION_ID,c.SQL_ID,c.SQL_PLAN_OPERATION,c.SQL_PLAN_OPTIONS from v$active_session_history c  where session_id='396' ;

看到几乎都是全表扫描

10    05-2月 -16 11.24.23.784 上午    20791275    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
11    05-2月 -16 11.24.22.774 上午    20791180    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
12    05-2月 -16 11.24.21.774 上午    20791074    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
13    05-2月 -16 11.24.20.774 上午    20790967    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
14    05-2月 -16 11.24.19.774 上午    20790870    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
15    05-2月 -16 11.24.18.761 上午    20790766    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
16    05-2月 -16 11.24.17.761 上午    20790663    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
17    05-2月 -16 11.24.16.761 上午    20790558    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL
18    05-2月 -16 11.24.15.751 上午    20790452    SELECT    396    1pt181n76fvzc    TABLE ACCESS    FULL

检查sql 执行计划

select * from TABLE(dbms_xplan.display_cursor('1pt181n76fvzc',1,'ADVANCED +PEEKED_BINDS'));

SQL_ID  1pt181n76fvzc, child number 0
-------------------------------------
SELECT COUNT(*) FROM TARGET_TAB T WHERE :B1 = TPC_ID
 
Plan hash value: 3416452088
 
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |            |     1 |    34 |            |          |
|*  2 |   TABLE ACCESS FULL| TARGET_TAB |     1 |    34 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :B1 (NUMBER): 27629632
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_NUMBER("TPC_ID")=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
 
Note
-----
   - dynamic sampling used for this statement (level=2)

开始以为优化器选择执行计划应该是有它的道理吧，那评估下全部执行总时间
加入dbms_utility.get_time来测量时间

 1 create or replace procedure proc_data_sync is
 2 n_num number :=0;
 3 n_cnt number;
 4 start_time number;
 5 end_time number;
 6 begin
 7 for s in (select pc_id,pc_name from source_tab where rownum <=10)
 8     loop
 9       n_num := n_num + 1;
10       start_time := dbms_utility.get_time;
11       begin
12         n_cnt := 0;
13         select count(*)
14           into n_cnt
15           from target_tab t
16          where s.pc_id = tpc_id;
17 
18         if n_cnt = 0 then
19           insert into target_tab
20             (tpc_id,
21              tpc_name
22              )
23           values
24             (s.pc_id,
25              s.pc_name
26              );
27         else
28           update target_tab
29              set tpc_name = s.pc_name
30            where tpc_id = s.pc_id;
31         end if;
32       exception
33         when others then
34         dbms_output.put_line('执行到pc_id='||s.pc_id||'时出错'||sqlerrm);
35       end;
36       end_time := dbms_utility.get_time;
37       dbms_output.put_line(' 单次执行时间为' ||to_char((end_time - start_time) / 100,
38                                      'fm999990.999') || 's.');
39       if mod(n_num, 5000) = 0 then
40         --每5000条提交一次
41         commit;
42       end if;
43     end loop;
44     commit;
45 end proc_data_sync;

end proc_data_sync;
结果是
单次执行时间为0.06s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.
单次执行时间为0.02s.

单次执行时间为0.02s，
select count(*)*0.02/60/60 from source_tab
结果是10个小时可以同步完，这个结果显然不太能接受
是不是因为没有收集优化器统计信息？
那就收一下

1 begin
2  dbms_stats.delete_table_stats(ownname=>'user_name',tabname=>'target_tab',cascade_indexes=>true);
3 end;

收完后重复上述过程问题依旧，什么情况？看执行计划，没什么发现，后注意到有这样的一行
2 - filter(TO_NUMBER("TPC_ID")=:B1)
这里做了函数的隐式类型转换，原来是这里的问题。
再去看看表结构 souce_tab的PC_ID列是number 类型，而target_tab的 TPC_ID是varrchar2的
至此问题就清晰了，在游标中做转换

for s in (select to_char(pc_id) pc_id,pc_name from source_tab)
再测试

单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.
单次执行时间为0.s.

再看执行计划

 1 SQL_ID  1pt181n76fvzc, child number 2
 2 -------------------------------------
 3 SELECT COUNT(*) FROM TARGET_TAB T WHERE :B1 = TPC_ID
 4  
 5 Plan hash value: 4054714533
 6  
 7 --------------------------------------------------------------------------------
 8 | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 9 --------------------------------------------------------------------------------
10 |   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
11 |   1 |  SORT AGGREGATE   |            |     1 |    34 |            |          |
12 |*  2 |   INDEX RANGE SCAN| IDX_TPC_ID |     1 |    34 |     1   (0)| 00:00:01 |
13 --------------------------------------------------------------------------------
14  
15 Query Block Name / Object Alias (identified by operation id):
16 -------------------------------------------------------------
17  
18    1 - SEL$1
19    2 - SEL$1 / T@SEL$1
20  
21 Outline Data
22 -------------
23  
24   /*+
25       BEGIN_OUTLINE_DATA
26       IGNORE_OPTIM_EMBEDDED_HINTS
27       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
28       DB_VERSION('11.2.0.3')
29       ALL_ROWS
30       OUTLINE_LEAF(@"SEL$1")
31       INDEX(@"SEL$1" "T"@"SEL$1" ("TARGET_TAB"."TPC_ID"))
32       END_OUTLINE_DATA
33   */
34  
35 Peeked Binds (identified by position):
36 --------------------------------------
37  
38    1 - :B1 (VARCHAR2(30), CSID=852): '27629632'
39  
40 Predicate Information (identified by operation id):
41 ---------------------------------------------------
42  
43    2 - access("TPC_ID"=:B1)
44  
45 Column Projection Information (identified by operation id):
46 -----------------------------------------------------------
47  
48    1 - (#keys=0) COUNT(*)[22]
49  
50 Note
51 -----
52    - dynamic sampling used for this statement (level=2)
53

"TPC_ID"=:B1 这个已经从过滤条件变成了访问条件,也就是用上tpc_id的列的索引了,隐式类型转换虽然让编写sql变得简单了,但是很多人不会注意到,这成了潜在的性能问题.

再跑测试出来后3分钟就搞定了,这个速度相差太远了吧.

posted on 2016-02-05 13:48 wangxingc 阅读(1505) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

wangxingc

oracle隐式转换问题

导航

公告