查找bad sql的方法
查找运行系统里bad sql是一个古老的话题 我们要根据自己的实际情况来分析
绝不能教条的运用下面介绍的这些方法
使用这些SQL语句时会对系统表产生分组操作当然也增大了系统的负载
建议大家在系统启动了一段时间后在半夜负载较轻的时间定时(例如:一个月)来查一查一定要具体问题具体分析
下面是我收藏的一些查找bad sql的方法:
column sql_text format a;
值得怀疑的SQL 来自
select
substr(to_char(spct ) ) || % load
sexecutions executes
psql_text
from
(
select
address
disk_reads
executions
pct
rank() over (order by disk_reads desc) ranking
from
(
select
address
disk_reads
executions
* ratio_to_report(disk_reads) over () pct
from
sysv_$sql
where
command_type !=
)
where
disk_reads > * executions
) s
sysv_$sqltext p
where
sranking <= and
paddress = saddress
order by
saddress ppiece
/
逻辑读多的SQL
select * from (select buffer_gets sql_text
from v$sqlarea
where buffer_gets >
order by buffer_gets desc) where rownum<=;
执行次数多的SQL
select sql_textexecutions from
(select sql_textexecutions from v$sqlarea order by executions desc)
where rownum<;
读硬盘多的SQL
select sql_textdisk_reads from
(select sql_textdisk_reads from v$sqlarea order by disk_reads desc)
where rownum<;
排序多的SQL
select sql_textsorts from
(select sql_textsorts from v$sqlarea order by sorts desc)
where rownum<;
分析的次数太多执行的次数太少要用绑变量的方法来写sql
set pagesize ;
set linesize ;
select substr(sql_text) sql count(*) sum(executions) totexecs
from v$sqlarea
where executions <
group by substr(sql_text)
having count(*) >
order by ;
游标的观察
set pages ;
select sum(avalue) bname
from v$sesstat a v$statname b
where astatistic# = bstatistic#
and bname = opened cursors current
group by bname;
select count() from v$open_cursor;
select user_namesql_textcount() from v$open_cursor
group by user_namesql_text having count()>;
查看当前用户&username执行的SQL
select sql_text from v$sqltext_with_newlines where (hash_valueaddress) in
(select sql_hash_valuesql_address from v$session where username=&username)
order by addresspiece;
