ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AWR报表解读-02

AWR报表解读-02

原创 Linux操作系统 作者:木呼 时间:2011-03-17 15:55:02 0 删除 编辑

五、首要的SQL语句

SQL ordered by Gets

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • Total Buffer Gets: 124,799,294
  • Captured SQL account for 30.2% of Total

Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
22,491,837 195 115,342.75 18.02 41.73 98338.50 dc9bdxa7xpv8s PL/SQL Developer declare t_owner varchar2(30...
7,133,380 268 26,617.09 5.72 34.94 47123.31 c1jrhw45gfvkj JDBC Thin Client SELECT NULL AS table_cat, ...
4,041,821 16 252,613.81 3.24 6.83 21081.17 44mwxbkuxcrsa PL/SQL Developer SELECT /*+rule*/ S.TABLE_OWNER...
3,732,650 2,306 1,618.67 2.99 154.53 3474181.21 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
2,586,532 199,022 13.00 2.07 511.23 9197480.15 6s6scbnr6f83t JDBC Thin Client select roles0_.USERID as USERI...
2,054,318 12,673 162.10 1.65 50.45 356134.77 6d64jpfzqc9rv   INSERT INTO MGMT_METRICS_RAW (...
1,877,833 2,383 788.01 1.50 39.01 305891.77 7gtztzv329wg0   select c.name, u.name from co...
1,786,656 8,814 202.71 1.43 19.19 1296212.94 az33m61ym46y4   SELECT NULL AS table_cat, ...
1,725,058 162,928 10.59 1.38 33.67 6468004.54 0h6b2sajwb74n   select privilege#, level from ...
1,171,434 0   0.94 6.56 10.44 6mcpb06rctk0x DBMS_SCHEDULER call dbms_space.auto_space_adv...

如果磁盘读操作不是很高,但是缓冲区读操作很高而执行次数较低,说明查询可能使用了糟糕的索引,或者以错误的顺序执行了连接。

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

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
9,197,480 511 199,022 46.21 72828.36 6s6scbnr6f83t JDBC Thin Client select roles0_.USERID as USERI...
6,468,005 34 162,928 39.70 51215.57 0h6b2sajwb74n   select privilege#, level from ...
3,474,181 155 2,306 1506.58 27509.59 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
2,847,440 15 50,626 56.24 22546.87 0ws7ahf1d78qa   select SYS_CONTEXT('USERENV', ...
1,944,786 13 53,891 36.09 15399.39 0k8522rmdzg4k   select privilege# from sysauth...
1,623,748 4 13,926 116.60 12857.32 du7nyv586fquh PL/SQL Developer declare runtime_info sys.dbm...
1,359,553 51 13,319 102.08 10765.34 g64qbsqnjv0yv JDBC Thin Client select bizproject0_.ID as ID69...
1,296,213 19 8,814 147.06 10263.80 az33m61ym46y4   SELECT NULL AS table_cat, ...
1,244,139 5 50,623 24.58 9851.46 459f3z9u4fb3u   select value$ from props$ wher...
1,104,041 13 19,736 55.94 8742.13 db78fxqxwxt7r   select /*+ rule */ bucket, en...
1,051,807 4 50,391 20.87 8328.52 5ur69atw3vfhj   select decode(failover_method,...
1,027,609 42 199,259 5.16 8136.91 crf03umxqfz8n JDBC Thin Client select branchs0_.USERID as USE...
807,017 32 27,515 29.33 6390.20 7ng34ruy5awxq   select i.obj#, i.ts#, i.file#,...
614,561 2 45,806 13.42 4866.28 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
539,461 3 2,236 241.26 4271.61 2p5skbyu7hzmg exp.exe SELECT SOURCE FROM SYS....
491,025 4 116,866 4.20 3888.08 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...
450,808 2 11,520 39.13 3569.63 803b7z0t84sq7   select job, nvl2(last_date, ...
443,960 14 197,043 2.25 3515.41 167x1z6377n6a JDBC Thin Client select ledgers0_.USERID as USE...

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 Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
511 9,197,480 199,022 0.00 72828.36 6s6scbnr6f83t JDBC Thin Client select roles0_.USERID as USERI...
155 3,474,181 2,306 0.07 27509.59 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
131 8,525 1,128 0.12 67.50 a5n6w6u98cf1v exp.exe BEGIN :RET := SYS.DBMS_...
129 155 1,058 0.12 1.23 41ssv70uw2mkr exp.exe SELECT 1 FROM role_role_privs ...
51 1,359,553 13,319 0.00 10765.34 g64qbsqnjv0yv JDBC Thin Client select bizproject0_.ID as ID69...
50 356,135 12,673 0.00 2819.98 6d64jpfzqc9rv   INSERT INTO MGMT_METRICS_RAW (...
42 98,339 195 0.21 778.67 dc9bdxa7xpv8s PL/SQL Developer declare t_owner varchar2(30...
42 1,027,609 199,259 0.00 8136.91 crf03umxqfz8n JDBC Thin Client select branchs0_.USERID as USE...
39 305,892 2,383 0.02 2422.14 7gtztzv329wg0   select c.name, u.name from co...
36 321,015 5,315 0.01 2541.89 abtp0uqvdb1d3   CALL MGMT_ADMIN_DATA.EVALUATE_...
35 47,123 268 0.13 373.14 c1jrhw45gfvkj JDBC Thin Client SELECT NULL AS table_cat, ...
34 6,468,005 162,928 0.00 51215.57 0h6b2sajwb74n   select privilege#, level from ...
32 807,017 27,515 0.00 6390.20 7ng34ruy5awxq   select i.obj#, i.ts#, i.file#,...
19 1,296,213 8,814 0.00 10263.80 az33m61ym46y4   SELECT NULL AS table_cat, ...
19 127,601 2,938 0.01 1010.38 5ddayam7mxm74 JDBC Thin Client select distinct sysmenupri0_.M...
17 12,287 27 0.63 97.29 bunssq950snhf   insert into wrh$_sga_target_ad...
15 2,847,440 50,626 0.00 22546.87 0ws7ahf1d78qa   select SYS_CONTEXT('USERENV', ...
14 443,960 197,043 0.00 3515.41 167x1z6377n6a JDBC Thin Client select ledgers0_.USERID as USE...

SQL ordered by Reads

  • Total Disk Reads: 345,050
  • Captured SQL account for 9.8% of Total

Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
12,783 2,306 5.54 3.70 154.53 3474181.21 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
10,064 10 1,006.40 2.92 0.88 69.13 dmnntnpc9mnnq   SELECT SEV.ROWID FROM MGMT_SEV...
10,048 9 1,116.44 2.91 0.86 68.62 crqc0d6r83p1r   BEGIN EM_SEVERITY.SEVERITY_PUR...
6,556 2 3,278.00 1.90 0.70 551.77 cm60fmwcnkfuj   select oshistorys0_.ID as ID0_...
3,899 19,736 0.20 1.13 12.98 1104041.42 db78fxqxwxt7r   select /*+ rule */ bucket, en...
1,743 18,151 0.10 0.51 5.04 253895.15 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
1,463 2,236 0.65 0.42 2.87 539460.67 2p5skbyu7hzmg exp.exe SELECT SOURCE FROM SYS....
1,272 5,315 0.24 0.37 36.25 321014.56 abtp0uqvdb1d3   CALL MGMT_ADMIN_DATA.EVALUATE_...
869 679 1.28 0.25 0.96 1858.11 0ybwd63u2any5   insert into sys.wri$_optstat_h...
685 3 228.33 0.20 2.34 9.61 8cz8whpyr6k4x DBMS_SCHEDULER call PACK_RISK_DYNAMICALLY.SP_...

SQL ordered by Executions

  • Total Executions: 4,063,357
  • Captured SQL account for 61.3% of Total

Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
199,259 610,728 3.06 0.00 5.16 crf03umxqfz8n JDBC Thin Client select branchs0_.USERID as USE...
199,022 5,086,739 25.56 0.00 46.21 6s6scbnr6f83t JDBC Thin Client select roles0_.USERID as USERI...
197,043 318,443 1.62 0.00 2.25 167x1z6377n6a JDBC Thin Client select ledgers0_.USERID as USE...
188,176 188,175 1.00 0.00 2.02 1r3tpapvur4aq JDBC Thin Client select bizstate0_.ID as ID77_0...
162,928 1,394,780 8.56 0.00 39.70 0h6b2sajwb74n   select privilege#, level from ...
146,635 146,627 1.00 0.00 2.34 fwuaq5spq8d4g JDBC Thin Client select sysdepartm0_.ID as ID10...
122,289 122,289 1.00 0.00 2.96 3az8w1f6563w6 JDBC Thin Client select sysuser0_.ID as ID33_0_...
116,866 125,148 1.07 0.00 4.20 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...
53,891 163,854 3.04 0.00 36.09 0k8522rmdzg4k   select privilege# from sysauth...
50,626 50,626 1.00 0.00 56.24 0ws7ahf1d78qa   select SYS_CONTEXT('USERENV', ...
50,623 50,623 1.00 0.00 24.58 459f3z9u4fb3u   select value$ from props$ wher...
50,391 50,391 1.00 0.00 20.87 5ur69atw3vfhj   select decode(failover_method,...
45,806 41,916 0.92 0.00 13.42 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...

SQL ordered by Parse Calls

  • Total Parse Calls: 1,426,390
  • Captured SQL account for 59.8% of Total

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
162,903 162,928 11.42 0h6b2sajwb74n   select privilege#, level from ...
53,867 53,891 3.78 0k8522rmdzg4k   select privilege# from sysauth...
50,625 50,626 3.55 0ws7ahf1d78qa   select SYS_CONTEXT('USERENV', ...
50,623 50,623 3.55 459f3z9u4fb3u   select value$ from props$ wher...
50,368 50,391 3.53 5ur69atw3vfhj   select decode(failover_method,...
18,529 18,542 1.30 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
18,527 18,542 1.30 c6awqs517jpj0   select /*+ index(idl_char$ i_i...
18,134 18,151 1.27 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
18,120 18,137 1.27 ga9j9xk5cy9s0   select /*+ index(idl_sb4$ i_id...
14,237 29,980 1.00 83taa7kaw59c1   select name, intcol#, segcol#,...

 

10g中,v$sql显示使用相同语句的多个用户的SQLV$SQL_PLAN_STATISTICS_ALL显示连接计划和统计数据,

V$SQL_PLAN_STATISTICS显示

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23754390/viewspace-689776/,如需转载,请注明出处,否则将追究法律责任。

上一篇: AWR报表解读-01
下一篇: AWR报表解读-03
请登录后发表评论 登录
全部评论

注册时间:2010-04-19

  • 博文量
    93
  • 访问量
    152561