好久没有写博客(吹nb)了,最近在工作中遇到了一些SQL硬编码的问题(系统部提出的要求,要求开发优化sql),下面具体说说。
1、硬解析即整个SQL语句的执行需要完完全全的解析,生成执行计划。
硬解析过程:
1.语法、语义及权限检查;
2.查询转换(通过应用各种不同的转换技巧,会生成语义上等同的新的SQL语句,如count(1)会转为count(*));
3.根据统计信息生成执行计划(找出成本最低的路径,这一步比较耗时);
4.将游标信息(执行计划)保存到库缓存。
2、软解析过程:
1.语法、语义及权限检查;
2.将整条SQL hash后从库缓存中执行计划。
软解析对比硬解析省了三个步骤。
3、总结:
1.尽可能的避免硬解析,因为硬解析需要更多的CPU资源,闩等。
2.cursor_sharing参数应权衡利弊,需要考虑使用similar与force带来的影响。
3.尽可能的使用绑定变量来避免硬解析。
生产环境中遇到的问题,系统上线一段时间后,监控发现某些语句很占内存:
可以用sql语句查询(需要DBA权限): select * from v$sql where sql_text like %表名%; 也可以不用条件,主要是查看的字段有sql_text,sql_fullText,sharable_mem(占用的共享内存大小,单位字节),presistent_mem(生命期类的固定内存大小,单位字节),runtime_mem(执行期内的固定内存大小); 具体可以参考/rnhhb/article/details/102791065;
硬解析产生的源头:编写sql语句不规范(包括通过java中编写的sql语句和oracle库中存储过程的sql语句),举一个简单的例子:
通过用户名和密码查询表user_Info表中的用户信息:"select * from user_info where user_name = "+ name +" and password="+password 【java中的错误写法】 这种写法虽然也能查询出来数据,但是存在问题:1.sql注入风险,2.会产生硬解析的问题。那么应该怎么解决呢?参数化:"select * from user_info where user_name =:1 and password=:2 " ,通过占位符的方式解决【正确的写法】。
oracle存储过程中的写法【会产生硬解析】:
create or repalce prodece A (namein varchar,outreslut out sys_refcursor)begin
v_sql varchar(2000) :="select * fromuser_info where user_name ="||name;
OPENoutreslut forv_sql USING name;
end A;
oracle存储过程中的写法【解决硬解析】:
create or repalce prodece A (namein varchar,outreslut out sys_refcursor)begin
v_sql varchar(2000) :="select * fromuser_info where user_name =:1 ";
--- execute immediate v_update_sql USINGname;(针对修改的);
--查询
OPENoutreslut forv_sql USING name;
end A;
综上所述:不要将参数拼接sql中,除非,参数是比较固定的值(或者变化频率不大(不超过10次)),应该采用参数化,通过参数占位符的方式传入参数。
另外可以参考博客:/leshami/article/details/6195483