http://oneeyewolf.iteye.com/blog/19464
不需要从应用层找SQL,方向性错误,太累,也看不出效果。
而应当使用pl/SQL, toad等工具,分析出最bad的SQL语句,一看到这些语句后,再修改应用层的查询就是了。又快又方便。
-- 逻辑读多的SQL
select * from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc) where rownum<=30;
-- 执行次数多的SQL
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<81;
-- 读硬盘多的SQL
select sql_text,disk_reads from
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;
-- 排序多的SQL
select sql_text,sorts from
(select sql_text,sorts from v$sqlarea order by sorts desc)
where rownum<21;
--分析的次数太多,执行的次数太少,要用绑变量的方法来写sql
set pagesize 600;
set linesize 120;
select substr(sql_text,1,80) "sql", count(*), sum(executions) "totexecs"
from v$sqlarea
where executions < 5
group by substr(sql_text,1,80)
having count(*) > 30
order by 2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22492385/viewspace-720597/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22492385/viewspace-720597/