oracle截取字段耗资源,oracle 中如何定位重要(消耗资源多)的SQL

在分析SQL性能的时候，经常需要确定资源消耗多的SQL，总结如下：

1查看值得怀疑的SQL

selectsubstr(to_char(s.pct,'99.00'),2)||'%'load,

s.executions executes,

p.sql_text

from(selectaddress,

disk_reads,

executions,

pct,

rank()over(orderbydisk_readsdesc) ranking

from(selectaddress,

disk_reads,

executions,

100*ratio_to_report(disk_reads)over() pct

fromsys.v_$sql

wherecommand_type!=47)

wheredisk_reads>50*executions) s,

sys.v_$sqltext p

wheres.ranking<=5

andp.address=s.address

orderby1, s.address, p.piece;

2查看消耗内存多的sql

selectb.username ,a. buffer_gets ,a.executions,

a.disk_reads/decode(a.executions,0,1,a.executions),a.sql_text SQL

fromv$sqlarea a,dba_users b

wherea.parsing_user_id = b.user_id

anda.disk_reads >10000

order bydisk_readsdesc;

3查看逻辑读多的SQL

select*

from(selectbuffer_gets, sql_text

fromv$sqlarea

wherebuffer_gets>500000

orderbybuffer_getsdesc)

whererownum<=30;

4查看执行次数多的SQL

selectsql_text, executions

from(selectsql_text, executionsfromv$sqlareaorderbyexecutionsdesc)

whererownum<81;

5查看读硬盘多的SQL

selectsql_text, disk_reads

from(selectsql_text, disk_readsfromv$sqlareaorderbydisk_readsdesc)

whererownum<21;

6查看排序多的SQL

selectsql_text, sorts

from(selectsql_text, sortsfromv$sqlareaorderbysortsdesc)

whererownum<21;

7分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

setpagesize600;

setlinesize120;

selectsubstr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs"

fromv$sqlarea

whereexecutions<5

groupbysubstr(sql_text,1,80)

havingcount(*)>30

orderby2;

8游标的观察

setpages300;

selectsum(a.value), b.name

fromv$sesstat a, v$statname b

wherea.statistic#=b.statistic#

andb.name='opened cursors current'

groupbyb.name;

selectcount(0)fromv$open_cursor;

selectuser_name, sql_text,count(0)

fromv$open_cursor

groupbyuser_name, sql_text

havingcount(0)>30;

9查看当前用户&username执行的SQL

selectsql_text

fromv$sqltext_with_newlines

where(hash_value, address)in

(selectsql_hash_value, sql_address

fromv$session

whereusername='&username')

orderbyaddress, piece;