AWR报告分析

Question

请教各位大侠，基于联机事务处理OLTP的数据分析，一般选取那些参数进行优化分析呢，多谢高手指点

Answer 1

等待事件汇总后可以显示数据库瓶颈消耗在那一类资源上。user io
system io
network
commit...以及cpu的繁忙程度，IO 等待情况。。avg_busy_time
avg_iowait_time
/.....

Answer 2

了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等

Answer 3

这个如何看参数呢？！
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read                 455           1      3   80.6 User I/O
control file sequential read            690           0      0   13.6 System I/O
DB CPU                                                0           5.1
db file scattered read                    1           0      1     .0 User I/O
log file sync                             1           0      0     .0 Commit
 

Answer 4

这个好复杂具体要看那些参数，如何优化呢？多谢
SQL ordered by CPU Time                        DB/Inst: DB/db  Snaps: 370-371
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total is the CPU Time divided into the Total CPU Time times 100
-> Total CPU Time (s):               0
-> Captured SQL account for      260.0% of Total    CPU      Elapsed                  CPU per          % Total
  Time (s)   Time (s)  Executions     Exec (s) % Total DB Time    SQL Id
---------- ---------- ------------ ----------- ------- ------- -------------
         0          3            1        0.14   180.0   170.9 1uk5m5qbzj1vt
Module: sqlplus@banshee (TNS V1-V3)
BEGIN dbms_workload_repository.create_snapshot; END;         0          0            1        0.02    20.0     1.4 4dy1xm4nxc0gf
insert into wrh$_system_event (snap_id, dbid, instance_number, event_id, to
tal_waits, total_timeouts, time_waited_micro, total_waits_fg, total_timeouts_
fg, time_waited_micro_fg) select :snap_id, :dbid, :instance_number, event_id
, total_waits, total_timeouts, time_waited_micro, total_waits_fg, total_ti         0          0            1        0.02    20.0     8.9 4tg8mr2bvy6gr
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map from smon_scn_time smontab, (select max(scn) scnmax,
 count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,0)) cnt
 from smon_scn_time) smontabv where smontab.scn = smontabv.scnmax         0          0            1        0.02    20.0     2.7 5ax5xu96u2ztd
insert into WRH$_EVENT_HISTOGRAM (snap_id, dbid, instance_number, event_i
d, wait_time_milli, wait_count) select :snap_id, :dbid, :instance_number,
 d.ksledhash, s.kslsesmaxdur, s.kslsesval from x$kslseshist s, x$ksled
d where s.kslsesenum = d.indx and s.kslsesval > 0 order by d.ksledhash,         0          0            1        0.02    20.0     1.7 bqnn4c3gjtmgu
insert into wrh$_bg_event_summary (snap_id, dbid, instance_number, event_id
, total_waits, total_timeouts, time_waited_micro) select /*+ ordered use_nl(
e) */ :snap_id, :dbid, :instance_number, e.event_id, sum(e.total_waits),
 sum(e.total_timeouts), sum(e.time_waited_micro) from v$session bgsids, v$s         0          0            1        0.00     0.0     1.3 00fx7adv5q5gm
SELECT HOUR, INTRADAY, EXTRADAY FROM BSLN_TIMEGROUPS         0          0            3        0.00     0.0     0.0 062savj8zgzut
UPDATE sys.wri$_adv_parameters SET datatype = :1,value = :2, flags = :3, descrip
tion = :4 WHERE task_id = :5 AND name = :6         0          0            1        0.00     0.0     3.4 0z1031991bd7w
insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, beg
in_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minv
al, average, standard_deviation, sum_squares) select :snap_id, :dbid, :ins
tance_number, begtime, endtime, intsize_csec, groupid, metricid, numin         0          0            1        0.00     0.0     4.2 1cq3qr774cu45
insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetyp
e_id, small_read_megabytes, small_write_megabytes, large_read_megabytes,
 large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_r
eqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_wr         0          0            4        0.00     0.0     0.0 1jqqpfkr0m3yk
SELECT COUNT(*) FROM SYS.WRI$_ADV_PARAMETERS A WHERE A.TASK_ID = :B2 AND A.NAME
= :B1