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
