隐式类型转换（SYS_OP_C2C）-记一次SQL调优

最新推荐文章于 2024-03-23 16:28:18 发布

congsong2560

最新推荐文章于 2024-03-23 16:28:18 发布

阅读量495

点赞数

对客户数据库生成的AWR报告中发现的一个简单的SQL引起了我的注意：
SELECT* FROM t_ol_srcorder_m WHERE olorderno = :olorderno1 AND ROWNUM <= 1

Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
374.76	96	3.90	7.79	25.30	75.26	7ja8tx2udc4vb	Erp2Ec.exe	SELECT * FROM t_ol_srcorder_m ...

一个小时的AWR报告中，这个SQL执行了96次，每次耗时接近4秒。于是拿来这个SQL文本，先explain plan

使用explain plan可以看到SQL走了索引。不过这个是预估的，应该是不准确的。

查询v$sql_plan

从v$sql_plan中可以发现，这个SQL实际的执行计划采用的是table access full，也就是全表扫描。

对此，不得其解。因为我并没有做10053事件窥探实际的绑定变量。

select * from v$sql_plan p where p.SQL_ID = '7ja8tx2udc4vb'

ADDRESS HASH_VALUE SQL_ID PLAN_HASH_VALUE CHILD_ADDRESS CHILD_NUMBER TIMESTAMP OPERATION OPTIONS

00000005B29B6878 3033928555 7ja8tx2udc4vb 2791902860 00000005B102E190 2 2016/9/20 14:28:53 SELECT STATEMENT

00000005B29B6878 3033928555 7ja8tx2udc4vb 2791902860 00000005B102E190 2 2016/9/20 14:28:53 COUNT STOPKEY

00000005B29B6878 3033928555 7ja8tx2udc4vb 2791902860 00000005B102E190 2 2016/9/20 14:28:53 TABLE ACCESS FULL

尝试使用dbms_sqltune

DECLARE

l_sql_id v$session.prev_sql_id%TYPE;

l_tuning_task VARCHAR2(30);

BEGIN

l_sql_id := '7ja8tx2udc4vb';

l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

--:tuning_task := l_tuning_task;

dbms_output.put_line(l_tuning_task);

dbms_sqltune.execute_tuning_task(l_tuning_task);

dbms_output.put_line(l_tuning_task);

END;

SELECT dbms_sqltune.report_tuning_task('dbms_output输出的taskid') FROM dual;

1- Restructure SQL finding (see plan 1 in explain plans section)

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

谓词 SYS_OP_C2C("T_OL_SRCORDER_M"."OLORDERNO")=:B1 (在执行计划的行 ID 2 处使用) 包含索引列

"OLORDERNO" 的隐式数据类型转换。此隐式数据类型转换使优化程序无法选择表 "H2"."T_OL_SRCORDER_M" 的索引。

Recommendation

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

竟然存在一个隐式的类型转换。这样的话这么长的执行时间也就可以理解了。

这样的话就好理解多了。

查了一下sys_op_c2c, 应当是应用层把参数的类型转换了。等价于对参数使用了to_nchar函数进行了转换。

自己做了一下实验，果然对变量进行to_nchar操作会导致隐式类型转换

SQL> var objname varchar2(20)

SQL> begin :objname := 'T1'; end;

2 /

PL/SQL 过程已成功完成。

SQL> select * from t1 where object_name = to_nchar(:objname);

执行计划

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

Plan hash value: 3617692013

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

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

| 0 | SELECT STATEMENT | | 944 | 190K| 339 (1)| 00:00:05 |

|* 1 | TABLE ACCESS FULL| T1 | 944 | 190K| 339 (1)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

1 - filter(SYS_OP_C2C("OBJECT_NAME")=SYS_OP_C2C(:OBJNAME))

Note

-----

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

统计信息

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

7 recursive calls

0 db block gets

1318 consistent gets

0 physical reads

0 redo size

1607 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

对于这个SQL，应用不能调整的话，只能创建函数索引
create index idx_t_ol_srcorder_no on t_ol_srcorder_m(SYS_OP_C2C(olorderno))

但是应用能调整这个问题是最好的。

参考：http://joze-senegacnik.blogspot.com/2009/12/what-is-purpose-of-sysopc2c-internal.html
http://blog.csdn.net/msdnchina/article/details/37876187

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-2125198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-2125198/