响应时间作为一种数据库性能的度量单位，在以前的版本中很难从数据库角度提取出来，但是从10g之后这样的度量单位变得更为简单。
在过去DBA做性能分析主要依据的指标是响应时间度量和用户活动情况，分析人员所面临的问题就是如果确定应用系统的哪一部分消耗了大部分时间，以及客观的反应用户对系统的使用体验。
OWI的出现犹如横空出世的利器，帮助DBA诊断瓶颈，发现瓶颈。但是OWI还是不能更直观的告诉用户关于活动会话或事物交易能力评估。很幸运的时候10g之后数据库中有关于会话和系统层面的响应时间相关的度量出现了。比如ADDM中有很多FINDING就已经给出官员响应时间的指标。10g提供的历史回溯功能可以更有效的帮助dba对系统过去时刻出现的性能问题做出基于响应时间的趋势分析。
系统级别分析
从全局角度考虑3个问题？
l 一般情况我们的数据库运行效率如何，是如何定义效率的呢？
l 我们的用户遭遇了什么样的平均响应时间？
l 系统中那些活动最影响系统的相应时间？
以上的疑问可以在10g中很高的通过METREC来回答。
---查看整体情况
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 to_char(end_time,'hh24:mi:ss'), value from sys.v_$sysmetric_history where metric_name = 'Database CPU Time Ratio' order by 1; |
---总体上 最大，最小，平均
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');
|
---可以从时间模型角度分析
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;
|
---从event class角度了解系统
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;
|
----查看一小时内的WAIT_CLASS
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
|
会话级别
---当然也可以查看具体哪个SESSION 的
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;
|
---下钻到某个时间的所有回话
select sess_id, username, program, wait_event, sess_time, round(100 * (sess_time / total_time), 2) pct_time_waited from (select a.session_id sess_id, decode(session_type, 'background', session_type, c.username) username, a.program program, b.name wait_event, sum(a.time_waited) sess_time from sys.v_$active_session_history a, sys.v_$event_name b, sys.dba_users c where a.event# = b.event# and a.user_id = c.user_id and sample_time > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' and b.wait_class = 'User I/O' group by a.session_id, decode(session_type, 'background', session_type, c.username), a.program, b.name), (select sum(a.time_waited) total_time from sys.v_$active_session_history a, sys.v_$event_name b where a.event# = b.event# and sample_time > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' and b.wait_class = 'User I/O') order by 6 desc;
SESS_ID USERNAME PROGRAM WAIT_EVENT SESS_TIME PCT_TIME_WAITED ------- -------- ---------- ------------------------- ---------- ------------- 242 SYS exp@RHAT9K db file scattered read 3502978 33.49 242 SYS oracle@RHA db file sequential read 2368153 22.64 242 SYS oracle@RHA db file scattered read 1113896 10.65 243 SYS oracle@RHA db file sequential read 992168 9.49
|
SQL级别
从ORACLE10g开始以下等待相关的时间列被加入到了视图中。
1. APPLICATION_WAIT_TIME
2. CONCURRENCY_WAIT_TIME
3. CLUSTER_WAIT_TIME
4. USER_IO_WAIT_TIME
5. PLSQL_EXEC_TIME
6. JAVA_EXEC_TIME
---按照IO等待取top5
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 < 6;
SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME ------------------------- ------------ ------------ ---------- --------------- select /*+ rule */ bucket db78fxqxwxt7 47815369 19000939 3423 SELECT :"SYS_B_0" FROM SY agdpzr94rf6v 36182205 10170226 2649 select obj#,type#,ctime,m 04xtrk7uyhkn 28815527 16768040 1345 select grantee#,privilege 2q93zsrvbdw4 28565755 19619114 803 select /*+ rule */ bucket 96g93hntrzjt 9411028 3754542 606 |
---再次进行下钻获得SQL详细
select event, time_waited, owner, object_name, current_file#, current_block# from sys.v_$active_session_history a, sys.dba_objects b where sql_id = '6gvch1xu9ca3g' and a.current_obj# = b.object_id and time_waited <> 0;
EVENT TIME_WAITED OWNER OBJECT_NAME file block ------------------------- ----------- ------ --------------------- ---- ------ db file sequential read 27665 SYSMAN MGMT_METRICS_1HOUR_PK 3 29438 db file sequential read 3985 SYSMAN SEVERITY_PRIMARY_KEY 3 52877 |
