Select * From v$sqlarea Order By cpu_time Desc

--最耗资源 SELECT SQL_TEXT,hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC;

--其中包括 Database Wait Time Ratio（数据库等待时间比）

--和 Database CPU Time Ratio（数据库 CPU 时间比）这两个时间度量

select METRIC_NAME, VALUE

from SYS.V_$SYSMETRIC

where METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

--还可以使用以下查询快速了解数据库在前一个小时运行时整体性能是否下降过：

select end_time, value from sys.v_$sysmetric_history where metric_name = 'Database CPU Time Ratio' order by 1;

--查询 V$SYSMETRIC_SUMMARY 视图以充分了解整个数据库效率的最小值、最大值和平均值：

select CASE METRIC_NAME WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)' WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)' ELSE METRIC_NAME END METRIC_NAME, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2) ELSE MINVAL END MININUM, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2) ELSE MAXVAL END MAXIMUM, CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2) WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2) ELSE AVERAGE END AVERAGE from SYS.V_$SYSMETRIC_SUMMARY where METRIC_NAME in ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec') ORDER BY 1

--如果响应时间比所需的时间长，则数据库管理员需要了解哪些用户活动类型使数据库运行如此困难

select case db_stat_name when 'parse time elapsed' then 'soft parse time' else db_stat_name end db_stat_name, case db_stat_name when 'sql execute elapsed time' then time_secs - plsql_time when 'parse time elapsed' then time_secs - hard_parse_time else time_secs end time_secs, case db_stat_name when 'sql execute elapsed time' then round(100 * (time_secs - plsql_time) / db_time,2) when 'parse time elapsed' then round(100 * (time_secs - hard_parse_time) / db_time,2) else round(100 * time_secs / db_time,2) end pct_time from (select stat_name db_stat_name, round((value / 1000000),3) time_secs from sys.v_$sys_time_model where stat_name not in('DB time','background elapsed time', 'background cpu time','DB CPU')), (select round((value / 1000000),3) db_time from sys.v_$sys_time_model where stat_name = 'DB time'), (select round((value / 1000000),3) plsql_time from sys.v_$sys_time_model where stat_name = 'PL/SQL execution elapsed time'), (select round((value / 1000000),3) hard_parse_time from sys.v_$sys_time_model where stat_name = 'hard parse elapsed time') order by 2 desc;

--除活动时间外，数据库管理员还需要知道全局等待时间。

--在 Oracle 数据库 10g 之前，数据库管理员必须查看一个一个的等待事件来了解等待和瓶颈，

--但 Oracle 现在通过等待类为等待提供了一个摘要/汇总机制：

select WAIT_CLASS, TOTAL_WAITS, round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS, ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS, round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME

from

(select WAIT_CLASS, TOTAL_WAITS, TIME_WAITED from V$SYSTEM_WAIT_CLASS where WAIT_CLASS != 'Idle'),

(select sum(TOTAL_WAITS) SUM_WAITS, sum(TIME_WAITED) SUM_TIME from V$SYSTEM_WAIT_CLASS

where WAIT_CLASS != 'Idle')

order by 5 desc;

--现在揭示批量总等待时间是由用户 I/O 等待而导致要比尝试和测量单个等待事件来获得全局映像要容易得多。

--与响应时间度量一样，您还可以使用如下所示的查询按时间回顾上一小时的情况：

select to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time, b.wait_class, round((a.time_waited / 100),2) time_waited

from sys.v_$waitclassmetric_history a, sys.v_$system_wait_class b where a.wait_class# = b.wait_class# and b.wait_class != 'Idle'

order by 1,2;

--当然，您可以使用 V$SESS_TIME_MODEL 视图只专注于一个 SID，并获取会话的所有统计数据。

--还可以使用以下查询查看使用新等待类的当前会话：

select a.sid, b.username, a.wait_class, a.total_waits, round((a.time_waited / 100),2) time_waited_secs

from sys.v_$session_wait_class a, sys.v_$session b

where b.sid = a.sid and b.username is not null and a.wait_class != 'Idle' order by 5 desc;

/* 在 Oracle 数据库 10g 中，已向 V$SQLAREA 中添加了六个新的与等待相关的时间列。 APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME */

--一个可用于查找具有最高用户 I/O 等待的前n个 SQL 语句可以是：

select * from (select sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time from sys.v_$sqlarea order by 5 desc) where rownum <200 order by sql select from b where and and> 0;