oracle性能问题排查,性能测试Oracle消耗排查记录

1.耗费CPU或者是执行计划较多的sql语句的查询

SELECT T.CPU_TIME,--语句解析和执行的cpu时间

T.EXECUTIONS,--从加入缓存后的执行次数

T.ELAPSED_TIME,--语句解析和执行的时间

T.SORTS,

T.PARSE_CALLS,--软硬解析次数

T.OPTIMIZER_COST,--优化器计算的成本

T.SQL_TEXT--执行的sql语句

FROM SYS.V_$SQL T

WHERE T.OPTIMIZER_COST IS NOT NULL

AND T.EXECUTIONS > 500

ORDER BY T.OPTIMIZER_COST DESC;

2.获取耗费资源最多的sql语句

select * from

(select b.username username, a.DISK_READS reads, a.EXECUTIONS exec, a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio, a.SQL_TEXT statement

from sys.v_$sqlarea a, sys.dba_users b

where a.PARSING_USER_ID = b.user_id

and (b.username = '' or b.username = '')

and a.DISK_READS >= 50 ) t

where t.rds_exec_ratio >= 1

order by t.rds_exec_ratio desc;

3.查看当前会话信息

通过plsql中的工具栏中的会话，查看当前活跃会话，通过查看会话当前执行sql，判断性能瓶颈问题

查询当前活跃会话sql:

select * from SYS.V_$SESSION t where t.STATUS='ACTIVE' and osuser='' and type='' and username=''

当前会话游标信息：

select * from v$open_cursor where sid = :sid；

当前会话sql信息：

select sql_text from v$sqltext_with_newlines where address = hextoraw(:sql_address) and hash_value = :sql_hash_value order by piece / concatenate /

当前会话耗费信息：

select names.name, stats.statistic#, stats.value from v$sesstat stats, v$statname names where stats.sid = :sid and names.Statistic# = stats.Statistic# order by stats.statistic#

当前会话锁信息：

select l.*, o.owner object_owner, o.object_Name from sys.all_objects o, v$lock l where l.sid = :sid and l.type = 'TM' and o.object_id = l.id1

根据会话查看当前sql的消耗：

SELECT T.SID,

T1.SQL_TEXT,

T1.CPU_TIME,

T1.EXECUTIONS,

T1.OPTIMIZER_COST,

T1.ELAPSED_TIME,

T1.ELAPSED_TIME / T1.EXECUTIONS AS UNIT_ELAPSED_TIME,

T1.CPU_TIME / T1.EXECUTIONS AS UNIT_CPU_TIME

FROM SYS.V$SESSION T, SYS.V$SQL T1

WHERE T.STATUS = 'ACTIVE'

AND TYPE = ''

AND USERNAME = ''

AND T1.HASH_VALUE = T.SQL_HASH_VALUE

ORDER BY T1.CPU_TIME DESC