有关存储过程中动态sql执行慢的问题

yuhangding 2012-03-16 09:45:25
各位高手,我现在有一个存储过程,就是从一张大表里根据时间,号码等条件查询记录,查询语句使用的是动态sql,传入的条件采用的是绑定变量,输出的是个游标。从程序中调用该过程感觉要7-8秒才能出结果,通过plsql developer中test也不是太快。可是根据查询条件,拼成静态sql则执行的就很快1秒内就出来了。静态sql的执行计划也是合理的。
所以我怀疑是不是在存储过程中,使用动态sql执行计划不对了,没有使用索引。但是怎么看调用一个存储过程的执行计划呢?是不是通过dbms_system.set_sql_trace_in_session或者10046事件就可以呢?oracle中会不会出现使用绑定变量而不使用索引的情况呢?
还有,我给存储过程中传入号码条件的时,因为可能要查询多个号码,所以使用了自定义的嵌套表类型,不知道使用这个会不会影响索引。
PS:我在号码和时间列有复合索引。
...全文
442 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
yuhangding 2012-04-18
  • 打赏
  • 举报
回复
我觉得大家提的意见都不错,动态SQL绑定变量,如果不用hint确实不会走索引的。
目前我是舍弃了绑定变量,使用拼串的办法,确实有了提升。
回头再试试hint的方式效果怎么样。
cowboyhn 2012-03-17
  • 打赏
  • 举报
回复
当查询条件中的时间范围较小、嵌套表数据量小的情况下,使用HINT强制索引效率会比较好,但要限制输入条件的范围,要不范围太大时使用索引效率更差。
你要使用的索引应该是号码和时间的复合索引。
不需要把时间拼起来,尽量使用绑定变量。

[Quote=引用 5 楼 yuhangding 的回复:]
目前程序接口肯定不好改变,我想了两个方式,不知道是否可以?一个是在语句里加hint,让使用索引,另一个虽然使用sql但不适用绑定变量,把查询的时间拼起来,是不是能好些呢,因为时间列有一个索引。
[/Quote]
凉一刻 2012-03-17
  • 打赏
  • 举报
回复
1. before you set the v_sqlcmd, insert the data of sa_num stringarray into a middle table: mid_num(num varchar2(20));
i think the data volume is very small, so you don't need to create index on the middle table.

2. modify your v_sqlcmd like:
v_sqlcmd := 'select a.* from voice a, mid_num a
where a.begintime between :1 and :2
and a.num = b.num';
lxyzxq2008 2012-03-17
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 yuhangding 的回复:]
目前程序接口肯定不好改变,我想了两个方式,不知道是否可以?一个是在语句里加hint,让使用索引,另一个虽然使用sql但不适用绑定变量,把查询的时间拼起来,是不是能好些呢,因为时间列有一个索引。
引用 4 楼 lxyzxq2008 的回复:

引用 3 楼 yuhangding 的回复:

代码差不多就是这样,凭印象。
我也查了一些资料,可能就是由于动态sql绑定变量,让查询计划不是最优……
[/Quote]
恩,用hint来优化oracle的执行计划,利用索引检索应该能提高一些速度!
能提高多少不好所,看你的sql语句了!
yuhangding 2012-03-16
  • 打赏
  • 举报
回复

目前程序接口肯定不好改变,我想了两个方式,不知道是否可以?一个是在语句里加hint,让使用索引,另一个虽然使用sql但不适用绑定变量,把查询的时间拼起来,是不是能好些呢,因为时间列有一个索引。
[Quote=引用 4 楼 lxyzxq2008 的回复:]

引用 3 楼 yuhangding 的回复:

代码差不多就是这样,凭印象。
我也查了一些资料,可能就是由于动态sql绑定变量,让查询计划不是最优了。
SQL code

create procedure p_query_voice(
v_startime date
,v_endtime date
,sa_num stringarray --这个是我自定义的嵌套表类型,里面放着……
[/Quote]
lxyzxq2008 2012-03-16
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 yuhangding 的回复:]

代码差不多就是这样,凭印象。
我也查了一些资料,可能就是由于动态sql绑定变量,让查询计划不是最优了。
SQL code

create procedure p_query_voice(
v_startime date
,v_endtime date
,sa_num stringarray --这个是我自定义的嵌套表类型,里面放着多个要查询的号码
,ResultSet out s……
[/Quote]
唉幺,我去啊,果然是这样的sql,这个慢基本就已成事实了,
你可以比较一下窗口执行静态的sql和动态sql的执行计划不同,
但是想要改善,好像没什么好办法!
yuhangding 2012-03-16
  • 打赏
  • 举报
回复
代码差不多就是这样,凭印象。
我也查了一些资料,可能就是由于动态sql绑定变量,让查询计划不是最优了。

create procedure p_query_voice(
v_startime date
,v_endtime date
,sa_num stringarray --这个是我自定义的嵌套表类型,里面放着多个要查询的号码
,ResultSet out sys_refcursor
)
as
v_sqlcmd varchar2(1000);
begin
v_sqlcmd := 'select * from voice
where begintime between :1 and :2
and num in (select column_value from table(:3))';
open ResultSet for
v_sqlcmd
using v_starttime,v_endtime,sa_num;
end p_query_voice;

[Quote=引用 2 楼 lxyzxq2008 的回复:]

再补充下,
直接在窗口中执行sql和你的动态sql执行,很长的sql,
执行计划10有8,9是不一样的,因为他们解析本来就是不同的,尤其是一些写法更可能会导致动态sql执行很慢,
比如拼接字符串之类的,in("......")等等
[/Quote]
lxyzxq2008 2012-03-16
  • 打赏
  • 举报
回复
再补充下,
直接在窗口中执行sql和你的动态sql执行,很长的sql,
执行计划10有8,9是不一样的,因为他们解析本来就是不同的,尤其是一些写法更可能会导致动态sql执行很慢,
比如拼接字符串之类的,in("......")等等
lxyzxq2008 2012-03-16
  • 打赏
  • 举报
回复
--首先很肯定的说动态sql的执行效率慢是必然的,这个没啥好解析的

--楼主想看执行计划,可以这么看:
--先在V$SQLAREA中找到这个SQL,然后得到SQL_ID,去V$SQL_PLAN去看它执行的计划
--如下:

--先根据sql语句查找sql_id
select *
from V$SQLAREA sr
where sr.SQL_TEXT like '你的sql%'
order by sr.FIRST_LOAD_TIME desc;

--再根据sql_id查找执行计划
select * from V$SQL_PLAN pa where pa.SQL_ID = '上面得到的sql_id';

--说实话楼主不如把你的代码贴出来研究下

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧