Oracle调优

1.在 Oracle 数据库 10g 中轻松进行响应时间分析

http://www.oracle.com/technology/global/cn/pub/articles/schumacher_analysis.html?_template=/ocom/technology/content/print

 

主要内容:

先，通过在 Oracle 数据库 10g 中发出以下查询可以大体上获知数据库的运行状况：

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); 



METRIC_NAME                         VALUE

------------------------------ ----------

Database Wait Time Ratio                6

Database CPU Time Ratio                94




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


select  end_time,

value

from    sys.v_$sysmetric_history

where   metric_name = 'Database CPU Time Ratio'

order by 1;



END_TIME                  VALUE

-------------------- ----------

22-NOV-2004 10:00:38         98

22-NOV-2004 10:01:39         96

22-NOV-2004 10:02:37         99

22-NOV-2004 10:03:38        100

22-NOV-2004 10:04:37         99

22-NOV-2004 10:05:38         77

22-NOV-2004 10:06:36        100

22-NOV-2004 10:07:37         96

22-NOV-2004 10:08:39        100




同时，您可以使用如下查询来查询 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



METRIC_NAME                       MINIMUM    MAXIMUM    AVERAGE

------------------------------ ---------- ---------- ----------

CPU Usage Per Sec                       0          7          1

CPU Usage Per Txn                       1         29          8

Database CPU Time Ratio                61        100         94

Database Wait Time Ratio                0         39          5

Executions Per Sec                      2         60          8

Executions Per Txn                     16        164         41

Response Time Per Txn (secs)            0        .28        .08

SQL Service Response Time (sec          0          0          0

User Transaction Per Sec                0          1          0

但如果响应时间比所需的时间长，则数据库管理员需要了解哪些用户活动类型使数据库运行如此困难。同样，在 Oracle 数据库 10g 之前，此信息更难获得，但现在只需查询一下即可：

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; DB_STAT TIME_SECS PCT_TIME ----------------------------- --------- -------- sql execute elapsed time 13263.707 45.84 PL/SQL execution elapsed time 13234.738 45.74 hard parse elapsed time 1943.687 6.72 soft parse time 520.584 1.8 .

 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;



WAIT_CLASS      TOTAL_WAITS  PCT_WAITS TIME_WAITED_SECS   PCT_TIME

--------------- ----------- ---------- ---------------- ----------

User I/O            2245204       7.48          4839.43      54.39

System I/O          2438387       8.12          2486.21      27.94

Application          920385       3.07           513.56       5.77

Other                 39962        .13           422.36       4.75

Commit               200872        .67           284.76        3.2

Network            24133213      80.38           162.26       1.82

Concurrency            6867        .02           102.63       1.15






现在揭示批量总等待时间是由用户 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;



END_TIME             WAIT_CLASS      TIME_WAITED

-------------------- --------------- -----------

22-NOV-2004 11:28:37 Application               0

22-NOV-2004 11:28:37 Commit                  .02

22-NOV-2004 11:28:37 Concurrency               0

22-NOV-2004 11:28:37 Configuration             0

22-NOV-2004 11:28:37 Network                 .01

22-NOV-2004 11:28:37 Other                     0

22-NOV-2004 11:28:37 System I/O              .05

22-NOV-2004 11:28:37 User I/O                  0

.

.






当然，您可以使用 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;



SID USERNAME   WAIT_CLASS      TOTAL_WAITS TIME_WAITED_SECS

--- ---------- --------------- ----------- ----------------

257 SYSMAN     Application          356104            75.22

255 SYSMAN     Commit                14508            25.76

257 SYSMAN     Commit                25026            22.02

257 SYSMAN     User I/O              11924            19.98

.

.

如果需要按时间回顾以发现哪些会话已被登录并正使用最多的资源，可以使用以下查询。在以下示例中，我们将查看从 2004 年 11 月 21 日午夜至凌晨 5 点发生的涉及用户 I/O 等待的活动：

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