查詢沒有使用繫結變數的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 ...