Oracle - AWR - 教你如何看 - 图文(5)

同上。

BUSY_TIME / NUM_CPUS IDLE_TIME / NUM_CPUS IOWAIT_TIME / NUM_CPUS SYS_TIME / NUM_CPUS

USER_TIME / NUM_CPUSar o

time equiv of %usr+%sys in sar output time equiv of %idle in sar time equiv of %wio in sar time equiv of %sys in sar

USER_TIME： time equiv of %usr in sar LOAD： 未知

OS_CPU_WAIT_TIME： supposedly time waiting on run queues

RSRC_MGR_CPU_WAIT_TIME： time waited coz of resource manager PHYSICAL_MEMORY_BYTES： total memory in use supposedly NUM_CPUS： number of CPUs reported by OS NUM_CPU_CORES： number of CPU sockets on motherboard 总的elapsed time也可以用以公式计算： BUSY_TIME + IDLE_TIME + IOWAIT TIME

或：SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME （因为BUSY_TIME = SYS_TIME+USER_TIME）

Back to Wait Events Statistics Back to Top

Service Statistics

?

ordered by DB Time

DB Time (s) DB CPU (s) Physical Reads Logical Reads 608.10 54.70 0.00 0.00 496.60 17.80 0.00 0.00 315,849 6,539 0 282 16,550,972 58,929 0 38,990 Service Name ICCI SYS$USERS ICCIXDB SYS$BACKGROUND Back to Wait Events Statistics Back to Top

Service Wait Class Stats

? ? ?

Wait Class info for services in the Service Statistics section.

Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network

Time Waited (Wt Time) in centisecond (100th of a second)

User I/O Total Wts 59826 6567 443 Service Name ICCI SYS$USERS SYS$BACKGROUND User I/O Wt Time 8640 3238 115 Concurcy Total Wts 4621 231 330 Concurcy Wt Time 338 11 168 Admin Total Wts 0 0 0 Admin Wt Time 0 0 0 Network Total Wts 1564059 7323 0 Network Wt Time 6552 3 0 Back to Wait Events Statistics Back to Top

SQL Statistics

? ? ? ? SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets SQL ordered by Reads ? ? ? ? ? ? SQL ordered by Executions SQL ordered by Parse Calls SQL ordered by Sharable Memory SQL ordered by Version Count SQL ordered by Cluster Wait Time Complete List of SQL Text 本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IOs最多的SQL语句。

在STATSPACK报告中，没有完整的SQL语句，可使用报告中的Hash Value通过下面语句从数据库中查到： select sql_text from stats$sqltext

where hash_value = &hash_value order by piece;

Back to Top

SQL ordered by Elapsed Time

? ?

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

% Elap Totaper l DB ExeTimc (s) e Elapsed Time (s) 93 76 58 51 38 35 CPU ExecutionTims e (s) 57 75 42 42 36 3 SQL Id SQL Module SQL Text 1 93.50 14.10 d8z0u8hgj8xdy cuidmain@HPGICCI1 (TNS V1-V3) 172,329 0.00 11.52 4vja2k2gdtyup load_fnsact@HPGICCI1 (TNS V1-V3) 8.75 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 7.68 ackxqhnktxnbc cusmmain@HPGICCI1 (TNS V1-V3) 5.67 7gtztzv329wg0 5.28 6z06gcfw39pkd SQL*Plus insert into CUID select CUID_... insert into ICCICCS values (:... insert into CUMI select CUSV_... insert into CUSM select CUSM_... select c.name, u.name from co... SELECT F.TABLESPACE_NAME, TO_... insert into iccifnsact values... DECLARE job BINARY_INTEGER := ... update ICCIFNSACT set BORM_AD... insert into OLDNEWACT values ... insert into ICCICCS values (:... select CUID_CUST_NO , CUID_ID_... 1 58.04 1 50.93 166,069 0.00 1 35.00 23 15 14 13 13 10 23 11 14 13 13 4 172,329 5 172,983 172,337 166,051 1 0.00 2.98 0.00 0.00 0.00 9.70 3.46 1dm3bq36vu3gload_fnsact@HPGICCI1 8 (TNS V1-V3) 2.25 djs2w2f17nw2z 2.16 7wwv1ybs9zguload_fnsact@HPGICCI1 z (TNS V1-V3) 2.00 gmn2w09rdxn1load_oldnewact@HPGIC4 CI1 (TNS V1-V3) 1.89 chjmy0dxf9mbj icci_migact@HPGICCI1 (TNS V1-V3) 1.46 0yv9t4qb1zb2b cuidmain@HPGICCI1 (TNS V1-V3) 10 8 5 1.91 1.44 1crajpb7j5tyz INSERT INTO STATS$SGA_TARGET_A... update ICCICCS set CCSMAXOVER... select * from ICCIPRODCODE wh... 8 8 8 8 172,329 172,983 0.00 0.00 1.25 38apjgr0p55ns load_fnsact@HPGICCI1 (TNS V1-V3) 1.16 5c4qu2zmj3guload_fnsact@HPGICCI1 x (TNS V1-V3) Back to SQL Statistics

Back to Top

SQL ordered by CPU Time

? ?

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

% CPU Totaper l DB ExeTimc (s) e CPU Time (s) 75 57 42 42 36 23 14 13 13 11 8 8 Elapsed Time (s) 76 93 51 58 38 23 14 13 13 15 8 10 Executions 172,329 SQL Id SQL Module SQL Text 0.00 11.52 4vja2k2gdtyup load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:... insert into CUID select CUID_... insert into CUSM select CUSM_... insert into CUMI select CUSV_... select c.name, u.name from co... insert into iccifnsact values... update ICCIFNSACT set BORM_AD... insert into OLDNEWACT values ... insert into ICCICCS values (:... DECLARE job BINARY_INTEGER := ... update ICCICCS set CCSMAXOVER... INSERT INTO STATS$SGA_TARGET_A... select * from ICCIPRODCODE wh... select CUID_CUST_NO , CUID_ID_... SELECT F.TABLESPACE_NAME, TO_... 1 57.31 14.10 d8z0u8hgj8xdy cuidmain@HPGICCI1 (TNS V1-V3) 1 42.43 1 42.01 166,069 172,329 172,983 172,337 166,051 5 172,329 5 0.00 0.00 0.00 0.00 0.00 2.23 0.00 1.60 7.68 ackxqhnktxnbc cusmmain@HPGICCI1 (TNS V1-V3) 8.75 569r5k05drsj7 cumimain@HPGICCI1 (TNS V1-V3) 5.67 7gtztzv329wg0 3.46 1dm3bq36vu3gload_fnsact@HPGICCI1 8 (TNS V1-V3) 2.16 7wwv1ybs9zguload_fnsact@HPGICCI1 z (TNS V1-V3) 2.00 gmn2w09rdxn1load_oldnewact@HPGIC4 CI1 (TNS V1-V3) 1.89 chjmy0dxf9mbj icci_migact@HPGICCI1 (TNS V1-V3) 2.25 djs2w2f17nw2z 1.25 38apjgr0p55ns load_fnsact@HPGI …… 此处隐藏：3778字，全部文档内容请下载后查看。喜欢就下载吧 ……