查找没有使用绑定变量的sql zt

http://www.itpub.net/335137.html[@more@]

1. Jametong
减产前40个字符相同, 有多于5个不同sql版本的sql语句^_^

select substr(sqltext,1,40) sql_text,count(*)
from v$sql
group by substr(sqltext,1,40)
having count(*) >= 5;

2. husthxd
定位应该使用绑定变量的sql语句

SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2
/

40表示sql语句的前40个字符是一样的，5表示执行次数小于5次，30表示在shared_pool_size中出现不下30次。

3. Yong Huang:

quote:
--------------------------------------------------------------------------------
最初由 jametong 发布
减产前40个字符相同, 有多于5个不同sql版本的sql语句^_^

select substr(sqltext,1,40) sql_text,count(*)
from v$sql
group by substr(sqltext,1,40)
having count(*) >= 5;
--------------------------------------------------------------------------------

That's a commonly suggested method to find SQLs not using bind variables.
Its shortcoming is obvious; how do you know 40 is enough or too much?
I have another way. For queries,

select sql_text from v$sql where upper(sql_text) like 'SELECT%WHERE%'
and sql_text not like '%:%' order by 1;

Change SELECT to other keywords. This approach is based on the fact
that SQLs using bind variables have colons in them. But the shortcoming
is that it will miss bad SQLs like this:

select * from myreport where msg like 'Today: good%' and msg_type = 123;

So, there's no absolutely perfect way. You get the idea.

Yong Huang

4. biti_rainy:

我的习惯,如果不是极度繁忙的系统，一般允许做如下操作
set line 1500
set pagesize 0
spool sql.txt
select sql_text,executions,... from v$sqlarea order by sql_text;
spool off;

我可能在几个有代表性的时间点做这个操作,然后统计出来对比

通常这可能有上万条sql，不过这不要紧，在 urltraedit or execel 中很容易看出来的，顺着往下一拉，一目了然，大量没有绑定而类似的sql，有几十条或者几百条的，你一下子就能感觉到的,类似的sql没有绑定，有多少句，分别大致执行了多少次，很容易就统计出来了。这就是我的 笨办法 你可能以为这很累，实际上我在半小时之内就几乎能统计好大部分没有绑定的sql。
另外，绑定的sql，在v$sqlarea 中，是用占位符号的(和你的绑定变量的定义符号无关)，可能形式如：
select * from t where ... col1 = :1 ... col2 = :a ...

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

转载于:http://blog.itpub.net/82387/viewspace-1022025/